加了索引还是慢：索引选择性、覆盖索引与回表成本的一次量化拆解

作者林砚舟-301

MySQL索引覆盖索引回表性能调优

结论先行：「加了索引」≠「走了快路径」。慢查询里相当一部分不是没索引，而是索引选择性差、没做成覆盖索引、或者优化器在回表成本和全表扫描之间选错了。我 3 年里被「明明有索引却慢」坑过不止一次，这次把三件事拆开量化讲清楚。

一、现象与背景

去年我们一个订单查询服务（Spring Boot 3.2，JDK 17，MySQL 8.0.34，单实例 16C32G，前置 6 个实例），运营后台一个「按商户 + 时间范围查订单列表」的接口，P99 从 80ms 一路涨到 2.3s，而同一张 orders 表（约 4200 万行）其它接口都正常。

诡异点在于：EXPLAIN 显示 type=ref、key=idx_merchant_id，看着完全正常，rows 估算也就几千。慢，但「看起来不慢」。

二、原理与根因

拆开看，慢来自三个叠加因素，每个都能单独解释一部分：

1. 选择性差（cardinality 低） merchant_id 在 4200 万行里有约 38 万个不同值，单列选择性其实还行；但业务上「头部商户」一个 merchant_id 就占了约 6% 的行（250 万行）。优化器按平均值估 rows，估出来几千，实际要扫 250 万行——估算值骗了人。

2. 回表（random I/O）成本没进估算 idx_merchant_id 只含 merchant_id 一列，SELECT 里要 id, amount, status, created_at 等字段，这些不在索引里 → 每命中一行都要拿主键回聚簇索引取整行。250 万行回表 = 250 万次随机 I/O，这才是 2.3s 的大头。

3. 优化器在「回表 + filesort」和「全表扫描 + filesort」之间的选择 当命中行数占比高时，回表成本可能高于顺序扫全表，优化器有时反而选全表扫，但带 ORDER BY created_at 又得 filesort——两条路都慢。

三、具体做法（可复现）

第 1 步：确认是不是回表

EXPLAIN SELECT id, amount, status, created_at
FROM orders
WHERE merchant_id = 12345
  AND created_at BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY created_at DESC
LIMIT 20\G

看 Extra 里是否有 Using index condition / Using index。没有 Using index 基本就是回表。

第 2 步：建覆盖索引，把回表干掉

按「等值列在前、范围列居中、排序列在后」的原则：

ALTER TABLE orders
  ADD INDEX idx_mid_ctime_cover (merchant_id, created_at, id, status, amount);

让 SELECT 的列全部落在索引里，EXPLAIN 出现 Using index（覆盖索引），回表归零。

第 3 步：让排序走索引序，干掉 filesort ORDER BY created_at DESC 配合 merchant_id 等值 + created_at 范围，索引本身就是有序前缀，配合 LIMIT 20 能提前截断。

第 4 步：对「头部大商户」单独治理 6% 占比的商户是长尾，建议按 merchant_id 做分库分表（我们按 merchant_id % 64 拆到 64 个库），把单商户数据量从 250 万降到约 4 万，单表 P99 直接回到 40ms 量级。

改造前后：P99 从 2.3s → 覆盖索引后 95ms → 分表后 40ms。

四、踩过的坑 / 反直觉

rows 是估算不是实测：选择性不均时，EXPLAIN 的 rows 会严重低估真实扫描量，别被「看着几千行」骗了。要看真实命中，得 SELECT COUNT(*) 或直接看慢日志 Rows_examined。
覆盖索引不是万能的：上面那条覆盖索引塞了 5 列，索引本身变大、写放大也变大。对写多读少的表，覆盖索引收益要打折，得权衡。
加列到索引 ≠ 用上了：idx_mid_ctime_cover 加了 id/status/amount，但如果查询里多一个不在索引里的列，覆盖索引立刻失效退回回表——索引列必须完全覆盖 SELECT 列才生效。
反直觉的优化器选择：命中占比高时，优化器可能弃用索引走全表扫（它认为回表更贵），这时 FORCE INDEX 反而能救场，但属于 hack，根因还是数据分布。

五、结论与可复用清单

遇到「有索引却慢」，按这个顺序排查：

EXPLAIN ... \G 看 type、key、rows、Extra 四项；
Extra 没有 Using index → 优先建覆盖索引，按「等值→范围→排序」排列；
有 filesort / Using temporary → 检查排序列能否落进索引前缀；
rows 估算明显偏小、Rows_examined 巨大 → 怀疑选择性不均 / 长尾大键，考虑分表或独立索引；
写多读少的表慎用大覆盖索引，权衡写放大；
别信 rows，信慢日志的 Rows_examined。

一句话：索引是给优化器的「提示」，不是「承诺」；选择性、覆盖度、数据分布三者都对得上，快才是真的快。

赞 0 · 收藏 0 · 评论 0 · 浏览 0

暂无评论。