对线面试官 - 如何理解 MySQL 的索引覆盖和索引下推

面试官：了解 MySQL 的索引吧？

派大星：是的，有了解。

面试官：那你能简单聊聊是什么 MySQL 的覆盖索引吗？

派大星：可以。

覆盖索引，也就是covering index。指的是一个查询语句的执行只用从索引中就能获取到目标数据，不必从数据表中读取。因此也可称之为实现了索引覆盖。

当我们执行一条查询语句符合覆盖索引时，MySQL 只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回查表操作，减少 I/O 并提高了效率。

比如：我们有一张表covering_tabel，其中有一个普通索引 idx_key1_key2(key1, key2)。当我们执行 SQLselect key1 from covering_table where key1 = "ketvalue"的时候，此时其实就 i 是通过了覆盖索引进行查询，无需回表。

但是在使用过程中要注意的是：有两种情况是不满足的：

1. sql 的 where 条件不符合最左前缀匹配原则

2. SQL 查询的字段不属于联合索引

比如如果 sql 不符合最左前缀匹配，即使是索引覆盖也是无法使用到索引的（会扫描索引树），比如这个 SQLselect key1 from covering_table where key2 = "keyvalue"

要是 SQL 中的查询字段也没有包含在联合索引中，其实也是不会走索引覆盖的。比如：select key2, key3 from covering_table where key1 = "keyvalue"

面试官：嗯，理解可以，那你知道什么是索引下推吗？

派大星：有了解，索引下推是 MySQL 在 5.6 中引入的一种优化技术，默认是开启状态的。当然也可以通过set optimizer_switch = index_condition_pushdown = off进行关闭。

官方文档中大致解释如下：

• 假设有一个 people 表中的(zipcode、lastname、firstname)构成一个索引。

SELECT * FROM people  WHERE zipcode='95054'  AND lastname LIKE '%etrunia%'  AND address LIKE '%Main Street%';

如果要是上述 SQL 在没有使用索引下推技术，则 MySQL 会通过 zipcode='95054' 从存储引擎中查询对应的数据，返回到 MySQL 服务端，然后 MySQL 服务端基于 lastname LIKE '%etrunia%' 和 address LIKE '%Main Street%'; 来判断是否符合条件。

但是如果使用了索引下推技术的话，MySQL 则会通过 zipcode='95054' 先返回符合条件的索引，然后根据 lastname LIKE '%etrunia%' 来判断索引是否符合条件。如果符合条件，就会根据该索引来定位对应的数据，如果不符合，则直接 reject 掉，有了索引下推的优化，可以在 like 条件查询的情况下，减少回表的次数。

需要注意的是：当一条 SQL 使用到了索引下推时，那么 explain 的执行计划中的 extra 字段对应的内容为：Using index condition。

这个具体可以参考官方文档：

https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html

如图：

面试官：挺好。那你觉得索引下推只是在 Like 的情况下吗？官方其实是只提到了 Like，这里你有什么想法吗？

派大星：其实，我个人认为在上面的例子以及官网中都是只提到了 like，但其实不知有 like。因为我认为索引下推其实是解决索引失效带来的效率低的问题的一种手段。

所以在联合索引中，由于某个前导列因为索引失效而要进行扫表并回表时，就可以进行索引下推优化了。

比如：有联合索引 a，b。类型都是 varchar，下面这个 SQL 也是可以用到索引下推的。

select d from t where a = "test" and b = 1;

因为上述 SQL 的字段类型不匹配导致索引失效，但是通过索引下推优化其实是可以减少回表的次数的。

面试官：不错那你知道什么是回表，怎么减少回表的次数吗？

派大星：这个了解一些。在 InnoDB 里，索引 B+ Tree 的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。而索引 B+ Tree 的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引。

在存储的数据方面，主键（聚簇）索引的 B+树的叶子节点直接就是我们要查询的整行数据了。而非主键（非聚簇）索引的叶子节点是主键的值。

那么，当我们根据非聚簇索引查询的时候，会先通过非聚簇索引查到主键的值，之后，还需要再通过主键的值再进行一次查询才能得到我们要查询的数据。而这个过程就叫做回表。

所以，在 InnoDB 中，使用主键查询的时候，是效率更高的， 因为这个过程不需要回表。另外，依赖覆盖索引、索引下推等技术，我们也可以通过优化索引结构以及 SQL 语句减少回表的次数。

面试官：嗯，理解的十分透彻。有想法。

派大星：谢谢。