MENU

MySQL 什么是覆盖索引

2021 年 07 月 28 日 • 阅读: 504 • 编码,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表示使用了索引覆盖。


参考文章:

最后编辑于: 2021 年 09 月 12 日