[MySQL] InnoDB行存储格式

站长

2023年06月22日 15:43 · 阅读数 15

前言

数据库表行格式决定了数据在磁盘上是如何存储的，反过来也会影响数据管理及数据查询的效率。因为存在一个数据页中的数据行越多，查询数据和索引会更快，缓存数据需要的buffer页面也会更少，更新数据时消耗的磁盘IO也会更少。

表中的数据被分散存储到很多数据页中，这些数据页被组织到一种B+Tree结构的index中，表数据页和索引数据页都是使用这种结构表示。使用主键作为树节点构造出来的索引是聚簇索引，聚簇索引的每一个叶子节点存储的是每一行的所有的列数据，索引节点内存储的数据是主键和页码。使用索引列构造出来的是二级索引，二级索引的叶子结点存储的是索引列和主键列内容，所以节点存储的是索引列和页码。

可变长度的列是一种特例，这种列存储的数据可能会比较多，不太适合保存在B+tree的页面中，因此对于这种存储数据比较多的可变列，有可能会被单独分配页面存储，这种大度分配的页面就称为溢出页，这样的列也被称为页外列。页外列(off-page columns)的数据被存储在被溢出页组成的单链表中，每一个页外列都有其独立的溢出列链表。列的所有数据都存储到溢出页还是前面一部分数据存储到溢出页，主要取决于列的数据长度。

InnoDB支持的行格式

Innodb主要支持4种行格式：REDUNDANT(冗余) 、COMPACT(紧凑)、 DYNAMIC(动态)、 COMPRESSED(压缩)，整体特性如下图所示：

[MySQL] InnoDB行存储格式

REDUNDANT(冗余)

REDUNDANT是mysql中比较早期的一种行存储格式，其行格式的全貌大体如下：

｜---字段数据结束偏移量列表(n->1)---|---记录头信息---|----各列的值(1->n)---|

**字段结束偏移量列表：**存储的从前到后依次存放的是字段n到字段1结束地址的偏移量，如：表中两个字段，字段一的长度是6，字段2的长度是6，那么该地方存储的值为0x0C 0x06，那么字段1的长度就是最后1bytes的值，通过第二个bytes的值 - 第一个bytes的值可以得到第二个字段的长度。

**记录头信息：**该位置占用48位，从左到右依次用途表示如下：

保留位：1位
保留位：1位
deleted_flag: 1位，表示记录是否被删除
min_rec_flag: 1位，是否为B+Tree叶子结点中最小的记录项
n_owned: 4位，存储页面会被分组，每一个组的第一条记录会通过n_owned记录该分组中的记录数，组内其他记录n_owned为0
heap_no: 13位，表示该记录在页面堆中的相对位置
n_field: 10位，表示记录中的列数
1byte_offs_flag: 1位，标识**字段数据结束偏移量列表**中，对一个的偏移量是使用一个字节还是两个字节表示，为1时表示偏移量使用一个字节存储，为2时表示偏移量使用两个字节存储。用L表示整条记录的长度，当 L <= 127时，1byte_offs_flag=1，当 L > 127 && L <= 32767时，1byte_offs_flag=2
next_record: 16位，下一条记录在页面中的绝对位置

**注意：**字段数据结束偏移量无论是采用两个字节表示还是一个字节表示，都会将便宜量值首位空出来，该空出来的这一位就用来标识该列是否是null。

**注意：当列内存储的的数据长度大于32767时，列内只会存储768字节的数据、20字节的溢出地址信息**

REDUNDANT如何处理CHAR(N)列

CHAR(M)会分配M个字符的空间，如果字符集大小不固定，会按照字符集占用的最大字节数计算需要的存储空间，如utf8占用的存储空间是1-3 bytes，CHAR(8)占用的空间就是24 bytes，超出实际存储数据意外的空间会填充空格，如CHAR(10)的空间里面只存储5个字符，那么剩余空间会填充空格字符。

VARCHAR(M)会分配实际存储的数据的空间，如果 VARCHAR(10)实际存储了5个字符，那么会分配5个字符的空间，但如果实际数据后面有空格，不会被移除。

参考文章：[MySQL] CHAR和VARCHAR的区别

REDUNDANT如何处理****NULL列

redundent对null值的处理分为两种情况：

定长字段null：那么**字段数据结束偏移量首字节为1，后面1-2bytes表示定长字段的长度，如utf8的CHAR(M)为null，那么该字段的数据部分会填充3*M个bytes的0x00，并将对应的偏移量信息写入字段数据结束偏移量**对应的位置。
非定长字段null: 那么**字段数据结束偏移量首字节为1，数据部分不为该字段保留任何空间，并将空位置对应的偏移量信息写入字段数据结束偏移量**对应的位置。

REDUNDANT填充列

每一个行记录中除了用户自己的数列，还有一些存储引擎管理所需的字段，填充字段放在字段用户列的前面，从前到后依次介绍如下：

raw_id: 占用6 bytes，当用户没有主键的时后，mysql系统会帮助用户插入该列
trx_id: 占用6 bytes，表示该列最后一次被修改时的事物id
roll_pointer: 占用7 bytes，mysql的行每次都被修改都会产生新记录，该列指向本次被修改前的记录

COMPACT(紧凑)

compact行格式相比于redundant行格式可以节省大约20%的存储空间，但是在某些操作上会增加CPU的消耗，可以根据系统瓶颈点是CPU还是磁盘，选择合适的行格式。

compact行格式会将列的前768字节保存在索引记录中，其他的字节保存在溢出页中。当数据没有超过768字节时，虽然在某些情况下会节省IO，但是会造成索引页中填充太多数据，影响索引的效率。

compact行格式大体如下：

｜---可变数据长度字段列表(n->1)---|---NULL值列表(n->1)---|---记录头信息---|---各非空列的值(1->n)---|

**可变数据长度字段列表：**可变数据长度除了包含呢VARCHAR、TEXT等变长类型，还保存定长类型使用了变长编码存储数据的列，如果CHAR类型列使用的编码是ascii类型，那么该字段属于定长类型CHAR(M)占用的数据长度是M字节，但是如果CHAR类型列使用的编码是utf-8类型，由于uft-8编码的长度是1-3字节，那么CHAR(M)，占用的长度就是M-3M之间，所以该列仍然是数据变长度可变列。

NULL值列表：用位表示，按照8位对齐，也就说如果表中有两列可以出现NULL值，那么该列表就会占用1个字节，可以出现NULL值的列从后向前表示，多余高6位用0填充，当这两列某一列的值为NULL时，该列即使是可变数据长度也不会在可变数据长度字段列表中出现。如：表中包含6列分别表示为c0, c1, c2, c3, c4, c5，如果只有c2,c3两列可以出现NULL值，因此当c2 ！= NULL，c3 == NULL时，NULL列表表示为：0000 0010，如果c2 == NULL，c3 == NULL，那么NULL列表表示为：0000 0011。

**记录头信息：**该位置占用40位，从左到右依次用途表示如下：

保留位：1位
保留位：1位
deleted_flag: 1位，表示记录是否被删除
min_rec_flag: 1位，是否为B+Tree叶子结点中最小的记录项
n_owned: 4位，存储页面会被分组，每一个组的第一条记录会通过n_owned记录该分组中的记录数，组内其他记录n_owned为0
heap_no: 13位，表示该记录在页面堆中的相对位置
record_type: 3位，表示当前记录类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录
next_record: 16位，下一条记录在页面中的绝对位置

COMPACT****填充列

每一个行记录中除了用户自己的数列，还有一些存储引擎管理所需的字段，填充字段放在字段用户列的前面，从前到后依次介绍如下：

raw_id: 占用6 bytes，当用户没有主键的时后，mysql系统会帮助用户插入该列
trx_id: 占用6 bytes，表示该列最后一次被修改时的事物id
roll_pointer: 占用7 bytes，mysql的行每次都被修改都会产生新记录，该列指向本次被修改前的记录

****COMPACT如何处理CHAR(N)列

CHAR(M)会分配M个字符的空间，如果字符集大小不固定，会按照字符集占用的最小字节数计算需要的存储空间，如utf8占用的存储空间是1-3 bytes，CHAR(8)至少占用的空间就是8字节，当存储的数据小于8字节时，更新数据直接在当前空间内直接更新，如果超过8字节就要重新分配空间，CHAR如果实际存储的数据后面存在空格会被移除。

VARCHAR(M)会分配实际存储的数据的空间，如果 VARCHAR(10)实际存储了5个字符，那么会分配5个字符的空间，但如果实际数据后面有空格，不会被移除。

DYNAMIC(动态)、 COMPRESSED(压缩)

这两种行格式类似于COMPACT行格式，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实数据处存储字符串的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。

另外，Compressed行格式会采用压缩算法对页面进行压缩。

参考

[1] mysql官方文档：dev.mysql.com/doc/refman/…