====== MySQL/MariaDB 相關技巧彙整 ====== ===== 1. 資料表效能優化 ===== * 參考 - https://www.thegeekstuff.com/2016/04/mysql-optimize-table/ - 找出需要優化的資料表 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; 結果顯示類似以下訊息 +----------------+----------------+--------------+ | 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) - 針對呈現出來的 Table 進行優化 OPTIMIZE TABLE dwg_files, pdm_to_erp_log, user_use_log; 結果顯示如下訊息 +--------------------------+----------+----------+-------------------------------------------------------------------+ | 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) - 查看 /var/lib/mysql/abc 內的資料檔在執行後會變小 ===== 2. 設定與查詢週期定時執行程序 ===== * 參考 - https://noter.tw/2306/mysql%E6%8E%92%E7%A8%8Bevent-scheduler/ - 查看現有排程 show events; Exp. +-----------+-------------------------------------------------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+ | 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 | : - 顯示詳細排程內容 Exp. 刪除1個月以上的curl資料 SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='刪除1個月以上的curl資料'; 顯示結果: +---------------+--------------+----------------------------------+----------------+-----------+------------+-------------------------------------------------------------------------------------+------------+------------+----------------+----------------+-------------------------------------------------------------------------------------------+---------------------+---------------------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+ | 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) - 建立新的排程 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; 顯示出來如下: +-----------+-------------------------------------------------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+ | 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 | : ===== 3. 出現異常訊息 - [ERROR] mysqld: Table './stats/stats_media' is marked as crashed and should be repaired ===== * 參考 - https://stackoverflow.com/questions/58386240/mysqld-exe-table-mysql-db-is-marked-as-crashed-and-should-be-repaired * 對 database : stats 進行修復 mysqlcheck -r --databases stats --use-frm 會在 log 內看到如下的訊息 : : 220803 15:11:48 [ERROR] mysqld: Table './stats/stats_media' is marked as crashed and should be repaired 220803 15:13:10 [Note] Found 6676 of 0 rows when repairing './stats/stats_access' 220803 15:13:10 [Note] Found 813 of 0 rows when repairing './stats/stats_edits' : 220803 15:13:11 [Note] Found 517 of 0 rows when repairing './stats/stats_searchwords' 220803 15:13:11 [Note] Found 1013 of 0 rows when repairing './stats/stats_session' {{tag>mysql tips}}