MySQL 慢查詢日誌完整配置與分析實戰：從開啟到毫秒級捕獲

慢查詢日誌核心配置

修改配置文件（Windows: my.ini，Linux: my.cnf），[mysqld] 區塊添加：

slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log  # 可選，默認 datadir/hostname-slow.log

修改後必須重啟 MySQL 生效（動態修改 SET GLOBAL slow_query_log=1 重啟失效）。

驗證測試

mysql> SELECT SLEEP(2);
+----------+
| SLEEP(2) |
+----------+
|        0 |
+----------+
1 row in set (2.12 sec)

日誌記錄示例：

# Time: 240115 14:30:45
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 2.104120  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1705312245;
SELECT SLEEP(2);

關鍵字段解讀

毫秒級慢查詢捕獲 (MySQL 5.1.21+)

-- 動態設置 100ms 閾值
SET GLOBAL long_query_time = 0.1;

-- 驗證
mysql> SELECT SLEEP(0.5);
# Query_time: 0.500028  ...

注意：long_query_time 支持微秒精度，生產環境建議設置 0.05 (50ms) 平衡日誌量與覆蓋面。

日誌分析工具鏈

1. mysqldumpslow：官方聚合分析工具

   mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
   # -s t 按時間排序，-t 10 顯示前10

2. pt-query-digest：Percona Toolkit 核心工具（推薦）

   pt-query-digest /var/log/mysql/slow.log --limit 20 --output json > digest.json

   輸出含：查詢指紋、執行次數、總/平均/最大時間、掃描行數、索引缺失建議等。

3. Grafana + Prometheus + mysqld_exporter：可視化監控大盤

生產環境最佳實踐

• 輸出格式：log_output = FILE（避免 TABLE 類型性能開銷）
• 不記錄管理語句：log_slow_admin_statements = 0
• 不記錄未使用索引：log_queries_not_using_indexes = 0（否則日誌爆炸）
• 輪轉策略：logrotate 每日切割，保留 30 天
• 定期分析：每日跑 pt-query-digest 產出 Top 20 優化清單

優化閉環流程

1. 慢查詢日誌捕獲高耗時 SQL
2. EXPLAIN 分析執行計劃，識別 type=ALL、Using filesort、Rows_examined >> Rows_sent
3. 補充索引、改寫 SQL、調整結構
4. 驗證效果，納入回歸測試