慢查詢日誌是 SQL 性能調優的「體檢報告」。本文詳解開啟慢查詢、pt-query-digest 多維分析技巧、可視化平台 Query-Digest-UI 部署與生產環境最佳配置。
# my.cnf [mysqld] 區塊
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 2 # 超過 2 秒記錄(建議 0.5-1 秒)
log_queries_not_using_indexes = 1 # 記錄未用索引查詢
log_throttle_queries_not_using_indexes = 60 # 限流,每分鐘最多 60 條
# 5.7+ 支持動態修改
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;
驗證:SHOW VARIABLES LIKE 'slow_query%';
# 基礎報告
pt-query-digest --report slow.log
# 最近 30 分鐘
pt-query-digest --report --since 1800s slow.log
# 指定時間窗口
pt-query-digest --report --since '2024-01-15 10:00:00' --until '2024-01-15 12:00:00' slow.log
# 僅 SELECT 語句
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log
# 指定用戶
pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.log
# 全表掃描或 Full Join
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")' slow.log
# 輸出 JSON 供程序處理
pt-query-digest --output json slow.log > slow.json
| 指標 | 含義 | 關注閾值 |
|---|---|---|
| Query_time_sum | 總耗時 | 佔比高者優先優化 |
| Query_time_avg | 平均耗時 | > 1s 需關注 |
| Rows_examined_sum / Rows_sent | 掃描行 / 返回行比 | > 100:1 疑似全表掃描 |
| Lock_time_sum | 總鎖等待時間 | 高鎖爭用 |
| Tmp_table_on_disk | 磁盤臨時表數 | > 0 需優化 |
| Full_scan | 全表掃描次數 | > 0 |
mysql -uroot -p < install.sql
# install.sql 包含 global_query_review、global_query_review_history 表結構
mysql -uroot -p -e "GRANT ALL ON slow_query_log.* TO 'slowlog'@'%' IDENTIFIED BY '123456';"
cd Query-Digest-UI
cp config.php.example config.php
vim config.php
# 修改 DSN、用戶、密碼、表名
pt-query-digest \
--user=slowlog --password=123456 \
--review h=192.168.1.190,D=slow_query_log,t=global_query_review \
--review-history h=192.168.1.190,D=slow_query_log,t=global_query_review_history \
--no-report --limit=0% \
--filter="\$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}='$HOSTNAME'" \
/usr/local/mysql/data/slow.log
logrotate 每日切割,保留 30 天pt-query-digest 入庫log_slow_verbosity=query_plan,explain (5.7+) 記錄執行計劃reviewed_status,形成知識庫EXPLAIN 檢查執行計劃:type=ALL、key=NULL、rows 偏大SHOW INDEX FROM tbl、pt-duplicate-key-checker 檢測冗余索引ANALYZE TABLE 更新SHOW ENGINE INNODB STATUS 觀察鎖等待