MySQL 慢查詢日誌啟用與分析實戰總結

慢查詢日誌核心配置

在 my.cnf [mysqld] 區段設定：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1          # 秒，微秒精度可設 0.1
log_queries_not_using_indexes = 1   # 記錄無索引查詢（慎用，量大）
log_slow_admin_statements = 1       # 記錄管理指令
log_slow_slave_statements = 1       # 記錄從庫複制慢 SQL
min_examined_row_limit = 100        # 忽略掃描行數小於此值的查詢

動態生效（無需重啟）：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;

日誌格式解讀

預設格式每條記錄含：

• # Time：執行時間戳。
• # User@Host：客戶端用戶與 IP。
• # Query_time：總耗時。
• # Lock_time：鎖等待時間。
• # Rows_sent / Rows_examined：返回/掃描行數（核心指標）。
• # Rows_affected：影響行數（DML）。
• SET timestamp=...; SQL 文本。

主流分析工具實戰

1. mysqldumpslow（官方內建）

# Top 10 耗時查詢
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Top 10 掃描行數
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

# 按照查詢模式分組統計
mysqldumpslow -s c -t 20 /var/log/mysql/slow.log

參數：-s 排序 t/r/c/al/ar/at（時間/行數/次數/平均鎖時間/平均行數/平均時間），-t Top N，-g 正則過濾。

2. pt-query-digest（Percona Toolkit，推薦生產）

pt-query-digest /var/log/mysql/slow.log --limit 20 --filter '$event->{Query_time} > 1' --output json > report.json

# 直接產生報表
pt-query-digest /var/log/mysql/slow.log --report-format=profile,query_report --limit 100% > digest.txt

報表關鍵欄位：Query_time、Rows_examined、QPS、Impact（總影響 = QPS × 平均時間）、Fingerprint（抽象化 SQL 模板）。

3. 視覺化平台

• PMM (Percona Monitoring and Management)：Grafana 儀表板，QAN (Query Analytics) 實時聚合。
• Grafana + Loki/Promtail：集中式日誌查詢與告警。
• MySQL Sys Schema：sys.statement_analysis 視圖直接查詢統計資訊（需 performance_schema 啟用）。

慢查詢優化閉環流程

1. 收集：持續開啟慢查詢日誌，設定合理 long_query_time（建議 0.5-1s）。
2. 聚合：每日/每週跑 pt-query-digest，產生 Top 20 報告。
3. 定位：對照 EXPLAIN ANALYZE、OPTIMIZER_TRACE 定位全表掃描、索引缺失、臨時表、Filesort。
4. 優化：加索引、改 SQL、調參數、拆分表。
5. 驗證：預發環境壓測，對比 Query_time、Rows_examined、Handler_read_* 計數器。
6. 固化：變更納入版本控制，監控儀表板新增該 SQL 指標告警。

進階技巧

• log_slow_extra=1（8.0.14+）輸出更多欄位：Rows_affected、Thread_id、Bytes_sent 等。
• 慢查詢寫入資料表：log_output = 'TABLE'，查詢 mysql.slow_log 便於 SQL 分析。
• 結合 performance_schema.events_statements_summary_by_digest 進行無侵入式全量 SQL 統計。

建立「日誌→分析→優化→驗證」自動化管道，讓慢查詢治理成為常態化能力，而非救火動作。