開啟慢查詢日誌
Windows (my.ini)
[mysqld]
slow_query_log = 1
slow_query_log_file = "F:/MySQL/log/mysql-slow.log"
long_query_time = 2
log_queries_not_using_indexes = 1
Linux (my.cnf)
[mysqld]
slow_query_log = 1
slow_query_log_file = /data/mysql/log/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
修改後 systemctl restart mysqld 或線上動態開啟：
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1; -- 秒，支援微秒 0.1
SET GLOBAL log_queries_not_using_indexes = 1;
關鍵參數解析
| 參數 | 說明 | 建議值 |
|---|
| slow_query_log | 總開關 | ON |
| slow_query_log_file | 日誌路徑 (需 mysql 寫入權限) | /var/log/mysql/slow.log |
| long_query_time | 超過此秒數記錄 (含微秒) | 1-2 (線上), 0.1 (開發) |
| log_queries_not_using_indexes | 未用索引查詢也記錄 (無論耗時) | ON (開發/測試), OFF (高併發線上) |
| log_slow_admin_statements | 記錄管理命令 (ALTER, ANALYZE 等) | ON |
| min_examined_row_limit | 掃描行數超過此值才記錄 (過濾小表) | 1000 |
mysqldumpslow 分析神器
# 基本用法
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
# -s 排序: c=次數, t=時間, l=鎖定時間, r=返回行數 (加 a 為倒序)
# -t 前 N 筆
# -g 正則過濾 (不區分大小寫)
實戰範例
| 需求 | 命令 |
|---|
| 最常執行的 20 條慢查詢 | mysqldumpslow -s c -t 20 /var/log/mysql/slow.log |
| 耗時最長的 10 條 | mysqldumpslow -s t -t 10 /var/log/mysql/slow.log |
| 返回行數最多的 10 條 | mysqldumpslow -s r -t 10 /var/log/mysql/slow.log |
| 含 LEFT JOIN 的慢查詢 | mysqldumpslow -s t -t 10 -g "left join" /var/log/mysql/slow.log |
| 特定資料庫 | mysqldumpslow -s t -t 10 -g "db_name" /var/log/mysql/slow.log |
慢查詢日誌格式解讀
# Time: 2024-01-15T10:30:00.123456Z
# User@Host: app[app] @ 192.168.1.5 [192.168.1.5]
# Thread_id: 12345 Schema: mydb QC_hit: No
# Query_time: 2.345 Lock_time: 0.001 Rows_sent: 10 Rows_examined: 150000
SET timestamp=1705312200;
SELECT * FROM big_table WHERE status=1 ORDER BY created DESC LIMIT 10;
Query_time：總耗時 (含鎖等待、IO、CPU)。Lock_time：等待鎖時間。Rows_examined：掃描行數 (核心優化指標)。Rows_sent：返回行數。
輔助診斷命令
| 命令 | 用途 |
|---|
| SHOW VARIABLES LIKE 'slow%'; | 確認慢查詢配置生效 |
| SHOW PROCESSLIST; | 即時查看阻塞/慢查詢執行緒 |
| SHOW OPEN TABLES; | 查看被鎖定/打開的表 |
| EXPLAIN SELECT ...; | 分析具體 SQL 執行計畫 |
線上安全開啟策略
- 設定
long_query_time=2、min_examined_row_limit=1000 過濾噪音。
- 日誌目錄掛載獨立磁碟/分區，設定
logrotate 切割保留 7-30 天。
- 定期 (每日/每週) 執行
mysqldumpslow 產出 Top N 報告，推送給開發團隊。
- 結合
pt-query-digest (Percona Toolkit) 獲得更豐富分析 (按指紋聚合、EXPLAIN 自動解析、歷史趨勢)。
總結
慢查詢日誌是 MySQL 效能調優「體檢報告」。**開啟 → 定期分析 → 針對優化 → 驗證關閉/調整閾值** 形成閉環。線上環境建議常態開啟高閾值 (2s+)，開發測試環境全開 (0.1s + log_queries_not_using_indexes)，持續消除全表掃描、缺失索引、大事務等效能隱患。
