SQL/MySQL--聚集索引/辅助索引/回表查询/覆盖索引--原理/优化

Myth丶恋晨 2022-09-14 01:51 228阅读 0赞

原文网址：[SQL/MySQL--聚集索引/辅助索引/回表查询/覆盖索引--原理/优化\_IT利刃出鞘的博客-CSDN博客][SQL_MySQL--_--_IT_-CSDN]

# 简介 #

本文介绍如下内容：聚集索引；辅助索引；什么是回表查询，如何优化回表查询；什么是覆盖索引，覆盖索引的应用场景等。

# 表结构和数据 #

为了便于展示概念，本处先建好一个用户表（t\_user），表建好后是这样的：

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBASVTliKnliIPlh7rpnpg_size_7_color_FFFFFF_t_70_g_se_x_16][]

语句

DROP DATABASE IF EXISTS demo;
    CREATE DATABASE demo DEFAULT CHARACTER SET utf8;
    USE demo;
    
    DROP TABLE IF EXISTS t_user;
    
    CREATE TABLE `t_user`
    (
      `id`   BIGINT(0) AUTO_INCREMENT,
      `user_name` VARCHAR(64),
      `code` VARCHAR(20),
      `age`  INT,
      PRIMARY KEY (`id`),
      KEY index_user_name (`user_name`),
      KEY index_code (`code`)
    ) ENGINE = InnoDB;
    
    INSERT INTO `t_user` VALUES (1, 'LiLei', 'aa', 21);
    INSERT INTO `t_user` VALUES (2, 'HanMeimei', 'bb', 23);
    INSERT INTO `t_user` VALUES (3, 'Lucy', 'cc', 25);
    INSERT INTO `t_user` VALUES (4, 'Lili', 'dd', 28);
    INSERT INTO `t_user` VALUES (5, 'WeiHua', 'ee', 24);
    INSERT INTO `t_user` VALUES (6, 'ZhangWei', 'ff', 30);
    INSERT INTO `t_user` VALUES (7, 'Anna', 'gg', 26);
    INSERT INTO `t_user` VALUES (8, 'Lisa', 'hh', 21);
    INSERT INTO `t_user` VALUES (9, 'ZhangWei', 'ii', 24);
    INSERT INTO `t_user` VALUES (10, 'Kate', 'jj', 29);

# 聚集索引和辅助索引 #

在介绍回表和覆盖索引之前，需要先介绍聚集索引和辅助索引。

InnoDB有两大类索引：聚集索引（Clustered Index）和辅助索引（Secondary Index）。

<table style="width:500px;"> 
 <tbody> 
 <tr> 
 <td style="width:61px;">项</td> 
 <td style="width:214px;">聚集索引</td> 
 <td style="width:224px;">辅助索引</td> 
 </tr> 
 <tr> 
 <td style="width:61px;">别名</td> 
 <td style="width:214px;">聚簇索引</td> 
 <td style="width:224px;">二级索引、普通索引、非聚集索引、非聚簇索引</td> 
 </tr> 
 <tr> 
 <td style="width:61px;">结构</td> 
 <td style="width:214px;"> 叶子页保存了整个行数据。 所以也将聚集索引的叶子节点称为数据页。 </td> 
 <td style="width:224px;"> 叶子节点只存储聚集索引的非叶子节点存储的值（一般是主键ID）。 想拿到行数据，要根据主键去聚集索引取行数据。 </td> 
 </tr> 
 <tr> 
 <td style="width:61px;">数量</td> 
 <td style="width:214px;">一张表必须有且只有一个聚集索引。</td> 
 <td style="width:224px;">一张表可以有任意个普通索引。（没有也可以）</td> 
 </tr> 
 <tr> 
 <td style="width:61px;">优点</td> 
 <td style="width:214px;"> 基于主键的查询非常快。 因为直接定位行记录。 </td> 
 <td style="width:224px;">更新代价比聚集索引要小</td> 
 </tr> 
 <tr> 
 <td style="width:61px;">缺点</td> 
 <td style="width:214px;">1. 更新代价大（可忽略此缺点，因为主键一般不变） 2. 依赖于有序的数据</td> 
 <td style="width:224px;"> 若需要回表，则速度慢。 （若覆盖索引，则速度快。） </td> 
 </tr> 
 </tbody> 
</table>

## **聚集索引** ##

**简介**

对于本文用户表来说，聚集索引是这样的：

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBASVTliKnliIPlh7rpnpg_size_20_color_FFFFFF_t_70_g_se_x_16][]

聚集索引的生成规则：

1.  如果表定义了PK（Primary Key，主键），那么PK就是聚集索引。
2.  如果表没有定义PK，则第一个NOT NULL UNIQUE的列就是聚集索引。
3.  否则InnoDB会另外创建一个隐藏的ROWID作为聚集索引。

**优点**

基于主键的查询非常快。因为直接定位行记录。

**缺点**

1.  **更新代价大**
    
    1.  如果对索引列的数据被修改时，那么对应的索引也将会被修改，可能涉及自旋操作维护平衡， 而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的。
2.  **依赖于有序的数据**
    
    1.  因为B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或UUID这种又长又难比较的数据，插入或查找的速度肯定比较慢。

## **辅助索引** ##

**简介**

InnoDB辅助索引的叶子节点存储主键值。想拿到行数据，要根据主键去聚集索引取行数据。  
注意：InnoDB不是存储行记录头指针，MyISAM的索引叶子节点存储记录指针。

对于本文t\_user表来说，辅助索引是这样的：

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBASVTliKnliIPlh7rpnpg_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

**优点**

1.  只读取索引时IO负载低
    
    1.  索引项通常远小于数据行大小，若只读取索引，会极大地减少数据访问量。
    2.  这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。辅助索引对于I/O 密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中（这对于M ylSAM尤其正确，因为MylSAM 能压缩索引以变得更小）。
2.  I/O 密集型的范围査询性能高。
    
    1.  索引是按照列值大小顺序存储的（至少在单个页内是如此），对于I/O 密集型的范围査询会比随机从磁盘读取每一行数据的I/O 要少得多。
    2.  对于某些存储引擎，例如 MylSAM和 Percona XtraDB，甚至可以通过OPTIMIZE命令使得索引完全顺序排列 ，这让简单的范围査询能使用完全顺序的索引访问。
3.  数据引擎能更好的缓存索引。比如MyISAM只缓存索引。
    
    1.  一些存储引擎如MylSAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。
    2.  这可能会导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销的场景。
4.  由于InnoDB的聚簇索引，辅助索引对InnoDB表特别有用。
    
    1.  InnoDB的辅助索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖索引，则可以避免对主键索引的二次査询。

# 回表查询 #

回表查询需要扫描两次索引树：先通过普通索引列扫描普通索引树获取到主键，再通过主键扫描聚集索引树获得数据，它的性能比扫一遍索引树低。

对于本文的t\_user，id为主键，user\_name为普通索引，code为普通索引，age不建索引。

下边这条语句就会进行回表查询

SELECT * FROM t_user WHERE user_name = 'Lisa';

这条语句会先通过user\_name列的普通索引找到主键，然后再拿这个主键去聚集索引中取行数据。因为第一次通过普通索引只能获得user\_name这一列和主键这两个数据，所以必须去聚集索引获取其他数据，如下图所示：

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBASVTliKnliIPlh7rpnpg_size_20_color_FFFFFF_t_70_g_se_x_16 2][]

因为这两个索引数的高度都是2，所以，这次回表要经过4次IO才能获得到数据。

# 覆盖索引（优化回表查询） #

## 简介 ##

**什么是覆盖索引**

索引覆盖是一种避免回表查询的优化策略。就是把所有需要查询的字段都放到普通索引中，这样普通索引查到的叶子结点中已经能够得到所需的所有字段，就不会再去聚集索引中查询。

在Explain时，输出的Extra信息中如果有“Using Index”，就表示这条查询使用了覆盖索引。

**索引覆盖适用范围**

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列值（因为SQL查询结果和查询条件的都涉及到具体的值，而覆盖索引需要覆盖查询结果和查询条件），而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree或B+Tree索引做覆盖索引。

不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引。

## 实例 ##

**1.WHERE列有索引，SELECT的列有没有索引的（不是覆盖索引）**

EXPLAIN SELECT * FROM t_user WHERE user_name = 'Lisa';

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBASVTliKnliIPlh7rpnpg_size_20_color_FFFFFF_t_70_g_se_x_16 3][]

**2.WHERE列有索引，SELECT WHERE列以及主键（是覆盖索引）**

EXPLAIN SELECT id, user_name FROM t_user WHERE user_name = 'Lisa';

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBASVTliKnliIPlh7rpnpg_size_20_color_FFFFFF_t_70_g_se_x_16 4][]

**3.WHERE列有索引，SELECT有单独索引的列（不是覆盖索引）**

EXPLAIN SELECT user_name, code FROM t_user WHERE user_name = 'Lisa';

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBASVTliKnliIPlh7rpnpg_size_20_color_FFFFFF_t_70_g_se_x_16 5][]

**4.WHERE列有单独索引，只SELECT id（不是覆盖索引）**

EXPLAIN SELECT id FROM t_user WHERE user_name = 'Lisa' AND code = 'hh';

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBASVTliKnliIPlh7rpnpg_size_20_color_FFFFFF_t_70_g_se_x_16 6][]

**5.WHERE的条件都为联合索引中的字段，且符合最左前缀原则（是覆盖索引）**

DROP DATABASE IF EXISTS demo;
    CREATE DATABASE demo DEFAULT CHARACTER SET utf8;
    USE demo;
    
    DROP TABLE IF EXISTS t_user;
    
    CREATE TABLE `t_user`
    (
      `id`   BIGINT(0) AUTO_INCREMENT,
      `user_name` VARCHAR(64),
      `code` VARCHAR(20),
      `age`  INT,
      PRIMARY KEY (`id`),
      KEY index_user_name_code (`user_name`, `code`)
    ) ENGINE = InnoDB;
    
    INSERT INTO `t_user` VALUES (1, 'LiLei', 'aa', 21);
    INSERT INTO `t_user` VALUES (2, 'HanMeimei', 'bb', 23);
    INSERT INTO `t_user` VALUES (3, 'Lucy', 'cc', 25);
    INSERT INTO `t_user` VALUES (4, 'Lili', 'dd', 28);
    INSERT INTO `t_user` VALUES (5, 'WeiHua', 'ee', 24);
    INSERT INTO `t_user` VALUES (6, 'ZhangWei', 'ff', 30);
    INSERT INTO `t_user` VALUES (7, 'Anna', 'gg', 26);
    INSERT INTO `t_user` VALUES (8, 'Lisa', 'hh', 21);
    INSERT INTO `t_user` VALUES (9, 'ZhangWei', 'ii', 24);
    INSERT INTO `t_user` VALUES (10, 'Kate', 'jj', 29);

EXPLAIN SELECT user_name, code FROM t_user WHERE user_name = 'Lisa';

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBASVTliKnliIPlh7rpnpg_size_20_color_FFFFFF_t_70_g_se_x_16 7][]

## 优化实例 ##

### **分页优化** ###

有一个日志表t\_log，需要进行分页查询，于是很容易就想到了limit \[offset偏移量\] \[count数量\]这个查询方式。当我们偏移量比较小时没什么问题

SELECT	* FROM	t_log WHERE	type = 1 LIMIT 5, 50
    -- 查询时间:0.45s

随着offset的增加，查询时间越来越长，但是每次查出的数据都只有50条

SELECT	* FROM	t_log WHERE	type = 1 LIMIT 500000, 50
    -- 查询时间:57.252s
    SELECT	* FROM	t_log WHERE	type = 1 LIMIT 1000000, 50
    -- 查询时间:89.15s

查阅资料发现“limit”的工作方式是：第一步：查询offset+count条数据；第二步：抛弃前offset条数据。

但是全字段查询肯定会有回表查询操作，这就导致了进行百万次的回表查询，速度肯定会很慢，解决思路是，在“第一步”时不进行回表查询，这样效率会提高很多，于是把sql改成下面的等效查询：

SELECT	*
    FROM	t_log t
    RIGHT JOIN (
    	SELECT uid
    	FROM t_log
    	WHERE type = 1
    	LIMIT 1000000,50
    ) tmp ON tmp.uid = t.uid
    -- 查询时间：0.64

这下时间缩短了一百倍多，查出来的结果也是正确的，达到了我们要的效果，到此sql已经优化好了。

# 其他网址 #

[Mysql 覆盖索引及其使用注意事项\_未名who的博客-CSDN博客][Mysql _who_-CSDN]  
[mysql-回表查询是什么\_小小少年的博客-CSDN博客\_mysql回表是什么意思][mysql-_-CSDN_mysql]  
[详解MySQL回表查询与索引覆盖\_晓呆同学的专栏-CSDN博客][MySQL_-CSDN]  
[MySQL中的回表查询与索引覆盖：一次百万级别分页查询使用Limit 从90秒到0.6秒的优化\_u011783999的博客-CSDN博客][MySQL_Limit _90_0.6_u011783999_-CSDN]

另见 《高性能MySQL 第三版》=> 5.3 高性能的索引策略  
《MySQL技术内幕 InnoDB存储引擎 第2版》=> 5.4 B+树索引

[SQL_MySQL--_--_IT_-CSDN]: https://knife.blog.csdn.net/article/details/120561754
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBASVTliKnliIPlh7rpnpg_size_7_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/e06ef2e537aa452ebe20b231aa516fad.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBASVTliKnliIPlh7rpnpg_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/78220cf0e26743f4b4e6160ebd58e3f9.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBASVTliKnliIPlh7rpnpg_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/f014fd87ade54c1fbce04f99c3f9cde1.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBASVTliKnliIPlh7rpnpg_size_20_color_FFFFFF_t_70_g_se_x_16 2]: /images/20220828/daa8fd1a78d94c749939a205e95ca2ca.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBASVTliKnliIPlh7rpnpg_size_20_color_FFFFFF_t_70_g_se_x_16 3]: /images/20220828/6cc9ba1271ae49218d90e33d68deebdb.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBASVTliKnliIPlh7rpnpg_size_20_color_FFFFFF_t_70_g_se_x_16 4]: /images/20220828/d7dd882975aa4cfeaeb6a8762ab833b0.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBASVTliKnliIPlh7rpnpg_size_20_color_FFFFFF_t_70_g_se_x_16 5]: /images/20220828/8d3efc096421419a8e0c09fa06e4631f.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBASVTliKnliIPlh7rpnpg_size_20_color_FFFFFF_t_70_g_se_x_16 6]: /images/20220828/ec4c533f19624bd2a4bfa74ce9765159.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBASVTliKnliIPlh7rpnpg_size_20_color_FFFFFF_t_70_g_se_x_16 7]: /images/20220828/c7e13bd4ed844f10a9f7a9b01786aed6.png
[Mysql _who_-CSDN]: https://blog.csdn.net/qq_2300688967/article/details/98212333
[mysql-_-CSDN_mysql]: https://blog.csdn.net/CPLASF_/article/details/108799381
[MySQL_-CSDN]: https://blog.csdn.net/daidaineteasy/article/details/110008970
[MySQL_Limit _90_0.6_u011783999_-CSDN]: https://blog.csdn.net/u011783999/article/details/105334301