MySQL中的索引下推

转载自：https://baijiahao.baidu.com/s?id=1664831035473859908&wfr=spider&for=pc

简单讲解

索引下推用一句话总结是：索引下推是数据库检索数据过程中为减少回表次数而做的优化。

首先介绍下什么是数据库回表，回表是一种数据库检索过程。通常发生在使用二级索引检索非主索引数据的过程中。举个例子：

假设有上面一张表（数据库是MYSQL，存储引擎是Innodb），上面的ID字段是主键索引，age是普通索引。

对比下面两条SQL语句：

select id from usertest where age = 10;

select name from usertest where age = 10;

第一条SQL语句不会产生回表：普通索引存储的值是主键的值。也就是说age索引里面存储的结构是下面的情况

根据age查询id的时候，索引中的值完全可以覆盖查询结果集字段时，不会产生回表操作。

由此也可以看出第二条SQL语句会产生回表是因为查询的结果集无法通过索引中的值直接获取。需要根据age查询到的id值再回到主键索引里面再次查询，这个过程叫做回表。

然后再看索引下推

还是上面的usertest表，只是索引变了，ID字段是仍主键索引，但是我们加上一个复合索引name_age(name,age)。

执行下面一条SQL语句：

select * from usertest where name like 'a%' and age = 10;

在Mysql5.6之前的执行流程是这样的：

1.根据最左前缀原则，执行name like 'a%'可以快速检索出id的值为1，5。

2.然后根据id的值进行回表操作，再次进行过滤age=10的数据。

查询id=1回表1次，id=5回表1次，这个过程总共回表了2次。

可能到这里都会有疑问，为什么不在索引里面直接过滤age=10的数据，因为复合索引里面也存了age的数据，这样明明可以减少回表1次。恭喜啦，Mysql5.6以后就这么做了，这就是索引下推。

这样可以看出，索引下推具体是在复合索引的查询中，针对特定的过滤条件而进行减少回表次数而做的优化(个人觉得本来就该这么设计的)。

深入了解

转载自：https://blog.csdn.net/sinat_29774479/article/details/103470244

概述

索引条件下推优化（Index Condition Pushdown (ICP) ）是MySQL5.6添加的，用于优化数据查询。

一条sql语句执行会先经过MYSQL服务器 再到 存储引擎，存储引擎检索出数据之后再将数据返回给MYSQL服务器，MYSQL服务器再对数据进行筛选（不明白的同学可以先去了解下）。

• 不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件。
• 当使用索引条件下推优化时，如果where条件中包含复合索引中一个以上的字段，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

适用条件

1. 需要整表扫描的情况。比如：range, ref, eq_ref, ref_or_null 。适用于InnoDB 引擎和 MyISAM 引擎的查询。（5.6版本不适用分区表查询，5.7版本后可以用于分区表查询）。
2. 对于InnDB引擎只适用于二级索引，因为InnDB的聚簇索引会将整行数据读到InnDB的缓冲区，这样一来索引条件下推的主要目的减少IO次数就失去了意义。因为数据已经在内存中了，不再需要去读取了。
3. 引用子查询的条件不能下推。
4. 调用存储过程的条件不能下推，存储引擎无法调用位于MySQL服务器中的存储过程。
5. 触发条件不能下推。

工作过程

不使用索引条件下推优化时的查询过程

1. 获取下一行，首先读取索引信息，然后根据索引将整行数据读取出来。
2. 然后通过where条件判断当前数据是否符合条件，符合返回数据。

使用索引条件下推优化时的查询过程

1. 获取下一行的索引信息。
2. 检查索引中存储的列信息是否符合索引条件，如果符合将整行数据读取出来，如果不符合跳过读取下一行。
3. 用剩余的判断条件，判断此行数据是否符合要求，符合要求返回数据。

EXPLAN分析

当使用explan进行分析时，如果使用了索引条件下推，Extra会显示Using index condition。并不是Using index因为并不能确定利用索引条件下推查询出的数据就是符合要求的数据，还需要通过其他的查询条件来判断。

图一：不使用ICP技术（过程使用数字符号标示，如①②③等）

过程解释：
①：MySQL Server发出读取数据的命令，这是在执行器中执行如下代码段，通过函数指针和handle接口调用存储引擎的索引读或全表表读。此处进行的是索引读。
②、③：进入存储引擎，读取索引树，在索引树上查找，把满足条件的（经过查找，红色的满足）从表记录中读出（步骤④，通常有IO），从存储引擎返回⑤标识的结果。此处，不仅要在索引行进行索引读取（通常是内存中，速度快。步骤③），还要进行进行步骤④，通常有IO。
⑥：从存储引擎返回查找到的多条元组给MySQL Server，MySQL Server在⑦得到较多的元组。
⑦–⑧：⑦到⑧依据WHERE子句条件进行过滤，得到满足条件的元组。注意在MySQL Server层得到较多元组，然后才过滤，最终得到的是少量的、符合条件的元组。

使用ICP技术（过程使用数字符号标示，如①②③等）

过程解释：
①：MySQL Server发出读取数据的命令，过程同图一。
②、③：进入存储引擎，读取索引树，在索引树上查找，把满足已经下推的条件的（经过查找，红色的满足）从表记录中读出（步骤④，通常有IO），从存储引擎返回⑤标识的结果。

此处，不仅要在索引行进行索引读取（通常是内存中，速度快。步骤③），还要在③这个阶段依据下推的条件进行进行判断，不满足条件的，不去读取表中的数据，直接在索引树上进行下一个索引项的判断，直到有满足条件的，才进行步骤④，这样，较没有ICP的方式，IO量减少。
⑥：从存储引擎返回查找到的少量元组给MySQL Server，MySQL Server在⑦得到少量的元组。因此比较图一无ICP的方式，返回给MySQL Server层的即是少量的、符合条件的元组。
另外，图中的部件层次关系，不再进行解释。

示例

假设有一张people表,包含字段name、address、first_name
索引为(name,address,first_name)
然后我们执行下面的查询

SELECT * FROM person  WHERE `name` = "1" AND `address` LIKE "%222" and first_name LIKE "%222";

如果不使用索引条件下推优化的话，MySQL只能根据索引查询出name=1的所有行，然后再依次比较是否符合全部条件。

当使用了索引条件下推优化技术后，可以通过索引中存储的数据判断当前索引对应的数据是否符合条件，只有符合条件的数据才将整行数据查询出来。查看执行计划时发现extra一栏中有Using index condition信息，说明使用了索引下推。

配置

索引下推优化是默认开启的。可以通过下面的脚本控制开关

SET optimizer_switch = 'index_condition_pushdown=off'; 
SET optimizer_switch = 'index_condition_pushdown=on';