系统城装机大师 - 固镇县祥瑞电脑科技销售部宣传站!

当前位置:首页 > 数据库 > Mysql > 详细页面

MySQL回表查询与索引覆盖

时间:2023-10-30来源:系统城装机大师作者:佚名

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 建表操作

1
2
3
4
5
6
7
8
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 填充数据

1
2
3
4
5
6
7
8
9
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+树即可通过聚簇索引定位到要查找的行记录数据。

1 select * from user where name = 'lisi';

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

实际执行过程:

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

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

3、索引覆盖

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

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

3.1 如何实现覆盖索引

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

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

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

- END -

希望今天的讲解对大家有所帮助,谢谢!

到此这篇关于一文带你分清MySQL回表查询与索引覆盖的文章就介绍到这了,

分享到:

相关信息

  • MySQL的核心查询语句详解

    一、单表查询 1、排序 2、聚合函数 3、分组 4、limit 二、SQL约束 1、主键约束 2、非空约束 3、唯一约束 4、外键约束 5、默认值 三、多表查询 1、内连接 1)隐式内连接: 2)显式内连接: 2、外连接 1)左外连接 2)右外连接 四...

    2023-10-30

  • Mysql中如何删除表重复数据

    Mysql删除表重复数据 表里存在唯一主键 没有主键时删除重复数据 Mysql删除表中重复数据并保留一条 准备一张表 用的是mysql8 大家自行更改 创建表并添加四条相同的数据...

    2023-10-30

系统教程栏目

栏目热门教程

人气教程排行

站长推荐

热门系统下载