MySQL/MariaDB 相關技巧彙整

找出需要優化的資料表 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 內的資料檔在執行後會變小

查看現有排程

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    |
: