MySQL之InnoDB记录结构

我们可以在创建或修改表的语句中指定行格式：

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

创建一个演示用的表record_format_demo，可以这样指定它的行格式：

CREATE TABLE record_format_demo (  c1 VARCHAR(10),  c2 VARCHAR(10) NOT NULL,  c3 CHAR(10),  c4 VARCHAR(10)) CHARSET=ascii ROW_FORMAT=COMPACT;

可以看到我们刚刚创建的这个表的行格式就是Compact，另外，我们还显式指定了这个表的字符集为ascii，因为ascii字符集只包括空格、标点符号、数字、大小写字母和一些不可见字符，所以我们的汉字是不能存到这个表里的。我们现在向这个表中插入两条记录：

INSERT INTO record_format_demo(c1, c2, c3, c4) VALUES('aaaa', 'bbb', 'cc', 'd');INSERT INTO record_format_demo(c1, c2, c3, c4) VALUES('eeee', 'fff', NULL, NULL);SELECT * FROM record_format_demo;

这部分信息是服务器为了描述这条记录而不得不额外添加的一些信息，这些额外信息分为3类，分别是变长字段长度列表、NULL值列表和记录头信息，我们分别看一下。

• ‍‍‍‍‍‍‍‍‍‍‍‍变长字段长度列表‍‍‍‍‍‍‍‍‍‍‍‍

SELECT LENGTH(c1) AS 'c1',LENGTH(c2) AS 'c2',LENGTH(c3) AS 'c3',LENGTH(c4) AS 'c4' FROM record_format_demo LIMIT 1;

所以确定使用1个字节还是2个字节表示真正字符串占用的字节数的规则就是这样：

• 如果M×W <= 255，那么使用1个字节来表示真正字符串占用的字节数。

• 如果M×W > 255，则分为两种情况：

• NULL值列表

2、如果表中没有允许存储 NULL 的列，则 NULL值列表 也不存在了，否则将每个允许存储NULL的列对应一个二进制位，二进制位按照列的顺序逆序排列，二进制位表示的意义如下：

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

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

3、MySQL规定NULL值列表必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补0。

表record_format_demo只有3个值允许为NULL的列，对应3个二进制位，不足一个字节，所以在字节的高位补0，效果就是这样：

以此类推，如果一个表中有9个允许为NULL，那这个记录的NULL值列表部分就需要2个字节来表示了。知道了规则之后，我们再返回头看表record_format_demo中的两条记录中的NULL值列表应该怎么储存。因为只有c1、c3、c4这3个列允许存储NULL值，所以所有记录的NULL值列表只需要一个字节。

对于第一条记录来说，c1、c3、c4这3个列的值都不为NULL，所以它们对应的二进制位都是0；对于第二条记录来说，c1、c3、c4这3个列中c3和c4的值都为NULL，所以对应的二进制位有2个1，1个0，就像下面的图：

第一条记录的NULL值列表用十六进制表示就是：0x00，第二条记录的NULL值列表用十六进制表示就是：0x06，这两条记录在填充了NULL值列表后的示意图就是这样：

• 记录头信息

除了变长字段长度列表、NULL值列表之外，还有一个用于描述记录的记录头信息，它是由固定的5个字节组成。5个字节也就是40个二进制位，不同的位代表不同的意思，如图：

这些二进制位代表的详细信息如下表：

对于record_format_demo表来说，记录的真实数据除了c1、c2、c3、c4这几个我们自己定义的列的数据以外，MySQL会为每个记录默认的添加一些列（也称为隐藏列），具体的列如下：

因为表record_format_demo并没有定义主键，所以MySQL服务器会为每条记录增加上述的3个列。现在看一下加上记录的真实数据的两个记录长什么样：

record_format_demo表的c1、c2、c4列的类型是VARCHAR(10)，而c3列的类型是CHAR(10)，record_format_demo表采用的是ascii字符集，这个字符集是一个定长字符集，也就是说表示一个字符采用固定的一个字节，如果采用变长的字符集（也就是表示一个字符需要的字节数不确定，比如gbk表示一个字符要1～2个字节、utf8表示一个字符要1~3个字节等）的话，c3列的长度也会被存储到变长字段长度列表中。

对于CHAR(M)类型的列来说，当列采用的是定长字符集时，该列占用的字节数不会被加到变长字段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表。

知道了Compact行格式之后，其他的行格式就是依葫芦画瓢了。我们现在要介绍的Redundant行格式是MySQL 5.0之前用的一种行格式，处于考古级别的行格式了，简单了解一下就好。

下边我们从各个方面看一下Redundant行格式有什么不同的地方：

• 字段长度偏移列表

• 没有了变长两个字，意味着Redundant行格式会把该条记录中所有列（包括隐藏列）的长度信息都按照逆序存储到字段长度偏移列表。

• 多了个偏移两个字，这意味着计算列值长度的方式不像Compact行格式那么直观，它是采用两个相邻数值的差值来计算各个列值的长度。

• 记录头信息

Redundant行格式的记录头信息占用6字节，48个二进制位，这些二进制位代表的意思如下：

与Compact行格式的记录头信息对比来看，有两处不同：

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

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

• 1byte_offs_flag的值是怎么选择的

字段长度偏移列表实质上是存储每个列中的值占用的空间在记录的真实数据处结束的位置。我们前边说过每个列对应的偏移量可以占用1个字节或者2个字节来存储，那到底什么时候用1个字节，什么时候用2个字节呢？其实是根据该条Redundant行格式记录的真实数据占用的总大小来判断的：

• 当记录的真实数据占用的字节数不大于127（十六进制0x7F，二进制01111111）时，每个列对应的偏移量占用1个字节。

• 当记录的真实数据占用的字节数大于127，但不大于32767（十六进制0x7FFF，二进制0111111111111111）时，每个列对应的偏移量占用2个字节。

• 有没有记录的真实数据大于32767的情况呢？有，不过此时的记录已经存放到了溢出页中，在本页中只保留前768个字节和20个字节的溢出页面地址（当然这20个字节中还记录了一些别的信息）。因为字段长度偏移列表处只需要记录每个列在本页面中的偏移就好了，所以每个列使用2个字节来存储偏移量就够了。

为了在解析记录时知道每个列的偏移量是使用1个字节还是2个字节表示的，Redundant行格式特意在记录头信息里放置了一个称之为1byte_offs_flag的属性：

• 当它的值为1时，表明使用1个字节存储。

• 当它的值为0时，表明使用2个字节存储。

• Redundant行格式中NULL值的处理

因为Redundant行格式并没有NULL值列表，Redundant行格式在字段长度偏移列表中的各个列对应的偏移量处做了一些特殊处理 —— 将列对应的偏移量值的第一个比特位作为是否为NULL的依据，该比特位也可以被称之为NULL比特位。也就是说在解析一条记录的某个列时，首先看一下该列对应的偏移量的NULL比特位是不是为1，如果为1，那么该列的值就是NULL，否则不是NULL。

这也就解释了上边介绍为什么只要记录的真实数据大于127（十六进制0x7F，二进制01111111）时，就采用2个字节来表示一个列对应的偏移量，主要是第一个比特位是所谓的NULL比特位，用来标记该列的值是否为NULL。

但是还有一点要注意，对于值为NULL的列来说，该列的类型是否为定长类型决定了NULL值的实际存储方式：

• 如果存储NULL值的字段是定长类型的，比方说CHAR(M)数据类型的，则NULL值也将占用记录的真实数据部分，并把该字段对应的数据使用0x00字节填充。

• 如果该存储NULL值的字段是变长数据类型的，则不在记录的真实数据处占用任何存储空间。

我们知道Compact行格式在CHAR(M)类型的列中存储数据的时候还挺麻烦，分变长字符集和定长字符集的情况，而在Redundant行格式中十分干脆，不管该列使用的字符集是啥，只要是使用CHAR(M)类型，占用的真实数据空间就是该字符集表示一个字符最多需要的字节数和M的乘积。比方说使用utf8字符集的CHAR(10)类型的列占用的真实数据空间始终为30个字节，使用gbk字符集的CHAR(10)类型的列占用的真实数据空间始终为20个字节。由此可以看出来，使用Redundant行格式的CHAR(M)类型的列是不会产生碎片的。

我们知道对于VARCHAR(M)类型的列最多可以占用65535个字节。其中的M代表该类型最多存储的字符数量，如果我们使用ascii字符集的话，一个字符就代表一个字节，我们看看VARCHAR(65535)是否可用：

从报错信息里可以看出，MySQL对一条记录占用的最大存储空间是有限制的，除了BLOB或者TEXT类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。所以MySQL服务器建议我们把存储类型改为TEXT或者BLOB的类型。这个65535个字节除了列本身的数据之外，还包括一些其他的数据（storage overhead），比如说我们为了存储一个VARCHAR(M)类型的列，其实需要占用3部分存储空间：

• 真实数据

• 真实数据占用字节的长度

• NULL值标识，如果该列有NOT NULL属性则可以没有这部分存储空间

• VARCHAR类型的列没有NOT NULL属性，那最多只能存储65532个字节的数据，因为真实数据的长度可能占用2个字节，NULL值标识需要占用1个字节；

• VARCHAR类型的列有NOT NULL属性，那最多只能存储65533个字节的数据，因为真实数据的长度可能占用2个字节，不需要NULL值标识；

• VARCHAR(M)类型的列使用的不是ascii字符集，那M的最大取值取决于该字符集表示一个字符最多需要的字节数。在列的值允许为NULL的情况下，gbk字符集表示一个字符最多需要2个字节，那在该字符集下，M的最大取值就是32766（也就是：65532/2），也就是说最多能存储32766个字符；utf8字符集表示一个字符最多需要3个字节，那在该字符集下，M的最大取值就是21844，就是说最多能存储21844（也就是：65532/3）个字符。

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

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

对于Compact和Redundant行格式来说，如果某一列中的数据非常多的话，在本记录的真实数据处只会存储该列的前768个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中，这个过程也叫做行溢出，存储超出768字节的那些页面也被称为溢出页。

那发生行溢出的临界点是什么呢？也就是说在列存储多少字节的数据时就会发生行溢出？

• ‍‍‍‍MySQL中规定一个页中至少存放两行记录，每个页除了存放我们的记录以外，也需要存储一些额外的信息，乱七八糟的额外信息加起来需要132个字节的空间（现在只要知道这个数字就好了），其他的空间都可以被用来存储记录。

• 每个记录需要的额外信息是27字节。

• 2个字节用于存储真实数据的长度

• 1个字节用于存储列是否是NULL值

• 5个字节大小的头信息

• 6个字节的DB_ROW_ID列

• 6个字节的DB_TRX_ID列

• 7个字节的DB_ROLL_PTR列

假设一个列中存储的数据字节数为n，MySQL规定如果该列不发生溢出的现象，就需要满足下边这个式子：

132 + 2×(27 + n) < 16384

求解这个式子得出的解是：n < 8099。也就是说如果一个列中存储的数据小于8099个字节，那么该列就不会成为溢出列，否则该列就需要成为溢出列。不过这个8099个字节的结论只是针对只有一个列的varchar_size_demo表来说的，如果表中有多个列，那上边的式子和结论都需要改一改了，所以重点就是：你不用关注这个临界点是什么，只要知道如果我们一条记录的某个列中存储的数据占用的字节数非常多时，该列就可能成为溢出列。

下边要介绍另外两个行格式，Dynamic和Compressed行格式，我现在使用的MySQL版本是5.7，它的默认行格式就是Dynamic，这俩行格式和Compact行格式挺像，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实数据处存储字段真实数据的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。Compressed行格式和Dynamic不同的一点是，Compressed行格式会采用压缩算法对页面进行压缩，以节省空间。

今天的理论知识着实有点多，哈哈，不过好在工作中实际用的不是很多，大家知道有这些名词就好，因为在后面的MVCC的讲解中，我们还会用到本文的一些专业名词。InnoDB定义了4种行格式，每种行格式的记录结构是不同的，同时我们还学习了记录结构中对定长、变长的数据类型的存储方式以及行溢出等相关知识，下面我们就来做一个总结：

• COMPACT行格式：

• REDUNDANT行格式：

• DYNAMIC和COMPRESSED行格式：

自己学完这部分知识的时候，很佩服本书作者的研究深度，虽然知识点多、难、杂，但是作者图文并茂的方式可以让我们很好的理解。本文中的图是根据作者原图重新画的，一是为了加深印象，二是有些地方为了方便我自己理解，做了一些微调，我只摘录了书中部分知识点，而非全部，如果大家真的感兴趣，强烈建议大家购买原书细读。站在巨人的肩膀上，每天进步一点点。

end