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

阳光穿透心脏的1/2处 2022-12-22 08:36 132阅读 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*

![4e64649658b472652588de7eef87ca3a.png][]

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

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

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

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

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

例如：

<table> 
 <tbody> 
  <tr> 
   <td style="width:44px;"> <p>1</p> </td> 
   <td> <p><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>t&nbsp;</code><code>where</code>&nbsp;<code>name</code><code>=</code><code>'lisi'</code><code>;　</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

![3119450ce7cc31a54b418b1c6acede66.png][]

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

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

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

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

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

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

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

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

![da2caa733c9d26cdcd42b30002d245e3.png][]

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

![a62bf703a7c0dc4582236a33f57a0e11.png][]

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

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

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

仍是之前中的例子：

<table> 
 <tbody> 
  <tr> 
   <td style="width:46px;"> <p>1</p> <p>2</p> <p>3</p> <p>4</p> <p>5</p> <p>6</p> </td> 
   <td> <p><code>create</code>&nbsp;<code>table</code>&nbsp;<code>user</code>&nbsp;<code>(</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>id&nbsp;</code><code>int</code>&nbsp;<code>primary</code>&nbsp;<code>key</code><code>,</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>name</code>&nbsp;<code>varchar</code><code>(20),</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>sex&nbsp;</code><code>varchar</code><code>(5),</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>index</code><code>(</code><code>name</code><code>)</code></p> <p><code>)engine=innodb;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

第一个SQL语句：

![0c6eb8b1f89a2da24c82a73e4aec5eec.png][]

<table> 
 <tbody> 
  <tr> 
   <td style="width:38px;"> <p>1</p> </td> 
   <td> <p><code>select</code>&nbsp;<code>id,</code><code>name</code>&nbsp;<code>from</code>&nbsp;<code>user</code>&nbsp;<code>where</code>&nbsp;<code>name</code><code>=</code><code>'shenjian'</code><code>;　</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

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

第二个SQL语句：

![cb2e6ad6be14e20465e6d84e09966d0b.png][]

<table> 
 <tbody> 
  <tr> 
   <td style="width:32px;"> <p>1</p> </td> 
   <td> <p><code>select</code>&nbsp;<code>id,</code><code>name</code><code>,sex&nbsp;</code><code>from</code>&nbsp;<code>user</code>&nbsp;<code>where</code>&nbsp;<code>name</code><code>=</code><code>'shenjian'</code><code>;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

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

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

<table> 
 <tbody> 
  <tr> 
   <td style="width:41px;"> <p>1</p> <p>2</p> <p>3</p> <p>4</p> <p>5</p> <p>6</p> </td> 
   <td> <p><code>create</code>&nbsp;<code>table</code>&nbsp;<code>user</code>&nbsp;<code>(</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>id&nbsp;</code><code>int</code>&nbsp;<code>primary</code>&nbsp;<code>key</code><code>,</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>name</code>&nbsp;<code>varchar</code><code>(20),</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>sex&nbsp;</code><code>varchar</code><code>(5),</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><strong><span style="color:#f33b45;"><code>index</code><code>(</code><code>name</code><code>, sex)</code></span></strong></p> <p><code>)engine=innodb;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![059f6219b3fc301a4f7fba07e9537f1f.png][]

可以看到：

<table> 
 <tbody> 
  <tr> 
   <td style="width:49px;"> <p>1</p> <p>2</p> </td> 
   <td> <p><code>select</code>&nbsp;<code>id,</code><code>name</code>&nbsp;<code>...&nbsp;</code><code>where</code>&nbsp;<code>name</code><code>=</code><code>'shenjian'</code><code>;</code></p> <p><code>select</code>&nbsp;<code>id,</code><code>name</code><code>,sex ...&nbsp;</code><code>where</code>&nbsp;<code>name</code><code>=</code><code>'shenjian'</code><code>;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

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

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

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

![582a0e64783a955462154b5f9817c532.png][]

原表为：

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

直接：

<table> 
 <tbody> 
  <tr> 
   <td style="width:64px;"> <p>1</p> </td> 
   <td> <p><code>select</code>&nbsp;<code>count</code><code>(</code><code>name</code><code>)&nbsp;</code><code>from</code>&nbsp;<code>user</code><code>;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

不能利用索引覆盖。

添加索引：

<table> 
 <tbody> 
  <tr> 
   <td style="width:45px;"> <p>1</p> </td> 
   <td> <p><code>alter</code>&nbsp;<code>table</code>&nbsp;<code>user</code>&nbsp;<code>add</code>&nbsp;<code>key</code><code>(</code><code>name</code><code>);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

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

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>select</code>&nbsp;<code>id,</code><code>name</code><code>,sex ...&nbsp;</code><code>where</code>&nbsp;<code>name</code><code>=</code><code>'shenjian'</code><code>;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

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

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>select</code>&nbsp;<code>id,</code><code>name</code><code>,sex ...&nbsp;</code><code>order</code>&nbsp;<code>by</code>&nbsp;<code>name</code>&nbsp;<code>limit 500,100;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

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

[4e64649658b472652588de7eef87ca3a.png]: /images/20221120/6a6fe5aab2c64d068c19ace47febda38.png
[3119450ce7cc31a54b418b1c6acede66.png]: /images/20221120/480d8deb2eef4f5bb3935337497f1f97.png
[da2caa733c9d26cdcd42b30002d245e3.png]: /images/20221120/7351ffa1b71d4a14a8a5399a59fa517c.png
[a62bf703a7c0dc4582236a33f57a0e11.png]: /images/20221120/6998ec6ee98c48918a3024d59c79c513.png
[0c6eb8b1f89a2da24c82a73e4aec5eec.png]: /images/20221120/75dc2911e44242b894eecf742c644889.png
[cb2e6ad6be14e20465e6d84e09966d0b.png]: /images/20221120/be2a89bcc8b0429caa21332a15846c53.png
[059f6219b3fc301a4f7fba07e9537f1f.png]: /images/20221120/4e311f4fb5934a7baeae602ab1a5d97b.png
[582a0e64783a955462154b5f9817c532.png]: /images/20221120/7b23459644054ed3a011c9ea4b395d83.png