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

大家好我是小耶写功课只是为了我踩过的坑你们别再踩了周三我们讲了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时可以检查是否能用复合索引替代。二、索引条件下推ICPICP是MySQL 5.6引入的一个重要优化。它的名字听起来很玄但原理其实很简单。在没有ICP之前存储引擎通过索引找到行后会立即回表读取整行然后再由Server层评估WHERE条件的剩余部分。这就像你要从图书馆找一本书先根据卡片找到书架位置然后把整本书拿出来回表再翻开书查看是否满足其他条件——如果书不对还得放回去。有了ICP​存储引擎层可以在索引遍历过程中直接评估部分WHERE条件​过滤掉不满足的行减少回表次数。相当于你在书架前就能看到书脊上的标签判断是不是你要的书不是就直接跳过不用把书抽出来。​例子​SELECT * FROM user WHERE name LIKE 张% AND age 20;假设有复合索引(name, age)。没有ICP时存储引擎用name匹配’张%找到所有行回表再检查age20。有ICP时在索引层同时检查age20不满足的直接跳过不回表。​执行计划标识​Extra: Using index condition。​适用条件​ICP只适用于二级索引。被下推的条件必须使用索引中的列。不能用于覆盖索引因为覆盖索引不需要回表。​什么时候ICP最有效​当索引过滤后的结果集很大但实际满足剩余条件的比例很低时ICP能大幅减少回表I/O。三、多范围读取MRRMRR是另一种优化主要用于减少随机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索引合并换复合索引原SQLSELECT * 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秒。​教训​索引合并不是银弹复合索引通常更优。案例2ICP大幅减少回表原SQLSELECT * FROM user WHERE name LIKE 张% AND age 20;索引(name, age)。没有ICP时扫描所有姓“张”的行假设5000行回表5000次耗时1.2秒。启用ICP后在索引层同时过滤age20假设只有500行满足回表500次耗时0.2秒。​如何确认ICP是否生效​查看EXPLAIN的Extra列是否包含Using index condition。如果包含说明ICP已启用MySQL 5.6默认开启。五、索引设计的实战法则汇总结合上期和本期内容总结索引设计的关键原则​优先使用复合索引而非多个单列索引​避免优化器走索引合并。​按照“等值在前范围在后高基数优先”设计复合索引顺序​。​利用覆盖索引避免回表​减少I/O。​关注执行计划中的 ​Extra​列​Using index覆盖索引好。Using index conditionICP生效较好。Using intersect/union索引合并可能暗示复合索引更优。Using MRR多范围读取对大量回表有帮助。​定期使用 ​ANALYZE TABLE​更新统计信息​让优化器准确评估基数。​对于高频查询可以强制使用索引​USE INDEX验证优化器选择但不建议长期依赖。索引优化不是一蹴而就的事而是持续观察、验证、调整的过程。理解索引合并、ICP、MRR这些高级特性能帮助你读懂执行计划里那些“奇怪”的标记判断优化器做的决策是否合理甚至主动引导它做出更好的选择。当你下次看到Using intersect时不再疑惑“这到底好还是不好”而是能马上判断这里应该改成复合索引。这就是从“背口诀”到“懂原理”的质变。小耶在手SQL 不愁还有什么想了解的欢迎留言小耶一定知无不言言无不尽……我们下次见~参考文献MySQL官方文档《Index Merge Optimization》、《Index Condition Pushdown》、《Multi-Range Read Optimization》《高性能MySQL》第4版第5章索引优化