使用者工具

網站工具


tech:mysqltips

MySQL 相關技巧彙整

1. 資料表效能優化

  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;

    結果顯示類似以下訊息

    +----------------+----------------+--------------+
    | 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)
    

  2. 針對呈現出來的 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)
    

  3. 查看 /var/lib/mysql/abc 內的資料檔在執行後會變小

2. 設定與查詢週期定時執行程序

  1. 查看現有排程
    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個月以上的紀錄                                   | [email protected] | 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資料                                     | [email protected] | 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    |
    :
    

  2. 顯示詳細排程內容 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資料          | [email protected] | 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)
    

  3. 建立新的排程 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個月以上的紀錄                                   | [email protected] | 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型資料表                                             | [email protected] | SYSTEM    | RECURRING | NULL       | 7              | DAY            | 2020-08-23 12:07:00 | NULL                | ENABLED |          1 | utf8                 | utf8_general_ci      | utf8_general_ci    |
    :
    

tech/mysqltips.txt · 上一次變更: 2020/08/20 12:41 由 jonathan_tsai