MySQL的一行数据是如何存储的？

目录

1.COMPACT 行格式长什么样？

例子1：用户设置了主键值，列都是not null的。(默认字符集是utf8mb4,在这种情况下，char(N)类型就不是定长的了）

例子2：没有设置主键，也没有唯一索引，列允许有null值。

2.COMPACT 行格式详解

变长字段长度列表

NULL 值列表

记录头信息

记录的真实数据

3.InnoDb的默认行格式Dynamic 及 MySQL 是怎么处理行溢出？

---

大多数人使用mysql一般都是使用 InnoDB 引擎，这也是默认的表引擎。我们这里讨论的也是默认引擎InnoDB。

InnoDB引擎表的数据是存储在磁盘的。那可能会好奇表的每一行是如何存储在磁盘中的呢？行结构是如何的呢？

行格式（row_format），就是一条记录的存储结构。

InnoDB 提供了 4 种行格式，分别是 Redundant、Compact、Dynamic和 Compressed 行格式。

MySQL5.7 版本之后，默认使用 Dynamic 行格式。而Dynamic行格式与Compact是很相似的。就先从Compact讲起。

我们需要知道文件的存储位置和开启单个文件存储，这样方便我们查看行数据结构。

1.COMPACT 行格式长什么样？

注意：

• 当表中没有变长字段时候，就没有变长字段长度列表；当表中的列都设置为not null,那null值列表也会没有。当表的列值中有null时候，蓝色框内就不会出现该列的值。
• 若是没有主键或者唯一索引，  隐藏列中的第一个就是mysql生成的row_id。若是有的话，就是用户设置的主键值。
• 变长字段长度列表和null值列表都是都是逆序存放。

当中的一些详细信息后面会具体讲解的。

例子1：用户设置了主键值，列都是not null的。(默认字符集是utf8mb4,在这种情况下，char(N)类型就不是定长的了）

mysql> create table has_key(id bigint primary key,age int not null,name varchar(10) not null,idcard char(18) not null);       Query OK, 0 rows affected (0.05 sec)

mysql> insert into has_key values(1,22,'aaaaaa','123456'),(2,23,'bbbb','12345678');       
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from has_key;
+----+-----+--------+----------+
| id | age | name   | idcard   |
+----+-----+--------+----------+
|  1 |  22 | aaaaaa | 123456   |
|  2 |  23 | bbbb   | 12345678 |
+----+-----+--------+----------+
2 rows in set (0.00 sec)

找到文件存储位置/var/lib/mysql,之后找到对应的数据库，进入该目录，之后会看到一个文件has_key.ibd。

 之后使用命令hexdump -C has_key.ibd。

 分析：

• 变长字段长度列表（绿框部分）：

绿框中的是变长字段长度列表(逆序存放的）。name varchar(10)字段数据'aaaaaa'对应的长度是06(十进制是6);idcard字段数据'123456'字段长度是12(十进制是18）,就对应char(18)中的18。char(N)类型的，只要数据字节大小不超过N的，都是开辟N字节的。

• 记录头信息(红框部分):

这里字段都是not null，所以没有null值列表。接着红框的就是5字节大小的记录头信息，具体内容后面会讲解。

• 隐藏列(黄线部分):

黄线的0x80 00 00 00 00 00 00 01是id主键值字段。id字段类型是bigint，8字节的。开头的 80 是因为，正数要以 1 开头，这是 mysql 规定的，0x80 的二进制就是 1000 0000。然后继续黄线的是6字节的TRX_ID,之后是7字节的ROLL_PTR。

• 真实数据(蓝线部分):

蓝线的是表的列值。0x80 00 00 16就是类age的值22，0x61 61 61 61 61 61就是列name的值'aaaaaa',0x31 32 33 34 35 36就是列idcard的值'123456'。

例子2：没有设置主键，也没有唯一索引，列允许有null值。

mysql> create table no_key(id int, name varchar(10));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into  no_key values(1,'2233'),(2,'aaaaa');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from no_key;
+------+-------+
| id   | name  |
+------+-------+
|    1 | 2233  |
|    2 | aaaaa |
+------+-------+
2 rows in set (0.00 sec)

 之后在命令行执行hexdump -C no_key.ibd。

 分析：

• 变长字段长度列表（蓝框部分）：

变长的列数据是'2233',即是4字节大小。

• null值列表(白框部分):

因为列中值可以为null,所以有null值列表。这是用二进制位来表示的，一个字节有8位，可以表示8个列值是否是null。也是逆序的。该位是0表示该列的值不为NULL。

• 记录头信息(红框部分):

红框的就是5字节大小的记录头信息。

• 隐藏列(黄线部分):

该表没有设置主键，所以使用mysql自动生成的row_id,6字节大小,0x00 00 00 00 03 00。之后6字节的rtx_id和7字节的roll_ptr。

• 真实数据(蓝线部分):

蓝线的是真实数据，0x80 00 00 01就是列id的值1,0x32 32 33 33 就是列name的值'2233'。

这里需要注意的是：不管是用户生成主键还是让mysql自动生成主键，其值的存储位置都是在row_id位置的。只是长度不同而已。用户生成的主键长度大小就由用户设置的类型来确定，mysql自动生成的就是6字节。

还有要是用户生成的主键，那在蓝色的不为null的列表值中就不会保存主键的值，即是蓝色框内是不会保存主键列的值。

用这两个例子来讲解，应该可以让大家对存储的解构有个比较清晰的认知了。接下来就来讲下那些具体的细节。

2.COMPACT 行格式详解

变长字段长度列表

mysql中有变长字段varchar(N),那保存的数据就是其字节大小和其数据。变长字段列表就是用来存储这些的。而不是变长字段的不用保存其字节大小的。

这些变长字段的真实数据占用的字节数会按照列的顺序逆序存放。has_key.ibd文件对应的图中的12 06，其对应的就是06 12。06是列name的长度大小，12(十进制是18)是列idcard的长度大小。

为什么要逆序存放，小林coding文章里有写，但我看了好像还是不太明白，等后续弄明白后，会补上这部分。

每个数据库表的行格式不一定有 变长字段字节数列表 。要是都是定长类型的列，那就没有该列表。

上面的例子，变长字段的字符串都是比较短的，长度大小可以使用1字节来表示。如果变长列的内容占用的字节数比较多(长度超过255),可能就需要用2个字节来表示。

用多少字节来表示真实数据占用的字节数，InnoDB 有它的一套规则。

我们先声明一些字母的意思：

• 用W表示某个字符集中表示一个字符最多需要使用的字节数，
• 用M表示对于变长类型 VARCHAR(M) 类型能存储最多 M 个字符，
• 用L来表示它实际存储的字符串占用的字节数。

比如类型是varchar(10) charset=utf8mb4的字符串能保存的长度大小最大是10*4=40字节。

规则：

• 当最大字节数(MxW)没有超过255，那么使用1个字节来表示真正字符串占用的字节数。
• 当最大字节数(MxW)没有超过255，分两种情况：

                如果L <= 127 ，则用1个字节来表示真正字符串占用的字节数。

                如果L >127 ，则用2个字节来表示真正字符串占用的字节数。

InnoDB在读记录的变长字段长度列表时先查看表结构，如果某个变长字段允许存储的最大字节 数大于255时，该怎么区分它正在读的某个字节是一个单独的字段长度还是半个字段长度？ 

mysql是把该字节的最左的一个二进制位(最高位）作为标志位：如果该字节的最高位为0，那 该字节就是一个单独的字段长度（使用一个字节表示不大于127的二进制的最高位都为0）， 如果该字节的最高位位为1，那该字节就是半个字段长度。

127的二进制表示为0b01111 1111(0是最高位)。

其规则总结：如果该可变字段允许存储的最大字节数（M×W）超过255字节并且真实存储的字节数（L） 超过127字节，则使用2个字节，否则使用1个字节。

NULL 值列表

null值列表也是逆序存放的，其是每个列对应一个二进制位（bit）。

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

 null值列表是一定要够整数字节的，只有7个列的话，那也要是一个字节。要是9列的话，那就需要2个字节来保存，以此类推的。如果使用的二进制位个数不足整数个字节，则在字节的高位补 0。

若是所有的列都不允许为null，那就没有null值列表。

所以在设计数据库表的时候，符合业务要求情况下，通常都是建议将字段设置为 NOT NULL，这样可以至少节省 1 字节的空间（NULL 值列表至少占用 1 字节空间）。

记录头信息

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

• 预留位1、2： 暂未使用
• delete_mask： 标记该记录是否被删除
• min_rec_mask： B+树的每层非叶子节点中的最小记录都会添加该标记
• n_owned： 当前记录拥有的记录数
• heap_no： 当前记录在记录堆中的位置信息
• record_type： 当前记录类型；0-普通记录，1-B+树非叶子节点记录(即所谓的目录项记录),2-最小记录，3-最大记录
• next_record： 下一条记录的相对位置

目前就简单了解下这些位表示的含义即可。

记录的真实数据

记录的真实数据除了自定义的列的数据以外，MySQL还会为每条记录默认的添加一些列（也称为隐藏列），具体的列如下：

每一行数据都会存储该事务id和回滚指针。

事务id，表示这个数据是由哪个事务生成的。 trx_id是必需的，占用 6 个字节。 

roll_ptr这个记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节。

当用户未设置数据表的主键时，MySQL会选择第一个非NULL的Unique列作为主键。而没有这个列的话，MySQL就会向数据表添加DB_ROW_ID字段用来作为主键(6字节大小)。

而当用户设置了主键的，那DB_ROW_ID就会让用户设置的主键代替，长度大小是用户设置的类型的大小。

那用户设置主键后，隐藏列后面的非null值的列就没有了主键存储的位置，因为这种情况主键存储在隐藏列的DB_ROW_ID位置了。

需要强调的是：记录的数据内容不包括字段值为NULL的数据内容。

3.InnoDb的默认行格式Dynamic 及 MySQL 是怎么处理行溢出？

对于Dynamic行格式而言，其和compact行格式比较相似。

不同的在于，对待处理行溢出的处理及策略。

MySQL 中磁盘和内存交互的基本单位是页，一个页的大小一般是 16KB，也就是 16384字节，而一个 varchar(n) 类型的列最多可以存储 65532字节，一些大对象如 TEXT可能存储更多的数据，这时一个页可能就存不了一条记录。

这个时候就会发生行溢出，多的数据就会存到另外的「溢出页」中。

compact会在该记录的数据内容的相应字段处存储该字段值前768个字节的数据，之后把剩余数据存储到溢出页。

Dynamic、Compressed行格式会把记录中数据量过大的字段值全部存储到溢出页中。

compact处理行溢出

当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数，因为可能不止溢出一页。

Dynamic处理行溢出

为什么溢出页地址是20字节大小，为什么compact会存储768个字节数据?这些都不清楚，应该是代码中是这样编写的，也没有文章写其原因。

而compressed相比较dynamic行格式来说，前者会使用压缩算法对所有页面(自然也包括溢出页)进行压缩以减少存储占用。