mysql四种行格式 mysql 行长度

文章目录

• 1 指定行格式的语法
• 2 COMPACT行格式

• 2.1 变长字段长度列表
• 2.2 NULL值列表
• 2.3 记录头信息（5字节）
• 2.4 记录的真实数据

• 3 Dynamic和Compressed行格式

1 指定行格式的语法

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称

ALTER TABLE 表名 ROW_FORMAT=行格式名称

2 COMPACT行格式

在MySQL 5.1版本中，默认设置为Compact行格式。一条完整的记录其实可以被分为记录的额外信息和记录的真实数据两大部分。

2.1 变长字段长度列表

MySQL支持一些变长的数据类型，比如VARCHAR(M)、VARBINARY(M)、TEXT类型，BLOB类型，这些数据类型修饰列称为变长字段，变长字段中存储多少字节的数据不是固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表。

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

注意：这里面存储的变长长度和字段顺序是反过来的。比如两个varchar字段在表结构的顺序是a(10)，b(15)。那么在变长字段长度列表中存储的长度顺序就是15，10，是反过来的。

InnoDB有它的一套规则.为了更好地表述清楚这个规则，我们引入W、M、L几个符号，先分别看看这些符号的意思

• 假设某个字符集中最多需要W字节来表示一个字符(也就执行 SHOW CHARSET句后结果中的 Maxlen 列) ，比如utf8mb4字符集中的W就是4 utf8mb3字符集中的W就是3，gbk字符集中的W就是2 ，ascii字符集中的W就是1
• 对于变长类型 VARCHAR(M)。这种类型表示能存储最多M个字符(注意是字符不是字节) 所以这种类型能表示字符串最多占用的字节数就是M×W
• 假设该变长字段实际存储的字符串占用的字节数是L。

确定使用1字节还是2字节来表示一个变长字段的真实数据占用的字节数的规则就是这样：
如果M×W≤255 ，最多存储小于255字节，那么使用1字节来表示真实数据占用的字节数.
如果M×W≥255 ，最多存储大于255字节，则分为下面两种情况:
如果L≤127，则用1字节来表示实际真实数据占用的字节数
如果L≥127 ，则用2字节来表示实际真实数据占用的字节数

总结下就是:如果该变长字段允许存储的最大字节数(M×W) 超过 255 字节，并且真实数据占用的字节数(L)超过127字节，则使用2字节来表示真实数据占用的字节数，否则使用1字节.

c1、c2、c4列 --》 04、03、01，c2列是null不存放在变长字段列表

逆序存放如下

2.2 NULL值列表

Compact行格式会把可以为NULL的列统一管理起来，存在一个标记为NULL值列表中。如果表中没有允许存储 NULL 的列，则 NULL值列表也不存在了。
为什么定义NULL值列表？
之所以要存储NULL是因为数据都是需要对齐的，如果没有标注出来NULL值的位置，就有可能在查询数据的时候出现混乱。如果使用一个特定的符号放到相应的数据位表示空值的话，虽然能达到效果，但是这样很浪费空间，所以直接就在行数据得头部开辟出一块空间专门用来记录该行数据哪些是非空数据，哪些是空数据，格式如下：

1. 二进制位的值为1时，代表该列的值为NULL。
2. 二进制位的值为0时，代表该列的值不为NULL。

注意：同样顺序也是反过来存放的

如果c1、c3、c4允许为NUL的列，所以这3个列和二进制位的对应关系如图所示，并且还是逆序存放

MySQL 规定 NULL 值列表必须用整数个字节的位表示，如果使用的二进制位个数不足整数个字节，则在字节的高位补0。

如果1个表中有9个值允许为 NULL 的列，则这个记录的 NULL 值列表部分

就需要2字节（1字节8比特）来表示了.

2.3 记录头信息（5字节）

名称	大小（单位：bit）	描述
预留位1	1	没有使用
预留位2	1	没有使用
delete_mask	1	标记该记录是否被删除
mini_rec_mask	1	B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned	4	一个页面中的记录会被分成若干个组，每个组中有一个记录是"带头大哥”，其余的记录都是"小弟"。带头大哥记录的 n_owned代表该组中所有的记录条数，"小弟"记录的n_owned 值都为 0
heap_no	13	表示当前记录在记录堆的位置信息
record_type	3	表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录
next_record	16	表示下一条记录的相对位置

• delete_mask：这个属性标记着当前记录是否被删除，占用1个二进制位。

• 值为0：代表记录并没有被删除
• 值为1：代表记录被删除掉了

被删除的记录为什么还在页中存储呢？
你以为它删除了，可它还在真实的磁盘上。这些被删除的记录之所以不立即从磁盘上移除，是因为移除它们之后其他的记录在磁盘上需要重新排列，导致性能消耗。所以只是打一个删除标记而已，所有被删除掉的记录都会组成一个所谓的垃圾链表，在这个链表中的记录占用的空间称之为可重用空间，之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉。

• min_rec_mask：B+树的每层非叶子节点中的最小记录都会添加该标记，min_rec_mask值为1。我们自己插入的四条记录的min_rec_mask值都是0，意味着它们都不是B+树的非叶子节点中的最小记录。
• record_type：这个属性表示当前记录的类型，一共有4种类型的记录：

• 0：表示普通记录
• 1：表示B+树非叶节点记录
• 2：表示最小记录
• 3：表示最大记录

• heap_no：这个属性表示当前记录在本页中的位置。

怎么不见heap_no值为0和1的记录呢？
MySQL会自动给每个页里加了两个记录，由于这两个记录并不是我们自己插入的，所以有时候也称为伪记录或者虚拟记录。这两个伪记录一个代表最小记录，一个代表最大记录。最小记录和最大记录的heap_no值分别是0和1，也就是说它们的位置最靠前

• n_owned：页目录中每个组中最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段
• next_record：记录头信息里该属性非常重要，它表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。

2.4 记录的真实数据

记录的真实数据除了c1 、c2、c3、c4 这几个我们自己定义
的列的数据外. MySQL 会为每个记录默认地添加一些列(也称为隐藏列如下)

列名	是否必须	占用空间	描述
row_id	否	6字节	行ID，唯一标识一条记录
transaction_id	是	6字节	事务ID
roll_pointer	是	7字节	回滚指针

一个表没有手动定义主键，则会选取一个Unique键作为主键，如果连Unique键都没有定义的话，则会为表默认添加一个名为row_id的隐藏列作为主键。所以row_id是在没有自定义主键以及Unique键的情况下才会存在的。

3 Dynamic和Compressed行格式

我们可以知道一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65533个字节，这样就可能出现一个页存放不了一条记录，这种现象称为行溢出

在Compact和Reduntant行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中进行分页存储，然后记录的真实数据处用20个字节存储指向这些页的地址（当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数），从而可以找到剩余数据所在的页。这称为页的扩展。

在MySQL 8.0中，默认行格式就是Dynamic，Dynamic、Compressed行格式和Compact行格式挺像，只不过在处理行溢出数据时有分歧

• Compressed和Dynamic两种记录格式对于存放在BLOB中的数据采用了完全的行溢出的方式。如图，在数据页中只存放20个字节的指针（溢出页的地址），实际的数据都存放在Off Page（溢出页）中。
• Compact和Redundant两种格式会在记录的真实数据处存储一部分数据（存放768个前缀字节）。