MySQL回表、索引覆盖、索引下推

准备

创建一张表，并创建一个自增主键索引和一个组合索引

CREATE TABLE index_opt_test (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(11) DEFAULT NULL,
  title varchar(11) DEFAULT NULL,
  age int(11) DEFAULT NULL,
  sex varchar(11) DEFAULT NULL,
  content varchar(500) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_cb (name,title,age)
) ENGINE=InnoDB;

回表

1. 什么是回表

   回表是发生在二级索引上的一种数据查询操作，简单点讲就是我们要查询的列不在二级索引的列中，那么就必须根据二级索引查到主键ID，然后再根据主键ID到聚簇索引树上去查询整行的数据，这一过程就叫作回表。

2. 为什么会回表

   写一个会回表查询的SQL：

   select id, name, age, sex from index_opt_test where name='cc' and title='T7' and age=35;
   

   解析：

   ​ SQL需要查询的列包括id、name、age、sex，查询条件命中索引idx_cb，其中列id、name、age都在索引idx_cb中可以获取到，但是sex不能通过索引获取到，必须要获取到整行数据之后再从结果中捞出来sex列的数据，这种情况就必须要回表。

3. 什么情况下不需要回表

   当所有的列都能在二级索引树中查询到，就不需要再回表了，这种情况就是索引覆盖。

索引覆盖

1. 什么是索引覆盖

   当SQL语句中查询的列都在索引中时，我们就不需要回表去把整行数据都捞出来了，可以从非聚簇索引树中直接获取到我们需要的列的数据，这就叫索引覆盖。简单点来讲就是：所有不需要回表的查询操作都叫索引覆盖。

2. 为什么会发生索引覆盖

   关于为什么会发生索引覆盖这个问题，通过一条SQL来理解：

   select id, name, age from index_opt_test where name='cc' and title='T7';
   

   这条SQL要查询的列id、name、age全部都能从非聚簇索引idx_cb中直接查询出来，可能会有个疑问：我们的索引列是name、title和age，为什么id明明不在组合索引中却还能发生索引覆盖？提出这个问题的同学真的是欠我一顿小烧烤，非聚簇索引的叶子节点里存的是什么东西：主键的关键字啊，我们这里主键是id，他的关键字就是id的值啊，那我们通过非聚簇索引是不是可以直接将主键id查出来，是不是就不用再回表了，不用回表是不是就发生了索引覆盖啊，就是那么简单。

索引下推

1. 什么是索引下推

   索引下推又叫索引条件下推(Index Condition Pushdown，简称ICP)，ICP默认是开启的，使用ICP可以减少存储引擎访问基础表的次数和Server访问存储引擎的次数。

   • ICP没有启用：Server层会根据索引的断桥原则将命中的索引字段推送到引擎层获取数据，并把匹配到的数据全部返回到Server层，由Server层再根据剩余的where条件进行过滤，即使where条件中有组合索引的其他未命中的字段，也会保留在Server层做筛选，然后返回给Client

     select id, name, sex from index_opt_test where name='cc' and title like '%7' and sex='male';
     

     执行过程：

     1. Server层把name推到引擎层

        1. 引擎层根据name去idx_cb的索引树中匹配主键
        2. 回表去捞数据返回给Server层
        3. Server层再根据title、sex筛选出最终的数据
        4. 最后返回给客户端

     

   • ICP启用：Server层会将where条件中在组合索引中的字段全部推送到引擎层，引擎层根据断桥原则匹配出索引数据，然后将其他索引字段带入再进行一次筛选，然后拿最终匹配的主键关键字回表查询出数据后返回给Server层，Server层再根据剩余的where条件做一次筛选，然后返回给Client

     select id, name, sex from index_opt_test where name='cc' and title like '%7' and sex='male';
     

     执行过程：

     1. Server把name和title都推到引擎层

        1. 引擎层根据name去idx_cb中查询出主键关键字和title、age
        2. 再由title筛选出匹配的主键关键字
        3. 回表去捞数据返回给Server层
        4. Server层再根据sex筛选出最终的数据
        5. 再返回给客户端

     

2. 索引下推适用条件

   • ICP 用于访问方法是 range/ref/eq_ref/ref_or_null，且需要访问表的完整行记录。
   • ICP适用于 InnoDB 和 MyISAM 的表，包括分区的表。
   • 对于 InnoDB 表，ICP只适用于二级索引。ICP 的目标是减少访问表的完整行的读数量从而减少 I/O 操作。对于 InnoDB 的聚簇索引，完整的记录已经读进 InnoDB 的缓存，使用 ICP 不能减少 I/O 。
   • ICP 不支持建立在虚拟列上的二级索引（InnoDB 支持在虚拟列上建立二级索引）。
   • 引用子查询、存储函数的条件没法下推，Triggered conditions 也没法下推。

   所以ICP 适用的一个隐含前提是二级索引必须是组合索引、且在使用索引进行扫描时只能采用最左前缀匹配原则。组合索引后面的列出现在 where 条件里，因此可以先过滤索引元组、从而减少回表读的数量。

3. 为什么会发生索引下推

   索引下推在5.6版本加入的，默认开启，可以通过命令SHOW VARIABLES like '%optimizer_switch%'查看当前状态

   

   • 关闭索引下推

     SET optimizer_switch = 'index_condition_pushdown=off';
     

     执行之后查看一下状态：

     

     然后我们执行一下SQL语句：

     explain
     select id, name, sex from index_opt_test where name='cc' and title like '%7' and sex='male';
     

     

     从执行计划我们可以看出当我们关闭索引下推后，Extra中的是Using where

   • 开启索引下推

     SET optimizer_switch = 'index_condition_pushdown=on';
     

     执行之后查看一下状态：

     

     然后我们执行以下SQL语句：

     explain
     select id, name, sex from index_opt_test where name='cc' and title like '%7' and sex='male';
     

     

     从执行计划中看到使用了Using index condition和Using where，Using index condition说明ICP生效了，title被推到了引擎层，而Using where是因为where条件中的sex字段