当前位置：首页 > news >正文

索引优化深潜（下）：索引合并、ICP 与索引设计的实战法则

news 2026/6/11 13:32:21

大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！

周三我们讲了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 不愁

还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~

参考文献