定义
如果一个索引包含(或者说覆盖)所有查询字段所需要的值,称之为覆盖索引,则只需要扫描索引而不需要回表。
回表:指从辅助索引(也称二级索引)查到主键值后,再去查主键索引(一级索引)然后才拿到数据。
需要扫描两次 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