什么是覆盖索引

前言

要搞明白覆盖索引首先就得明白主键索引和辅助索引的区别,以及查询时引擎的工作方式。

当然,以上都是基于innoDB引擎来说。

主键索引

叶子节点保存数据,

辅助索引

叶子节点保存主键值

查询一条数据是如何工作的呢

由于辅助索引只存储主键的值,如果使用辅助索引搜索数据就必须先从辅助索引取到主键的值,再使用主键的值去主键索引上查询,直到找到叶子节点上的数据返回。  ----  这个也称之为"回表"

那么如何避免回表查询的发生呢?

如果辅助索引上已经存在我们需要的数据,那么引擎就不会去主键上去搜索数据了。 ---- 这个就是所谓的"覆盖索引"

概念

如果一个索引覆盖所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道在InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键和列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢。而覆盖索引就是把要查询出的列和索引是对应的，不做回表操作。

举例

例如 user 上有 id name age email address 四个字段其中 name age email 三个字段是复合索引 id(主键索引忽略不看)

那么 select name age email from user where email = '123@qq.com'

这样的查询就叫覆盖索引, 就是要查询的内容与建立索引的个数顺序相等

验证

CREATE TABLEuser(idint(11) unsigned NOT NULL AUTO_INCREMENT,namevarchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,ageint(11) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (id), KEYidx_name_age(name,age) USING BTREE ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

生成测试数据

delimiter $$ DROP PROCEDURE IF EXISTS proc_batch_insert; CREATE PROCEDURE proc_batch_insert() BEGIN DECLARE pre_name BIGINT; DECLARE ageVal INT; DECLARE i INT; SET ageVal=100; SET i=1; WHILE i < 100000 DO INSERT INTO user(name`,age) VALUES(substring(MD5(RAND()),1,8),(ageVal+i)%30);
SET pre_name=pre_name+100;
SET i=i+1;
END WHILE;
END $$

delimiter ;
call proc_batch_insert();`

查询数据

select * from user where age = 10

查看耗时

图片描述

分析一下语句

   explain select * from user where age = 20

查看执行计划

图片描述

这样的查询速度快吗?

只需要稍微改变一下查询的字段, 我们就发现其中的区别了。

select age,name from user where age = 20

查看一下耗时

图片描述

可以看到耗时减少了!

发生了什么呢,我们再来分析一下语句

图片描述

可以看到extra列有一个 using idnex , 这个的意思就是使用了覆盖索引,无需回表查询了。

总结
实践是检验原理的唯一标准。通过此次实践,想必你已经充分了解并且体验到覆盖索引的概念及其意义了。其核心就是只从辅助索引要数据。那么, 普通索引(单字段)和联合索引,以及唯一索引都能实现覆盖索引的作用。

最后编辑于：2021.12.21 21:25:30