innodb的索引下推

索引下推，是mysql优化联合索引查询的一种方案，叫做索引下推不如翻译为索引条件下推更合适（Index Condition Pushdown）简称ICP，因为他实际上是把where中的查询索引条件，下推给了存储引擎

本文涉及到的内容有：

• 回表
• 索引下推
• 索引覆盖

预备知识：

• b+tree
• 主键索引和非主键索引
• 聚簇索引和非聚簇索引

主键索引和非主键索引

说到回表，我们先回顾一下innodb主键索引和非主键索引（辅助索引）的区别（具体的之后写一篇文章来讲解，这里只是简单带过）。

他们数据存储的方式是一样的，但是由于innodb使用的辅助索引的叶子节点上只有主键（不是数据指针哦），如果需要更多列数据，查找就需要分成了两步。

1. 第一步在辅助索引B+树中检索，到达其叶子节点获取对应的主键。
2. 第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

当我们使用辅助索引检索数据的时候，会有两种情况

• 当想要查询的字段（列）并不都包含在索引中的时候，一定会涉及到回表取数据，即拿到辅助索引叶子节点上的主键再去主键索引上检索取到全部数据。这个过程叫做回表
• 当sql语句的所求查询字段（select列）和查询条件字段（where子句）全都包含在一个索引中，可以直接使用索引查询而不需要回表。这就是覆盖索引,可以减少一次树的检索，是常用的性能优化手段。

索引下推为了解决什么问题

对低于5.0的mysql来说，只能使用单个索引来筛选数据，从5.1开始，引入了 index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描，但是和全表扫描或只使用一个索引的速度比起来，去分析两个索引二叉树可能更加耗费时间，所以绝大多数情况下数据库都是是用一个索引。

在这个前提下，当一个索引选择度（就是通过索引能过滤掉的数据比例）比较低的时候，可能单个索引过滤掉的数据并没有太多。所以我们一般会使用联合索引。但是联合索引又受最左前缀影响。有时候会不起作用，所以才做出了索引下推这个功能。（最左前缀具体请参看上一篇文章：https://blog.csdn.net/winterfeng123/article/details/108150223）

我们来看一下有索引下推和没有索引下推，在一条sql执行过程中的区别。

sql执行一般经过三层

• mysql server
• 接口层
• 存储和事务管理层
  由于在索引下推过程中接口层没有什么实际操作，所以简略书写一下。下面分别说一下两种情况的流程

不使用索引下推

1. MySQL Server发出读取数据的命令，通过函数指针和handle接口（接口层）调用存储引擎的索引读或全表表读。如果有可使用的索引则进行的是索引读，但是只能选择一个索引。
2. 指令进入存储引擎，读取索引树，在索引树上查找，拿到到符合条件的主键值，回表把满足条件的记录从表记录中读出，从存储引擎返回标识的结果
3. 从存储引擎返回查找到的多条元组给MySQL Server，MySQL Server在得到单一索引过滤后的数据
4. MySQL Server拿到上述数据后，按照where中其他的条件进行过滤，得到符合条件的最终结果。

使用索引下推

首先看上面的我们已经大概知道，如果就只有一列索引，是无所谓有没有索引下推这个功能的，所以我们只讨论存在多个where条件，这些条件中的多个列上有索引的情况。

1. 同上，MySQL Server发出读取数据的命令，通过函数指针和handle接口（接口层）调用存储引擎的索引读或全表表读。如果有可使用的索引则进行的是索引读，但是这里会把含有索引的列的where条件都下放到存储引擎层。
2. 指令进入存储引擎，读取某个索引树，在该索引树上查找过滤出相应数据后，并不直接去回表查询数据，而是继续通过联合索引的其他索引条件进行过滤把满足已经下推的条件的主键拿到。然后再去回表查询所有符合条件的数据（数据较少，io也就较少）。例如

#假test表有个联合索引包含两列 a，b。

假如语句如下：
select * from test where a like "zhang%" and b > 10;
两个条件都会下推到存储引擎，因为最左前缀，会先按照a来筛选。其次再使用b来过滤筛选过的索引值。然后再回表查询。

3. 从存储引擎返回查找到的少量数据给MySQL Server，MySQL Server在根据其他的条件进行筛选。

对比

• 第 1 步中MySQL Server发送了额外的索引条件到存储引擎，多了一点点网络开销
• 第 2 步中的回表阶段：由于比第一步过滤掉了更多的表数据，所以少了很多磁盘io
• 第 3 步中

接收数据阶段：要接收更多的数据，增加内部开销

二次过滤数据阶段：要处理更多的数据，占用了更多的cpu和内存。

总结：

总体来说，在有联合索引的情况下，索引下推使得查询的效率有明显提升。但是也不是任何时候都可以使用索引下推的，因为其主要通过减少了回表查询的数量来优化查询，所以其限制如下：

1. innodb引擎的表，索引下推只能用于辅助索引，这是因为主键索引树叶子结点上保存的是全行数据，根本不涉及到回表问题。

2. 索引下推一般可用于非索引覆盖的情况下，因为索引覆盖也是没有必要回表的。

优点如下：

1. 提高了有联合索引时的查询效率
2. 一定程度上打破了联合索引的最左前缀原则，详情请看 https://blog.csdn.net/winterfeng123/article/details/108150223

索引下放也可以关闭，但是不建议管理。关闭和开启语句如下：

#关闭
SET optimizer_switch = 'use_index_extensions=off';
#开启
SET optimizer_switch = 'use_index_extensions=on';