MySQL记录删除后竟能按中间被删除的主键加回去,磁盘空间被重用!——底层揭秘MySQL行格式记录头信息

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 当Mysql记录被删除,页中记录存储结构如何变化?当删除的记录再次被插入,页中记录存储结构如何变化?本篇讲解记录头信息的底层原理和计算,让你从原理理解它!

说在前面——本篇也是我读书总结笔记,因为是讲底层原理,我个人认为本文难度是相当高的,可能需要一定的基础。

我将文中的图片包含的计算原理全都演算了一遍,因为书本没有过程详细计算,欢迎大家阅读和仔细推敲。

如果是大忙人,也可以跳过计算,看懂过程和大致原理即可。

1.记录头信息有什么用?

  记录头信息里面有很多属性,最容易理解的就是next_record指针,单链表都会有next指针,这样才会找得到下一个结点,这对于页中的每条记录也是一样,上一条记录需要知道下一条记录在哪里。

  上一篇说到了innodb行格式,重点讲了一下dynamic行格式,知道一条记录实际存储如下图。没办法,说到底层原理如果不看上一篇文章是不可能完全理解的,耶稣来了也没法一篇说明白,见这里MySQL的varchar水真的太深了——InnoDB记录存储结构,必须记住下图的上面行格式部分,每条记录不仅是记录的真实数据,还有记录的额外信息。

如果你目前只是为了开发学习本部分知识,那么可以直接跳到本篇第4、5节。传送-->

  在utf8mb4字符集中,能用0~4字节表示一个字符,像varchar这种变长类型和char这种定长类型实际占用的字节数都会被记录到变长字段列表

  如果字段没有被NOT NULL限定,那么就允许为NULL,该列就会有NULL值列表

  关于记录头信息,下面这个表先列出来,往后面看的时候不理解时可以返回查看这个表,方便理解。

名称 大小(单位:bit) 描述
预留位1 1 没有使用
预留位2 1 没有使用
delete_mask 1 标记该记录是否被删除
min_rec_mask 1 B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned 4 表示当前记录拥有的记录数
heap_no 13 表示当前记录在记录堆的位置信息
record_type 3 表示当前记录的类型,0表示普通记录,1表示B+树非叶节点记录,2表示Infimum记录,3表示Supremum记录
next_record 16 表示本条记录真实数据部分到下一条记录真实数据的距离

从表中所说可以看到,记录头信息一共是40bit就是5个字节

2.记录在页中的存储结构

  innodb管理存储空间的基本单位,一个页的大小默认是16KB,插入的记录会按照指定的行格式(默认dynamic)存储到User Records部分。但是在一开始生成页的时候,其实并没有User Records这个部分,每当我们插入一条记录,都会从Free Space部分(也就是尚未使用的存储空间) 申请一个记录大小的空间,并将这个部分划分到User Records部分,当Free Space部分的空间全部被User Records部分替代掉之后,也就意味着这个页使用完了,如果还有新的记录插入的话,就需要去申请新的页了。

有人会疑问了,图中这个Infimum+Supremum是什么?

   Infimum记录 的下一条记录就是本页中主键值最小的用户记录,而本页中主键值最大的用户记录的下一条记录就是 Supremum记录。但是这两条记录不在User Records部分,是单独占用的空间,可结合上一张图理解。都是由5个字节的记录头和8个字节的一个固定单词组成,如下所示

3.记录头信息的底层原理和计算(show time,难度搞起来)

首先,建个表record_test

CREATE TABLE record_test(
 c1 INT,
 c2 INT,
 c3 VARCHAR(10000),
  PRIMARY KEY (c1)
) CHARSET=utf8mb4;

执行插入语句

INSERT INTO record_test VALUES(1, 100, 'aa你好'), (2, 200, 'bb哈哈'), (3, 300, 'cc来了'), (4, 400, 'dd在哪');

最终表数据如下

4条记录的存储结构示意图如下

注意:
1.图中画记录的时候只选取了记录头的一部分,省略了变长列表和NULL值列表,但是实际计算的时候要带上。
2.这里把隐藏列省略了,归并到 "其他信息" 里面了

  看到这里,你一定和我有着相同的疑问,为什么next_record显示36,它表示本条记录真实数据部分到下一条记录真实数据的距离。这个怎么计算的呢?

  现在我来和你说说底层那些不为人知的东西。要知道,记录的真实数据除了所有的数据列之外,MySQL还会为每条记录默认添加一些列(也称为隐藏列),隐藏列也包含在记录的真实数据部分,如下
| 列名|是否必须 | 占用空间 |描述 |
|--|--|--|--|
|DB_ROW_ID| 否 |6字节 | 行ID,唯一标识一条记录 |
| DB_TRX_ID| 是| 6字节 | 事务ID |
| DB_ROLL_PTR| 是| 7字节| 回滚指针 |

  InnoDB表对主键的生成策略:优先使用用户自定义主键作为主键,如果用户没有定义主键,则选取一个Unique键作为主键(必须NOT NULL不允许存NULL),如果表中连Unique键都没有定义的话,则InnoDB会为表默认添加一个名为DB_ROW_ID的隐藏列作为主键。

  从上表中可以看出:InnoDB存储引擎会为每条记录都添加 DB_TRX_IDDB_ROLL_PTR这两个列,但是 DB_ROW_ID是可选的(在没有自定义主键以及不允许存NULL值的Unique键的情况下才会添加该列)。这些隐藏列的值不用我们操心,InnoDB存储引擎会自己帮我们生成的。

所以刚刚next_record36字节的计算方法就是
6+7(隐藏列2个,因为有自定义主键)=13字节
4(int长度)+4(int长度)+8(变长varchar实际字节数)=16字节
下一列记录的额外信息(变长列表+NULL值列表+记录头)
1+1+5=7字节
总共13+16+7=36

注意,图中画记录的时候只选取了记录头的一部分,计算的时候直接记录头按5字节计算,加上变长列表长度和NULL值列表长度即可。

如果变长列表NULL值列表不知道怎么计算长度,见上一篇MySQL的varchar水真的太深了——InnoDB记录存储结构,不看上篇不可能理解的。

而且你可能会疑问为什么第4条记录的下一条却要-123字节?

  前面说过,最大记录的下一条记录是Supremum记录,而Infimum记录的heap_no0,而Supremum记录的heap_no1,存放位置是在所有记录之前,最小记录的heap_no是从2开始的。前面给大家看过记录在页中的存储结构,知道InfimumSupremum记录在User Records之前。

  所以最大记录的下一条就是要找到Supremum记录,那么就要往回走3条记录和第一条记录的最小记录变长列表+NULL值列表+头信息(共7字节),然后加上Supremum真实数据部分的固定8个字节。

36*3+7+8=123字节

  所以为第4条记录的next_record-123,代表指针往前走123字节就是下一条记录的真实数据部分的地址。


如果你还细致的观察到Infimum记录的next_record28,我觉得你挺适合做研究。

  在存储结构上,Infimum记录后面是Supremum记录,接着才是第一条数据记录。
  逻辑上,Infimum下一条记录是第一条数据记录,所以计算方法是
8(Infimum固定字节) + 5(Supremum记录头) + 8(Supremum固定字节) + 7(第一条数据记录的变长字段列表+NULL值列表+记录头) = 28字节。

  为了更形象的表现next_record的作用,我们用箭头来替代next_record的值,注意箭头指向的位置,都是指向真实数据开始的地址。

你可能会疑问,为啥要next_record指向记录头信息和真实数据之间的位置呢?指向整条记录的开头位置不好吗?

  因为这个位置刚刚好,向左读取就是记录头信息,向右读取就是真实数据。我们前边还说过变长字段长度列表、NULL值列表中的信息都是逆序存放,这样可以使记录中位置靠前的字段和它们对应的字段长度信息在内存中的距离更近,可能会提高高速缓存的命中率。

4.当记录被删除,页中记录存储结构如何变化?

当然最大的疑问就是被删除的记录还在页中么?
  是的,你以为记录删除了,可它还在真实的磁盘上(占用空间依然存在)。这些被删除的记录之所以不从磁盘上移除,是因为移除它们之后,还需要再磁盘中重新排序其他记录,这会带来一定的性能损耗,所以只是打一个删除标记就可以避免这个问题,首先deleted_mask设置为1,然后被删除掉的记录加入到垃圾链表,记录在这个链表中占用的空间称为可重用空间,之后如果有新记录插入到表中的话,它们就可能覆盖掉被删除的这些记录占用的空间。

来演示一下

delete from record_test where c1 = 2;

发现第二条记录被删了

在内存中是怎么样的呢?

删除第2条记录变化如下

  • 2条记录并没有从存储空间中移除,而是把该条记录的delete_mask值设置为1
  • 2条记录的next_record值变为了0,意味着该记录没有下一条记录了。
  • 1条记录的next_record指向了第3条记录。
  • 最大记录的n_owned值从5变成了4,因为除了自身Supremum记录外,还有3条数据记录(注:Infimumn_owned1是因为包含自身算一条记录)

  无论怎么对页中的数据进行增删改操作,InnoDB始终会维护记录的一个单向链表,链表中的各个节点是按照主键值从小到大的顺序链接起来的。

5.当删除的记录再次被插入,页中记录存储结构如何变化?

INSERT INTO record_test VALUES(2, 200, 'bb哈哈');

可以看到,刚刚删除的第二条数据又回来了

内存结构变化如下

  InnoDB并没有因为新记录的插入而为它申请新的存储空间,而是直接复用了原来被删除记录的存储空间。

  当数据页中存在多条被删除掉的记录时,这些记录的next_record属性将会把这些被删除掉的记录组成一个垃圾链表,以备之后重用这部分存储空间。


本篇总结:
  本篇主要讲了Infimum+Supremum部分,分别是页中最小记录的前一个和最大记录的后一个记录,User Records部分使我们插入的真实数据部分,Free Space是页总尚未使用的部分。然后讲解了图中next_record指针地址的计算。
  我们知道,页中的记录是单链表,页与页之间是双向链表,其实每个数据页的File Header部分有上一页和下一页的编号,所以所有数据页会组成一个双向链表。



欢迎一键三连~



有问题请留言,大家一起探讨学习



----------------------Talk is cheap, show me the code-----------------------

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
关系型数据库 MySQL
MySQL查看连接数和进程信息
这篇文章介绍了如何在MySQL中查看连接数和进程信息,包括当前打开的连接数量、历史成功建立连接的次数、连接错误次数、连接超时设置,以及如何查看和终止正在执行的连接进程。
558 10
|
22天前
|
SQL 关系型数据库 MySQL
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
31 0
|
1月前
|
存储 SQL 关系型数据库
mysql中主键索引和联合索引的原理与区别
本文详细介绍了MySQL中的主键索引和联合索引原理及其区别。主键索引按主键值排序,叶节点仅存储数据区,而索引页则存储索引和指向数据域的指针。联合索引由多个字段组成,遵循最左前缀原则,可提高查询效率。文章还探讨了索引扫描原理、索引失效情况及设计原则,并对比了InnoDB与MyISAM存储引擎中聚簇索引和非聚簇索引的特点。对于优化MySQL性能具有参考价值。
|
1月前
|
存储 SQL 关系型数据库
MySQL 存储过程错误信息不打印在控制台
MySQL 存储过程错误信息不打印在控制台
61 1
|
1月前
|
存储 关系型数据库 MySQL
MySQL 如何存储地理信息
MySQL 如何存储地理信息
98 1
|
2月前
|
存储 关系型数据库 MySQL
MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计
覆盖索引、前缀索引、索引下推、SQL优化、EXISTS 和 IN 的区分、建议COUNT(*)或COUNT(1)、建议SELECT(字段)而不是SELECT(*)、LIMIT 1 对优化的影响、多使用COMMIT、主键设计、自增主键的缺点、淘宝订单号的主键设计、MySQL 8.0改造UUID为有序
MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计
|
1月前
|
存储 关系型数据库 MySQL
Mysql行格式DYNAMIC和COMPACT区别
总之,选择哪种行格式取决于具体的应用场景,如数据类型分布、读写比例、存储与性能需求等。在处理大量文本或二进制数据且对存储空间敏感的应用中,DYNAMIC格式可能是更好的选择;而对于混合型数据且对读取性能有一定要求的场景,COMPACT格式可能更合适。在设计数据库时,评估这些因素并进行适当测试,可以帮助确定最适合的行格式。
120 0
|
3月前
|
存储 SQL 关系型数据库
mysql百分数转小数点格式
在MySQL中,将百分数转换为小数点格式是一个简单直接的操作,可以通过基本的数学表达式和函数实现。无论是处理以字符串形式存储的百分数值,还是直接以数值形式表示的百分比,都可以通过适当的转换查询轻松实现这一目标。通过理解和应用这些基本的转换方法,可以有效地处理和分析数据库中的百分比数据。
50 5
|
4月前
|
DataWorks 监控 关系型数据库
利用 DataWorks 数据推送定期推播 MySQL 或 StarRocks Query 诊断信息
DataWorks 近期上线了数据推送功能,能够将数据库查询的数据组织后推送到各渠道 (如钉钉、飞书、企业微信及 Teams),除了能将业务数据组织后推送,也能将数据库自身提供的监控数据组织后推送,这边我们就以 MySQL (也适用于StarRocks) 为例,定期推播 MySQL 的数据量变化等信息,帮助用户掌握 MySQL 状态。
107 1
|
4月前
|
XML Java 关系型数据库
Action:Consider the following: If you want an embedde ,springBoot配置数据库,补全springBoot的xml和mysql配置信息就好了
Action:Consider the following: If you want an embedde ,springBoot配置数据库,补全springBoot的xml和mysql配置信息就好了