MySQL 慢查詢日志分析進階：pt-query-digest 多維報告與 Query-Digest-UI 可視化部署

慢查詢日志原始文本難以直觀定位核心問題。本文深度講解 pt-query-digest 進階過濾報告、審計庫持久化、Query-Digest-UI 可視化平台部署，構建「采集→分析→存儲→展示→告警」閉環體系。

一、 確認慢查詢已開啟

SHOW VARIABLES LIKE '%slow%';
-- 期望：slow_query_log=ON, long_query_time=1 (或更小), log_queries_not_using_indexes=ON

動態修改：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.1;  -- 100ms 閾值
SET GLOBAL log_queries_not_using_indexes = ON;

二、 慢查詢日志格式關鍵字段

# Time: 130905 14:15:59
# User@Host: root[root] @ [183.239.28.174]
# Query_time: 0.735883 Lock_time: 0.000078 Rows_sent: 262 Rows_examined: 262
SET timestamp=1378361759;
SHOW TABLES FROM `test_db`;

• Query_time：總耗時（秒，微秒精度）
• Rows_examined：引擎層掃描行數，遠大於 Rows_sent 表明全掃/索引失效
• Lock_time：鎖等待佔比高提示熱點鎖爭用

三、 pt-query-digest 進階報告模式

# 基礎報告
pt-query-digest --report slow.log

# 最近 30 分鐘
pt-query-digest --report --since 1800s slow.log

# 指定時間窗
pt-query-digest --report --since '2023-02-10 21:48:59' --until '2023-02-16 02:33:50' 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

四、 可視化平台 Query-Digest-UI 部署

1. 下載與解壓

wget https://nodeload.github.com/kormoc/Query-Digest-UI/zip/master
unzip Query-Digest-UI-master.zip
cd Query-Digest-UI-master

2. 建庫建表

-- install.sql
DROP DATABASE IF EXISTS slow_query_log;
CREATE DATABASE slow_query_log;
USE slow_query_log;

CREATE TABLE `global_query_review` ( ... );  -- 指紋級元數據
CREATE TABLE `global_query_review_history` ( ... );  -- 時間序列統計

3. 創建審計賬號

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

4. 配置 config.php

$reviewhost = array(
  'dsn' => 'mysql:host=192.168.1.190;port=3306;dbname=slow_query_log',
  'user' => 'slowlog',
  'password' => '123456',
  'review_table' => 'global_query_review',
  'history_table' => 'global_query_review_history',
);

5. 定時入庫腳本

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

加入 crontab 每 5 分鐘跑一次，Web 端即可按時間序列、Top SQL、指紋聚合多維檢視。

五、 生產環境運維建議

1. 閾值分級：核心庫 long_query_time=0.5，非核心 =1，避免日志爆炸。
2. 采集端隔離：從庫開啟慢查詢，主庫關閉，消除主庫 I/O 干擾。
3. 指紋去噪：--filter 正則剔除 SET NAMES、COMMIT 等噪聲語句。
4. 告警規則：Query_time_pct_95 > 1s 或 Rows_examined > 100000 觸發釘釘/企微告警。
5. 歸檔策略：審計庫按月分區，保留 13 個月，滾動清理。