開啟慢查詢日誌
配置檔永久生效
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slowquery.log
long_query_time = 1 # 超過 1 秒記錄(可設小數,如 0.5)
log_queries_not_using_indexes = 1 # 記錄未用索引查詢(可選,噪音大)
systemctl restart mysqld
動態生效(無需重啟)
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slowquery.log';
mysqldumpslow 分析工具核心參數
| 參數 | 說明 |
-s | 排序方式:c(計數) t(時間) l(鎖定) r(返回行數);前綴 a 倒序 |
-t N | Top N 筆 |
-g "pattern" | 正則過濾(不區分大小寫) |
-a | 不抽象化數值/字串(預設會將具體值替換為 N/S) |
實戰分析範例
1. 找出耗時最長的前 10 條
mysqldumpslow -s t -t 10 /var/lib/mysql/slowquery.log
2. 找出執行次數最多的前 20 條(高頻慢查詢)
mysqldumpslow -s c -t 20 /var/lib/mysql/slowquery.log
3. 找出鎖定時間最長的查詢
mysqldumpslow -s l -t 10 /var/lib/mysql/slowquery.log
4. 針對特定表分析
mysqldumpslow -s t -t 10 -g "users" /var/lib/mysql/slowquery.log
輸出解讀範例
Count: 12 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (12), root[root]@localhost
SELECT COUNT(N) FROM tab_1
Count: 2 Time=412.54s (825s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@localhost
SELECT COUNT(N) FROM sbtest, t1 WHERE t1.c = sbtest.c
Count:執行次數。
Time=avg (total):平均/總耗時。
Lock=avg (total):平均/總鎖定時間。
Rows=avg (total):平均/總返回行數。
user[user]@host:執行用戶/客戶端。
進階:pt-query-digest(Percona Toolkit,更強大)
pt-query-digest /var/lib/mysql/slowquery.log --limit 20 --filter '$event->{bytes} > 100000'
- 支援按多維度聚合、輸出 JSON/HTML 報告、篩選條件豐富。
- 可直接分析
tcpdump 抓包流量(--type tcpdump)。
慢查詢優化閉環
- 開啟慢查詢日誌(
long_query_time=1)。
- 定期
mysqldumpslow / pt-query-digest 分析 Top SQL。
EXPLAIN 分析執行計劃,補索引/改 SQL/調參數。
- 驗證優化效果,觀察慢查詢數量/耗時下降。
- 持續迭代,納入 CI/CD 自動化 SQL 審核。