索引优化实战法则：索引合并、ICP与设计技巧

索引优化这件事，说浅了是“建个索引就能快”，说深了，就是今天要聊的这些——索引合并、索引条件下推（ICP）、多范围读取（MRR）。这些特性在MySQL 5.6/8.0中早已成熟，但很多开发者只停留在“有索引就能快”的认知层面，却不知道优化器可能同时用多个索引来合并结果，也不知道ICP能让过滤提前到索引层。

理解了它们，你就能看懂执行计划里那些Using intersect、Using index condition标记背后到底发生了什么，以及这些到底是好事还是坏事。

从一个真实场景说起

一个典型的慢查询：表上有两个单列索引 idx_shop_id(shop_id) 和 idx_status(status)，查询条件是 WHERE shop_id = 123 AND status = 'PAID'。按常规思路，优化器应该只选一个索引，然后回表过滤另一个条件。但执行计划里却出现了 Extra: Using intersect(idx_shop_id, idx_status); Using where。

这就好比你要找一本“计算机类”且“2026年出版”的书。图书馆有两个卡片柜：一个按分类，一个按年份。传统做法是只查一个柜子（比如先找所有计算机类书目），然后一本本翻看年份。索引合并就是同时查两个柜子，分别拿到书单，再取交集。听起来很聪明，但真的总比复合索引好吗？未必。下面拆解细节。

一、索引合并（Index Merge）

索引合并是优化器的一种策略：当WHERE条件涉及多个列，且每个列都有独立索引时，优化器可能同时使用多个索引，然后将结果合并（交集、并集或排序并集），最后回表取数据。

1. 交集合并（Intersection）

适用于多个等值条件用 AND 组合。优化器分别扫描每个索引，得到主键集合，取交集后再回表。

例子：

SELECT * FROM orders WHERE shop_id = 123 AND status = 'PAID';

两个列都有单列索引时，执行计划可能出现 Using intersect(idx_shop_id, idx_status)。

性能分析：如果每个索引筛选出的主键集合都很大，交集后可能很小，回表次数少。但索引合并本身需要扫描两个索引并计算交集，代价不一定比使用一个复合索引低。最佳方案仍然是创建复合索引 (shop_id, status)，直接走一个索引，一次索引查找就能定位，无需合并。

2. 并集合并（Union）

适用于多个等值条件用 OR 组合。优化器分别扫描索引，得到主键集合，取并集。

例子：

SELECT * FROM orders WHERE shop_id = 123 OR status = 'PAID';

可能出现 Using union(idx_shop_id, idx_status)。注意：OR条件可能导致优化器放弃索引而全表扫描，因为并集太大。如果每个条件筛选的比例都很高，全表扫描可能更快。

3. 排序并集合并（Sort-Union）

适用于范围条件（>, <, BETWEEN）的OR组合。优化器先分别扫描索引得到主键集合，排序后去重，再回表。

例子：

SELECT * FROM orders WHERE shop_id > 1000 OR status = 'PAID';

索引合并的陷阱：虽然索引合并是优化器的“智能”策略，但它往往暗示你的索引设计不够好。一个设计良好的复合索引通常比索引合并更高效，因为单次索引查找就能定位数据，避免了合并开销。当你看到执行计划中间出现 Using intersect/union 时，可以检查是否能用复合索引替代。

二、索引条件下推（ICP）

ICP是MySQL 5.6引入的重要优化，名字听起来高大上，原理其实很直观。

在没有ICP之前，存储引擎通过索引找到行后，会立即回表读取整行，再由Server层评估WHERE条件的剩余部分。这就像你从图书馆找书，先根据卡片找到书架位置，然后把整本书拿出来（回表），再翻开书查看是否满足其他条件——如果不对还得放回去。

有了ICP，存储引擎层可以在索引遍历过程中直接评估部分WHERE条件，过滤掉不满足的行，减少回表次数。相当于你在书架前就能看到书脊上的标签，判断是不是你要的书，不是就直接跳过，不用把书抽出来。

例子：

SELECT * FROM user WHERE name LIKE '张%' AND age = 20;

假设有复合索引 (name, age)。没有ICP时：存储引擎用name匹配'张%'找到所有行，回表，再检查age=20。有ICP时：在索引层同时检查age=20，不满足的直接跳过，不回表。

执行计划标识：Extra: Using index condition。

适用条件：

• ICP只适用于二级索引。
• 被下推的条件必须使用索引中的列。
• 不能用于覆盖索引（因为覆盖索引不需要回表）。

什么时候ICP最有效：当索引过滤后的结果集很大，但实际满足剩余条件的比例很低时，ICP能大幅减少回表I/O。

三、多范围读取（MRR）

MRR是另一种优化，主要用于减少随机I/O。可以理解为“先整理再取货”。

举个例子：你要去超市买十样东西，分布在不同的货架。如果不规划路线，你会来回跑（随机I/O）。如果你先把购物清单按货架顺序排好，然后一趟走完（顺序I/O），效率更高。MRR做的就是这件事。

在没有MRR时，存储引擎按索引顺序找到主键后立即回表（随机I/O）。有了MRR，存储引擎先把要回表的主键收集到缓冲区，排序后再批量回表，把随机I/O转换为顺序I/O。

执行计划标识：Extra: Using MRR。

适用场景：范围查询或索引合并后需要大量回表。排序后回表能显著提升机械硬盘或普通SSD下的性能。但在高端NVMe SSD上，随机I/O和顺序I/O差距已缩小，MRR收益不明显。

四、高级索引特性的实战案例

案例1：索引合并换复合索引

原SQL：

SELECT * FROM orders WHERE shop_id = 10086 AND status = 'PAID';

原索引：两个单列索引 idx_shop_id 和 idx_status。
执行计划：Using intersect(idx_shop_id, idx_status); Using where，扫描两个索引取交集，回表1200行，耗时0.3秒。

优化：创建复合索引 (shop_id, status)。
新执行计划：ref 模式，单次索引查找，扫描200行，耗时0.05秒。

教训：索引合并不是银弹，复合索引通常更优。

案例2：ICP大幅减少回表

原SQL：

SELECT * FROM user WHERE name LIKE '张%' AND age = 20;

索引：(name, age)。没有ICP时，扫描所有姓“张”的行（假设5000行），回表5000次，耗时1.2秒。启用ICP后，在索引层同时过滤age=20，假设只有500行满足，回表500次，耗时0.2秒。

如何确认ICP是否生效：查看 EXPLAIN 的 Extra 列是否包含 Using index condition。如果包含，说明ICP已启用（MySQL 5.6 默认开启）。

五、索引设计的实战法则汇总

结合上期和本期内容，总结索引设计的关键原则：

• 优先使用复合索引而非多个单列索引，避免优化器走索引合并。
• 按照“等值在前，范围在后，高基数优先”设计复合索引顺序。
• 利用覆盖索引避免回表，减少I/O。
• 关注执行计划中的 Extra 列：
  • Using index：覆盖索引，好。
  • Using index condition：ICP生效，较好。
  • Using intersect/union：索引合并，可能暗示复合索引更优。
  • Using MRR：多范围读取，对大量回表有帮助。
• 定期使用 ANALYZE TABLE 更新统计信息，让优化器准确评估基数。
• 对于高频查询，可以强制使用索引（USE INDEX）验证优化器选择，但不建议长期依赖。

索引优化不是一蹴而就的事，而是持续观察、验证、调整的过程。理解索引合并、ICP、MRR这些高级特性，能帮助你读懂执行计划里那些“奇怪”的标记，判断优化器做的决策是否合理，甚至主动引导它做出更好的选择。当你下次看到 Using intersect 时，不再疑惑“这到底好还是不好”，而是能马上判断：这里应该改成复合索引。这就是从“背口诀”到“懂原理”的质变。