MySQL 慢查詢日誌深度解析：配置、參數與分析工具全攻略

MySQL 慢查詢日誌深度解析：配置、參數與分析工具全攻略

什麼是慢查詢日誌？

MySQL 慢查詢日誌是用來記錄執行時間超過閾值（long_query_time）的 SQL 語句。預設閾值為 10 秒，意即執行超過 10 秒的查詢才會被記錄。預設情況下慢查詢日誌是關閉的，需手動開啟。雖然它是效能調校的利器，但開啟會帶來一定的效能開銷，非調優需求下不建議常駐開啟。

根據官方文件，慢查詢日誌記錄符合以下條件的 SQL：

• 執行時間超過 long_query_time 秒
• 掃描行數至少達到 min_examined_row_limit

核心參數詳解

動態開啟與永久生效配置

1. 線上動態開啟（重啟後失效）

mysql> SET GLOBAL slow_query_log = 1;
mysql> SET GLOBAL long_query_time = 4;  -- 設為 4 秒

注意： SET GLOBAL 修改後，需重新連線或開啟新會話才能生效；當前會話可用 SHOW GLOBAL VARIABLES LIKE 'long_query_time' 查看。

2. 修改配置檔永久生效

編輯 my.cnf（或 Windows 的 my.ini），在 [mysqld] 區段加入：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
log_output = FILE

修改後重啟 MySQL 服務。

日誌輸出目標：檔案 vs 資料表

log_output = FILE 為預設且推薦方式，效能開銷較小。若設為 TABLE，日誌寫入 mysql.slow_log 系統表，方便 SQL 查詢分析，但消耗更多資源。生產環境建議優先使用 FILE。

慢查詢日誌內容範例

執行 SELECT SLEEP(3); 後，日誌會記錄類似以下資訊：

# Time: 160616 17:24:35
# User@Host: root[root] @ localhost []  Id: 5
# Query_time: 3.002615  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1466069075;
select sleep(3);

若使用 log_output = TABLE，可直接查詢 mysql.slow_log 表獲取結構化資料。

進階過濾參數

• log_queries_not_using_indexes = ON： 記錄未用索引的查詢（含全索引掃描），對發現潛在效能問題極有幫助。
• log_slow_admin_statements = ON： 記錄管理語句（如 ANALYZE TABLE、ALTER TABLE）。
• --log-short-format： 啟動參數，減少日誌輸出資訊量。

查看慢查詢統計

mysql> SHOW GLOBAL STATUS LIKE 'Slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 2104  |
+---------------+-------+

日誌分析神器：mysqldumpslow

MySQL 內建 mysqldumpslow 工具可彙總分析慢查詢日誌，避免人工逐行閱讀。

常用參數

實戰範例

# 返回記錄集最多的前 10 條 SQL
mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log

# 訪問次數最多的前 10 條 SQL
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log

# 按查詢時間排序，前 10 條且包含 "left join"
mysqldumpslow -s t -t 10 -g "left join" /var/log/mysql/mysql-slow.log

# 建議搭配 more 避免刷屏
mysqldumpslow -s r -t 20 /var/log/mysql/mysql-slow.log | more

參考資源

• MySQL 官方文件：慢查詢日誌
• MySQL 官方文件：long_query_time 系統變數

善用慢查詢日誌與 mysqldumpslow，能快速定位效能瓶頸，是 MySQL 調優不可或缺的技能組合。