MySQL日志（一）：slow query log

MySQL的慢查詢日志可以用來找出執行時間過長的查詢語句，并進行針對性的優化。

一、slow log相關參數 以下參數都是動态參數，可以在執行個體運作時修改。

slow_query_log=1       #是否啟用慢查詢日志，1為啟用，0為禁用
slow_query_log_file=slow.log       #指定慢查詢日志檔案的路徑和名字，可使用絕對路徑指定；預設值是'主機名_slow.log'，位于datadir目錄
long_query_time=2     #SQL語句運作時間門檻值，執行時間大于參數值的語句才會被記錄下來
min_examined_row_limit=100     #SQL語句檢測的記錄數少于設定值的語句不會被記錄到慢查詢日志，即使這個語句執行時間超過了long_query_time的門檻值
log_queries_not_using_indexes=1   #将沒有使用索引的語句記錄到慢查詢日志
log_throttle_queries_not_using_indexes=10     #設定每分鐘記錄到日志的未使用索引的語句數目，超過這個數目後隻記錄語句數量和花費的總時間
log-slow-admin-statements=1    #記錄執行緩慢的管理SQL，如alter table,analyze table, check table, create index, drop index, optimize table, repair table等。
log_slow_slave_statements=0    #記錄從庫上執行的慢查詢語句
log_timestamps=system     #5.7版本新增時間戳所屬時區參數，預設記錄UTC時區的時間戳到慢查詢日志，應修改為記錄系統時區
log_output=FILE,TABLE     #指定慢查詢日志的輸出方式，從5.5版本開始可以記錄到日志檔案(FILE，慢查詢日志)和資料庫表(TABLE，mysql.slow_log)中
           

二、使用MySQL slow log 1. 線上修改資料庫相關參數：

mysql> set global slow_query_log=1;
mysql> set global slow_query_log_file='my57-slow.log';
mysql> set global long_query_time=2;
mysql> set global min_examined_row_limit=50;
mysql> set global log_queries_not_using_indexes=1;
mysql> set global log_throttle_queries_not_using_indexes=10;
mysql> set global log_slow_admin_statements=1;
           

2. 檢視slow log内容：

/usr/local/mysql/bin/mysqld, Version: 5.7.17 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql1.sock
Time                 Id Command    Argument
           

3. 執行一個很慢的SQL後，slow log記錄的内容：

# Time: 2017-03-13T09:48:23.823837Z
# [email protected]: root[root] @ localhost []  Id:     7
# Query_time: 2.898604  Lock_time: 0.000295 Rows_sent: 2700216  Rows_examined: 300033
use emp;
SET timestamp=1489398503;
select * from employees,departments;

# Time: 2017-03-13T09:49:13.610594Z
# [email protected]: root[root] @ localhost []  Id:     7
# Query_time: 0.529270  Lock_time: 0.000228 Rows_sent: 300024  Rows_examined: 300024
SET timestamp=1489398553;
select * from employees;
           

可以看到上面的例子中記錄了兩條SQL語句，第一句符合了執行時間大于2秒的門檻值，第二句符合了沒有使用索引的限制。與在第一步中設定的參數值相符。

三、根據MySQL slow log記錄進行優化 從慢查詢日志中找到執行時間過長或沒有使用索引的語句後，我們可以通過分析執行計劃對語句進行調優：

mysql> explain extended select * from employees;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299556 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 2 warnings (0.02 sec)
           

四、使用slow log的Tips 1. 慢查詢日志可能随着系統運作時間而增長的很大，是以需要定期做日志輪轉。 線上輪轉慢查詢日志的方法為： a. 修改slow log檔案名 b. 執行flush slow logs;指令打開新的日志檔案

2. 慢查詢日志是文本檔案，可以使用mysqldumpslow指令對日志檔案進行彙總處理，以減輕分析日志的工作量。