MySQL索引下推知识
Contents
什么是索引下推
索引下推 (Index Condition Pushdown，索引条件下推，简称 ICP)，是 MySQL5.6 版本的新特性，它可以在对联合索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，能有效的减少回表次数。
适用条件
索引下推的使用条件及限制：
-
只支持 select。
-
当需要访问全表时，ICP 用于 range，ref，eq_ref 和 ref_or_null 访问类型。
-
ICP 可用于 InnoDB 和 MyISAM 表，包括分区的 InnoDB 和 MyISAM 表。（5.6 版本不适用分区表查询，5.7 版本后可以用于分区表查询）。
-
对于 InnDB 引擎只适用于二级索引（也叫辅助索引），因为 InnDB 的聚簇索引会将整行数据读到 InnDB 的缓冲区，这样一来索引条件下推的主要目的减少 IO 次数就失去了意义。因为数据已经在内存中了，不再需要去读取了。
-
在虚拟生成列上创建的辅助索引不支持 ICP（注：InnoDB 支持虚拟生成列的辅助索引）。
-
使用了子查询的条件无法下推。
-
使用存储过程或函数的条件无法下推（因为因为存储引擎没有调用存储过程或函数的能力）。
-
触发条件无法下推。（有关触发条件的信息，请参阅官方资料：Section 8.2.2.3, “Optimizing Subqueries with the EXISTS Strategy”.。）
原理介绍
MySQL 服务层主要负责 SQL 语法解析、生成执行计划等，并调用存储引擎层去执行数据的存储和查询。 索引下推的下推其含义就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。 在 MySql 5.6 版本之前没有索引下推这个功能，从 5.6 版本后才加上了这个优化项。
简单对比一下使用和未使用 ICP 两种情况下，MySql 的查询过程。
- 未使用 ICP 的情况下：
- 存储引擎读取索引记录；
- 根据索引中的主键值，定位并读取完整的行记录；
- 存储引擎把记录交给 Server 层去检测该记录是否满足 WHERE 条件。
- 使用 ICP 的情况下：
- 存储引擎读取索引记录（不是完整的行记录）；
- 判断 WHERE 条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；
- 条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
- 存储引擎把记录交给 Server 层，Server 层检测该记录是否满足 WHERE 条件的其余部分。
控制参数
在explain执行计划中，看到 Extra 一列里 Using index condition，就是用到了索引下推。
Mysql 索引下推功能默认是开启的，可以用系统参数 optimizer_switch 来控制是否开启。
查看状态命令：
select @@optimizer_switch;
关闭命令：set optimizer_switch="index_condition_pushdown=off";
开启命令：set optimizer_switch="index_condition_pushdown=on";
总结
回表操作：当所要查找的字段不在非主键索引树上时，需要通过叶子节点的主键值去主键索引上获取对应的行数据，这个过程称为回表操作。
索引下推：索引下推主要是减少了不必要的回表操作。对于查找出来的数据，先过滤掉不符合条件的，其余的再去主键索引树上查找。