MySQL索引下推

一、概念

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于提高查询效率。
在MySQL5.6之前的版本，使用非主键索引进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL Server 层，由其查询主键索引（聚簇索引），判断数据是否符合查询条件。
Mysql5.6推出索引下推后，使用非主键索引进行查询时，存储引擎通过索引检索到数据，并根据索引包含的字段对数据再进行一次筛选，再返回给MySQL Server 层，由其最终判断数据是否符合查询条件，可减少查询主键索引（聚簇索引）的次数。

二、示例

/* 开启索引下推优化，默认开启 */
SET optimizer_switch = 'index_condition_pushdown=on';

/* 关闭索引下推优化 */
SET optimizer_switch = 'index_condition_pushdown=off';

假定emp员工表中存在如下数据记录：

id	name	job	hiredate	sal_grade	age
1	huatRb	开发	2021-03-18	5	70
2	hueNCv	开发	2021-06-07	5	60
3	hueNKC	产品	2021-06-07	5	70
4	huANCD	开发	2021-06-07	5	70

且已创建联合索引index(name, age)，执行SQL语句select * from emp where name like "hu%" and age = 70 and job = '开发';

在关闭索引下推优化的情况下，由于全部数据记录的name字段都符合like 'hu%'，数据库引擎会将编号为1、2、3、4总共四条数据全部返回给MySQL Server层，再由其进行进一步筛选。
在开启索引下推优化的情况下，虽然全部数据记录的name字段都符合like 'hu%'，但由于联合索引还有age字段可用于初步过滤数据，数据库引擎会将编号为2，age字段不符合的数据记录过滤掉（若联合索引有job字段，则还可过滤编号为3的数数据记录），只返回编号为1、3、4的数据给MySQL Server层，再由其进行最后的筛选。

三、实践

1、在开启索引下推优化的情况下使用explain查看执行计划
explain select * from emp where name like "hu%" and age = 70 and job = '开发';

2、在关闭索引下推优化的情况下使用explain查看执行计划

我们可以看到在开启索引下推优化的情况下，Extra的值是Using index condition，表明数据库引擎会使用索引来进一步过滤数据。而在关闭索引下推优化的情况下，Extra的值是Using where，表明数据库引擎直接将筛选数据的任务交给了MySQL Server层，由其回表查询后再统一过滤数据，这增加了回表的次数，效率较低。