MySQL 5.6引入了索引下推优化。默认开启，使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。

  • 有了索引下推优化，可以在减少回表次数
  • 在InnoDB中只针对二级索引有效

官方文档中给的例子和解释如下：

在 people_table中有一个二级索引(zipcode，lastname，firstname)，查询是SELECT * FROM people WHERE zipcode=’95054′ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;

  • 如果没有使用索引下推技术，则MySQL会通过zipcode=’95054’从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断数据是否符合条件
  • 如果使用了索引下推技术，则MYSQL首先会返回符合zipcode=’95054’的索引，然后根据lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。

Mysql5.6之前是没有索引下推这个功能，后面为了提高性能，避免不必要的回表5.6之后就有了索引下推优化的功能。

假如我们有一个用户表，并且使用用户的name，age两个字段建立联合索引，name在没有索引下推的功能，执行下面的sql，执行的流程如下图所示：

1
select * from tuser where name like '张%' and age=10 and ismale=1;

图片

当比较第一个索引字段name like ‘张%’ 就会筛选出四行数据，后面它不会再比较age值是否符合要求，直接获取到主键值，然后在回表查询，回表后再对比age、ismale是否符合条件。

从上面的数据看来其实name，age两个字段建立的联合索引，两个字段的值会存储在联合索引树中，可以直接对比age字段是否符合查询的条件age=10，那么索引下推就是做了这些事：

图片

索引下推会再次根据你的age进行比较，发现有两条记录不符合条件直接过滤掉，符合条件的才会进行回表查询，这样就减少了不必要的回表查询。