【MySQL】表中的一条数据在磁盘上是如何存放的？-Toy模板网

这篇具有很好参考价值的文章主要介绍了【MySQL】表中的一条数据在磁盘上是如何存放的？。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

1 InnoDB行格式

不同的存储引擎一般是为实现不同的特性来开发的，真实数据在不同存储引擎中的存放格式一般是不同的，甚至有的存储引擎（比如 MEMORY）都不用磁盘来存储数据，也就是对于使用 MEMORY 存储引擎的表来说，关闭服务器后表中的数据就消失了。 lnnoDB是MySQL 默认的存储引擎，也是我们最常用到的存储引擎。

当我们想从表中获取某些记录时， lnnoDB 存储引擎需要一条一条地把记录从磁盘上读出来么？不，这样重复访问磁盘会非常非常非常慢😶，lnnoDB 采取的方式是，将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位。lnnoDB 页的大小一般为16KB = 16384B 。也就是在一般情况下，一次最少从磁盘中读取 16KB 的内容到内存中，一次最少把内存中的 16KB 内容刷新到磁盘中。

Tips：系统变量innodb_page _size只能在第一次初始化MySQL数据目录时指定，运行过程中不可更改页面大小。

我们平时都是以记录为单位向表中插入数据的，这些记录在磁盘上的存放形式也被称为行格式或者记录格式，可以在创建或修改表的语句中指定记录所使用的行格式。目前有4种行格式：

COMPACT：占用空间较小，适合具有大量重复数据的表。
REDUNDANT：不再被推荐使用，用于兼容旧版本。
DYNAMIC：支持更多的数据类型和功能，适用于各种情况。
COMPRESSED：将数据进行压缩，可以显著减小存储空间，但可能会增加CPU负担。

1.1 COMPACT行格式

1.1.1 记录的额外信息

【MySQL】表中的一条数据在磁盘上是如何存放的？,mysql

1、变长字段长度列表（如果无变长字段，没有该部分）

变长字段列表仅存储非NULL列。

VARCHAR(M)、 BINARY(M)、各种 TEXT 类型、各种 BLOB 类型等变长字段占用的存储空间分为两部分：真正的数据内容 + 该数据占用的字节数。COMPACT 行格式中，所有变长字段的真实数据占用字节数都存放在记录的开头位置，各变长字段的真实数据占用的字节数按照列顺序逆序存放。

1个字节能表示的最大值为0xFF = 255，2个字节能表示的最大值为0xFFFF = 65535。给定以下符号表示。

$W$ ：某个字符集中最多需要W字节来表示一个字符，比如utf8mb4字符集中的W就是4

$M$ ：对于变长类型 VARCHAR(M)，能存储最多M个字符

$L$ ：该变长字段实际存储的字符串占用的字节数是 L

如果该变长字段允许存储的最大字节数 $M\times W$ 超过 255 字节，并且真实数据占用的字节数 $L$ 超过 127 字节，则使用2字节来表示真实数据占用的字节数，否则使用1字节。

如果某个字段占用的字节数特别多， InnoDB 有可能把该字段的值的一部分数据存放到所谓的溢出页中。

对于 CHAR(M)类型的列来说，当列采用的是定长编码的字符集时，该列占的字节数不会被加到变长字段长度列表:而如果采用变长编码的字符集时该列占用的字节数就会被加到变长字段长度列表。

2、NULL值列表（如果无NULL值，没有该部分）

COMPACT 行格式把一条记录中值为 NULL 的列统一管理起来，存储到 NULL 值列表。对表中未限制Not Null的列，按列顺序逆序存储二进制位，1代表NULL，0代表不为NULL。MYSQL规定NULL值列表必须为整数字节，若不足，在高位补0。

3、记录头信息(固定5字节)

deleted_flag：这些被删除的记录之所以不从磁盘上移除，是因为在移除它们之后还需要在磁盘上亟新排列其他的记录这会带来性能消耗，所以只打一个删除标记就可以避免这个问题.所有被删除掉的记录会组成一个垃圾链衰，记录在这个链表中占用的空间称为可重用空间。

【MySQL】表中的一条数据在磁盘上是如何存放的？,mysql

1.1.2 记录的真实数据

row_id：如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id不是必需的，占用 6 个字节。
trx_id：由哪一个事务生成
roll_pointer：这条记录上一个版本的指针

【MySQL】表中的一条数据在磁盘上是如何存放的？,mysql

1.2 Dynamic & ComPRESSED

如果某个字段占用的字节数特别多， InnoDB 有可能把该字段的值的一部分数据存放到所谓的溢出页中，在记录的真实数据处只会存储该列的部分数据，而把剩余的数据分散存储在几个其他的页中然后在记录的真实数据处用 20 字节存储指向这些页的地址。

Compressed 和 Dynamic 这两个行格式和 Compact 非常类似，主要的区别在于处理行溢出数据时有些区别。

这两种格式采用完全的行溢出方式，记录的真实数据处不会存储该列的一部分数据，只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中。

【MySQL】表中的一条数据在磁盘上是如何存放的？,mysql

1.3 VarChar(n)中n的最大取值？

MySQL规定：一行记录除了 TEXT、BLOBs 类型的列（不包括隐藏列和记录头信息），限制最大为 65535 字节。

SO：要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535

2 InnoDB页格式

2.1 数据页组成

记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。

因此，InnoDB 的数据是按「数据页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。

数据库的 I/O 操作的最小单位是页，InnoDB 数据页的默认大小是 16KB，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。

【MySQL】表中的一条数据在磁盘上是如何存放的？,mysql

2.2 最大、最小记录与用户记录

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

设计InnoDB 的大叔把记录一条一条亲密无间排列的结构称之为堆 (beap) 。为了方便管理这个堆，他们把一条记录在堆中的相对位置称之为heap_no ，放在一条记录的记录头信息中。

【MySQL】表中的一条数据在磁盘上是如何存放的？,mysql

对于一条完整的记录来说，比较记录的大小就是比较主键的大小。设计InnoDB 的大叔设计了两个虚拟记录，规定infimum记录为一个页面中最小的记录，supermum记录为一个页面中最大的记录。

2.3 页目录

数据页中的记录按照「主键」顺序组成单向链表，单向链表的特点就是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索。因此，数据页中有一个页目录，起到记录的索引作用。

页目录创建的过程如下：

将所有的记录划分成几个组，这些记录包括最小记录和最大记录，但不包括标记为“已删除”的记录；
每个记录组的最后一条记录就是组内最大的那条记录，并且最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段（图中粉红色字段）
页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录。

【MySQL】表中的一条数据在磁盘上是如何存放的？,mysql