慢查询治理:从告警到根因的一套流程
接手时,核心库每天产生 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% |
有了这个分布,治理就有了顺序:先干占比最大的那类,一次改动覆盖最多问题。
三、归因:三个必看的信息
每条慢查询归因时,我们强制看三样东西:
- 执行计划:
EXPLAIN FORMAT=JSON,重点看rows_examined与using_filesort; - 数据分布:
SHOW INDEX的基数和字段选择率,判断索引是否真的可用; - 调用来源:哪个服务、哪个接口、什么时间点。
第三点经常揭示真问题。比如有一条查询 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 手艺。流程跑通之后,即使换人接手,效果也能保持——因为判断依据是数据,不是直觉。
最后提醒一句:不要追求”零慢查询”。总会有报表、对账、数据修正这类合理的长查询。把它们识别出来、隔离到只读实例,比强行优化更划算。