【MySQL优化】使用索引覆盖优化查询速度

👏大家好！我是和风coding，希望我的文章能给你带来帮助！
🔥如果感觉博主的文章还不错的话，请👍三连支持👍一下博主哦
📝点击 我的主页 还可以看到和风的其他内容噢，更多内容等你来探索！
📕欢迎参观我的个人网站：Gentlewind

---

---

在讲解索引覆盖之前，我们还需要知道什么是回表查询。

回表查询

要了解回表查询，我们首先要知道 MySQL 的索引的分类和原理。

在 MySQL 中，索引分为两大类：

• 聚簇索引
• 普通索引

关于聚簇索引：

（1）如果表定义了PK，则PK就是聚集索引；

（2）如果表没有定义PK，则第一个not NULL unique列是聚集索引；

（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

区别：在索引创建的 B+树的叶子节点中，聚簇索引储存的是整个行记录，而普通索引 仅储存索引值和对应的聚簇索引值

举个例子：

假如有表 t：

t(id PK, name KEY, sex, flag);
// id是聚集索引，name是普通索引。

表中有四条行记录：

1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B

那么两个索引创建的 B+树就如上图所示：

可以看到：

1. id 为聚簇索引，叶子节点储存了行记录
2. name 为普通索引，叶子结点存储聚簇索引的值和索引值

因此：既然使用普通索引是无法直接找到行记录的，那么普通索引如何查询除聚簇索引和索引值以外的字段呢？这使用到了回表查询

**回表查询：**也就是扫描两遍索引树，先通过普通索引找到对应的聚簇索引值，再去聚簇索引树查询行记录中的其他字段。

例如：

select * from t where name='lisi';　

如粉红色路径，需要扫码两遍索引树：

（1）先通过普通索引定位到主键值id=5；

（2）在通过聚集索引定位到行记录；

这就是回表查询，对比使用聚簇索引一次查询出行记录，回表查询的性能是比较低的。

索引覆盖

那么如果避免回表查询，来提高 sql 查询的性能呢？这就可以使用索引覆盖。

索引覆盖没有太明确的概念，通常是指：

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快

或者可以通过查询计划：

如果在 extra 中出现了using inde 就表明触发了索引覆盖

那么最重要的是如何实现索引覆盖？最常用的方法就是使用联合索引：

将被查询的字段，建立到联合索引中，保证能够在一颗索引树中查询到所有字段。

举个例子：

我们有这张表

create table user (
    id int primary key,
    name varchar(20),
    sex varchar(5),
    index(name, sex)
)engine=innodb;

第一个SQL语句：

select id,name from user where name='shenjian';　

都能够命中name索引，索引叶子节点存储了主键id，通过name的索引树即可获取id和name，无需回表，符合索引覆盖，效率较高。

第二个SQL语句：

select id,name,sex from user where name='shenjian';

能够命中name索引，索引叶子节点存储了主键id，但sex字段必须回表查询才能获取到，不符合索引覆盖，需要再次通过id值扫码聚集索引获取sex字段，效率会降低。

但是如果把(name)单列索引升级为联合索引(name, sex)就不同了。

create table user (
    id int primary key,
    name varchar(20),
    sex varchar(5),
    index(name, sex)
)engine=innodb;

可以看到：

select id,name ... where name='shenjian';
 
select id,name,sex ... where name='shenjian';

都能够命中索引覆盖，无需回表。