一篇文章带你了解MySQL索引下推
在日常使用MySQL进行数据查询时，很多开发者都遇到过查询效率不高的问题，尤其是涉及联合索引的多条件查询场景。MySQL 5.6版本引入的索引下推（Index Condition Pushdown，简称ICP）优化特性，就是针对这类场景的重要性能优化手段。本文将详细介绍索引下推的概念、工作原理、适用场景以及实际使用示例。
什么是索引下推
索引下推是MySQL的一种查询优化策略，核心思路是在存储引擎层就过滤掉不符合条件的索引记录，减少回表次数，从而降低服务层需要处理的数据量，提升查询效率。
在ICP出现之前，使用联合索引进行查询时，存储引擎会先根据索引的最左前缀原则匹配符合条件的索引记录，然后逐条回表查询完整行数据，再将数据返回给服务层，由服务层再根据剩余的查询条件进行过滤。而开启ICP之后，存储引擎在遍历索引的过程中，就会先判断索引中是否包含查询条件里的其他列，如果包含，就直接在索引层面过滤掉不符合条件的记录，只将符合条件的记录回表查询，最后再返回给服务层。
索引下推的工作流程
我们可以通过一个完整的查询流程对比，来更清晰地理解ICP的作用：
未开启ICP的流程
服务层向存储引擎发送查询请求，查询条件包含联合索引的部分前缀和索引外的其他条件
存储引擎根据联合索引的最左前缀，找到所有符合前缀条件的索引记录
存储引擎对每一个符合前缀的索引记录，都进行回表操作，查询出完整的行数据
存储引擎将完整行数据返回给服务层
服务层再根据剩余的查询条件，对返回的行数据进行过滤，得到最终结果
开启ICP后的流程
服务层向存储引擎发送查询请求，查询条件包含联合索引的部分前缀和索引内的其他条件
存储引擎根据联合索引的最左前缀，找到符合前缀条件的索引记录
存储引擎判断当前索引记录中是否包含查询里的其他索引列条件，如果包含，就先在索引层面判断这些条件是否满足
只有同时满足前缀条件和索引内其他条件的索引记录，才会进行回表操作查询完整行数据
存储引擎将回表得到的行数据返回给服务层，服务层如果存在索引外的其他条件，再做最终过滤（如果没有则直接返回结果）
适用场景与限制
索引下推并不是所有场景都生效，它有以下适用条件和限制：
适用场景
查询使用的是联合索引，且查询条件中除了最左前缀条件外，还包含联合索引中的其他列条件
查询需要回表获取数据（如果查询的所有列都包含在索引中，属于覆盖索引场景，IPC不会生效，因为不需要回表）
支持的存储引擎：InnoDB和MyISAM都支持ICP，其中InnoDB仅支持二级索引的ICP，聚集索引不适用
查询条件中的条件是范围查询（如>、<、BETWEEN、LIKE等）或者等值查询，且这些条件对应联合索引中的列
不适用场景
查询条件中的过滤条件都不在联合索引中，只能回表后由服务层过滤
属于覆盖索引查询，不需要回表，ICP没有作用空间
使用聚集索引的查询（InnoDB的主键索引是聚集索引，索引叶子节点存储完整行数据，不存在回表过程）
查询条件中包含子查询、函数操作（比如对索引列使用函数，会导致索引失效，无法走ICP）
实际示例演示
下面我们通过一个具体的表结构和查询示例，来直观感受索引下推的效果。
1. 准备测试数据
首先创建一张用户表，包含用户id、姓名、年龄、城市四个字段，其中姓名字段是联合索引的最左列，年龄是联合索引的第二列：
-- 创建用户表
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`age` int(11) NOT NULL,
`city` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入测试数据
INSERT INTO `user` (`name`, `age`, `city`) VALUES
('张三', 20, '北京'),
('张三', 25, '上海'),
('李四', 22, '广州'),
('李四', 28, '深圳'),
('王五', 20, '杭州');2. 执行查询并查看执行计划
我们执行一个查询：查询姓名为"张三"，且年龄大于22岁的用户：
EXPLAIN SELECT * FROM `user` WHERE `name` = '张三' AND `age` > 22;
在MySQL 5.6及以上版本中，默认是开启ICP的，执行上述EXPLAIN语句后，在Extra列会看到Using index condition的提示，这就表示当前查询使用了索引下推优化。
如果我们手动关闭索引下推，再查看执行计划：
-- 关闭索引下推 SET optimizer_switch = 'index_condition_pushdown=off'; -- 再次查看执行计划 EXPLAIN SELECT * FROM `user` WHERE `name` = '张三' AND `age` > 22;
此时Extra列会显示Using where，表示存储引擎先根据name='张三'找到所有符合条件的索引记录，然后全部回表，再在服务层过滤age>22的条件。
3. 两种情况的对比
开启ICP时，存储引擎在遍历idx_name_age索引时，找到name='张三'的索引记录后，会先判断age>22的条件，只有age=25的那条记录符合条件，才会回表查询；而关闭ICP时，name='张三'的两条记录（age=20和age=25）都会回表，再在服务层过滤掉age=20的记录，显然开启ICP的回表次数更少，效率更高。
如何控制索引下推
MySQL提供了系统变量来控制索引下推的开关，默认是开启状态：
查看当前ICP状态：
SHOW VARIABLES LIKE 'optimizer_switch';，在结果中找到index_condition_pushdown字段，值为on表示开启，off表示关闭全局开启/关闭ICP：
SET GLOBAL optimizer_switch = 'index_condition_pushdown=on/off';当前会话开启/关闭ICP：
SET optimizer_switch = 'index_condition_pushdown=on/off';
一般情况下，建议保持ICP默认开启状态，除非遇到特定的查询问题需要排查，否则不需要手动关闭。
总结
索引下推是MySQL 5.6引入的非常实用的查询优化特性，它的核心优势是减少不必要的回表操作，在联合索引的多条件查询场景下能显著提升查询效率。使用时需要注意它的适用场景，只有当查询条件包含联合索引中的非最左列，且需要回表获取数据时，ICP才会生效。在实际开发中，我们可以结合执行计划中的Extra字段，判断查询是否使用了ICP，进而优化索引和查询语句，提升数据库查询性能。