MySQL 8.0 复合索引第二列是范围条件，第三列还能被用到吗

背景：MySQL 8.0.36，InnoDB，一张约 1200 万行的订单表 orders，现有复合索引 idx_a_b_c (a, b, c)，a 是租户 id（bigint，等值），b 是订单时间（datetime，范围），c 是状态（tinyint，等值）。典型查询：SELECT id FROM orders WHERE a = ? AND b BETWEEN ? AND ? AND c = ?;

现象：EXPLAIN 显示 key=idx_a_b_c，key_len 只覆盖 a+b 两列，Extra 出现 Using index condition（ICP），rows 估算约 8 万，但实际扫描行数明显更高，且 c 这一列看起来并没有"参与索引定位"。

已尝试：1) 把 c 提到 b 前面改成 (a, c, b)，EXPLAIN rows 立刻降到约 2 千，但 key_len 又只覆盖 a+c；2) 单独加 (a, c) 索引，效果与 1 类似；3) 翻过官方文档关于"范围条件之后的列不再用于索引定位、但可被 ICP 下推"的说明，与现象一致。

想请教：1) 在这种"中间列是范围"的写法下，是否应该优先考虑把等值列前置（即改索引顺序为 a, c, b），而不是依赖 ICP？两者在 b 分布非常偏（热点租户 b 跨度大）时的实际差距有多大？2) 有没有什么场景下"保持 a, b, c + ICP"反而比"a, c, b 全索引定位"更划算？想听听大家踩过的具体例子。

结论先行：你的方向是对的——绝大多数场景应该把等值列前置（a, c, b），不要依赖 ICP；但「a, b, c + ICP 反而更划算」的场景确实存在，且不是少数。下面分三段：先讲为什么等值前置是默认选择，再讲 ICP 的真实代价，最后给两个 ICP 反而更优的具体场景（含量化判据）。

1) 为什么 (a, c, b) 是默认选择

核心原则：索引定位的代价是 O(log N) 一次，ICP 的代价是 O(K) 每行一次（K = 范围区间内的行数）。

• (a, c, b) 走索引定位：用 a（等值）+ c（等值）定位到索引前缀，再在 b 维度上范围扫描。索引树里定位到 (a, c) 前缀后，b 是有序存储的，范围扫描是连续的索引页读取，IO 模式是顺序读。
• (a, b, c) 走 ICP：用 a（等值）+ b（范围）定位，c 的过滤推迟到存储引擎层逐行做。这意味着：
  1. 索引树定位到的范围比 (a, c, b) 大得多（因为 b 是范围，c 没参与定位）；
  2. 每一行都要额外读一次 c 列（从索引叶子节点读，或回表读）；
  3. 过滤掉的行仍然产生了索引读取开销，只是省了「回表读其他列」的开销（你的查询只 SELECT id，所以回表本来就少，ICP 省的那点更少）。

量化对比（基于你给的 rows 估算）：

• (a, b, c) + ICP：rows ≈ 8 万，每行读 1 次索引（含 c 列）+ 0 次回表（只 SELECT id）。总读取 ≈ 8 万索引条目。
• (a, c, b) + 索引定位：rows ≈ 2 千，每行读 1 次索引。总读取 ≈ 2 千索引条目。

差距 = 40 倍。在 b 分布偏（热点租户 b 跨度大）的场景下，这个差距会进一步放大，因为 ICP 要扫的索引条目数正比于 b 的跨度。

唯一需要警惕的点：(a, c, b) 的索引写入代价更高——每次 INSERT/UPDATE 都要维护这个索引，且 c 是 tinyint（基数低），如果 c 只有 2–3 个枚举值，(a, c) 前缀的局部性其实不如 (a, b) 前缀（b 是 datetime，基数高，局部性更好）。如果这张表写多读少，(a, b, c) 的写入局部性优势要算进去。

2) ICP 的真实代价：不是「免费」的

很多人对 ICP 的误解是「ICP 只是把过滤下推，没有额外代价」——错。ICP 的代价是：

1. 索引叶子节点里多读一列：c 列的值在索引条目里，ICP 要逐行读出来做比较。这一步的代价是每行 1 次内存访问（如果索引页在 Buffer Pool 里）或每行 1 次磁盘读（如果索引页不在）。
2. 索引条目更宽：c 列存在索引里，索引条目比 (a, b) 索引宽 1 字节（tinyint），索引页能放的条目数略少，索引树略高，定位的 IO 次数略多。
3. 优化器估算偏差：你提到「rows 估算约 8 万，但实际扫描行数明显更高」——这说明优化器对 (a, b, c) 的选择性估算偏乐观。ICP 的过滤效果不进入优化器的成本模型（优化器只按 (a, b) 前缀估算 rows），所以实际扫描行数 > 估算行数是 ICP 方案的固有偏差，不是 bug。

验证命令（对比两种索引方案的实际扫描行数）：

-- 方案 A：(a, b, c) + ICP
EXPLAIN ANALYZE SELECT id FROM orders WHERE a = ? AND b BETWEEN ? AND ? AND c = ?;
-- 关注 "rows" 和 "filtered" 两列：
--   rows = 优化器估算的扫描行数
--   filtered = 优化器估算的过滤后比例（ICP 的过滤效果体现在这里）
--   实际扫描行数 = rows * (1 - filtered) 的近似，但 EXPLAIN ANALYZE 会给出真实值

-- 方案 B：(a, c, b) + 索引定位
EXPLAIN ANALYZE SELECT id FROM orders WHERE a = ? AND b BETWEEN ? AND ? AND c = ?;
-- 关注 "rows" 列：应该是 (a, c, b) 前缀的索引定位结果

-- 对比两者：
--   方案 A 的实际扫描行数 / 方案 B 的实际扫描行数 = ICP 的额外代价倍数

如果这个倍数 > 5，ICP 方案明显不划算；如果 < 2，两者差距不大，可以按写入局部性取舍。

3) ICP 反而更划算的两个具体场景

场景 A：b 的跨度极窄（热点查询）

如果典型查询的 b BETWEEN 跨度只有几小时到一两天（不是 90 天），(a, b, c) 走 ICP 扫描的索引条目数会很少（可能只有几百到几千），ICP 的「每行多读一列 c」的代价可以忽略。而 (a, c, b) 的索引写入局部性更差（c 基数低，(a, c) 前缀的局部性不如 (a, b)），写多读少的表上 (a, b, c) 更优。

量化判据：

• 典型查询的 b 跨度 < 1 天，且 (a, b, c) 的 ICP 扫描行数 < 5000，且表写 QPS / 读 QPS > 1 → 选 (a, b, c) + ICP。

场景 B：c 的基数极低（2–3 个枚举值）且 b 是主要过滤维度

如果 c 只有 2 个值（比如 status = 0/1），(a, c, b) 的索引前缀 (a, c) 的局部性比 (a, b) 差（c 基数低，(a, c) 前缀在索引树里分布更散）。而 (a, b, c) 的 (a, b) 前缀局部性好（b 是 datetime，连续值，索引页利用率高）。

这种情况下，(a, b, c) + ICP 的索引读取效率（顺序读、页利用率高）可能超过 (a, c, b) 的索引定位精度（定位准但页利用率低）。

量化判据：

• c 的基数 < 5，且 b 的跨度 > 7 天，且表是读多写少 → 两者接近，看 EXPLAIN ANALYZE 的实际扫描行数再定。
• 如果 (a, b, c) 的 ICP 扫描行数 / (a, c, b) 的索引定位行数 < 3 → 选 (a, b, c) + ICP（局部性优势弥补了 ICP 的额外代价）。

一句话总结

默认选 (a, c, b) + 索引定位，因为索引定位的代价是 O(1) 一次，ICP 的代价是 O(K) 每行一次；只有当 b 跨度极窄（< 1 天）或 c 基数极低（< 5）且表写多读少时，(a, b, c) + ICP 才可能更优。用 EXPLAIN ANALYZE 对比两种方案的实际扫描行数和总耗时，差距 > 2 倍就按扫描行数少的方案走，差距 < 2 倍就按写入局部性取舍。

参考：MySQL 8.0 官方 Index Condition Pushdown、Composite Indexes。

结论先行：你的直觉是对的——等值列前置（a, c, b）几乎总是比「a, b, c + ICP」更划算，ICP 只是「回表前过滤」的兜底优化，不是「索引定位」的替代品。但有一个场景例外：当 b 的范围极窄（比如只覆盖 1–2 天）且 c 的区分度极低（比如 c 只有 3 个枚举值，每个值占 33%）时，a, b, c + ICP 可能更划算，因为 a, c, b 的索引定位虽然精确，但 b 的范围窄意味着 a, c, b 的索引扫描范围也窄，两者差距被压缩到 ICP 的过滤开销以内。

逐条展开。

一、先厘清 ICP 到底是什么、不是什么

ICP（Index Condition Pushdown）是 MySQL 5.6+ 的优化：把「索引里能查到的列的过滤条件」下推到存储引擎层，在回表之前用索引记录里的值过滤掉不满足条件的行。

你的场景：索引 (a, b, c)，查询 WHERE a = ? AND b BETWEEN ? AND ? AND c = ?。

• 索引定位阶段：用 a 等值 + b 范围定位索引区间，c 不参与定位（因为 c 在 b 后面，b 是范围，c 的有序性被破坏）；
• ICP 阶段：在回表之前，用索引记录里的 c 值过滤掉 c ≠ ? 的行，避免无效回表；
• 回表阶段：只对通过 ICP 过滤的行回表取 id。

关键：ICP 省的是「回表 IO」，不是「索引扫描 IO」。索引区间（a 等值 + b 范围）里有多少行，就要扫多少行索引记录，ICP 只是让你不用对其中 c ≠ ? 的行回表。

二、a, c, b vs a, b, c + ICP：量化对比

假设热点租户 a = 1，b 跨度 90 天，c 有 3 个枚举值（每个值占 ~33%），该租户总行数 1200 万。

方案 A：a, b, c + ICP

• 索引定位：a=1（等值）+ b 90 天范围 → 索引区间覆盖 1200 万行 × 90/365 ≈ 296 万行索引记录；
• ICP 过滤：c = ? 过滤掉 ~66% → 剩 ~99 万行回表；
• 总 IO：296 万行索引扫描 + 99 万行回表。

方案 B：a, c, b

• 索引定位：a=1（等值）+ c=?（等值）→ 索引区间覆盖 1200 万 / 3 = 400 万行；
• b 范围过滤：在索引内部用 b 范围过滤（因为 b 是索引最后一列，在 a, c 确定的前提下，b 是有序的，可以做范围扫描）→ 400 万 × 90/365 ≈ 98 万行；
• 回表：98 万行。
• 总 IO：98 万行索引扫描（a, c 等值定位后，b 范围扫描）+ 98 万行回表。

差距：方案 A 扫 296 万行索引记录，方案 B 扫 98 万行，方案 B 的索引扫描量是方案 A 的 1/3。回表量两者几乎一样（都是 ~98–99 万行），所以瓶颈在索引扫描，方案 B 赢。

三、什么时候 a, b, c + ICP 反而更划算？

场景：b 的范围极窄 + c 的区分度极低

假设 b 只覆盖 1 天（而不是 90 天），c 只有 2 个枚举值（每个值占 50%）。

方案 A：a, b, c + ICP

• 索引定位：a=1 + b 1 天范围 → 1200 万 / 365 ≈ 3.3 万行索引记录；
• ICP 过滤：c = ? 过滤掉 50% → 1.6 万行回表；
• 总 IO：3.3 万行索引扫描 + 1.6 万行回表。

方案 B：a, c, b

• 索引定位：a=1 + c=? → 1200 万 / 2 = 600 万行；
• b 范围过滤：600 万 × 1/365 ≈ 1.6 万行；
• 回表：1.6 万行；
• 总 IO：600 万行索引扫描（a, c 等值定位后，b 范围扫描）+ 1.6 万行回表。

差距：方案 A 扫 3.3 万行索引，方案 B 扫 600 万行索引，方案 A 的索引扫描量是方案 B 的 1/180。回表量一样，所以瓶颈在索引扫描，方案 A 赢。

本质：当 b 的范围窄到「a, b 定位后的索引区间已经很小」时，c 的区分度再怎么低也不影响大局，ICP 的过滤开销可以忽略。而当 b 的范围宽（90 天），a, b 定位后的索引区间很大，c 的区分度就成了决定「扫多少行」的关键因素，等值列前置才有意义。

四、实操建议

1. 先测 b 的实际范围分布：

SELECT MIN(b), MAX(b), DATEDIFF(MAX(b), MIN(b)) as span_days
FROM orders WHERE a = ?;

如果 span_days > 7 天，优先 a, c, b；如果 span_days < 3 天，两者差距不大，a, b, c + ICP 够用（少建一个索引，写放大更小）。

2. 量化对比用 EXPLAIN ANALYZE 的实际执行时间，不要只看 rows 估算：

EXPLAIN ANALYZE SELECT id FROM orders WHERE a = ? AND b BETWEEN ? AND ? AND c = ?;
-- 对比两个索引的实际执行时间（单位 ms）
-- 如果差距 < 20%，选维护成本低的（少建索引）
-- 如果差距 > 50%，选执行时间短的

3. 如果 c 的区分度极高（比如 c 是 10+ 枚举值，每个值占 < 10%），无论 b 范围多宽，a, c, b 都赢，因为 c 的等值过滤比 b 的范围过滤强得多。

4. 别为了「理论最优」建一堆索引：每多一个索引，INSERT/UPDATE/DELETE 的写放大就多一份。如果 a, b, c + ICP 的执行时间在 SLO 内，别折腾。