vlambda博客
学习文章列表

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

写一个会回表查询的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列的数据，这种情况就必须要回表。

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

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

关于为什么会发生索引覆盖这个问题，通过一条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查出来，是不是就不用再回表了，不用回表是不是就发生了索引覆盖啊，就是那么简单。

索引下推又叫索引条件下推(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推到引擎层

2. 引擎层根据name去idx_cb的索引树中匹配主键

3. 回表去捞数据返回给Server层

4. Server层再根据title、sex筛选出最终的数据

5. 最后返回给客户端

• 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都推到引擎层

2. 引擎层根据name去idx_cb中查询出主键关键字和title、age

3. 再由title筛选出匹配的主键关键字

4. 回表去捞数据返回给Server层

5. Server层再根据sex筛选出最终的数据

6. 再返回给客户端

• 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 条件里，因此可以先过滤索引元组、从而减少回表读的数量。

索引下推在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字段