mysql 回表的代价(InnoDB)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 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子句来限制查询返回的记录数,这可能会让查询优化器倾向于选择使用二级索引+回表的方式来进行查询。

创作不易,点个赞吧~👍

最后的最后送大家一句话

白驹过隙,沧海桑田

与君共勉

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
25天前
|
存储 SQL 关系型数据库
MySQL底层概述—2.InnoDB磁盘结构
InnoDB磁盘结构主要包括表空间(Tablespaces)、数据字典(Data Dictionary)、双写缓冲区(Double Write Buffer)、重做日志(redo log)和撤销日志(undo log)。其中,表空间分为系统、独立、通用、Undo及临时表空间,分别用于存储不同类型的数据。数据字典从MySQL 8.0起不再依赖.frm文件,转而使用InnoDB引擎存储,支持事务原子性DDL操作。
212 100
MySQL底层概述—2.InnoDB磁盘结构
|
22天前
|
SQL 关系型数据库 MySQL
MySQL底层概述—10.InnoDB锁机制
本文介绍了:锁概述、锁分类、全局锁实战、表级锁(偏读)实战、行级锁升级表级锁实战、间隙锁实战、临键锁实战、幻读演示和解决、行级锁(偏写)优化建议、乐观锁实战、行锁原理分析、死锁与解决方案
MySQL底层概述—10.InnoDB锁机制
|
24天前
|
存储 缓存 关系型数据库
MySQL底层概述—5.InnoDB参数优化
本文介绍了MySQL数据库中与内存、日志和IO线程相关的参数优化,旨在提升数据库性能。主要内容包括: 1. 内存相关参数优化:缓冲池内存大小配置、配置多个Buffer Pool实例、Chunk大小配置、InnoDB缓存性能评估、Page管理相关参数、Change Buffer相关参数优化。 2. 日志相关参数优化:日志缓冲区配置、日志文件参数优化。 3. IO线程相关参数优化: 查询缓存参数、脏页刷盘参数、LRU链表参数、脏页刷盘相关参数。
MySQL底层概述—5.InnoDB参数优化
|
25天前
|
存储 SQL 关系型数据库
MySQL底层概述—4.InnoDB数据文件
本文介绍了InnoDB表空间文件结构及其组成部分,包括表空间、段、区、页和行。表空间是最高逻辑层,包含多个段;段由若干个区组成,每个区包含64个连续的页,页用于存储多条行记录。文章还详细解析了Page结构,分为通用部分(文件头与文件尾)、数据记录部分和页目录部分。此外,文中探讨了行记录格式,包括四种行格式(Redundant、Compact、Dynamic和Compressed),重点介绍了Compact行记录格式及其溢出机制。最后,文章解释了不同行格式的特点及应用场景,帮助理解InnoDB存储引擎的工作原理。
MySQL底层概述—4.InnoDB数据文件
|
25天前
|
存储 缓存 关系型数据库
MySQL底层概述—3.InnoDB线程模型
InnoDB存储引擎采用多线程模型,包含多个后台线程以处理不同任务。主要线程包括:IO Thread负责读写数据页和日志;Purge Thread回收已提交事务的undo日志;Page Cleaner Thread刷新脏页并清理redo日志;Master Thread调度其他线程,定时刷新脏页、回收undo日志、写入redo日志和合并写缓冲。各线程协同工作,确保数据一致性和高效性能。
MySQL底层概述—3.InnoDB线程模型
|
25天前
|
缓存 算法 关系型数据库
MySQL底层概述—1.InnoDB内存结构
本文介绍了InnoDB引擎的关键组件和机制,包括引擎架构、Buffer Pool、Page管理机制、Change Buffer、Log Buffer及Adaptive Hash Index。
217 97
MySQL底层概述—1.InnoDB内存结构
|
1月前
|
存储 SQL 缓存
MySQL原理简介—2.InnoDB架构原理和执行流程
本文介绍了MySQL中更新语句的执行流程及其背后的机制,主要包括: 1. **更新语句的执行流程**:从SQL解析到执行器调用InnoDB存储引擎接口。 2. **Buffer Pool缓冲池**:缓存磁盘数据,减少磁盘I/O。 3. **Undo日志**:记录更新前的数据,支持事务回滚。 4. **Redo日志**:确保事务持久性,防止宕机导致的数据丢失。 5. **Binlog日志**:记录逻辑操作,用于数据恢复和主从复制。 6. **事务提交机制**:包括redo日志和binlog日志的刷盘策略,确保数据一致性。 7. **后台IO线程**:将内存中的脏数据异步刷入磁盘。
|
3月前
|
存储 缓存 关系型数据库
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL的存储引擎是其核心组件之一,负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性,可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
|
3月前
|
存储 关系型数据库 MySQL
MySQL存储引擎详述:InnoDB为何胜出?
MySQL 是最流行的开源关系型数据库之一,其存储引擎设计是其高效灵活的关键。InnoDB 作为默认存储引擎,支持事务、行级锁和外键约束,适用于高并发读写和数据完整性要求高的场景;而 MyISAM 不支持事务,适合读密集且对事务要求不高的应用。根据不同需求选择合适的存储引擎至关重要,官方推荐大多数场景使用 InnoDB。
92 7
|
4月前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
226 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件