MySQL优化：如何避免回表查询？_什么是索引覆盖？

一时失言乱红尘 2023-09-30 13:32 20阅读 0赞

数据库表结构：

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

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

**多查询了一个属性，为何检索过程完全不同？**

**什么是回表查询？**

**什么是索引覆盖？**

**如何实现索引覆盖？**

**哪些场景，可以利用索引覆盖来优化SQL？**

这些，这是今天要分享的内容。

*注：本文试验基于MySQL5.6-InnoDB。*

**一、什么是回表查询？**

这先要从InnoDB的索引实现说起，InnoDB有两大类索引：

*  聚集索引(clustered index)
 *  普通索引(secondary index)

InnoDB聚集索引和普通索引有什么差异？

InnoDB**聚集索引**的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：

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

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

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

*注：所以PK查询非常快，直接定位行记录。*

InnoDB**普通索引**的叶子节点存储主键值。

*画外音：注意，不是存储行记录头指针，MyISAM的索引叶子节点存储记录指针。*

举个栗子，不妨设有表：

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*

![img][]

两个B+树索引分别如上图：

（1）id为PK，聚集索引，叶子节点存储行记录；

（2）name为KEY，普通索引，叶子节点存储PK值，即id；

既然从普通索引无法直接定位行记录，那**普通索引的查询过程是怎么样的呢？**

通常情况下，需要扫码两遍索引树。

例如：

select * from t where name='lisi';

**是如何执行的呢？**

![img][img 1]

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

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

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

这就是所谓的**回表查询**，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

**二、什么是索引覆盖(Covering index)？**

额，并没有在MySQL的官网找到这个概念。

*画外音：治学严谨吧？*

借用一下SQL-Server官网的说法。

![img][img 2]

MySQL官网，类似的说法出现在explain查询计划优化章节，即explain的输出结果Extra字段为**Using index**时，能够触发索引覆盖。

![img][img 3]

不管是SQL-Server官网，还是MySQL官网，都表达了：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

**三、如何实现索引覆盖？**

常见的方法是：将被查询的字段，建立到联合索引里去。

仍是之前中的例子：

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

第一个SQL语句：

![img][img 4]

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

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

*画外音，Extra：**Using index**。*

第二个SQL语句：

![img][img 5]

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

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

*画外音，Extra：**Using index condition**。*

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

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

![img][img 6]  
可以看到：

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

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

*画外音，Extra：**Using index**。*

**四、哪些场景可以利用索引覆盖来优化SQL？**

**场景1：全表count查询优化**

![img][img 7]  
原表为：

*user(PK id, name, sex)；*

直接：

select count(name) from user;

不能利用索引覆盖。

添加索引：

alter table user add key(name);

就能够利用索引覆盖提效。

**场景2：列查询回表优化**

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

这个例子不再赘述，将单列索引(name)升级为联合索引(name, sex)，即可避免回表。

**场景3：分页查询**

select id,name,sex ... order by name limit 500,100;

将单列索引(name)升级为联合索引(name, sex)，也可以避免回表。

**InnoDB聚集索引普通索引**，**回表**，**索引覆盖**

## 什么是覆盖索引? ##

\#\# **前言**

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

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

### **主键索引与辅助索引的区别** ###

相信大家也了解过这方面的知识,这里就不展开了,直接上总结。

主键索引

> 叶子节点保存数据

辅助索引

> 叶子节点保存主键值

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

先说查询过程:

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

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

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

接下来我们来证明一下它。

### 回表查询 ###

假如有这样一张表:

CREATE TABLE `test` (
      `id` int(11) NOT NULL,
      `age` int(11) NOT NULL,
      `name` varchar(255) NOT NULL DEFAULT '',
      PRIMARY KEY (`id`),
      KEY `idx_age_name` (`age`,`name`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我们给age添加一个索引, 接下来随意插入几条数据

insert into test(`id`,`age`,`name`) VALUES(1,10,"小明"),(2,11,"小红"),(3,12,"小伟");

查询一条数据

select * from test where age = 10

查看一下耗时

![img][img 8]

分析一下语句:

desc select * from test where age = 10

查看执行计划:

![在这里插入图片描述][c3ad2e4fdfeb4700b0fdf5db9de048f5.png]

可以看到extra列为空,key则使用了idx\_age索引, 大致的查询耗时在0.024秒左右。

**这样的查询速度快吗?**

> 我说我还能再优化一下,你敢信吗? - 鲁迅(我没说过)

### 覆盖索引 ###

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

> select age,name from test where age = 10

查看一下耗时:  
![在这里插入图片描述][18132fd850c94b62a313178986c37814.png]

可以看到耗时减少了!

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

desc select age,name from test where age = 10

![在这里插入图片描述][7bc9dd5c48c645dc92a4861fdf250a97.png]

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

### **总结** ###

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

--------------------

\# 什么叫做覆盖索引？

*  解释一： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
 *  解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。
 *  解释三：是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句\[select子句\]与查询条件\[Where子句\]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引当发起一个被索引覆盖的查询(也叫作索引覆盖查询)时，在EXPLAIN的Extra列可以看到“Using index”的信息。

注：遇到以下情况，执行计划不会选择覆盖查询。

1.  select选择的字段中含有不在 索引 中的字段 ，即索引没有覆盖全部的列。
2.  where条件中不能含有对索引进行like的操作。

## mysql聚集索引，辅助索引，联合索引，覆盖索引 ##

**聚集索引**：一个表中只能有一个，聚集索引的顺序与数据真实的物理存储顺序一致。查询速度贼快，聚集索引的叶子节点上是该行的所有数据 ，数据索引能加快范围查询(聚集索引的顺序和数据存放的逻辑顺序一致)。主键!=聚集索引。

**辅助索引(非聚集索引)**：一个表中可以有多个，叶子节点存放的不是一整行数据，而是键值，叶子节点的索引行中还包含了一个’书签’，这个书签就是指向聚簇索引的一个指针，从而在聚簇索引树中找到一整行数据。

**联合索引**：就是由多列组成的的索引。遵循最左前缀规则。对where，order by，group by 都生效。

**覆盖索引**：指从辅助索引中就能获取到需要的记录，而不需要查找聚簇索引中的记录。使用覆盖索引的一个好处是因为辅助索引不包括一条记录的整行信息，所以数据量较聚集索引要少，可以减少大量io操作。

**聚集索引与辅助索引的区别**：叶子节点是否存放的为一整行数据

**最左前缀规则**：假设联合索引由列(a,b,c)组成，则一下顺序满足最左前缀规则：a、ab、abc；selece、where、order by 、group by都可以匹配最左前缀。其它情况都不满足最左前缀规则就不会用到联合索引。

[img]: https://img-blog.csdnimg.cn/img_convert/655ddf8c55009ecef5823cef07b9fb79.png
[img 1]: https://img-blog.csdnimg.cn/img_convert/6127ef3ecae9e3e0b3c672fa993e8a9a.png
[img 2]: https://img-blog.csdnimg.cn/img_convert/85cf88f82007363b5e7dd2c2ebaeb55d.png
[img 3]: https://img-blog.csdnimg.cn/img_convert/4c13031e93811ba913a7b37004388afc.png
[img 4]: https://img-blog.csdnimg.cn/img_convert/0ea75fee9e678222ce21ce6ebf9dce15.png
[img 5]: https://img-blog.csdnimg.cn/img_convert/83b675def3904e005f331e72f46e3400.png
[img 6]: https://img-blog.csdnimg.cn/img_convert/1ed2c3907d1860d5ca40e91a318127ad.png
[img 7]: https://img-blog.csdnimg.cn/img_convert/ace70bc6af399a6102fdf28fd16e49c6.png
[img 8]: https://img-blog.csdnimg.cn/img_convert/49f42abca7be68ce3005badc00946903.png
[c3ad2e4fdfeb4700b0fdf5db9de048f5.png]: https://img-blog.csdnimg.cn/c3ad2e4fdfeb4700b0fdf5db9de048f5.png
[18132fd850c94b62a313178986c37814.png]: https://img-blog.csdnimg.cn/18132fd850c94b62a313178986c37814.png
[7bc9dd5c48c645dc92a4861fdf250a97.png]: https://img-blog.csdnimg.cn/7bc9dd5c48c645dc92a4861fdf250a97.png