這是本文件的舊版！
MySQL 相關技巧彙整
1. 資料表效能優化
- 找出需要優化的資料表 Exp. 資料庫 abc
USE abc; SELECT TABLE_NAME, round(data_length/1024/1024) AS data_length_mb, round(data_free/1024/1024) AS data_free_mb FROM information_schema.tables WHERE round(data_free/1024/1024) > 500 ORDER BY data_free_mb;
結果顯示類似以下訊息<xtermrtf>
+—————-+—————-+————–+
| table_name | data_length_mb | data_free_mb |
+—————-+—————-+————–+
| dwg_files | 312 | 527 |
| pdm_to_erp_log | 24 | 1277 |
| user_use_log | 66 | 879 |
+—————-+—————-+————–+ 3 rows in set (0.006 sec) </xtermrtf>
- 針對呈現出來的 Table 進行優化
OPTIMIZE TABLE dwg_files, pdm_to_erp_log, user_use_log;
結果顯示如下訊息<xtermrtf>
+————————–+———-+———-+——————————————————————-+
| Table | Op | Msg_type | Msg_text |
+————————–+———-+———-+——————————————————————-+
| abc.dwg_files | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| abc.dwg_files | optimize | status | OK |
| abc.pdm_to_erp_log | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| abc.pdm_to_erp_log | optimize | status | OK |
| abc.user_use_log | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| abc.user_use_log | optimize | status | OK |
+————————–+———-+———-+——————————————————————-+ 6 rows in set (31.340 sec) </xtermrtf>
- 查看 /var/lib/mysql/abc 內的資料檔在執行後會變小
2. 設定與查詢週期定時執行程序
- 查看現有排程
SHOW events;Exp. <xtermrtf>
+———–+————————————————————-+—————-+———–+———–+————+—————-+—————-+———————+———————+———+————+———————-+———————-+——————–+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+———–+————————————————————-+—————-+———–+———–+————+—————-+—————-+———————+———————+———+————+———————-+———————-+——————–+
| abc | SE同步刪除1個月以上的紀錄 | root@localhost | SYSTEM | RECURRING | NULL | 1 | DAY | 2019-09-15 00:00:00 | 2030-09-30 00:00:00 | ENABLED | 1 | utf8mb4 | utf8mb4_unicode_ci | utf8_general_ci |
| abc | 刪除1個月以上的curl資料 | root@localhost | SYSTEM | RECURRING | NULL | 1 | DAY | 2019-10-08 00:00:00 | 2030-10-31 00:00:00 | ENABLED | 1 | utf8mb4 | utf8mb4_unicode_ci | utf8_general_ci |
: </xtermrtf>
- 顯示詳細排程內容 Exp. 刪除1個月以上的curl資料
SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='刪除1個月以上的curl資料';
顯示結果: <xtermrtf>
+—————+————–+———————————-+—————-+———–+————+————————————————————————————-+————+————+—————-+—————-+——————————————————————————————-+———————+———————+———+—————+———————+———————+———————+—————+————+———————-+———————-+——————–+
| EVENT_CATALOG | EVENT_SCHEMA | EVENT_NAME | DEFINER | TIME_ZONE | EVENT_BODY | EVENT_DEFINITION | EVENT_TYPE | EXECUTE_AT | INTERVAL_VALUE | INTERVAL_FIELD | SQL_MODE | STARTS | ENDS | STATUS | ON_COMPLETION | CREATED | LAST_ALTERED | LAST_EXECUTED | EVENT_COMMENT | ORIGINATOR | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
+—————+————–+———————————-+—————-+———–+————+————————————————————————————-+————+————+—————-+—————-+——————————————————————————————-+———————+———————+———+—————+———————+———————+———————+—————+————+———————-+———————-+——————–+
| def | abc | 刪除1個月以上的curl資料 | root@localhost | SYSTEM | SQL | delete FROM abc_curl_log WHERE DATE_SUB(CURDATE(), INTERVAL 15 DAY) > DATE(dd) | RECURRING | NULL | 1 | DAY | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | 2019-10-08 00:00:00 | 2030-10-31 00:00:00 | ENABLED | PRESERVE | 2020-07-07 09:12:28 | 2020-07-07 09:12:28 | 2020-08-20 00:00:00 | 1 | utf8mb4 | utf8mb4_unicode_ci | utf8_general_ci |
+—————+————–+———————————-+—————-+———–+————+————————————————————————————-+————+————+—————-+—————-+——————————————————————————————-+———————+———————+———+—————+———————+———————+———————+—————+————+———————-+———————-+——————–+ 1 row in set (0.001 sec) </xtermrtf>
- 建立新的排程 Exp. 針對特定 Table 每周日中午 12:07 進行優化
CREATE EVENT 優化LOG型資料表 ON SCHEDULE EVERY 7 DAY STARTS '2020-08-23 12:07' DO OPTIMIZE TABLE erptools_to_sunlikeerp_log, erptools_curl_log, se_synsetting_log ;
如果沒有錯誤訊息, 就可以透過
SHOW events;顯示出來如下:<xtermrtf>
+———–+————————————————————-+—————-+———–+———–+————+—————-+—————-+———————+———————+———+————+———————-+———————-+——————–+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+———–+————————————————————-+—————-+———–+———–+————+—————-+—————-+———————+———————+———+————+———————-+———————-+——————–+
| abc | SE同步刪除1個月以上的紀錄 | root@localhost | SYSTEM | RECURRING | NULL | 1 | DAY | 2019-09-15 00:00:00 | 2030-09-30 00:00:00 | ENABLED | 1 | utf8mb4 | utf8mb4_unicode_ci | utf8_general_ci |
| abc | 優化LOG型資料表 | root@localhost | SYSTEM | RECURRING | NULL | 7 | DAY | 2020-08-23 12:07:00 | NULL | ENABLED | 1 | utf8 | utf8_general_ci | utf8_general_ci |
: </xtermrtf>