覆盖索引 & 索引条件下推

先建一张表，后面会用到：

create table index_demo (
    c1 int,
    c2 varchar(10),
    c3 int,
    PRIMARY KEY (c1),
    key idx_key (c2)
) row_format=compact;

覆盖索引

当执行一条查询语句时，MySQL 会选择最优的执行方案，无非就是选择全表扫描还是使用某个索引执行

如果选择某个索引执行，当查询列表中包含索引列之外的字段时，查询到结果后还需要根据主键进行一次回表操作，在聚簇索引中查询到完整用户记录

覆盖索引：如果查询列表中刚好只包含索引列，那么就可以不需要回表，直接在该索引中就可以获取所有字段

如果执行下面查询：

select * from index_demo where c2 = 'a';

MySQL 选择使用 idx_key 索引查询，而且需要回表将完整用户记录查询出来并返回给客户端。下面从底层介绍查询流程：

• server 层调用存储引擎接口定位到满足c2 = 'a'条件的第一条二级索引记录

• 存储引擎根据 B+ 树索引快速定位到这条二级索引记录后，根据该二级索引记录的主键进行回表操作，将完整用户数据返回给 server 层

• server 层会将该条记录返回给客户端，然后向存储引擎要下一条记录

• 存储引擎通过 next_record 属性可以快速定位到下一条数据，然后根据该二级索引记录的主键进行回表操作，将完整用户数据返回给 server 层。重复重复上个步骤，直至扫描完区间['a', 'a']

如果执行下面查询：

select c2 from index_demo where c2 = 'a';

其实流程和上面差不多，区别就在于第二个步骤，存储引擎根据 B+ 树索引快速定位位置后，直接返回给 server 层，不需要回表，因为索引列包含了查询列表

注意：回表操作由存储引擎完成；判断条件是否满足要求由 server 层完成

索引条件下推

如果执行下面查询：

select * from index_demo where c2 >= 'a' and c2 like '%b';

首先 MySQL 有两种执行方案的选择，第一种可以像上面那样：

• server 层调用存储引擎接口定位到满足c2 >= 'a'条件的第一条二级索引记录

• 存储引擎根据 B+ 树索引快速定位到这条二级索引记录后，根据该二级索引记录的主键进行回表操作，将完整用户数据返回给 server 层

• server 层再判断其它的搜索条件是否满足，如果满足则将该条记录返回给客户端，否则跳过该记录，然后向存储引擎要下一条记录

• 存储引擎通过 next_record 属性可以快速定位到下一条数据，然后根据该二级索引记录的主键进行回表操作，将完整用户数据返回给 server 层。重复重复上个步骤，直至扫描完区间['a', +∞]

总结：在该方案中，存储引擎只负责根据 server 层传来的条件定位记录，然后回表后返回完整记录，不会做任何判断

如果一条记录c2 = 'a'，它不满足第二个条件，但存储引擎依旧会回表交由 server 层判断，这无疑是增加了开销，因为 c2 也属于索引列，完全可以不需要回表，直接由存储引擎判断c2 = 'a'是一条不满足搜索条件的记录

下面要介绍的第二种方案就是增加了该优化：

• server 层调用存储引擎接口定位到满足c2 >= 'a'条件的第一条二级索引记录

• 存储引擎根据 B+ 树索引快速定位到这条二级索引记录后，不着急回表，而是判断c2 >= 'a' and c2 like '%b'是否成立，如果成立再回表将完整数据返回给 server 层

• server 层再判断其它的搜索条件是否满足 (本句查询没有其他条件需要判断)，如果满足则将该条记录返回给客户端，否则跳过该记录，然后向存储引擎要下一条记录

• 存储引擎通过 next_record 属性可以快速定位到下一条数据，然后重复步骤 2，将完整用户数据返回给 server 层。重复重复步骤 3，直至扫描完区间['a', +∞]

可以看出第二种方案可以减少回表的次数，而且回表也是开销很大的操作，这样可以降低查询语句执行的成本

总结：在该方案中，存储引擎即负责根据 server 层传来的条件定位记录，也负责判断含有索引列的条件，只有满足条件后才回表给 server 层返回完整记录

第二种方案也被称之为索引条件下推，将含有索引列的条件下推给存储引擎判断，可以减少回表次数 (注意：下推的是条件)

注意：索引条件下推仅适用于select语句，不适用于update和delete这些需要修改记录的语句

我们通过explain命令看看上面查询语句的执行计划：

mysql> explain select * from index_demo where c2 >= 'a' and c2 like '%b';
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | index_demo | NULL       | range | idx_key       | idx_key | 33      | NULL |    2 |   100.00 | Using index condition |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+

从执行计划中可以看出使用 idx_key 索引执行，Using index condition 表示使用了索引条件下推

下面再来看一条查询语句的执行计划：

mysql> explain select c2 from index_demo where c2 >= 'a' and c2 like '%b';
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | index_demo | NULL       | index | idx_key       | idx_key | 33      | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+

这条查询和上一条查询唯一的区别就在于查询列表，本条的查询列表只包含索引列，所以可以使用覆盖索引。Using where 表示有搜索条件在 server 层判断；Using index 表示使用覆盖索引执行查询

从执行计划可以看出本查询并没有使用索引条件下推，而是使用覆盖索引，而且存储引擎并没有判断搜索条件c2 like '%b'，而是由 server 层判断

虽然我个人感觉如果c2 >= 'a' and c2 like '%b'都交给存储引擎判断，满足要求后才返回给 server 层会更快，但是 MySQL 好像没有这么做，其实不需要回表这两种判断方法相差也不多！