还傻傻分不清MySQL回表查询与索引覆盖？

原创已于 2023-03-27 10:07:55 修改 · 420 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #java #开发语言

于 2023-02-20 08:30:15 首次发布

MySQL应用探究专栏收录该内容

59 篇文章

订阅专栏

文章介绍了MySQL中InnoDB存储引擎的聚集索引（主键索引）和非聚集索引（二级索引）的概念，以及回表查询的过程。非聚集索引的叶子节点存储主键值，查询时可能需要通过主键回表到聚集索引获取完整数据。为提高效率，文章提到了覆盖索引的概念，即通过索引直接获取所有查询数据，避免回表。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

hello，大家好，我是张张，「架构精进之路」公号作者。

最近的工作中，遇到一个查询里用到主键索引与二级索引并存的问题情况，那对于这种情况，索引是如何高效执行的，是否会产生回表查询呢？

等等，首先解释一下，什么是回表？

回表定义：先索引扫描，再通过ID去取索引中未能提供的数据，即为回表。

即先定位主键值，再定位行记录。

1、两类索引

为了更好地阐释这个问题，我们还是从索引来介绍吧。

InnoDB 索引分为两大类，一类是聚集索引(Clustered Index)，一类是非聚集索引(Secondary Index)

1.1 聚集索引（聚簇索引）

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

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

这种机制使得基于PK的查询速度非常快，因为直接定位的行记录。

1.2 非聚集索引（普通索引、非聚簇索引、二级索引）

普通索引也叫二级索引，除聚簇索引外的索引，即非聚簇索引。

InnoDB的普通索引叶子节点存储的是主键（聚簇索引）的值，而MyISAM的普通索引存储的是记录指针。

Q：为什么非主键索引结构叶子结点存储的是主键值？

A：减少了出现行移动或者数据页分裂时二级索引的维护工作（当数据需要更新的时候，二级索引不需要修改，只需要修改聚簇索引，一个表只能有一个聚簇索引，其他的都是二级索引，这样只需要修改聚簇索引就可以了，不需要重新构建二级索引）

在使用非聚集索引时，为了取到具体数据，则需要通过PK回到聚集索引里去查询数据。这就叫回表查询，扫描了2次索引树，所以效率相对较低。

2、应用示例

一例胜千言，show me you code！

2.1 建表操作

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

id 字段是聚簇索引，name 字段是普通索引（二级索引）

2.2 填充数据

mysql> select * from user;
+----+--------+------+------+
| id |  name  |  sex | type |
+----+--------+------+------+
| 1 | sj  |  m  |  A  |
| 3 | zs  |  m  |  A  |
| 5 | ls  |  m  |  A  |
| 9 | ww  |  f  |  B  |
+----+-----+-----+-----+

2.3 索引结构

聚簇索引（ClusteredIndex）

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

普通索引（secondaryIndex）

name 是普通索引（二级索引），非聚簇索引，其叶子节点存储的是聚簇索引的的值

2.4 查找过程

普通索引查找过程

如果查询条件为主键（聚簇索引），则只需扫描一次B+树即可通过聚簇索引定位到要查找的行记录数据。

select * from user where name = 'lisi';

普通索引因为无法直接定位行记录，其查询过程在通常情况下是需要扫描两遍索引树的。

实际执行过程：

路径需要扫描两遍索引树，第一遍先通过普通索引定位到主键值id=5，然后第二遍再通过聚集索引定位到具体行记录。

这就是所谓的回表查询，即先定位主键值，再根据主键值定位行记录，性能相对于只扫描一遍聚集索引树的性能要低一些。

3、索引覆盖

索引覆盖是一种避免回表查询的优化策略。

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

3.1 如何实现覆盖索引

将要查询的数据作为索引列建立普通索引（可以是单列索引，也可以一个索引语句定义所有要查询的列，即联合索引），这样的话就可以直接返回索引中的的数据，不需要再通过聚集索引去定位行记录，避免了回表的情况发生。

explain select id, name from user where name = 'lisi';

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

往期热文推荐：

关注公众号，免费领学习资料

如果您觉得还不错，欢迎关注和转发~