MySQL之InnoDB行记录

MySQL 不同的存储引擎中真实数据存放的格式一般是不同的，下文简单介绍InnoDB中的行记录格式。

InnoDB存储引擎目前共支持四种行格式，如下表：

行格式	紧凑存储	增强的可变长度列存储	大索引键前缀支持	支持压缩
REDUNDANT	否	否	否	否
COMPACT	是	否	否	否
DYNAMIC	是	是	是	否
COMPRESSED	是	是	是	是

MySQL 5.0之后的默认行格式为Compact ，MySQL5.7之后的默认行格式为Dynamic。

Compact

示意图如下。

记录的额外信息

变长字段长度列表

varchar(M) 、 varbinary(M) 、text、blob等不确定数据具体长度的数据类型中，存储多少字节的数据是不固定的，我们在存储真实数据的时候需要把这些数据占用的字节数也存起来，读取数据的时候，才能准确读完整这些不确定长度的数据。所以变长字段实际需要存储一下信息

• 实际的字段值
• 实际字段长度（字节长度）

如果该可变字段允许存储的最大字节数超过255字节并且真实存储的字节数超过127字节，则使用2个字节，否则使用1个字节。原由如下所述：

最大字节数由表设置的字符编码集和varchar(M)的M来共同决定。比如在utf-8字符编码集中，一个字符最多需要使用 3 个字节来来表示，那么最大字节数就是 3*M。

字节长度的最高位不用来表示长度，而是用来区分它正在读的某个字节是一个单独的字段长度还是半个字段长度。如果该字节的第一个位为0，那 该字节就是一个单独的字段长度（使用一个字节表示不大于127的二进制的第一个位都为0）， 如果该字节的第一个位为1，那该字节就是半个字段长度。

在 Compact 行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长 字段长度列表，各变长字段数据占用的字节数按照列的顺序**逆序存放**。

变长字段长度列表中只存储值为非NULL 的列内容占用的长度，值为 NULL 的列的长度 是不储存的

char(M)算不算变长？

**看具体使用的字符集！**如果是ascii字符集，所有字符都固定占用1字节，那就不需要记录。如果是utf-8，一个字符占用的字节数为1~3，这种不固定的字符集，就需要存储了变长字段长度了。例如CHAR(10)使用utf-8字符集，占用的存储空间范围是10~30，列的实际长度还是不确定的。

NULL值列表

表中的某些列可能存储 NULL 值，如果把这些 NULL 值都放到 记录的真实数据 中存储会浪费磁盘空间，所
以 Compact 行格式把这些值为NULL 的列统一管理起来，存储到 NULL 值列表中。

1. 首先统计表中允许存储 NULL的列有哪些
2. 如果表中没有允许存储 NULL的列，则 NULL值列表 也不存在了，否则将每个允许存储 NULL的列对应一个 二进制位，**二进制位按照列的顺序逆序排列**，二进制位表示的意义如下
   • 二进制位的值为 1 时，代表该列的值为 NULL
   • 二进制位的值为 0 时，代表该列的值不为 NULL
3. MySQL 规定 NULL值列表 必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节 的高位补 0

为什么变长字段和NULL值列表都是逆序存储？

记录头信息里有一个指针，将一条条记录串联成单向链表。指针指向的位置并不是一条完整记录的起始位置，而是图中「记录的真实数据」的起始位置。这样的好处是，往右读就是真实数据，往左读就是头信息，根据计算机的局部性原理，更容易提高二者缓存的命中率。

记录头信息

由固定的 5 个字节组 成。 5 个字节也就是 40 个二进制位，不同的位代表不同的意思。

名称	大小（bit）	说明
预留位1	1	没有使用
预留位2	1	没有使用
deleted_flag	1	记录删除标记
min_rec_flag	1	B+树非叶子节点的最小目录项标记
n_owned	4	同一页内同一组里最大的记录会记录组里的记录数量，其余记录该值为0
heap_no	13	当前记录在页面堆里的相对位置
record_type	3	记录类型。0: 普通记录, 1: B+树非叶子节点目录项记录, 2: Infimum记录, 3: Supremum记录.
next_record	16	下一条记录的相对位置

deleted_flag

DELETE命令删除记录，并不会真的将它从磁盘中删除，而是仅仅打一个标记，然后把该条记录加入到「垃圾链表」里，垃圾链表占用的空间称为「可重用空间」，以后如果在这个位置插入新的记录就可以重用这部分空间了。如果一个页内所有的记录都被删除了，那么这个页就称为「可重用的页」。

min_rec_flag

InnoDB引擎组织数据的形式采用了B+树，用户记录存储在叶子节点，目录项(也可叫索引项)存储在非叶子节点，一个个节点就是一个个页，同一个非叶子节点内最小的目录项该比特位为1，其余均为0。

n_owned

InnoDB引擎页大小默认是16KB，同一个页内可能会存储很多的用户记录，甚至上千条。为了提高页内的检索效率，InnoDB会将记录划分为多个不同的组，组内记录值最大的一条称为“大哥”，其余的都是“小弟”，“大哥”会利用该属性来记录组内的记录数量，各个组的“大哥”的值会按照顺序被记录在页内的页目录位置。

这里记录的组内记录数量是有效记录的数量，不包括被移除到垃圾链表的记录

heap_no

用户记录存储在页的User Records部分，MySQL将这部分结构称作堆(Heap)，每申请一块记录空间，都会为其分配一个heap_no，越靠前的记录heap_no越小，越靠后的记录heap_no越大。

record_type

记录类型，共有以下几种值

• 0：用户自己插入的记录，或二级索引叶子节点记录。
• 1：B+树非叶子节点目录项记录，冗余的索引项记录。
• 2：页内虚拟的最小记录：Infimum
• 3：页内虚拟的最大记录：Supremum

next_record

表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量，它指向的位置是记录头和真实列数据的中间，往左读就是记录头信息，往右读就是真实数据

用户记录会根据主键值排序并构建一条单向链表，链表就是通过该属性来构建的。它代表当前记录的真实数据到下一条记录的真实数据的距离，值为正数代表下一条记录在后面，值为负数代表下一条记录在前面。

MySQL规定，页中Infimum的下一条记录是本页中主键值最小的记录，主键值最大的记录next_record一定指向Supremum。

Redundant

字段长度偏移列表
REDUNDANT没有区别对待定长和变长字段，将所有列占用的存储空间都逆序存放在字段长度偏移列表中。根据字段的偏移量就可以定位到字段的存储位置，和下一个偏移量的差值可以计算出字段的长度，从而取出字段的完整信息。

记录头信息
REDUNDANT记录头信息固定占用6字节，即48个比特位，每个比特位代表的含义如下表：

名称	大小（bit）	说明
预留位1	1	没有使用
预留位2	1	没有使用
deleted_flag	1	记录删除标记
min_rec_flag	1	B+树非叶子节点的最小目录项标记
n_owned	4	同一页内同一组里最大的记录会记录组里的记录数量，其余记录该值为0
heap_no	13	当前记录在页面堆里的相对位置
n_field	10	记录中列的数量
1byte_offs_flag	1	标识字段长度偏移列表里用1字节还是2字节存储长度
next_record	16	下一条记录的相对位置

使用几个字节来记录字段长度偏移量？
当记录所有列的总长度不超过127时，使用1字节存储，因为总长度都没超过127，单个字段的长度肯定不会超过127。列总长度大于127时，使用2字节存储。2字节最多能表示65535，有没有可能一行记录占用的空间超过了65535呢？是有可能的，这种情况称为 行溢出，记录的真实数据处只会保存前768字节的数据+20字节的指针，剩余的数据则存储在专门的「溢出页」中。

如何处理NULL？
REDUNDANT没有专门的「NULL值列表」，那它是如何处理NULL值的呢？还记得「字段长度偏移列表」吗？1字节最大能表示255，为啥超过127就开始使用2字节呢？原因就在于，REDUNDANT会把第0位用来标记是否为NULL，第0位是1则代表值为NULL，是0就不为NULL。

定长列和变长列处理NULL值的区别？
如果定长列存储的是NULL值，则NULL值也会占用存储空间，数据全部用0x00字节填充。例如char(10)就会占用10个字节(与字符集有关，utf8则直接占用30字节)，这样做的好处是，以后update该列时，可以直接复用这一块空间。如果变长列存储的是NULL值，则NULL值本身不占空间。

Dynamic

和Compact很类似，只是处理行溢出的方式不太一样。

是MySQL 版本 5.7 之后的默认行格式。

Compressed

Compressed 行格式和 Dynamic 不同的一点是， Compressed 行格式会采用压缩算法对页面进行压缩，以节省空间。

行溢出

MySQL中管理数据的最小单位是页，默认情况下，页大小为**16KB，并且要求每页至少保存两条记录。**

MySQL规定一行记录，除了 BLOB 或者 TEXT 类型的列之 外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节。

比如说我们为了存储一个 VARCHAR(M) 类型的列，其实需要占用3部分存储 空间：

• 真实数据
• 真实数据占用字节的长度
• NULL 值标识，如果该列有 NOT NULL 属性则可以没有这部分存储空间 如果该 VARCHAR 类型的列没有 NOT NULL 属性，那最多只能存储 65532 个字节的数据，因为真实数据的长度可能 占用2个字节， NULL 值标识需要占用1个字节。

即会有以下两种情况

1. 创建表时，定义的数据列所占用的字节大小，超过了MySQL规定的上限（会报错）
2. 一页是**16KB即16384Byte**，一行最大是 65535Byte，如果插入的数据超过了页的大小，也是行溢出

不只是 VARCHAR(M) 类型的列，其他的 TEXT、BLOB 类型的列在存储数据非常多的时候 也会发生行溢出。

在Compact 和 Reduntant 行格式中，对于占用存储空间非常大的列，在记录的真实数据 处只会存储该列的前 768 个字节的数据和一个指向其他页的地址（20字节，这20个字节中还包括这些分散在其他页面中的数据的占用的字节数），把剩余的数据分散存储在几个其他的页中。这个 过程也叫做 行溢出 ，存储超出 768 字节的那些页面也被称为 溢出页 。

Dynamic 和 Compressed 行格式，它们不会在记 录的真实数据处存储字段真实数据的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。

查看、修改行格式

查看

查看表信息，即可看到行格式(Row_format)，命令如下

show table status from db_name like "table_name";

效果如下：

Name           |Value              |
---------------+-------------------+
Name           |table_name         |
Engine         |InnoDB             |
Version        |10                 |
Row_format     |Dynamic            |
Rows           |0                  |
Avg_row_length |0                  |
Data_length    |16384              |
Max_data_length|0                  |
Index_length   |32768              |
Data_free      |0                  |
Auto_increment |2                  |
Create_time    |2022-08-10 18:41:32|
Update_time    |2022-08-10 18:38:27|
Check_time     |                   |
Collation      |utf8mb4_general_ci |
Checksum       |                   |
Create_options |                   |
Comment        |样例表              |

修改

ALTER TABLE 表名 ROW_FORMAT = 行格式名称;

参考

• MySQL行格式
• 《从根上理解MySQL是如何运行的》