MySQL如何从二进制内容看InnoDB行格式

共计 7873 个字符，预计需要花费 20 分钟才能阅读完成。

这篇文章主要介绍“MySQL 如何从二进制内容看 InnoDB 行格式”，在日常操作中，相信很多人在 MySQL 如何从二进制内容看 InnoDB 行格式问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL 如何从二进制内容看 InnoDB 行格式”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！

InnoDB 是一个将表中的数据存储到磁盘上的存储引擎，所以即使关机后重启我们的数据还是存在的。而真正处理数据的过程是发生在内存中的，所以需要把磁盘中的数据加载到内存中，如果是处理写入或修改请求的话，还需要把内存中的内容刷新到磁盘上。而我们知道读写磁盘的速度非常慢，和内存读写差了几个数量级，所以当我们想从表中获取某些记录时，InnoDB 存储引擎需要一条一条的把记录从磁盘上读出来么？

InnoDB 采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB 中页的大小一般为 16KB。也就是在一般情况下，一次最少从磁盘中读取 16KB 的内容到内存中，一次最少把内存中的 16KB 内容刷新到磁盘中。

mysql  show variables like  %innodb_page_size% 
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。InnoDB 存储引擎设计了 4 种不同类型的行格式，分别是 Compact、Redundant、Dynamic 和 Compressed 行格式。

行记录格式的分类和介绍

在早期的 InnoDB 版本中，由于文件格式只有一种，因此不需要为此文件格式命名。随着 InnoDB 引擎的发展，开发出了不兼容早期版本的新文件格式，用于支持新的功能。为了在升级和降级情况下帮助管理系统的兼容性，以及运行不同的 MySQL 版本，InnoDB 开始使用命名的文件格式。

在 msyql 5.7.9 及以后版本，默认行格式由 innodb_default_row_format 变量决定，它的默认值是 dynamic：

mysql  show variables like  innodb_file_format 
+--------------------+-----------+
| Variable_name | Value |
+--------------------+-----------+
| innodb_file_format | Barracuda |
+--------------------+-----------+
1 row in set (0.01 sec)
mysql  show variables like  innodb_default_row_format 
+---------------------------+---------+
| Variable_name | Value |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+
1 row in set (0.00 sec)

查看当前表使用的行格式：

mysql  show table status like  dept_emp \G*************************** 1. row ***************************
 Name: dept_emp Engine: InnoDB
 Version: 10
 Row_format: Dynamic Rows: 331570
 Avg_row_length: 36
 Data_length: 12075008Max_data_length: 0
 Index_length: 5783552
 Data_free: 0
 Auto_increment: NULL
 Create_time: 2021-08-11 09:04:36
 Update_time: NULL
 Check_time: NULL
 Collation: latin1_swedish_ci
 Checksum: NULL
 Create_options: Comment:1 row in set (0.00 sec)

指定表的行格式：

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

如果要修改现有表的行模式为 compressed 或 dynamic，必须先将文件格式设置成 Barracuda：set global innodb_file_format=Barracuda;，再用 ALTER TABLE tablename ROW_FORMAT=COMPRESSED; 去修改才能生效。

行格式 COMPACT

变长字段列表

MySQL 支持一些变长的数据类型，比如 VARCHAR(M)、VARBINARY(M)、各种 TEXT 类型，各种 BLOB 类型，我们也可以把拥有这些数据类型的列称为变长字段，变长字段中存储多少字节的数据是不固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。如果该可变字段允许存储的最大字节数（M×W）超过 255 字节并且真实存储的字节数（L）超过 127 字节，则使用 2 个字节记录，否则使用 1 个字节记录。

问题一：那么为什么用 128 作为分界线呢？一个字节可以最多表示 255，但是 MySQL 设计长度表示时，为了区分是否是一个字节表示长度，规定，如果最高位为 1，那么就是两个字节表示长度，否则就是一个字节。例如，01111111，这个就代表长度为 127，而如果长度是 128，就需要两个字节，就是 10000000 10000000，首个字节的最高位为 1，那么这就是两个字节表示长度的开头，第二个字节可以用所有位表示长度，并且需要注意的是，MySQL 采取 Little Endian 的计数方式，低位在前，高位在后，所以 129 就是 10000001 10000000。同时，这种标识方式，最大长度就是 2^15-1=32767，也就是 32KB。

问题二：如果两个字节也不够表示的长度，该怎么办？innoDB 页大小默认为 16KB，对于一些占用字节数非常多的字段，比方说某个字段长度大于了 16KB，那么如果该记录在单个页面中无法存储时，InnoDB 会把一部分数据存放到所谓的溢出页中，在变长字段长度列表处只存储留在本页面中的长度，所以使用两个字节也可以存放下来。这个溢出页机制参考后面的数据溢出。

NULL 值列表

表中的某些列可能存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中存储会很占地方，所以 Compact 行格式把这些值为 NULL 的列统一管理起来，存储到 NULL 值列表。每个允许存储 NULL 的列对应一个二进制位，二进制位的值为 1 时，代表该列的值为 NULL。二进制位的值为 0 时，代表该列的值不为 NULL。

记录头信息

用于描述记录的记录头信息，它是由固定的 5 个字节组成。5 个字节也就是 40 个二进制位，不同的位代表不同的意思。

字段长度（bit）说明预留位 11 没有使用预留位 21 没有使用 delete_mask1 标记该记录是否被删除 min_rec_mask1B+ 树的每层非叶子节点中的最小记录都会添加该标记 n_owned4 表示当前记录拥有的记录数 heap_no13 表示当前记录在页的位置信息 record_type3 表示当前记录的类型，0 表示普通记录，1 表示 B + 树非叶子节点记录，2 表示最小记录，3 表示最大记录 next_record16 表示下一条记录的相对位置隐藏列

记录的真实数据除了我们自己定义的列的数据以外，MySQL 会为每个记录默认的添加一些列（也称为隐藏列），包括：

DB_ROW_ID(row_id)：非必须，6 字节，表示行 ID，唯一标识一条记录

DB_TRX_ID：必须，6 字节，表示事务 ID

DB_ROLL_PTR：必须，7 字节，表示回滚指针

InnoDB 表对主键的生成策略是：优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个 Unique 键作为主键，如果表中连 Unique 键都没有定义的话，则 InnoDB 会为表默认添加一个名为 row_id 的隐藏列作为主键。

DB_TRX_ID（也可以称为 trx_id）和 DB_ROLL_PTR（也可以称为 roll_ptr）这两个列是必有的，但是 row_id 是可选的（在没有自定义主键以及 Unique 键的情况下才会添加该列）。

其他的行格式和 Compact 行格式差别不大。

Redundant 行格式

Redundant 行格式是 MySQL5.0 之前用的一种行格式，不予深究。

Dynamic 行格式

MySQL5.7 的默认行格式就是 Dynamic，Dynamic 行格式和 Compact 行格式挺像，只不过在处理行溢出数据时有所不同。

Compressed 行格式

Compressed 行格式在 Dynamic 行格式的基础上会采用压缩算法对页面进行压缩，以节省空间。以 zlib 的算法进行压缩，因此对于 BLOB、TEXT、VARCHAR 这类大长度数据能够进行有效的存储（减少 40%，但对 CPU 要求更高）。

数据溢出

如果我们定义一个表，表中只有一个 VARCHAR 字段，如下：

CREATE TABLE test_varchar( c VARCHAR(60000))

然后往这个字段插入 60000 个字符，会发生什么？前边说过，MySQL 中磁盘和内存交互的基本单位是页，也就是说 MySQL 是以页为基本单位来管理存储空间的，我们的记录都会被分配到某个页中存储。而一个页的大小一般是 16KB，也就是 16384 字节，而一个 VARCHAR(M) 类型的列就最多可以存储 65532 个字节，这样就可能造成一个页存放不了一条记录的情况。

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

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

实战分析行格式

准备表及数据：

create table row_test (t1 varchar(10),
 t2 varchar(10),
 t3 char(10),
 t4 varchar(10)
) engine=innodb charset=latin1 row_format=compact;
insert into row_test values( a , bb , bb , ccc  
insert into row_test values( d , ee , ee , fff  
insert into row_test values(d ,NULL,NULL, fff

在 Linux 环境下，使用 hexdump -C -v mytest.ibd>mytest.txt，打开 mytest.txt 文件，找到如下内容：

0000c070 73 75 70 72 65 6d 75 6d 03 02 01 00 00 00 10 00 |supremum........|
0000c080 2c 00 00 00 00 02 00 00 00 00 00 0f 61 c8 00 00 |,...........a...|
0000c090 01 d4 01 10 61 62 62 62 62 20 20 20 20 20 20 20 |....abbbb |
0000c0a0 20 63 63 63 03 02 01 00 00 00 18 00 2b 00 00 00 | ccc........+...|
0000c0b0 00 02 01 00 00 00 00 0f 62 c9 00 00 01 b2 01 10 |........b.......|
0000c0c0 64 65 65 65 65 20 20 20 20 20 20 20 20 66 66 66 |deeee fff|
0000c0d0 03 01 06 00 00 20 ff 98 00 00 00 00 02 02 00 00 |..... ..........|
0000c0e0 00 00 0f 67 cc 00 00 01 b6 01 10 64 66 66 66 00 |...g.......dfff.|

该行记录从 0000c078 开始，第一行整理如下：

03 02 01 //  变长字段长度列表，逆序，t4 列长度为 3，t2 列长度为 2，t1 列长度为 1
00 // NULL 标志位，第一行没有 NULL 值
00 00 10 00 2c //  记录头信息，固定 5 字节长度
00 00 00 2b 68 00 // RowID 我们建的表没有主键，因此会有 RowID，固定 6 字节长度
00 00 00 00 06 05 //  事务 ID，固定 6 个字节 80 00 00 00 32 01 10 //  回滚指针，固定 7 个字节 61 
// t1 数据 a 62 62 
// t2 bb 62 62 20 20 20 20 20 20 20 20 // t3 数据 bb 63 63 63 // t4 数据 ccc

第二行整理如下：

03 02 01 //  变长字段长度列表，逆序，t4 列长度为 3，t2 列长度为 2，t1 列长度为 1
00 // NULL 标志位，第二行没有 NULL 值
00 00 18 00 2b //  记录头信息，固定 5 字节长度
00 00 00 00 02 01 // RowID 我们建的表没有主键，因此会有 RowID，固定 6 字节长度
00 00 00 00 0f 62 //  事务 ID，固定 6 个字节
c9 00 00 01 b2 01 10 //  回滚指针，固定 7 个字节 64 // t1 数据 d 65 65 
// t2 数据 ee 65 65 20 20 20 20 20 20 20 20 // t3 数据 ee 66 66 66 
// t4 数据 fff

第三行整理如下：

03 01 //  变长字段长度列表，逆序，t4 列长度为 3，t1 列长度为 1
06 // 00000110 NULL 标志位，t2 和 t3 列为空
00 00 20 ff 98 //  记录头信息，固定 5 字节长度
00 00 00 00 02 02 // RowID 我们建的表没有主键，因此会有 RowID，固定 6 字节长度
00 00 00 00 0f 67 //  事务 ID，固定 6 个字节
cc 00 00 01 b6 01 10 //  回滚指针，固定 7 个字节 64 // t1 数据 d 66 66 66 // t4 数据 fff

接下来更新下数据：

mysql  update row_test set t2=null where t1= a 
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql  delete from row_test where t2= ee 
Query OK, 1 row affected (0.01 sec)

查看二进制内容（需要等一会，有可能只写入了缓存，磁盘上的文件并没有更新）：

0000c070 73 75 70 72 65 6d 75 6d 03 01 02 00 00 10 00 58 |supremum.......X|
0000c080 00 00 00 00 02 00 00 00 00 00 0f 68 4d 00 00 01 |...........hM...|
0000c090 9e 04 a9 61 62 62 20 20 20 20 20 20 20 20 63 63 |...abb cc|
0000c0a0 63 63 63 63 03 02 01 00 20 00 18 00 00 00 00 00 |cccc.... .......|
0000c0b0 00 02 01 00 00 00 00 0f 6a 4e 00 00 01 9f 10 c0 |........jN......|
0000c0c0 64 65 65 65 65 20 20 20 20 20 20 20 20 66 66 66 |deeee fff|
0000c0d0 03 01 06 00 00 20 ff 98 00 00 00 00 02 02 00 00 |..... ..........|
0000c0e0 00 00 0f 67 cc 00 00 01 b6 01 10 64 66 66 66 00 |...g.......dfff.|

该行记录从 0000c078 开始，第一行整理如下：

03 01 //  变长字段长度列表，逆序，t4 列长度为 3，t1 列长度为 1
02 // 0000 0010 NULL 标志位，表示 t2 为 null
00 00 10 00 58 //  记录头信息，固定 5 字节长度
00 00 00 00 02 00 // RowID 我们建的表没有主键，因此会有 RowID，固定 6 字节长度
00 00 00 00 0f 68 //  事务 ID，固定 6 个字节
4d 00 00 01 9e 04 a9 //  回滚指针，固定 7 个字节 61 // t1 数据 a 62 62 20 20 20 20 20 20 20 20 // t3 数据 bb 63 63 63 // t4 数据 ccc

第二行整理如下：

03 02 01 //  变长字段长度列表，逆序，t4 列长度为 3，t2 列长度为 2，t1 列长度为 1
00 // NULL 标志位，第二行没有 NULL 值 20 00 18 00 00 // 0010 delete_mask=1  标记该记录是否被删除   记录头信息，固定 5 字节长度
00 00 00 00 02 01 // RowID 我们建的表没有主键，因此会有 RowID，固定 6 字节长度
00 00 00 00 0f 6a //  事务 ID，固定 6 个字节
4e 00 00 01 9f 10 c0 //  回滚指针，固定 7 个字节 64 // t1 数据 d 65 65 // t2 数据 ee 65 65 20 20 20 20 20 20 20 20 // t3 数据 ee 66 66 66 // t4 数据 fff

第三行数据未发生变化。

到此，关于“MySQL 如何从二进制内容看 InnoDB 行格式”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！