大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！
周三我们讲了InnoDB的索引结构、Cardinality和最左前缀原则。今天继续进阶，聊聊那些听起来有点高级的词：索引合并（Index Merge）、索引条件下推（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年出版”的书。图书馆有两个卡片柜：一个按分类（计算机类），一个按年份（2026年）。传统做法是只查一个柜子（比如先找所有计算机类的书），然后一本本翻看年份。索引合并就是同时查两个柜子：先找出计算机类的书单，再找出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 时，不再疑惑“这到底好还是不好”，而是能马上判断：这里应该改成复合索引。这就是从“背口诀”到“懂原理”的质变。
小耶在手，SQL 不愁
还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~
