MySQL面试：谈谈你对覆盖索引的认识？

谁践踏了优雅 2022-09-16 12:22 142阅读 0赞

# 什么是覆盖索引 #

**如果一个索引包含了满足查询语句中字段与条件的数据就叫做覆盖索引**。即只需扫描索引而无须回表。

>  *  我们知道MySQL的B+Tree索引是用我们字段的数据来建立索引的  
>     比如说我们的主键id字段，就是用所有的id来组织这颗索引树，如果我们再对name字段建立索引的话，这个二级索引就是用name字段的数据来组织这颗索引树。
>  *  那么问题就来了，我们知道对于二级索引而言他的叶子节点存储了对应数据行的id（MySQL系列-B+Tree索引详解），也就是说最后我们的查询还是要通过主键id来进行查询获取数据。
>  *  如果我们只需要name这个字段呢？比如说 select name from table where name>‘aaa’; **我们这个二级索引上保存了的name字段的所有数据，那么就没有必要再通过id去访问数据行了，直接从索引上获取数据即可。称之为覆盖索引，有的也翻译为索引覆盖**。

**由于覆盖索引可以减少树的搜索次数，显著提高性能，所以使用覆盖索引是一个常用的优化手段**

# 优点 #

*  索引条目通常远远小于数据行的大小，只需要读取索引，则mysql会极大的减少数据访问量
 *  因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多
 *  一些存储引擎（比如MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不适用系统调用（比较费时）
 *  对于InnoDB引擎，如果辅助索引上已经存在我们需要的数据，那么引擎就不会去主键上搜索数据了。【有效避免“回表”查询】

# 限制 #

*  覆盖索引并不适用于任意的索引类型，索引必须存储类的值
 *  覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以MySQL只能用B-Tree做覆盖查询
 *  不同的存储引擎实现覆盖索引都是不同的，并不是所有的存储引擎都支持覆盖索引
 *  如果要使用覆盖索引，一定要注意SELETC列表值取出需要的列，不可以支持SELECT \* ，因为如果将所有字段一起做索引会导致时索引文件过大，查询性能下降

索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引是就需要权衡考虑了。

# 判断是否使用覆盖索引 #

我们可以通过expalin来判断查询是否覆盖索引，主要看extra字段是否有 using index 。只要有了，就说明用到了覆盖索引。

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_20_color_FFFFFF_t_70_g_se_x_16]

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

必看的博客：

*  [在MySQL中如何使用覆盖索引优化limit分页查询][MySQL_limit]

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

# 两大类索引 #

使用的存储引擎：MySQL5.7 InnoDB

## 聚簇索引 ##

*  InnoDB的聚簇索引的叶子节点存储的是行记录（其实是页结构，一个页包含多行数据）
 *  由此可见，使用聚簇索引查询会很快，因为可以直接定位到行记录

> InnoDB必须至少要有一个聚簇索引

*  如果表设置了主键，则主键就是聚簇索引
 *  如果表没有主键，则会默认第一个NOT NULL，且唯一（UNIQUE）的列作为聚簇索引
 *  以上都没有，则会默认创建一个隐藏的row\_id作为聚簇索引

## 普通索引 ##

*  普通索引也叫做二级索引，是除了聚簇索引外的索引，即非聚簇索引
 *  InnoDB的普通索引叶子节点存储的是主键（聚簇索引）的值，而MyISAM的普通索引存储的是记录指针

# 示例 #

## 建表 ##

mysql> create table user(
        -> id int(10) auto_increment,
        -> name varchar(30),
        -> age tinyint(4),
        -> primary key (id),
        -> index idx_age (age)
        -> )engine=innodb charset=utf8mb4;

id字段是聚簇索引，age字段是普通索引

## 填充数据 ##

insert into user(name,age) values('张三',30);
    insert into user(name,age) values('李四',20);
    insert into user(name,age) values('王五',40);
    insert into user(name,age) values('刘八',10);
    
    mysql> select * from user;
    +----+--------+------+
    | id | name  | age |
    +----+--------+------+
    | 1 | 张三  |  30 |
    | 2 | 李四  |  20 |
    | 3 | 王五  |  40 |
    | 4 | 刘八  |  10 |
    +----+--------+------+

## 索引存储结构 ##

id是主键，所以是聚簇索引，其叶子节点存储的是对应行记录的数据

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_20_color_FFFFFF_t_70_g_se_x_16 1]

age是普通索引(二级索引)，非聚簇索引，其叶子节点存储的是聚簇索引的值

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_20_color_FFFFFF_t_70_g_se_x_16 2]  
如果查询条件为主键（聚簇索引），则只需要扫描一次B+树就可以通过聚簇索引定位到要查找的行记录数据。比如：`select * from user where id = 1;`

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_20_color_FFFFFF_t_70_g_se_x_16 3]

如果查询条件为普通索引（非聚簇索引），需要扫描两次B+树，第一次扫描通过普通索引定位到聚簇索引的值，然后第二次扫描通过聚簇索引的值定位到要查找的行记录数据。 如：select \* from user where age = 30;

1.  先通过普通索引 age=30 定位到主键值 id=1
2.  再通过聚集索引 id=1 定位到行记录数据  
    ![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_20_color_FFFFFF_t_70_g_se_x_16 4]  
    ![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_20_color_FFFFFF_t_70_g_se_x_16 5]

## 回表查询 ##

*  先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫一次索引树更低

## 覆盖索引（避免回表） ##

*  只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。例如：`select id,age from user where age = 10;`

## 如何实现覆盖索引 ##

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

1.  比如实现：`select id,age from user where age = 10;`

explain分析：因为age是普通索引，使用到了age索引，通过一次扫描B树就可以查询到相应的结构，这样就实现了覆盖索引

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

1.  实现：`select id,age,name from user where age = 10;`

expliain分析：arg是普通索引，但是name列不在索引树上，所以通过age索引在查询id和age的值后，需要进行回表再查询name的值。此时的Extra列的NULL表示进行了回表查询。

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

为了实现索引覆盖，需要建组合索引idx\_age\_name(age,name)

drop index idx_age on user;
    create index idx_age_name on user(`age`,`name`);

explain分析：此时字段age和name是组合索引idx\_age\_name，查询的字段id、age、name的值刚刚都在索引树上，只需扫描一次组合索引B+树即可，这就是实现了索引覆盖，此时的Extra字段为Using index表示使用了索引覆盖。  
![在这里插入图片描述][1d65927d8bcf416e914237c739c23155.png]

## 哪些场景适合使用索引覆盖来优化SQL ##

### 全表count查询优化 ###

mysql> create table user(
        -> id int(10) auto_increment,
        -> name varchar(30),
        -> age tinyint(4),
        -> primary key (id),
        -> )engine=innodb charset=utf8mb4;

例如：select count(age) from user;  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_20_color_FFFFFF_t_70_g_se_x_16 6]  
使用索引覆盖优化：创建age字段索引

create index idx_age on user(age);

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_20_color_FFFFFF_t_70_g_se_x_16 7]

### 列查询回表优化 ###

前文在描述索引覆盖使用的例子就是  
例如：`select id,age,name from user where age = 10;`  
使用索引覆盖：建组合索引idx\_age\_name(age,name)即可

### 分页查询 ###

例如：`select id,age,name from user order by age limit 100,2;`  
因为name字段不是索引，所以在分页查询需要进行回表查询，此时Extra为Using filesort文件排序，查询性能低下。

![在这里插入图片描述][21924e4940ce4fb58ed7d377146fd074.png]  
使用索引覆盖：建组合索引idx\_age\_name(age,name)  
![在这里插入图片描述][abca885a60834792a24b1f616eefa916.png]

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/05656de1c0eb48f9b95ceaac78157aa1.png
[MySQL_limit]: https://www.cnblogs.com/lxwphp/p/10399852.html
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/7397809662814c8ebc8bf4767a5c7b0d.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_20_color_FFFFFF_t_70_g_se_x_16 2]: /images/20220828/bcb755fc6d704ccfa31b1f8f5640802b.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_20_color_FFFFFF_t_70_g_se_x_16 3]: /images/20220828/497b91b79bd7403f9762e193a043c30c.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_20_color_FFFFFF_t_70_g_se_x_16 4]: /images/20220828/16efbb736d2a406ea220c3193138a371.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_20_color_FFFFFF_t_70_g_se_x_16 5]: /images/20220828/a86a40fc8c9f43618487bd4d8bf81e62.png
[2b6726220b2548f5aa0f96cab985c963.png]: /images/20220828/74b22ed2be86437a8ecbc1b9c4979793.png
[ed599eabb13542b7b3c430fc665f79c3.png]: /images/20220828/09c27bccc5984fce9ce086fb2f4f4929.png
[1d65927d8bcf416e914237c739c23155.png]: /images/20220828/43e89bf7e3ea494fa3ad94d450c25082.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_20_color_FFFFFF_t_70_g_se_x_16 6]: /images/20220828/94be3de0bd244292935e8ff46582c045.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_20_color_FFFFFF_t_70_g_se_x_16 7]: /images/20220828/6d1831e5747f40b4a2504dd06688612b.png
[21924e4940ce4fb58ed7d377146fd074.png]: /images/20220828/405ff9f935234311baf993de03a7f7cb.png
[abca885a60834792a24b1f616eefa916.png]: /images/20220828/c472ff19371a40b491d679d06e455509.png