How MySQL sees InnoDB row format from binary content

InnoDB is a storage engine that can store data in tables on disk, so even if the server is shut down after a restart, our data can still be retained. The actual process of processing data occurs in memory, so the data in the disk needs to be loaded into the memory. If it is processing a write or modification request, the contents in the memory also need to be refreshed to the disk. And we know that the speed of reading and writing to disk is very slow, which is several orders of magnitude different from reading and writing in memory. So when we want to get certain records from the table, does the InnoDB storage engine need to read the records from the disk one by one?

The method adopted by InnoDB is to divide the data into several pages, and use pages as the basic unit of interaction between disk and memory. The size of a page in InnoDB is generally 16KB. That is to say, under normal circumstances, at least 16KB of content is read from the disk to the memory at a time, and at least 16KB of the content in the memory is refreshed to the disk at a time.

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

We usually insert data into tables in units of records. The way these records are stored on disk is also called row format or record format. The InnoDB storage engine has designed four different types of row formats, namely Compact, Redundant, Dynamic and Compressed row formats.

Classification and introduction of row record formats

In early InnoDB versions, since there was only one file format, there was no need to name this file format. In order to support new features and incompatibility with earlier versions, the InnoDB engine has developed a new file format. To help manage system compatibility in upgrade and downgrade situations, as well as running different MySQL versions, InnoDB started using a named file format.

In msyql 5.7.9 and later versions, the default row format is determined by the innodb_default_row_format variable, and its default value is 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)

View the current table using Row format:

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)

Specify the row format of the table:

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

If you want to modify the row mode of an existing table to compressed or dynamic, you must first set the file format to Barracuda: set global innodb_file_format=Barracuda;, and then use ALTER TABLE tablename ROW_FORMAT=COMPRESSED; to modify it to take effect.

Row format

COMPACT

Variable length field list

MySQL supports some variable length data Types, such as VARCHAR(M), VARBINARY(M), various TEXT types, and various BLOB types. We can also call columns with these data types variable-length fields. How many bytes of data are stored in variable-length fields? It is not fixed, so when we store real data, we need to store the number of bytes occupied by these data. If the maximum number of bytes allowed to be stored in the variable field (M × W) exceeds 255 bytes and the actual number of bytes stored (L) exceeds 127 bytes, use 2 bytes to record, otherwise use 1 byte Record.

Question 1: So why use 128 as the dividing line? One byte can represent up to 255, but when MySQL designs the length representation, in order to distinguish whether it is a byte that represents the length, it is stipulated that if the highest bit is 1, then two bytes represent the length, otherwise it is one byte. For example, 01111111, this means the length is 127, and if the length is 128, two bytes are needed, which is 10000000 10000000. The highest bit of the first byte is 1, then this is the beginning of the two bytes representing the length. The second byte can use all bits to represent the length, and it should be noted that MySQL adopts Little Endian counting method, with the low bit first and the high bit last, so 129 is 10000001 10000000. The maximum length of this identification method is 32767, which is 32KB.

Question 2: What should I do if two bytes are not enough to represent the length? The default page size of innoDB is 16KB. For some fields that occupy a lot of bytes, for example, the length of a field is greater than 16KB. If the record cannot be stored in a single page, InnoDB will store part of the data in the so-called overflow. In the page, only the length left in this page is stored in the variable-length field length list, so it can be stored using two bytes. This overflow page mechanism refers to the data overflow later.

NULL value list

Some columns in the table may store NULL values. If these NULL values ​​are stored in the real data of the record, it will take up a lot of space, so the Compact row format These columns with NULL values ​​are managed uniformly and stored in a NULL value list. For each column that can store NULL, there will be a corresponding binary bit. When the value of the binary bit is 1, it means that the value of the column is NULL. When the binary bit value is 0, it means that the value of the column is not NULL.

Record header information

The record header information used to describe the record, which is composed of fixed 5 bytes. 5 bytes are 40 binary bits, and different bits represent different meanings.

字段	长度（bit）	说明
预留位1	1	没有使用
预留位2	1	没有使用
delete_mask	1	标记该记录是否被删除
min_rec_mask	1	B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned	4	表示当前记录拥有的记录数
heap_no	13	表示当前记录在页的位置信息
record_type	3	表示当前记录的类型，0 表示普通记录，1 表示B+树非叶子节点记录，2 表示最小记录，3 表示最大记录
next_record	16	表示下一条记录的相对位置

隐藏列

记录的真实数据除了我们自己定义的列的数据以外，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'

第三行数据未发生变化。

The above is the detailed content of How MySQL sees InnoDB row format from binary content. For more information, please follow other related articles on the PHP Chinese website!