MySQL--对线面试官 - 如何理解MySQL的索引覆盖和索引下推--其乐融融的IT技术小站

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

派大星：是的，有了解。

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

派大星：可以。

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

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

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

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

sql的where条件不符合最左前缀匹配原则
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语句减少回表的次数。

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

派大星：谢谢。

相关推荐