关于索引覆盖与索引下推

索引覆盖

• 覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。
• 当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。
• 如，表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。
  • 当我们通过SQL语句：select key2 from covering_index_sample where key1 = ‘keytest’;的时候，就可以通过覆盖索引查询，无需回表。
  • 但是以下SQL，因为不符合最左前缀匹配，虽然是索引覆盖，但是也无法用到索引（会扫描索引树）：select key1 from covering_index_sample where key2 = ‘keytest’;
  • 但是如果SQL中查询的信息不包含在联合索引中，那么就不会走索引覆盖。如：select key2,key3 from covering_index_sample where key1 = ‘keytest’；
  • 例外：MySQL 8.0.13 版本中，对于range查询（什么是range后面会提到)，引入了索引跳跃扫描（Index Skip Scan）优化，支持不符合组合索引最左前缀原则条件下的SQL，依然能够使用组合索引，减少不必要的扫描。
    • 限制：这种查询优化比较适合于f1的取值范围比较少，区分度不高的情况，一旦f1的区分度特别高的话，这种查询可能会更慢。真正要不要走索引跳跃扫描，还是要经过MySQL的优化器进行成本预估之后做决定的。****
    • 但是话又说回来了，我们一般不太会把区分度不高的字段放在联合索引的左边，不过事无绝对，既然MySQL给了一个优化的方案，就说明还是有这样的诉求的。

索引下推

• 索引下推是 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%’;

• 如果没有使用索引下推技术，则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

• 减少回表次数