差異處

這裏顯示兩個版本的差異處。

--- tech:mysqltips [2020/08/20 10:22] – 建立 jonathan_tsai
+++ tech:mysqltips [2022/08/03 15:24] (目前版本) – jonathan
@@ 行 1: / 行 1: @@
+====== MySQL/MariaDB 相關技巧彙整 ======
+===== 1. 資料表效能優化 =====
+  * 參考 - https://www.thegeekstuff.com/2016/04/mysql-optimize-table/
+  - 找出需要優化的資料表 Exp. 資料庫 abc <code sql>
+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;
+</code> 結果顯示類似以下訊息<cli>
++----------------+----------------+--------------+
+| table_name     | data_length_mb | data_free_mb |
++----------------+----------------+--------------+
+| dwg_files      |            312 |          527 |
+| pdm_to_erp_log |             24 |         1277 |
+| user_use_log   |             66 |          879 |
++----------------+----------------+--------------+
+rows in set (0.006 sec)
+</cli>
+  - 針對呈現出來的 Table 進行優化 <code sql>
+OPTIMIZE TABLE dwg_files, pdm_to_erp_log, user_use_log;
+</code> 結果顯示如下訊息<cli>
++--------------------------+----------+----------+-------------------------------------------------------------------+
+| 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                                                                |
++--------------------------+----------+----------+-------------------------------------------------------------------+
+rows in set (31.340 sec)
+</cli>
+  - 查看 /var/lib/mysql/abc 內的資料檔在執行後會變小
+===== 2. 設定與查詢週期定時執行程序 =====
+  * 參考 - https://noter.tw/2306/mysql%E6%8E%92%E7%A8%8Bevent-scheduler/
+  - 查看現有排程 <code sql>
+show events;
+</code> Exp. <cli>
++-----------+-------------------------------------------------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
+| 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    |
+:
+</cli>
+  - 顯示詳細排程內容 Exp. 刪除1個月以上的curl資料 <code sql>
+SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='刪除1個月以上的curl資料';
+</code> 顯示結果: <cli>
++---------------+--------------+----------------------------------+----------------+-----------+------------+-------------------------------------------------------------------------------------+------------+------------+----------------+----------------+-------------------------------------------------------------------------------------------+---------------------+---------------------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+
+| 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    |
++---------------+--------------+----------------------------------+----------------+-----------+------------+-------------------------------------------------------------------------------------+------------+------------+----------------+----------------+-------------------------------------------------------------------------------------------+---------------------+---------------------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+
+row in set (0.001 sec)
+</cli>
+  - 建立新的排程 Exp. 針對特定 Table 每周日中午 12:07 進行優化 <code sql>
+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 ;
+</code> 如果沒有錯誤訊息, 就可以透過 <code sql>
+SHOW events;
+</code> 顯示出來如下:<cli>
++-----------+-------------------------------------------------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
+| 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    |
+:
+</cli>
+===== 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 進行修復 <cli>
+mysqlcheck -r --databases stats --use-frm
+</cli> 會在 log 內看到如下的訊息<cli>
+:
+:
+15:11:48 [ERROR] mysqld: Table './stats/stats_media' is marked as crashed and should be repaired
+15:13:10 [Note] Found 6676 of 0 rows when repairing './stats/stats_access'
+15:13:10 [Note] Found 813 of 0 rows when repairing './stats/stats_edits'
+:
+15:13:11 [Note] Found 517 of 0 rows when repairing './stats/stats_searchwords'
+15:13:11 [Note] Found 1013 of 0 rows when repairing './stats/stats_session'
+</cli>
+{{tag>mysql tips}}