Mysql系列（四）—— InnoDB的行格式

一、InnoDb中的页

我们知道，要处理数据，必须先把数据放到内存中来，那么Mysql读写记录时，是怎么读写的勒？Mysql是将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。

二、InnoDB有哪些行格式

所谓行格式就是表的一条记录在磁盘里存储的二进制格式。迄今为止，InnoDB有四种行格式，分别是Compact、Redundant、Dynamic和Compressed行格式。下面分别介绍下这几种行格式。

（一）Compact

示意图：

一条记录存储分为记录的额外信息和记录的真实数据两部分：

1.记录的额外信息

记录的额外信息又包括变长字段长度列表、NULL值列表、记录头信息：

（1）变长字段长度列表

所谓变长字段，指的是如VARCHAR(M)、VARBINARY(M)、各种TEXT类型，各种BLOB类型的字段，变长字段列表主要是存储的这些字段的真实数据占用的字节长度，该列表的顺序是按表字段逆序。在长度列表中每个字段用1-2个字节来其字节长度，具体是1还是2个字节是通过该记录该字段占用的最大字节长度和真实数据长度来计算得到的。具体规则是：先看字段最大字节长度，小于255直接用1个字节表示，那如果大于255的勒？比如utf8编码格式下的varchar(100)，最大字节长度是3*100=300，超过了一个字节能表示的最大的数。这时应该看真实数据字符占用字节数，如果真实数据字符占用数据字节数小于127，用1个字节，大于用2个字节。为什么是用127做划分勒，因为一个字节有8位，首位被用来标识需不需要一起读取下个字节作为字段的字节长度（即这个字段用的是1个字节表示长度还是2个字节）。0需要，1表示不需要。如果碰到该记录数据字节太长，产生行溢出时（后面会细讲），这种情况的话，变长字段长度列表中表示该字段的长度还是2个字节，只表示该记录在本页的数据长度。因为2个字节所能表示的字节长度有2的15次方，远远大于InnoDb读写一页（16KB）的长度了，所以就算该记录只有一个字段，本页数据全存该字段的数据，那2个字节来表示本页所占长度也是完全放得下的。

注：对于 CHAR(M) 类型的列来说，当列采用的是定长字符集时，该列占用的字节数不会被加到变长字段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表。另外定长字符集下的CHAR类型字段，如果涉及更新或删除的话，不会产生硬盘碎片，效率比varchar高。

（2）NULL值列表

null值列表只有在该记录所在表的元数据规定有字段可以存在null值才会有null值列表，null值列表是基于位向量来维护字段是否为null的，即用二进制位的0和1表示字段是否为null，该列表也是逆序的。另外InnoDB还规定NULL值列表必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补0。

（3）记录头信息

记录头信息由固定5个字节组成包括：

上图中，有些概念可能不清楚，后面如果再开文章的话再学习。

2.记录的真实数据

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

其中row_id不一定是必须的，只有在表中不存在主键的时候InnoDB才会自动添加这列。

（二）Redundant

示意图：

这个行格式名称是也就是Redundant，表示它是已经是过时了的了，现在一般不用，但这里还是介绍一下，对比与Compact的区别。

1.字段长度偏移列表

与变长字段长度列表有两处不同：

• 没有了变长两个字，意味着Redundant行格式会把该条记录中所有列（包括隐藏列）的长度信息都按照逆序存储到字段长度偏移列表。
• 多了个偏移两个字，这意味着计算列值长度的方式不像Compact行格式那么直观，它是采用两个相邻数值的差值来计算各个列值的长度。比如每个字段长度按逆序列表的10进制表示是6、12、9，那么偏移之后就是6、18（18-6=12）、27（27-18=9）。

从上面看出，字段长度偏移列表实质上是存储每个列中的值占用的空间在记录的真实数据处结束的位置，这种表示方法相对来说更简单直观。

注：对于到底用1个字节或2个字节，规则类似Compact，但判断的是该记录所有字段真实数据长度，如果真实数据小于127字节，则每个列对应的偏移量占用1个字节，大于127，用两个字节来划分，当然真实数据可能超过了2个字节所能表示的最大字节数32767，这时依旧是两个字节，原因同Compact，2个字节足够表示该页的最大偏移（因为1页就16K，也就是16384个字节），剩下的为溢出列数据，交由其他页存放，本页只存其他页的指向地址。

2.记录头信息

与Compact的记录头信息相比:

• Redundant行格式多了n_field和1byte_offs_flag这两个属性。

Redundant跟Compact不一样的是，把是一个或两个字节表示长度放在了头信息里面，具体规则类似Compact，但判断的是该记录所有字段真实数据长度，如果真实数据小于127字节，则每个列对应的偏移量占用1个字节，大于127（这里可能会疑问为什么是127而不是255（1个字节表示的最大的数），因为与Compact格式不同，Redundant对null值信息没有集中存储，而是将字段长度偏移列表首个字节利用起来，标识了该字段为不为null），用两个字节来划分，当然真实数据可能超过了2个字节所能表示的最大字节数32767，这时依旧是两个字节，原因同Compact，2个字节足够表示该页的最大偏移（因为1页就16K，也就是16384个字节），剩下的为溢出列数据，交由其他页存放，本页只存其他页的指向地址。

• Redundant行格式没有record_type这个属性。

（三）Dynamic

与Compact的区别是：

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

（四）Compressed

与Dynamic不同的一点是：

Compressed行格式会采用压缩算法对页面进行压缩，以节省空间。

三、如何查看行格式

在我使用的版本Mysql5.7.26版本中，行格式默认为Dynamic。如何进行查看某个表的行格式命令是：

show table STATUS like '表名'

三、指定行格式的语法

创建或修改表的语句:

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

四、针对行溢出数据的处理

（一）什么是行溢出

• DDL定义时报错字段溢出

MySQL对一条记录占用的最大存储空间是有限制的，除了BLOB或者TEXT类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。如果超过会报ERROR，比如创建一个只有一个字段编码格式为ascii（1个字节为1个字符）的表：

CREATE TABLE varchar_size_demo(
    ->     c VARCHAR(65535)
    -> ) CHARSET=ascii ROW_FORMAT=Compact;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

注：这里的65535包含除了列本身的数据之外，还包括一些其他的数据（storage overhead）如NULL值标识（可以为null的字段需要这个标识）、真实数据占用字节的长度。这样可以计算一下，如果只有一个字段的表，可以为null，那么该字段真实数据可用字节就是65535-2(真实数据占用字节的长度)-1(null值标识)=65532，以上语句可以改为：

CREATE TABLE varchar_size_demo(
     c VARCHAR(65532)
) CHARSET=ascii ROW_FORMAT=Redundant
> OK
> 时间: 0.124s

这样就恰恰够装。

• 运行时的行溢出（不报错）

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

那么这里怎么计算，产生行溢出的数据长度的临界点勒？这里与几个限制有关：

• MySQL中规定一个页中至少存放两行记录
• 一页只有16K

即只要保证2条数据，加起来数据大小不超过16K减去页中其他不用于存储记录的大小（固定132个字节），就不会产生行溢出，但是一条数据不止有存储真实数据还有其他。以Compact为例，假设只有1个字段，且可以为Null,则每个记录需要的额外信息是27字节，包括：

• 2个字节用于存储真实数据的长度
• 1个字节用于存储列是否是NULL值(不超过255个字段可以为null,所以直接用1个字节)
• 5个字节大小的头信息
• 6个字节的row_id列
• 6个字节的transaction_id列
• 7个字节的roll_pointer列

假设一个列中存储的数据字节数为n，只要满足：

132 + 2×(27 + n) < 16384

则该记录不会造成行溢出。当然如果表中有多个字段，上面公式中的27可能会增加（因为“真实数据的长度”和“列是否是NULL值”占用字节可能会增加）。