概述
一个索引包含了所有需要查询的字段值，那么就称为覆盖索引。
好处
- 索引的大小通常远小于数据行大小，所以如果只需要读取索引，那么MySQL会极大的减少数据访问量。
- 索引是按照值得顺序存储的。
- InnoDB使用聚集索引，也称为First Index，InnoDB的二级索引叶子节点中保存了行的主键值，所以如果二级索引能够覆盖查询，那么可以避免对主键索引的二次查询。
并不是所有类型的索引都是称为覆盖索引。覆盖索引必须要存储列的值，所以哈希索引、空间索引和全文索引这三类不存储列值得索引都不能作为覆盖索引，所以MySQL中只能使用B+tree索引可以做覆盖索引。
当查询使用了覆盖索引，可以在EXPLAIN的Extra列看到"Using index"的信息。
实验
基于MySQL 8.0.x
表结构和索引情况：
MySQL [employees]> desc sys_user;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| name_pinyin | varchar(255) | NO | | NULL | |
| id_card | varchar(255) | NO | UNI | NULL | |
| phone | varchar(20) | YES | MUL | NULL | |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.07 sec)
MySQL [employees]> show indexes from sys_user;
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------------------+---------+------------+
| sys_user | 0 | PRIMARY | 1 | id | A | 3494 | NULL | NULL | | BTREE | | | YES | NULL |
| sys_user | 0 | uni_idx_id_card | 1 | id_card | A | 3494 | NULL | NULL | | BTREE | | 唯一索引-身份证号 | YES | NULL |
| sys_user | 1 | idx_phone_name | 1 | phone | A | 3493 | NULL | NULL | YES | BTREE | | 普通索引-手机号 | YES | NULL |
| sys_user | 1 | idx_phone_name | 2 | name | A | 3493 | NULL | NULL | | BTREE | | 普通索引-手机号 | YES | NULL |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------------------+---------+------------+
4 rows in set (1.54 sec)
覆盖索引查询
- 从sys_user表中查询手机号和姓名
explain select phone,name from sys_user;EXPLAIN 输出结果：
MySQL [employees]> explain select phone,name from sys_user;
+----+-------------+----------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | sys_user | NULL | index | NULL | idx_phone_name | 125 | NULL | 3494 | 100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
- 从sys_user表中查询phone,name和id
explain select id, phone,name from sys_user;EXPLAIN结果输出：
MySQL [employees]> explain select id, phone,name from sys_user;
+----+-------------+----------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | sys_user | NULL | index | NULL | idx_phone_name | 125 | NULL | 3494 | 100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
参考
- 高性能MySQL（第三版）
