MUYI · 后端工程师的生产级知识体系

Appearance

线上慢SQL复盘-范围条件断前缀与回表风暴 ​

一条带 LIMIT 1 的"看起来无害"的查询，把线上连接池打到满载、拖垮全部接口——这是索引设计与回表代价最典型的一课。事故链条：联合索引第 4 列上的范围条件切断了后续列的使用，5w+ 候选行逐行回表，LIMIT 1 并不能省掉扫描与排序代价，慢 SQL 叠加并发把连接池占满，故障向全站传导。本篇按"现象 → 现场 → 原理 → 修复 → 防线"复盘，索引基础见 MySQL索引详解，失效场景清单见 数据库索引失效总结。

1 事故现象：连接池满，全站接口雪崩 ​

  • 告警起点：应用日志刷屏 Connection is not available, request timed out after 30000ms（HikariCP 池满告警），随后健康检查接口也开始超时，上游熔断，故障面扩大到整个服务群；
  • 数据库侧佐证：show processlist 里大量同形态 SQL 处于 Sending data / executing 状态，堆积时间普遍 5~20 秒；
  • 慢日志锁定嫌疑：单条 SQL 平均执行 8s+，QPS 30 左右——30 并发 × 8s = 240 连接秒需求，而池上限只有 20，连接池被这一种 SQL 独占，其余业务拿不到连接排队超时。

嫌疑 SQL 长这样（参数全部占位符绑定）：

sql
SELECT * FROM biz_order
WHERE tenant_id = ?          -- 等值
  AND channel = ?            -- 等值
  AND status  = ?            -- 等值
  AND create_time > ?        -- 范围：过去一天
  AND biz_no   LIKE ?        -- 前缀匹配
ORDER BY create_time DESC
LIMIT 1;

LIMIT 1、参数绑定、命中的还是主键之外的联合索引——每个要素单看都"没毛病"。

2 现场还原：索引结构与数据分布 ​

表的联合索引（化名）：

sql
KEY idx_query (tenant_id, channel, status, create_time, biz_no, extra_id)
--                        等值      等值    等值    ↑范围起断点      第5列    第6列

两个致命事实：

  • 时间列在第 4 位，且以范围条件（>）出现：按最左前缀规则，范围条件之后的列（biz_no、extra_id）不能继续走索引检索，也无法利用索引完成排序去重——索引在这一列被"截断"；
  • 选择性塌方：create_time > 过去一天 在该租户下命中 5w+ 行。EXPLAIN 实测：
type: range
key:  idx_query
key_len: <只覆盖到 create_time 的前缀等值三列+时间列>
rows: 52318
Extra: Using index condition; Using where

rows 五万级、Using where 说明存储引擎层过滤后还要回 Server 层二次过滤，而 SELECT * 决定了每一行都要回表取整行。

3 原理拆解：为什么 LIMIT 1 救不了它 ​

1）范围条件截断最左前缀的"后半段"。 B+ 树里 tenant/channel/status 三列等值定位后，create_time > x 在索引上是一段连续区间——这段区间内第 5、6 列是无序的，所以 biz_no LIKE 'xx%' 不能在索引上跳着找，只能逐条扫。这就是 key_len 只算到第 4 列的原因（索引选择性与 key_len 的读法见 MySQL索引详解）。

2）回表是"逐行随机 I/O"，5w 次起。 命中的每个二级索引项都要回聚簇索引取整行（SELECT * 不可避免），5w+ 次回表在冷数据页上是 5w+ 次近似随机的页读取。单次 B+ 树 descend 不贵，贵在次数。

3）ORDER BY create_time DESC LIMIT 1 的执行顺序坑。 直觉是"倒着扫第一条就返回"，但 biz_no LIKE ? 这个索引后半段才可能过滤的条件，使得存储引擎层无法保证按索引序过滤后第一条就满足全部条件——优化器选择了扫完 range 区间、逐行回表并过滤、（部分场景还需 filesort）后取第一条。LIMIT 1 缩小的是返回集，不是扫描集。这正是事故 SQL "看着便宜实际巨贵"的核心。

4）连接池满的传导机制。 单条 8s × 并发 30 = 240 连接·秒的占用需求 vs 池 20——池内全是同一形态慢 SQL，其余请求在 getConnection 处排队 30s 超时。数据库本身还没挂，应用先被自己的连接池勒死；若上游无熔断，还会向依赖方放大（超时重试 → 更高并发 → 更慢）。这就是"一条慢 SQL = 一次线上事故"的完整链条。

4 修复：三层解法，索引侧是根治 ​

① 索引重排——等值列前置，范围列殿后，能覆盖则覆盖：

sql
-- 方案 A：把范围列挪到联合索引最后一列，等值列全部前置
KEY idx_query_v2 (tenant_id, channel, status, biz_no, extra_id, create_time)
-- biz_no 的 LIKE '前缀%' 升为等值匹配段，range 只发生在最后一列
-- 若 SELECT 收敛为少数列，可把排序列一并纳入形成覆盖索引，Extra 出现 Using index 即消除回表

实测效果：range 之前五列全等值/前缀匹配，候选行从 5w+ 收敛到几百；若建成覆盖索引则回表为 0，SQL 从 8s 到 40ms 级。

② SQL 改写——延迟关联先定位主键： 无法立刻动索引时的止血手段：

sql
SELECT t.* FROM biz_order t
JOIN ( SELECT id FROM biz_order
       WHERE tenant_id=? AND channel=? AND status=?
         AND create_time > ? AND biz_no LIKE ?
       ORDER BY create_time DESC LIMIT 1 ) k ON t.id = k.id;

子查询在索引内完成过滤与定位（少回表甚至不回表），外层只回表 1 行。配套手段：SELECT * 改为明确列清单，为覆盖索引创造条件（见 数据库语句优化手段）。

③ 业务与时间窗收窄： "最近一天"业务上是否等价于"最近一小时"？范围窗口直接影响 rows 数量级；高频轮询场景再加本地缓存/布隆过滤前置拦截。参数一律占位符绑定——本事故排查时还顺手排除了拼接 SQL 导致的失效计划复用问题。

5 防线：让同类事故在到达用户前被拦下 ​

  • 连接池可观测：Hikari active/idle/await 指标接入告警（active 持续 >80% 即预警），别等 30s 超时刷屏才知道；
  • 慢 SQL 阈值分级：long_query_time 按业务分级（核心库 0.5s），配合 APM 把"新上线 SQL 的 p99"纳入发布卡点；
  • 池内隔离：多租户/运营后台类查询走独立数据源或独立池，避免拖垮交易主链路；
  • 演练闭环：连接池满的故障剧本纳入预案——第一时间 show processlist 找同形态堆积、kill 止血、再回头治理索引。

面试追问链 ​

  • 为什么联合索引里范围条件后的列会失效？→ range 之后 B+ 树上第 5、6 列不再有序，无法二分定位，只能逐条比对；用 key_len 怎么验证？→ key_len 只覆盖前 4 列（时间列按 4~5 字节计）；
  • LIMIT 1 为什么没让 SQL 变快？→ LIMIT 缩的是结果集不是扫描集；当过滤条件不全在索引上时，需要扫完区间逐行回表过滤才能确定"第一条"；
  • 回表贵在哪？→ 每条二级索引命中一次聚簇索引随机读，5w 次放大成 5w 次页 I/O；怎么消灭回表？→ 覆盖索引（查询列+排序列全进索引，Extra=Using index）；
  • 连接池满的传导过程？→ 慢 SQL 占住连接 → 池耗尽 → 其他请求 getConnection 排队超时 → 全接口失败 → 上游重试放大；为什么数据库没挂应用先挂？→ 池是应用侧瓶颈，DB 只是慢不是死；
  • 索引该怎么改？→ 等值列前置、范围列殿后、尽量覆盖；改索引的线上风险与操作顺序？→ online DDL/gh-ost、低峰执行、新旧索引并行观察后下线旧索引。

面试官视角小结 ​

这道题的分层信号非常清晰：只说"加个索引"是不及格的——索引本来就有；能指出"范围条件截断最左前缀 + LIMIT 1 不省扫描"才算入门；能算清"5w 候选 × 回表随机 I/O × 并发占用 = 连接池满"这本账、并给出索引重排/延迟关联/池隔离三层解法与止血顺序，才是有真实事故处置体感的答案。主动提"连接池 active 指标应该提前告警"这类防线建设，是把一次事故变成体系能力的加分项。

2026-10 新增（生产事故复盘）。