MySQL 什么是覆盖索引

定义

如果一个索引包含（或者说覆盖）所有查询字段所需要的值，称之为覆盖索引，则只需要扫描索引而不需要回表。

回表：指从辅助索引（也称二级索引）查到主键值后，再去查主键索引（一级索引）然后才拿到数据。

需要扫描两次 B+树 才能拿到数据，而覆盖索引只需要扫描一次

优点

一些存储引擎如 MyISAM 在内存中指缓存索引，数据则依赖于操作系统缓存，因此访问数据需要一次系统调用。

InnoDB 的主键是聚簇索引，覆盖索引对 InnoDB 表特别有用。（InnoDB 的辅助索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询）

注意

• 覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以 MySQL 只能用 B-tree 索引做覆盖索引。
• 使用覆盖索引需要保证会走这个索引，如果 SQL查询的时候 根本不会走这个辅助索引，自然也不会走覆盖索引。（如通配符开头的 LIKE 查询等）

使用覆盖索引

测试环境：MySQL 5.7.30 存储引擎：InnoDB （经测试MyISAM也可以使用覆盖索引）

创建测试表

mysql> show create table test_index\G;
*************************** 1. row ***************************
       Table: test_index
Create Table: CREATE TABLE `test_index` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `age` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`),
  KEY `idx_age_name` (`age`,`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> SELECT * FROM test_index;
+----+-----+--------+
| id | age | name   |
+----+-----+--------+
|  1 |  18 | 张三   |
|  2 |  19 | 王五   |
|  3 |  20 | 赵六   |
+----+-----+--------+
3 rows in set (0.00 sec)

这里创建了 idx_age_name 索引，且索引类型为 BTREE

查询所有数据

mysql> explain SELECT * FROM test_index WHERE age = 18\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_index
   partitions: NULL
         type: ref
possible_keys: idx_age_name
          key: idx_age_name
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

因为查询所有数据时，idx_age_name 索引不包含 id字段的值 所以需要 “回表”，进行二次查询，这时 Extra 表示进行了回表查询。

只查询 age,name 字段

mysql> explain SELECT age,name FROM test_index WHERE age = 18\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_index
   partitions: NULL
         type: ref
possible_keys: idx_age_name
          key: idx_age_name
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

查询字段均被 idx_age_name 覆盖，此时的Extra字段为Using index表示使用了索引覆盖。

参考文章：

• MySQL 的覆盖索引与回表 https://juejin.cn/post/6844904062329028621
• mysql覆盖索引详解 https://www.cnblogs.com/pyng/p/9599977.html
• 什么是覆盖索引 https://zhuanlan.zhihu.com/p/339666157