覆盖索引的隐形账单：给热查询加覆盖索引后 P99 从 12ms 涨到 68ms 的取舍账本

结论先行：覆盖索引不是免费的午餐，它的收益是「省回表」，账单是「索引膨胀 + 写放大 + buffer pool 装得下更少的行」三笔。这三笔账里前两笔你在建索引时看得见，第三笔（缓存效率下降）往往要过两周、被别的查询拖慢后才暴露。我 15 年里最典型的「加了索引反而更慢」案例，就是覆盖索引吃掉了 buffer pool 的随机 IO 吞吐。

一、现象：单条 SQL 从 12ms 压到 0.4ms，但全局 P99 涨了 5 倍

场景：一套结算系统，核心表 t_settle_item（结算明细），规模 2400 万行、主键 B+Tree 约 1.8GB、InnoDB、MySQL 8.0.32、8C16G、innodb_buffer_pool_size=128M（老实说偏小，但这是当时的物理机上限）。

最热的查询是「按商户号拉最近 20 条明细」：

SELECT item_id, shop_id, biz_no, amount, status, created_at
FROM t_settle_item
WHERE shop_id = ? AND created_at > ?
ORDER BY created_at DESC LIMIT 20;

原索引 idx_shop_time (shop_id, created_at)，EXPLAIN 是 type=range, key=idx_shop_time, rows≈40，P99 12ms——主要开销在回表：每次取 20 行要回主键 B+Tree 读 20 次。

我们的动作很「标准」：把 SELECT 的 6 个字段全塞进索引，建成覆盖索引：

ALTER TABLE t_settle_item
  ADD INDEX idx_shop_time_cover (shop_id, created_at, biz_no, amount, status, item_id);

单条 SQL 立刻变好：EXPLAIN 多了 Using index，P99 从 12ms 掉到 0.4ms，收益 30 倍，验收通过，旧索引保留观察一周后删除。

但两周后，同库另一条高频 SQL（按 biz_no 点查）的 P99 从 3ms 涨到 68ms，全库慢日志从 200 条/天涨到 3100 条/天。 我们查的正是这条 SQL——而它走的是 uk_biz_no 唯一索引，跟覆盖索引毫无关系。

二、原理：三笔账，前两笔看得见，第三笔看不见

第一笔：索引膨胀。 原 idx_shop_time 每行存 (shop_id, created_at) 约 28 字节；覆盖版多了 biz_no(32B 字符串) + amount(8B) + status(1B) + item_id(8B)，单行约 80 字节。2400 万行，索引从约 700MB 涨到 约 2.0GB，整库数据字典从 1.8GB 涨到 4.1GB（+128%）。

第二笔：写放大。 这张表日均 INSERT 约 210 万行（每笔结算平均 6 行明细）。每行 INSERT 现在要额外维护一个 80B 的叶子节点，B+Tree 分裂、页分裂的代价比 28B 版本高；innodb_log_file_size 没变，redo 写入量按字节算涨了约 45%。这笔账当时我们算了，接受了。

第三笔（真正的坑）：buffer pool 装得下更少的行。 innodb_buffer_pool_size=128M，覆盖索引上线后，热表的可缓存行数从「主键树 1.8GB 里挑热的」变成「4.1GB 里挑热的」，但 buffer pool 还是 128M。关键机制是：

• InnoDB 一次随机 IO 读 16KB 页。索引每行 28B → 一个页约 570 行；每行 80B → 一个页约 200 行。
• 同样 128M buffer pool 原来能挂住约 8200 个数据页，现在热集变大、每页行数变小，LRU young 区的有效命中率从 93% 掉到 81%（SHOW ENGINE INNODB STATUS 的 Buffer pool hit rate 从 0.9993 → 0.9981，看着没掉多少，但冷读抖动频率从每天 3 次涨到 11 次）。
• 冷读一旦发生，16KB 页里只有 200 行可用，而 uk_biz_no 那类点查依赖的「热页常驻」被打破——它走的是另一个索引，但 buffer pool 是共享的，被覆盖索引的写入和分裂页挤占。

核心反直觉点：覆盖索引让目标查询变快 30 倍，却让全库的随机 IO 吞吐下降，因为同样的内存装不下等量的「行」。 这是单条 SQL 视角看不到的。

三、具体做法：加覆盖索引前的三步量化

Step 1：算索引膨胀比

SELECT
  COUNT(*) AS rows,
  AVG(LENGTH(biz_no)) + 8 + 1 + 8 + 28 AS cover_bytes_per_row
FROM t_settle_item WHERE shop_id = <sample>;
-- 实测约 80 字节/行 → 2400 万行 ≈ 2.0GB 增量

Step 2：算 buffer pool 容量比

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';  -- 134217728 (128M)
-- 规则：热表总大小 / buffer_pool < 3，否则覆盖索引的缓存收益会被冷读吃掉
-- 本例 4.1GB / 128M ≈ 32 → 危险区间

Step 3：写放大压测

用 sysbench 或业务影子流量压 30 分钟 INSERT，对比 SHOW GLOBAL STATUS LIKE 'Innodb_rows_inserted' 与 redo 写入速率（performance_schema.events_waits_summary_global_by_event_name 里 wait/io/file/innodb/innodb_log_file）。我们实测 redo 写入量 +45%，可接受但记录在案。

最终方案（取舍后的选择）：

• 不建全字段覆盖索引，改成「部分覆盖」：idx_shop_time_cover (shop_id, created_at, amount, status, item_id)——把最长的 biz_no（32B 字符串）踢出去，单行从 80B 降到 48B，索引总量 2.0GB → 1.2GB。
• biz_no 回表读一次，20 行 × 1 次回表，P99 从 0.4ms 回到 2.1ms——仍然比原方案 12ms 快 5.7 倍，但全库冷读抖动频率回到 4 次/天。
• 同时把 innodb_buffer_pool_size 从 128M 提到 512M（运维窗口期内完成），这才是根治。

四、踩过的坑（反直觉清单）

1. Using index 不是越出现越好。 它说明这条 SQL 省了回表，但没说别的 SQL 在替你付缓存代价。
2. EXPLAIN 看不出「共享 buffer pool」的竞争。 两个走不同索引的 SQL，慢的根因可能是第三个索引的膨胀。
3. 删掉覆盖索引后 P99 反而变好——这是我们验证根因的关键实验：把 idx_shop_time_cover 临时 DROP，uk_biz_no 点查 P99 立刻从 68ms 回落到 4ms。这一步比任何理论分析都有说服力。
4. Buffer pool hit rate 是个「看着没掉多少」的指标。 0.9993 → 0.9981 看着像噪声，但冷读抖动频率（用 Innodb_buffer_pool_reads 的 delta 算）从 3 次/天涨到 11 次/天，P99 直接 ×5。
5. 覆盖索引对 INSERT/UPDATE 的写放大，在 2400 万行、日均 210 万行写入的表上，redo 量 +45% 是真实数字——别拿「这点写入不算什么」糊弄自己。

五、结论与可复用清单

覆盖索引的取舍不是「要不要加」，而是「加哪几个字段」。 判断顺序：

1. 目标 SQL 的 P99 收益是否 ≥ 5 倍？（本例 30 倍 → 值得）
2. 热表总大小 / buffer_pool 是否 < 3？（本例 32 → 必须先扩内存再上全字段覆盖）
3. 把最长的字符串字段踢出索引，用「部分覆盖」换缓存效率（本例省 40% 索引体积，只损失 5 倍 → 2.1ms 的 P99）
4. 上线后 2 周观察全库 Innodb_buffer_pool_reads delta 与慢日志趋势，而不是只看目标 SQL
5. 验证根因的终极手段：临时 DROP 掉新索引，看别的 SQL P99 是否回落——这一步 5 分钟，比写 5 页分析文档都管用

一句话：覆盖索引省的是「回表 IO」，花的是「全库缓存」——在 buffer pool 紧张的环境里，这笔账往往不划算，先扩内存再谈覆盖。