mysql 回表的代价(InnoDB)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: mysql 回表的代价(InnoDB)

为了方便理解我们先来看一个sql语句

SELECT * FROM demo_table where key1 > 'a' and key1 < 'c';

假设我们为key1列设置二级索引,索引结构为B+树

对于上面这个sql有两种执行方式:

1. 以全表扫描的方式执行该查询

全表扫描也就是直接扫描全部的聚簇索引记录,针对每一条聚簇索引记录,都判断搜索条件是否成立,如果成立则发送到客户端,否则跳过这条记录。

2. 使用idx_key1,也就是对应key1列的索引来执行该查询

  • 过程

可以根据搜索条件 key1 > ‘a’ and key1 < ‘c’ 得到对应的扫描区间 (‘a’,‘c’),然后扫描该区间中的二级索引记录。由于二级索引记录idx_key1索引的叶子节点存储的不是完整的用户记录信息,而是只存储了二级索引列key1列和主键id这两个列,而我们的sql查询的列表是 * ,这意味着我们需要获取每条二级索引记录对应的聚簇索引记录(通过在二级索引记录中得到的主键id去然后去聚簇索引中查询完整的用户信息),也就是执行回表操作,然后获取到完整的用户信息发送给客户端。

  • 执行回表的代价

对于InnoDB存储引擎来说,索引中的数据都是存放在磁盘中的,等到需要的时候再将磁盘中的数据加载到内存当中。这些数据页会被存放到磁盘中的一个或多个文件中,页面的页号对应着该页在磁盘文件中的偏移量,以16k大小的页面为例,页号为0的页面对应着这些文件中偏移量为0的位置,页号为1的页面对应着文件中偏移量为16k的位置。

我们知道B+树每层节点也就是每个数据页会使用双向链表连接起来,上一个节点和下一个节点的页号可以不相邻(不过会尽量相邻)。

也就是说idx_key1在扫描区间(‘a’,‘c’)中的二级索引记录所在的页面的页号会尽可能相邻,即使这些页面的页号不相邻,但是一个页面也可以存放很多记录,也就是说在执行完一次页面IO后,就可以把很多二级索引记录加载到内存当中。这种情况是因为我们要查询的数据量比较小。需要注意的一点是,我们通过二级索引得到的id值是没有规律的,因为二级索引是通过二级索引列来排序的,我们每得到一条二级索引记录,就要通过二级索引记录中的主键id去聚簇索引中查询,也就是需要执行回表操作。如果对应的聚簇索引的数据不在内存中,就需要将页面从磁盘加载到内存。由于要读取很多id值不连续的聚簇索引记录,而这些聚簇索引记录分布在不同的数据页中,这些数据的页号也没有规律,因此会造成大量的随机IO。

上面说的可能有点啰嗦,总结一下就是:

我们通过二级索引来查询的时候,通过二级索引建立的B+树存储的不是完整的用户记录,并且是按照二级索引列来排序的,主键id是无序的,当我们要查询完整的用户记录时,就需要回表。由于主键id在二级索引中没有顺序,所以主键id可能分布在多个页面,这个时候需要读取多个页面,造成大量的IO,如果是有序的可能就分布在一个页面,这样一次就读取到内存了

  • 最好不要使用二级索引的情况
    当需要执行回表操作的记录越来越多,使用二级索引查询的效率也就越来越低,有些查询宁可全表扫描也不使用二级索引。比如key1值在’a’ ~ ‘c’ 之间的用户数占总记录的99%以上,如果使用二级索引的话,有99%以上的id值都需要执行回表操作,回表的代价上面已经提到了。
    一般情况下,可以给查询语句指定LIMIT子句来限制查询返回的记录数,这可能会让查询优化器倾向于选择使用二级索引+回表的方式来进行查询。

创作不易,点个赞吧~👍

最后的最后送大家一句话

白驹过隙,沧海桑田

与君共勉

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
相关文章
|
28天前
|
存储 关系型数据库 MySQL
介绍MySQL的InnoDB引擎特性
总结而言 , Inno DB 引搞 是 MySQL 中 高 性 能 , 高 可靠 的 存 储选项 , 宽泛 应用于要求强 复杂交易处理场景 。
64 15
|
6月前
|
存储 网络协议 关系型数据库
MySQL8.4创建keyring给InnoDB表进行静态数据加密
MySQL8.4创建keyring给InnoDB表进行静态数据加密
155 1
|
6月前
|
存储 SQL 关系型数据库
MySQL 中的回表是什么?
在 MySQL 中,“回表”是指通过二级索引查询时,因二级索引仅存储索引字段值和主键值,需再根据主键到聚簇索引查找完整行数据的过程。此操作涉及两次索引查找,可能增加 IO 消耗,影响性能。优化方法包括使用覆盖索引或联合索引,避免回表,提升查询效率。合理设计索引对高并发、大数据量场景下的数据库性能至关重要。
341 17
|
10月前
|
存储 缓存 关系型数据库
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL的存储引擎是其核心组件之一,负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性,可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
1687 57
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
|
6月前
|
SQL 缓存 关系型数据库
使用温InnoDB缓冲池启动MySQL测试
使用温InnoDB缓冲池启动MySQL测试
110 0
|
10月前
|
存储 关系型数据库 MySQL
MySQL存储引擎详述:InnoDB为何胜出?
MySQL 是最流行的开源关系型数据库之一,其存储引擎设计是其高效灵活的关键。InnoDB 作为默认存储引擎,支持事务、行级锁和外键约束,适用于高并发读写和数据完整性要求高的场景;而 MyISAM 不支持事务,适合读密集且对事务要求不高的应用。根据不同需求选择合适的存储引擎至关重要,官方推荐大多数场景使用 InnoDB。
234 7
|
22天前
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能(下)
使用MYSQL Report分析数据库性能
57 3
|
28天前
|
关系型数据库 MySQL 数据库
自建数据库如何迁移至RDS MySQL实例
数据库迁移是一项复杂且耗时的工程,需考虑数据安全、完整性及业务中断影响。使用阿里云数据传输服务DTS,可快速、平滑完成迁移任务,将应用停机时间降至分钟级。您还可通过全量备份自建数据库并恢复至RDS MySQL实例,实现间接迁移上云。
|
15天前
|
关系型数据库 MySQL 分布式数据库
阿里云PolarDB云原生数据库收费价格:MySQL和PostgreSQL详细介绍
阿里云PolarDB兼容MySQL、PostgreSQL及Oracle语法,支持集中式与分布式架构。标准版2核4G年费1116元起,企业版最高性能达4核16G,支持HTAP与多级高可用,广泛应用于金融、政务、互联网等领域,TCO成本降低50%。
|
16天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格:MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB,多种引擎优惠上线!MySQL倚天版88元/年,SQL Server 2核4G仅299元/年,PostgreSQL 227元/年起。高可用、可弹性伸缩,安全稳定。详情见官网活动页。

推荐镜像

更多