動態開啟（無需重啟）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 3; -- 秒，執行時間超過 3 秒記錄
-- 可選：記錄未使用索引的查詢
SET GLOBAL log_queries_not_using_indexes = ON;
永久配置（my.cnf）
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = ON
# 可選：微秒精度
log_slow_admin_statements = ON
log_slow_slave_statements = ON
查看日誌檔案位置
SHOW VARIABLES LIKE 'slow_query_log_file';
-- 例：/var/lib/mysql/host-slow.log
分析工具
1. mysqldumpslow（官方內建）
# 前 10 慢查詢，按總耗時排序
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# 按查詢次數排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
# 含抽象化參數（去除具體值）
mysqldumpslow -a -s t -t 20 /var/log/mysql/slow.log
2. pt-query-digest（Percona Toolkit，推薦）
pt-query-digest /var/log/mysql/slow.log --limit 20 --output json > digest.json
# 輸出報告含：執行次數、總時間、平均時間、Rows examined、Rows sent、查詢抽象、EXPLAIN 計劃
3. 手工分析關鍵指標
| 指標 | 關注閾值 | 優化方向 |
|---|
| Query_time | > 1s | 索引、SQL 改寫、分頁優化 |
| Lock_time | > 0.1s | 鎖競爭、長事務、隔離級別 |
| Rows_examined / Rows_sent | > 100:1 | 全表掃描、索引失效、覆蓋索引 |
| Rows_affected | 大量寫入 | 批次提交、延遲索引維護 |
完整排查流程
- 開啟慢查詢日誌（動態或配置檔）。
- 運行業務高峰期 30-60 分鐘收集樣本。
- 用
pt-query-digest 生成 TOP 20 報告。
- 對每條 TOP SQL：
EXPLAIN、檢查索引、評估改寫方案。
- 驗證優化後效果，關閉或調整
long_query_time 歸檔。
注意事項
- ⚠️ 慢查詢日誌本身有 I/O 開銷，生產環境建議
long_query_time >= 1。
- ⚠️
log_queries_not_using_indexes 會產生大量噪音，短期診斷用，長期關閉。
- ⚠️ 日誌輪轉：配置
logrotate 避免磁碟佔滿。
- ✅ 5.7+ 支援
sys.schema_table_statistics、sys.statements_with_runtimes_in_95th_percentile 等視圖，輔助分析。
