萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 慢查詢日誌分析實戰:pt-query-digest + 可視化平台部署指南

慢查詢日誌是 SQL 性能調優的「體檢報告」。本文詳解開啟慢查詢、pt-query-digest 多維分析技巧、可視化平台 Query-Digest-UI 部署與生產環境最佳配置。

1. 開啟慢查詢日誌

# 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%';

2. pt-query-digest 核心分析命令

# 基礎報告
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

3. 關鍵報告指標解讀

指標含義關注閾值
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

4. 可視化平台 Query-Digest-UI 部署

步驟 1:創建存儲數據庫表

mysql -uroot -p < install.sql
# install.sql 包含 global_query_review、global_query_review_history 表結構

步驟 2:創建數據庫賬號

mysql -uroot -p -e "GRANT ALL ON slow_query_log.* TO 'slowlog'@'%' IDENTIFIED BY '123456';"

步驟 3:配置 UI

cd Query-Digest-UI
cp config.php.example config.php
vim config.php
# 修改 DSN、用戶、密碼、表名

步驟 4:導入分析結果

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

5. 生產環境最佳實踐

  • 日誌輪轉:logrotate 每日切割,保留 30 天
  • 定時分析:Crontab 每小時跑一次 pt-query-digest 入庫
  • 告警聯動:新增慢查詢 fingerprint 觸發釘釘/企業微信告警
  • 樣本完整性:log_slow_verbosity=query_plan,explain (5.7+) 記錄執行計劃
  • 審核流程:DBA 定期在 UI 標記 reviewed_status,形成知識庫

6. 進階:慢查詢根因定位清單

  1. EXPLAIN 檢查執行計劃:type=ALL、key=NULL、rows 偏大
  2. 索引缺失:SHOW INDEX FROM tbl、pt-duplicate-key-checker 檢測冗余索引
  3. 統計信息過期:ANALYZE TABLE 更新
  4. 隱式類型轉換:WHERE 字段與參數類型不匹配導致索引失效
  5. 大事務/長鎖:SHOW ENGINE INNODB STATUS 觀察鎖等待
copyright © 萬盛學電腦網 all rights reserved