慢查询治理:从告警到根因的一套流程

接手时,核心库每天产生 400–600 条慢查询告警,值班同学基本处于麻木状态——告警太多等于没有告警。

半年后,稳定在每周 5 条以内,且每条都有明确归属。这篇文章讲流程,不讲索引优化技巧(那些网上都有)。

一、先解决”看不清”

原始的慢日志是一堆孤立的 SQL,无法判断哪条重要。我们做了三件事:

统一采集。 用 pt-query-digest 每 5 分钟聚合一次,输出指纹(fingerprint)而非原始 SQL。WHERE id = 1 和 WHERE id = 2 是同一类问题,聚合后数量直接降了两个数量级。

带上业务上下文。 在应用层给每条 SQL 埋 biz_tag(订单查询、商品详情、库存校验……),通过注释传给 MySQL:

/* biz=order_detail, api=/order/get */
SELECT * FROM orders WHERE user_id = 1024 ORDER BY created_at DESC LIMIT 20;

这一步是整件事的关键。有了 biz_tag,才能回答”这条慢查询影响的是哪个功能”,而不是”这条 SQL 慢”。

按影响面排序。 不是按耗时排序,而是按 耗时 × 调用次数 排序。一条 8 秒但每天只跑 3 次的报表 SQL,优先级远低于一条 200ms 但每小时跑 50 万次的详情查询。

二、聚类:把 500 条变成 12 类

聚合之后,我们用指纹前缀 + 表名 + 执行计划特征做二次聚类,得到 12 个根因类别。典型的几类:

类别 特征 占比
缺索引 / 索引失效 全表扫描,rows 很大 41%
深分页 LIMIT 100000, 20 18%
N+1 查询 同一指纹高频短查询 15%
大事务 / 锁等待 慢在 Waiting for lock 12%
隐式类型转换 字符列与数字比较 8%
其他 — 6%

有了这个分布,治理就有了顺序:先干占比最大的那类,一次改动覆盖最多问题。

三、归因:三个必看的信息

每条慢查询归因时,我们强制看三样东西:

  1. 执行计划:EXPLAIN FORMAT=JSON,重点看 rows_examined 与 using_filesort;
  2. 数据分布:SHOW INDEX 的基数和字段选择率,判断索引是否真的可用;
  3. 调用来源:哪个服务、哪个接口、什么时间点。

第三点经常揭示真问题。比如有一条查询 SELECT * FROM order_items WHERE order_id IN (...),单看 SQL 完全正常,索引也在。查调用来源才发现,某个批量接口一次传了 2000 个 ID,把 IN 列表撑爆了。问题在调用方,不在 SQL。

四、深分页:业务方案优于技术方案

LIMIT 100000, 20 这类查询,优化器必须扫描并丢弃前 10 万行。技术上可以用延迟关联或游标分页:

-- 延迟关联:先在索引上取 ID,再回表
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders WHERE status = 1 ORDER BY id LIMIT 100000, 20) t
  ON o.id = t.id;

但真正解决问题的是产品侧改造:把”跳到第 5000 页”改掉,只允许翻到第 100 页,超出提示”请缩小筛选范围”。改造后这类查询基本消失。

技术优化让人感觉专业,业务约束往往更有效。

五、防复发:把规则前移

治理完存量,重点是别再产生新的。我们做了三道闸:

上线前 SQL 审查。 CI 里跑 EXPLAIN,对涉及大表的查询检查:是否走索引、rows_examined 是否超过阈值、是否有 SELECT *。不通过则阻断发布。

索引变更走评审。 新增索引必须评估:字段选择率、写入放大的代价、是否需要 Online DDL。我们曾因为一个不必要的大表索引,导致写入 RT 上升 40%。

容量水位联动。 单表超过 2000 万行触发分区/归档评估。很多慢查询的根因是表太大了,索引优化只是延缓。

六、结果

指标 治理前 治理后
日均慢查询告警 480 3.2
慢查询导致的 P2+ 故障 月均 2.1 起 0
平均查询耗时 P99 420ms 96ms
值班响应时间 平均 40 分钟 平均 6 分钟

七、总结

慢查询治理的本质是可观测性工程,不是 DBA 手艺。流程跑通之后,即使换人接手,效果也能保持——因为判断依据是数据,不是直觉。

最后提醒一句:不要追求”零慢查询”。总会有报表、对账、数据修正这类合理的长查询。把它们识别出来、隔离到只读实例,比强行优化更划算。