mysql5.6 索引下推

参考
MySQL索引下推，原来这么简单！
“MySQL5.6”、“索引优化”，其实都是索引下推

1、什么是索引下推

索引条件下推，也叫索引下推，英文全称Index Condition Pushdown，简称ICP。

索引下推是MySQL5.6新添加的特性，用于优化数据的查询。

在MySQL5.6之前，通过使用非主键索引进行查询的时候，存储引擎通过索引查询数据，然后将结果返回给MySQL server层，在server层判断是否符合条件。

在MySQL5.6及以上版本，可以使用索引下推的特性。当存在索引的列做为判断条件时，MySQL server **将这一部分判断条件传递给存储引擎 ** ，然后存储引擎会筛选出符合MySQL server传递条件的索引项，即 在存储引擎层根据索引条件过滤掉不符合条件的索引项，然后回表查询得到结果，将结果返回给MySQL server。索引下推的下推其实就是指 将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。

可以看到，有了索引下推的优化，在满足一定的条件下，存储引擎层会在回表查询之前对数据进行过滤，可以减少存储引擎回表查询的次数。

2、举例

id	name	level
1	大彬	1
2	盖聂	2
3	卫庄	3
4	大铁锤	4

假如需要匹配姓名第一个字为"大"，并且level为1的用户，SQL语句如下：

SELECT * FROM user_info WHERE name LIKE "大%" AND level = 1;

MySQL5.6以前的版本没有索引下推，其执行过程如下：

查询条件name LIKE 不是等值匹配，根据最左匹配原则，在(name, level)索引树上只用到name去匹配，查找到两条记录（id为1和4），拿到这两条记录的id分别回表查询，然后将结果返回给MySQL server，在MySQL server层进行level字段的判断。整个过程需要回表2次。

然后看看MySQL5.6及以上版本的执行过程，如下图。

相比5.6以前的版本，多了索引下推的优化，在索引遍历过程中，对索引中的字段先做判断，过滤掉不符合条件的索引项，也就是判断level是否等于1，level不为1则直接跳过。因此在(name, level)索引树只匹配一个记录，之后拿着此记录对应的id（id=1）回表查询全部数据，整个过程回表1次。

可以使用explain查看是否使用索引下推，当Extra列的值为Using index condition，则表示使用了索引下推。

3、索引下推使用条件

1、只能用于range、 ref（使用索引）、 eq_ref（多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果）、ref_or_null访问方法；
2、只能用于InnoDB和 MyISAM存储引擎及其分区表；
3、对InnoDB存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;

4、相关系统参数

索引条件下推默认是开启的，可以使用系统参数optimizer_switch来控制器是否开启。

查看默认状态：

mysql> select @@optimizer_switch\G;
*************************** 1\. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)

切换状态：

set optimizer_switch="index_condition_pushdown=off";
set optimizer_switch="index_condition_pushdown";