MySQL(InnoDB剖析):16---table之（行记录格式(Compact、Redundant、Compressed、Dynamic)、行溢出数据、CHAR的行结构存储）

董哥的黑板报

已于 2022-08-18 08:23:06 修改

阅读量3.8k

点赞数 1

分类专栏： MySQL(InnoDB剖析) 文章标签：行记录格式：Compact、Redundant Compressed、Dynamic 行溢出数据 CHAR的行结构存储

于 2020-02-01 15:11:01 首次发布

本文链接：https://blog.csdn.net/qq_41453285/article/details/104125757

版权

MySQL(InnoDB剖析) 专栏收录该内容

55 篇文章 24 订阅

订阅专栏

一、InnoDB行记录格式

InnoDB存储引擎和大多数数据库一样，记录是以行的形式存储的，意味着页中保存着表中一行行的数据
在InnoDB 1.0.X版本之前，InnoDB提供了Compact和Redundant两种格式来存放行记录数据。Redundant是为了兼容之前版本而保留的
如果阅读了InnoDB的源代码会发现，代码中分别用PHYSICAL RECORD（NEW STYLE）和PHYSICAL RECORD（OLD STYLE）来区分Compact和Redundantde
在MySQL 5.1版本中，默认以Compact行格式
用户可以通过下面的命令来查看当前表使用的行格式

-- 其中table_name为要查看的表的名称
show table status like 'table_name';

知道了页中行记录的组织规则之后，就可以自己编写工具/脚本来读取其中的记录

二、Compact行记录格式

Compact行记录是在MySQL 5.0中引入的，其设计目标是高效地存储数据。一个页中存放的行数据越多，其性能就越高

Compact行记录格式如下：

变长字段长度列表

首部是一个非NULL变长字段长度列表，并且其是按照列的顺序逆序放置的，其长度为：
1字节：若列的长度小于255字节
2字节：大列的长度大于255字节
变长字段的长度最大不可以超过2字节，因为MySQL数据库中varchar类型的最大长度限制为65535

NULL标志位

该位用来指示该行数据中是否有NULL值，哪一个列的值为NULL，其哪一位的之就为1，反之则为0
该部分所占的字节应该为1字节

记录头信息（record header）

该部分固定占用5字节（40位），每位的含义如下：

之后的部分

之后的部分就是实际存储每个列的数据

NULL列、两个隐藏列(事务ID、回滚指针)、rowid列

特别注意：NULL是不占用任何空间的，即NULL除了占用NULL标志位外，实际上不占用任何存储空间
每行数据除了用户定义的列外，还有两个隐藏列：事务ID列（6字节）和回滚指针列（7字节）
InnoDB如果没有定义主键，每行还会增加一个6字节的rowid列

演示案例（剖析.ibd文件）

此处我们开启innodb_file_per_table参数，那么创建的表格会拥有一个单独的.ibd文件来存储表数据
第一步：创建一个表
create table mytest(
    t1 varchar(10),
    t2 varchar(10),
    t3 char(10),
    t4 varchar(10),
)ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT;
第二步：插入3行数据（注意第3行插入了NULL值）
insert into mytest values('a','bb','bb','ccc');

insert into mytest values('d','ee','ee','fff');

insert into mytest values('d',NULL,NULL,'fff');
第三步：找到mytest表的.ibd文件，使用“hexdump -C -v mytest.ibd>mytest.txt”命令将内容定向到一个文件中，然后文件，结果如下，行的记录从0000c078开始

第四步：现在开始分析第一行的数据
变长字段长度列表是逆序存放的，因此变长字段长度列表为03 02 01而不是01 02 03
没有NULL值，因此NULL标志位为0
记录头字段（record header）：0x2c为next_recorder字段的值，其代表下一个记录的偏移量(即当前记录的位置加上0x2c就是下条记录的其实额日志)。因此InnoDB在页内部是通过链表的结构来串连各个行记录的
下面是两个隐藏列TransactionID和Rool Pointer
再下面的就是列的数据了（注意列3 char类型的字段，未能完全占用其长度空间时，用0x20进行填充）

第二行的数据就不再讲解了，除了RowID不同外，其余的与第一行大同小异
第五步：现在来分析第三行的数据
因为有NULL值，因此NULL标志位为06，转换为二进制为00000110，为1的值代表第2列和第3列的数据为NULL
另外，因为第2列和第3列的值为NULL，因此没有存储这两个列的值

三、Redundant行记录格式

Redundant是MySQL 5.0版本之前InnoDB的行记录存储格式，MySQL 5.0支持Redundant是为了兼容之前版本的页格式

Redundant行记录格式如下：

字段长度偏移列表

与Compact行格式的一样，同样是按照列的顺序逆序放置的。其大小为：
1字节：若列的长度小于255字节
2字节：若列的长度大于255字节

记录头信息（record header）

不同于Compact的行格式，Redundant的记录头占用6字节（48位），其中每位的含义如下：
n_fields：代表一行中列的数量，占用10位。这也解释了MySQL中一行支持最多的列为1023个
1byte_offs_flags：该值定义了偏移列表占用1字节还是2字节

之后的字段

之后的部分就是实际存储每个列的数据

演示案例（剖析.ibd文件）

第一步：现在创建一个与上面mytest一样的表mytest2，但是表的行记录格式为“Redundant”
create table mytest2 engine=InnoDB ROW_FORMAT=Redundant
as
select * from mytest;
第二步：同样使用hexdump命令将表空间mytest2.ibd导出到文件中，然后进行查看

第三步：现在分析第一行的数据，如下
长度偏移列表：23 20 16 14 13 0c 06逆转为06 0c 13 14 16 20 23，分别代表第1列、2列、3列、4列、5列、6列、7列的长度分别为6、(6+6=0x0C)、(6+6+7=0x13)、(6+6+7+1=0x14)、(6+6+7+1+2=0x16)、(6+6+7+1+2+10=0x20)、(6+6+7+1+2+10+3=0x23)
记录头信息：注意48位中的第22~32位（为0000000111），表示共有7个列（包含了隐藏的3列），接下来的第33位为1，代表偏移列表为一个字节
接下来就是每个列存储的数据了

第二行的数据就不分析了，与第一行类似
第四步：现在来分析第三行的数据，其他的都类似，就是第三列数据不同
长度偏移列表：逆序之后为06 0c 13 14 94 9e 21（前4个都可以理解，第5个NULL值变为了94），接着第6个char类型的NULL值为9e(94+10=0x9e)，21代表(14+3=0x21)
可以看到对于varchar类型的null值，Redundant行记录格式同样不占用任何存储空间，而char类型的null值需要占用空间

表的字符集为Latin1，每个字符最多只占用1字节。若用户将表的字符集改为utf8，则每个字符就占用3个字节。因此在Redundant下，更加浪费空间

三、行溢出数据

InnoDB可以将一条记录中的某些数据存储在真正的数据页面之外。一般认为BLOB、LOB这类的大对象列类型的存储会把数据存放在数据页面之外。但是，这个理解有点偏差，BLOB可以不将数据放在溢出页面，而且即便是varchar列数据类型，依然有可能被存放为行溢出数据

剖析varchar的溢出

首先对varchar数据类型进行研究
MySQL的varchar可以存放65535字节。但是真的在创建表时不能够创建那么大的varchar，如果创建会显示如下的错误：

从上面错误消息可以看到InnoDB存储引擎并不支持65535长度的varchar，这是因为还有别的开销。通过测试发现varchar的最大长度为65532。例如，创建下面的表：

需要注意的是，如果在执行上述案例的时候没有将SQL_MODE设为严格模式，或许可以建立表，但是MySQL会抛出一个警告，如下：

警告提示了这次可以创建成功，是因为MySQL自动将varchar转换为了text类型。查看test的表结构可以发现：

还需要注意的是上面创建的varchar长度为65532的表，其字符类型是latin1，如果换成GBK或者UTF-8的话，又会创建失败，如下所示：

从上图可以看出两次报错中max值不同。因此我们知道varchar(N)中的N指的是字符的长度，单位为字节
此外需要注意的是，MySQL中定义的65535长度是指所有varchar列的总和，如果列的长度总和超过了这个长度，依然无法创建成功，如下所示：

3个列长度总和是66000，因此InnoDB存储引擎再次报了同样的错误

varchar的行溢出

InnoDB存储引擎的页为16KB，即16384字节，那么怎么能存储65532字节呢？因此，在一般情况下，InnoDB存储引擎的数据都是存放在页类型为B-tree node中，但是当发生行溢出时，数据存放在页类型为Uncompress BLOB页中
下面来看一个例子，创建一个列a长度为65532的varchar类型的表t，然后插入列a长度为65532的记录

下面通过工具py_innodb_page_info查看表空间文件，可以看到的页类型有：

通过工具观察到表空间中有一个数据页节点B-tree Node，另外有4个未压缩的二进制大对象页Uncompressed BLOB Page，在这些页中才真正存放了65532字节的数据
既然实际存放的数据都在BLOB页中，那数据页中又存放了些什么内容呢？通过hexdump工具来读取表空间文件，从数据页c000开始查看：

可以看到从0x0000c093到0x0000c392数据页面其实只保存了varchar(65532)的前768字节的前缀(prefix)数据（这里都是a），之后是偏移量，指向行溢出页，也就是前面所看到的Uncompressed BLOB Page

行溢出数据的存储方式

因此，对于行溢出数据，其存放采用下图所示的方式：

那么多长的varchar是保存在单个数据页中，从多长开始又会保存在BLOB页呢？
可以这样思考：InnoDB表时索引组织的，即B+ Tree的结构，这样每个页中至少应该有两条行记录（否则失去了 B+ Tree的意义，变成链表了）。因此，如果页中只能存放下一条记录，那么InnoDB存储引擎会自动将行数据存放到溢出页中
查看下面表的一种情况：

a字段的长度为9000，故能存放在一个数据页中，但是这并不能保证两条长度为9000的记录都能存放在一个页中。
此时通过py_innodb_page_info工具查看，可知行数据是否存放在BLOB页中

但是，如果可以在一个页中至少放入两行数据，那varchar类型的行数据就不会存放到BLOB页中去。经过多次试验，发现这个阈值的长度为8098。如用户建立一个列为varchar(8098)的表，然后插入2条记录

接着使用py_innodb_page_info工具对表空间ibd文件查看，可以发现此时的行记录都是存放在数据页中，而不是在BLOB页中了（熟悉SQL Server数据库的DBA可能会感觉InnoDB存储引擎对于varchar类型的管理和SQL Server的varchar(max)类似）

TEXT和BLOB如何存储

对于TEXT或BLOB的数据类型，用户总是以为它们是存放在Uncompressed BLOB Page中的，其实这也是不准确的。是放在数据页中还是BLOB页中，和前面讨论的varchar一样，至少保证一个页能存放两条记录，如：

上述例子建立含有BLOB类型列的表，然后插入4行数据长度为8000的记录。若通过py_innodb_page_info工具对表空间ibd文件查看，会发现其实数据并没有保存在BLOB页中

当然既然用户使用了BLOB列类型，一般不可能存放长度这么小的数据。因此在大多数情况下BLOB的行数据还是会发生行溢出，实际数据保存在BLOB页中，数据页只保存数据的前768字节

四、Compressed和Dynamic行记录格式

InnoDB 1.0.x版本引入了新的文件格式（可以理解为新的页格式）
Antelope文件格式、Barracuda文件格式：
- Antelope：上面介绍的支持Compact和Redundant的格式称为Antelope文件格式
- Barracuda：新的文件格式称为Barracuda文件格式
Barracuda文件格式包括：Compressed和Dynamic

特点①

Compressed、Dynamic格式对于存放在BLOB中的数据采用了完全的行溢出方式，如下图所示：
数据页中只存放20个字节的指针
实际的数据都存放在Off Page中

而之前Compact和Redundant两种格式会存放768个前缀字节

特点②

Compressed行记录格式的另一个功能是：存储在其中的行数据会以zlib的算法进行压缩。因此对于BLOB、TEXT、VARCAHR这类大长度类型的数据能够进行非常有效的存储

五、CHAR的行结构存储

通常理解下，varchar是存储变长长度的字符类型，char是存储固定长度的字符类型
然而，值得注意的是之前给出的例子中字符集都是单字节的latin1格式。从MySQL 4.1开始，char(N)中的N代表字符的长度而不是字节长度。也就是说在不同的字符集下，char类型列内部存储的可能不是定长的数据

演示案例

创建表j的字符集是GBK，然后用户分别插入了两个字符的数据'ab'和'我们'
create table j(
    a char(2)
)CHARSET=GBK ENGINE=InnoDB;

insert into j select 'ab';

set names gbk;

insert into j select '我们';

insert into j select 'a';
查看所占字节，结果如下，可以看到，前两个记录'ab'和'我们'字符串的长度都是2(char_length)，而内部存储上'ab'占用2字节，而'我们占用4字节'：
select a,char_length(a),length(a) from j;
如果通过HEX函数查看内部十六进制的存储，可以看到：
select a,hex(a) from j;
对于字符串'ab'，其内部存储为0x6164。而字符串'我们'为0xE68891E4
因此对于多字节的字符编码，char类型不再代表固定长度的字符串了。例如，对于UTF-8下的char(10)类型的列，其最小可以存储10字节的字符串，而最大可以存储30字节的字符
因此，对于多字节字符编码的char数据类型的存储，InnoDB存储引擎在内部将其视为变长字符类型
这也意味着在变长长度列表中会记录char数据类型的长度
下面通过hexdump工具来查看表空间j.ibd文件：

整理之后，每一行的记录如下

上述例子清楚地显示了InnoDB存储引擎内部对char类型在多字节字符集类型的存储
char类型被明确视为了变长字符类型，对于未能占满长度的字符还是填充0x20。InnoDB内部对字符的存储和我们用HEX函数看到的也是一致的。因此可以认为在多字节字符集的情况下，char和varchar的十几行存储基本时没有区别的