慢查询治理实战：用 pt-query-digest 锁定 Top SQL

问题背景：慢查询治理不能只盯单条 SQL

很多团队处理 MySQL 慢查询时，习惯看到报警就复制一条 SQL 去 EXPLAIN，然后临时加索引。短期看问题似乎消失了，但几天后慢查询数量又回升。根因通常不是某条 SQL 写得差，而是缺少对慢日志的归类：哪些 SQL 总耗时最高，哪些 SQL 调用最频繁，哪些 SQL 只是偶尔扫描大量数据。

pt-query-digest 是 Percona Toolkit 中的慢日志分析工具，擅长把大量慢日志按指纹（fingerprint）聚合，将字面量替换为 ?，从而把同类 SQL 归并为 Top SQL。治理时优先处理响应时间占比高、执行次数多的类别，收益远大于逐条优化。

典型流程如下：

方案对比：为什么优先选择 pt-query-digest

常见慢查询分析方式有三类。

第一，直接查询 mysql.slow_log。优点是不需要额外工具，CSV 或 FILE 表也能检索；缺点是 SQL 指纹聚合能力弱，不同参数值会被视为不同语句，统计总耗时不方便。

第二，使用数据库性能视图，如 MySQL Performance Schema。它能获得更细粒度的事件统计，适合实时诊断；但配置和字段相对复杂，对历史慢日志文件做离线分析不如工具直接。

第三，使用 pt-query-digest。它可以直接解析 slow log、general log、binary log，也支持从进程列表抓样本。对慢查询治理最有价值的是默认输出：总响应时间、执行次数、平均耗时、锁时间、发送行数、扫描行数，以及按指纹归并后的 SQL 样本。

在资源有限时，建议先按 pt-query-digest 的总响应时间贡献度确定 Top SQL，再对这些 SQL 做 EXPLAIN、索引验证和 SQL 改写，而不是平均用力。

实操步骤：构造可复现的慢查询场景

下面使用一个订单明细表模拟业务高频查询。为了便于直接运行，示例表控制了数据量，同时通过缺少合适索引和大范围扫描来复现问题。

sql

-- 建表：订单明细表
CREATE TABLE order_item (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  order_no VARCHAR(32) NOT NULL COMMENT '订单编号',
  user_id BIGINT UNSIGNED NOT NULL COMMENT '用户 ID',
  shop_id BIGINT UNSIGNED NOT NULL COMMENT '店铺 ID',
  status TINYINT NOT NULL COMMENT '状态：0待支付 1已支付 2已完成 3已取消',
  amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '金额',
  created_at DATETIME NOT NULL COMMENT '创建时间',
  updated_at DATETIME NOT NULL COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细表';

-- 写入若干测试数据，实际治理时样本应来自业务高峰期慢日志
INSERT INTO order_item(order_no, user_id, shop_id, status, amount, created_at, updated_at)
SELECT
  CONCAT('O', LPAD(n, 10, '0')),
  10000 + (n % 5000),
  200 + (n % 80),
  CASE WHEN n % 10 = 0 THEN 3 WHEN n % 5 = 0 THEN 2 ELSE 1 END,
  ROUND(50 + RAND() * 500, 2),
  DATE_ADD('2026-01-01', INTERVAL FLOOR(RAND() * 180) DAY),
  NOW()
FROM (
  SELECT a.N + b.N * 10 + c.N * 100 + 1 AS n
  FROM (SELECT 0 N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
        UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
  CROSS JOIN (SELECT 0 N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
        UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
  CROSS JOIN (SELECT 0 N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
        UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
) t;

先确认慢查询记录参数，生产环境不要把 long_query_time 设得过大，否则只能捕获特别慢的 SQL，无法发现高频 200ms 到 800ms 的隐性损耗。

sql

-- 查看慢日志配置
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

-- 建议在业务低峰调整，线上应通过参数文件持久化
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.2;
SET GLOBAL log_queries_not_using_indexes = ON;

执行两个典型慢 SQL。第一个按用户和状态查询订单，第二个按时间范围做店铺统计。

sql

-- Top SQL 候选 1：用户订单列表，缺少合适联合索引
SELECT id, order_no, status, amount, created_at
FROM order_item
WHERE user_id = 12345
  AND status IN (1, 2)
ORDER BY created_at DESC
LIMIT 20;

-- Top SQL 候选 2：店铺时间范围统计，过滤性不足
SELECT shop_id, status, COUNT(*) AS cnt, SUM(amount) AS total_amount
FROM order_item
WHERE created_at >= '2026-02-01'
  AND created_at < '2026-03-01'
GROUP BY shop_id, status
ORDER BY total_amount DESC;

慢日志解析：用 pt-query-digest 归类 Top SQL

假设慢日志文件为 /var/log/mysql/mysql-slow.log，执行：

bash

# 按默认响应时间占比分析慢日志
pt-query-digest /var/log/mysql/mysql-slow.log > slow_report.txt

# 只分析最近 24 小时的慢日志
pt-query-digest --since '24 hours ago' /var/log/mysql/mysql-slow.log

# 只看某个业务库，并按执行次数排序
pt-query-digest --filter '$db eq "trade"' --order-by Query_time:cnt /var/log/mysql/mysql-slow.log

# 将结果写入表，便于跨天跟踪，需要提前创建 percona_schema 相关表
pt-query-digest \
  --review h=127.0.0.1,D=ops_db,t=query_review \
  /var/log/mysql/mysql-slow.log

报告中重点看三个区域。

Profile：SQL 指纹排名，关注 Response time 占比、Calls、R/Call。
EXPLAIN 信息：部分版本会展示表名和指纹，帮助定位具体对象。
查询详情：Query_time、Lock_time、Rows_examined、Rows_sent 的分布。

判断优先级时可使用一个简单公式：治理价值 ≈ 总响应时间 × 可优化空间 × 调用增长趋势。一个平均耗时 3 秒但每天执行 2 次的报表 SQL，未必比平均 300ms、每天执行 50 万次的列表 SQL 更紧急。

索引设计与 SQL 改写

对第一条 SQL，当前只有主键，可能出现 type=ALL 全表扫描，或者利用不合适的单列索引后再做 filesort。更合理的是建立符合等值过滤、范围/排序、覆盖字段的联合索引。

sql

-- 先查看执行计划
EXPLAIN
SELECT id, order_no, status, amount, created_at
FROM order_item
WHERE user_id = 12345
  AND status IN (1, 2)
ORDER BY created_at DESC
LIMIT 20;

-- 联合索引：user_id 等值在前；status 为低基数 IN；created_at 支持排序
CREATE INDEX idx_user_status_created
ON order_item(user_id, status, created_at);

-- 如果希望进一步减少回表，可使用覆盖索引，但要评估字段更新成本
CREATE INDEX idx_user_status_created_cover
ON order_item(user_id, status, created_at, id, order_no, amount);

需要注意，IN (1,2) 会使优化器在每个状态值内利用 created_at 排序，再做合并。若线上数据分布特殊，也可以改写为两段查询合并，但不要在没有实测证据时盲目改写。

第二条统计 SQL 如果经常按时间范围聚合，可建立以下索引：

sql

-- 支持范围扫描与分组
CREATE INDEX idx_created_shop_status_amount
ON order_item(created_at, shop_id, status, amount);

EXPLAIN
SELECT shop_id, status, COUNT(*) AS cnt, SUM(amount) AS total_amount
FROM order_item
WHERE created_at >= '2026-02-01'
  AND created_at < '2026-03-01'
GROUP BY shop_id, status
ORDER BY total_amount DESC;

如果时间范围跨度很大，索引仍需扫描大量记录。此时更好的治理方式是预聚合：

sql

-- 用日汇总表承接高频统计需求
CREATE TABLE shop_order_daily_agg (
  stat_date DATE NOT NULL,
  shop_id BIGINT UNSIGNED NOT NULL,
  status TINYINT NOT NULL,
  order_cnt INT UNSIGNED NOT NULL DEFAULT 0,
  total_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (stat_date, shop_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='店铺订单日汇总表';

-- 查询从明细表大范围聚合改为汇总表小范围扫描
SELECT shop_id, status, SUM(order_cnt) AS cnt, SUM(total_amount) AS total_amount
FROM shop_order_daily_agg
WHERE stat_date >= '2026-02-01'
  AND stat_date < '2026-03-01'
GROUP BY shop_id, status
ORDER BY total_amount DESC;

跟踪闭环：让 Top SQL 治理可量化

优化前后必须保留同一口径的数据：慢日志时间窗口、SQL 指纹、总响应时间、调用次数、平均耗时、P95、扫描行数、发送行数。上线后至少观察一个完整业务周期，不能只在测试库执行一次 EXPLAIN 就判定成功。

建议把 Top SQL 分为四类跟踪：已优化、观察中、需业务改写、暂不处理。每次索引变更都记录原因和收益，例如“平均扫描行数从 82000 降到 20，P95 从 780ms 降到 95ms”。这样后续面对新慢查询时，可以复用判断标准，而不是重复争论。

避坑总结：慢查询治理中的高频问题

只按最大耗时排序，忽略总响应时间。 常见现象是每天优化一条夜间批处理 SQL，但接口高峰期数据库 CPU 仍然很高。修正方法是默认按 Response time 占比排序，再结合 Calls 和 R/Call，优先治理高频高损耗 SQL。
看到 log_queries_not_using_indexes=ON 就给所有 SQL 加索引。 这会导致小表全表扫描也被写入慢日志，报告被低价值 SQL 淹没，同时盲目加索引会拖慢写入。修正方法是设置合理的 long_query_time，对小表允许全表扫描，索引设计结合过滤性、排序、回表和写入成本。
联合索引顺序凭字段顺序或基数直觉创建。 例如把 created_at 放在最前，但查询同时按 user_id 等值过滤，导致扫描大量时间范围记录。修正方法是遵循“等值过滤字段优先、排序字段配合、覆盖字段兜底”的原则，并以实际 EXPLAIN ANALYZE 或慢日志数据验证。
索引上线后不跟踪副作用。 典型现象是查询变快了，但订单写入延迟上升、磁盘空间增长明显。修正方法是在上线前后同时监控 TPS、写响应时间、缓冲池命中率、索引大小和慢查询数量，必要时使用不可见索引或灰度方式验证。
SQL 指纹没有沉淀，每次重新分析。 业务迭代后相似 SQL 反复出现，团队无法判断是新问题还是回归问题。修正方法是把 pt-query-digest 的 review 结果入库，为每个 Top SQL 指定负责人、优化方案、验证日期和当前状态，形成可持续的慢查询治理机制。

慢查询治理的关键不是工具命令本身，而是用统一口径把零散慢日志归类为可决策的 Top SQL，再通过执行计划、索引、改写和架构预聚合逐层处理。当 Top SQL 的响应时间占比持续下降，数据库性能改善才是可验证、可复盘、可长期维护的。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

慢查询治理实战：用 pt-query-digest 锁定 Top SQL ​

问题背景：慢查询治理不能只盯单条 SQL ​

方案对比：为什么优先选择 pt-query-digest ​

实操步骤：构造可复现的慢查询场景 ​

慢日志解析：用 pt-query-digest 归类 Top SQL ​

索引设计与 SQL 改写 ​

跟踪闭环：让 Top SQL 治理可量化 ​

避坑总结：慢查询治理中的高频问题 ​