慢查詢日誌是 MySQL 性能調優的「聽診器」,記錄執行時間超過閾值的 SQL,幫助定位全表掃描、索引缺失、鎖等待等問題。本文詳細說明配置啟用、關鍵參數、動態控制、日誌分析工具鏈與最佳實踐。
SHOW VARIABLES LIKE '%slow%';
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/hostname-slow.log |
| long_query_time | 10.000000 |
| log_queries_not_using_indexes | OFF |
| log_slow_admin_statements | OFF |
+---------------------+-------------------------------+
/etc/my.cnf 或 my.ini [mysqld] 區塊[mysqld]
# 慢查詢閾值(秒,支持微秒,如 0.5)
long_query_time = 2
# 慢查詢日誌文件路徑(目錄需 mysql 用戶寫權限)
slow_query_log_file = /var/lib/mysql/slow.log
# 開啟慢查詢日誌
slow_query_log = 1
# 記錄未使用索引的查詢(可選,會產生大量日誌)
log_queries_not_using_indexes = 1
# 記錄管理命令(ALTER、ANALYZE 等,可選)
log_slow_admin_statements = 0
保存後重啟:systemctl restart mysqld。
-- 開啟
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 即時調整閾值
SET GLOBAL log_queries_not_using_indexes = ON;
-- 關閉
SET GLOBAL slow_query_log = OFF;
動態修改適合臨時排查,生產環境持久化請改配置文件。
| 參數 | 建議值 | 說明 |
|---|---|---|
long_query_time | 0.5 - 2 | 太大漏掉慢 SQL,太小產生海量日誌。建議從 2 秒起步,逐步降至 0.5 秒。 |
slow_query_log_file | 獨立磁盤/分區 | 避免與數據目錄爭搶 I/O,便於日誌輪轉管理。 |
log_queries_not_using_indexes | OFF (生產) | 全表掃描但執行快的查詢也會記錄,噪音大。建議定期臨時開啟排查。 |
log_slow_admin_statements | OFF | 避免 Binlog Dump、ALTER TABLE 等管理命令污染日誌。 |
min_examined_row_limit | 100 | 忽略掃描行數小於此值的查詢(5.6+),過濾微小表全掃描噪音。 |
# 統計前 10 條按平均耗時排序
mysqldumpslow -s at -t 10 /var/lib/mysql/slow.log
# 統計按總耗時排序
mysqldumpslow -s t -t 20 /var/lib/mysql/slow.log
# 含數值佔位符抽象化
mysqldumpslow -a -s at -t 10 /var/lib/mysql/slow.log
pt-query-digest /var/lib/mysql/slow.log --limit 20 --output json > digest.json
# 輸出:按總響應時間排序、每類 SQL 執行次數、平均/最大耗時、掃描行數、索引使用率、表結構建議
# logrotate 配置 /etc/logrotate.d/mysql-slow
/var/lib/mysql/slow.log {
daily
rotate 30
compress
delaycompress
missingok
notifempty
create 640 mysql mysql
sharedscripts
postrotate
/usr/bin/mysqladmin flush-logs > /dev/null 2>&1
endscript
}
logrotate 防止佔滿根分區。pt-query-digest 生成報告,推送至運維群/郵件。EXPLAIN → 索引優化/改寫 SQL/調整架構。performance_schema、sys schema 進行多維度性能診斷。慢查詢日誌是 MySQL 性能優化的起點。規範配置參數、建立自動化分析流程、形成「發現→分析→優化→驗證→固化」閉環,才能持續保障數據庫高性能穩定運行。