【MySQL】InnoDB存储引擎的行结构

前言

MySQL服务器上负责对表中数据的读取和写入工作的部分是存储引擎，而服务器又支持不同类型的存储引擎，如InnoDB、MyISAM、Memory。不同的存储引擎一般是由不同的人为实现不同的特性而开发的，真实数据在不同存储引擎中存放的格式一般是不同的。

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表而不是基于库的，所以存储引擎也可被称为表类型。

在MySQL5.5.5版本开始，InnoDB便成为了MySQL默认的存储引擎，之前版本默认的存储引擎为MyISAM。

引用的优秀资源

• 书籍：《MySQL 是怎样运行的：从根儿上理解 MySQL》——小孩子4919
• 文章：MySQL InnoDB存储引擎的行结构——南星
• 视频：黑马程序员 MySQL数据库入门到精通

1、MySQL的体系结构

大体来说，MySQL可以分为Server层和存储引擎层两部分(在参考书籍中将其分成了三部分，分别为：连接管理、解析与优化、存储引擎，而在本文中则是将前两个结合在一起统称为Server层)。

Server层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数(如日期、时间、数学和加密函数等)，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

而存储引擎层负责数据的读取和写入，为可插拔存储引擎，除了已支持的InnoDB、MyISAM、Memory等多个存储引擎，还可在原有的基础上进行拓展。

2、InnoDB逻辑存储结构

InnoDB是一个将表中的数据存储到磁盘上的存储引擎，而真正处理数据的过程是发生在内存中的，所以在处理数据的时候需要把磁盘中的数据加载到内存中。如果是处理写入或修改请求的话，还需要把内存中的内容刷新到磁盘上。当我们想从表中获取某些记录时，InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。

3、InnoDB记录行结构

3.1、概述

InnoDB存储引擎是面向行的，也就是说数据是按行进行存放的，按行存放的数据便是一条条的记录，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。目前InnoDB中共有4中不同类型的行格式，分别为Compact、Redundant、Dynamic和Compressed行格式。

3.2、语法操作

创建表时指定行格式：

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称;-- 举个例子CREATE TABLE record_format_demo (
    c1 VARCHAR(10),
    c2 VARCHAR(10) NOT NULL,
    c3 CHAR(10),
    c4 VARCHAR(10)) CHARSET=ascii ROW_FORMAT=Redundant;

修改表的行格式：

ALTER TABLE 表名 ROW_FORMAT=行格式名称;-- 举个例子ALTER TABLE record_format_demo ROW_FORMAT = COMPACT;

现在所创建的表record_format_demo行格式为Compact，另外，我们还显式指定了这个表的字符集为ascii，我们现在向这个表中插入两条记录：

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

3.3、Compact行格式

3.3.1、示意图

从上图中可以看出来，Compact行格式中一条完整的记录其实可以被分为以下两大部分：

• 记录的额外信息：这部分信息是服务器为了描述这条记录而不得不额外添加的一些信息，这些额外信息分为3类：
  • 变长字段列表：每列的长度用1或2个字节；
  • NULL值列表：整数个字节；
  • 记录头信息：5个字节。
• 记录的真实数据：这里除了每一条记录中我们插入的数据，还有一些隐藏的字段数据：
  • DB_ROW_ID：非必须值，用于充当行ID，唯一标识一条记录，即主键，也可称为row_id；
  • DB_TRX_ID：必需值，事务ID，也可称为transaction_id；
  • DB_ROLL_PTR：必需值，回滚指针，也可称为roll_pointer。

针对于上面两条记录，用16进制来表示如下图所示：

根据示意图，将数据表record_format_demo中的两条记录用16进制表示如下如所示：

3.3.2、记录的额外信息

变长字段长度列表

在MySQL中支持一些变长的数据类型，如常见的VARCHAR、TEXT、BLOG等，我们将这些变长的数据类型指定的列称之为变长字段，这些字段中的数据长度是不固定的。而在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序**逆序**存放。

以第一条记录为例：

• c1的类型是VARCHAR，因此该列为变长字段，真实数据为aaaa，长度为4，转换成16进制为0x04；
• c2的类型是VARCHAR，因此该列为变长字段，真实数据为bbb，长度为3，转换成16进制为0x03；
• c3的类型是CHAR，不符合变长字段的要求，因此在变长字段长度列表中不会存储该字段中的真实数据的字节长度；
• c4的类型是VARCHAR，因此该列为变长字段，真实数据为d，长度为1，转换成16进制为0x01。

摒弃不符合要求的c3列，列的顺序为c4 c2 c1，按照变长字段长度列表中需要逆序的要求，最终第一条记录的变长字段长度列表中的字节串用十六进制表示的效果为(各个字节之间实际上没有空格，用空格隔开只是方便理解)：

01 03 04

另外需要注意的一点是，变长字段长度列表中只存储值为 非NULL 的列内容占用的长度，值为 NULL 的列的长度是不储存的 。

以第二条记录为例：

• c1的类型是VARCHAR，因此该列为变长字段，真实数据为eeee，长度为4，转换成16进制为0x04；
• c2的类型是VARCHAR，因此该列为变长字段，真实数据为fff，长度为3，转换成16进制为0x03；
• c3值为NULL，不纳入考虑范围；
• c4值为NULL，不纳入考虑范围。

摒弃不符合要求的c3和c4列，列的顺序为c2 c1，按照变长字段长度列表中需要逆序的要求，最终第一条记录的变长字段长度列表中的字节串用十六进制表示的效果为(各个字节之间实际上没有空格，用空格隔开只是方便理解)：

03 04

NULL值列表

在表中某些列可能存储NULL值(如果没有NOT NULL约束)，这些NULL值在Compact行格式中会被存放至NULL值列表中统一管理，处理过程分别为：

• 统计表中允许存储NULL的列；
• 如果表中没有允许存储NULL的列，则不存在NULL值列表，否则将每个允许存储NULL的列对应一个二进制位：
  • 二进制位按照列的顺序逆序排列；
  • 二进制位个数用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补0；
  • 二进制位的值为1时，代表该列的值为NULL。
  • 二进制位的值为0时，代表该列的值不为NULL。

针对于表record_format_demo，只用c2列存在NOT NULL约束，因此允许存储NULL的列为c1 c3 c4。

以第一条记录为例：

• 因为共有3列允许存储NULL，因此对应着3位二进制位；
• 此记录中没有NULL值，按照列的顺序逆序排列为000，正确表示如下：

整数个字节的位表示：00000000
16进制表示：0x00

以第二条记录为例：

• 因为共有3列允许存储NULL，因此对应着3位二进制位；
• 此记录中c3 c4值皆为NULL，按照列的顺序逆序排列为011，正确表示如下：

整数个字节的位表示：00000110
16进制表示：0x06

记录头信息

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

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

名称	大小（单位：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	表示下一条记录的相对位置

3.3.3、记录的真实数据

MySQL会为每个记录默认的添加一些列(也称为隐藏列)，具体的列如下：

列名	是否必须	占用空间	描述
row_id	否	6字节	行ID，唯一标识一条记录
transaction_id	是	6字节	事务ID
roll_pointer	是	7字节	回滚指针

这里需要提一下InnoDB表对主键的生成策略：

• 优先使用用户自定义主键作为主键
• 如果用户没有定义主键，则选取一个Unique键作为主键
• 如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。
  通过上述可以看出：InnoDB存储引擎会为每条记录都添加 transaction_id 和 roll_pointer 这两个列，但是 row_id 是可选的(在没有自定义主键以及Unique键的情况下才会添加该列)。

而表record_format_demo并没有定义主键，也没有Unique键，因此在该表中三个隐藏列都会被添加上。

3.3.4、定长字段补充

不知道各位有没有发现上面讨论的都是变长字段类型，那么定长字段类型的怎么办呢？

这又得让焦点定格在表record_format_demo上了，在创建该表时，我们显式的将表字符集设定成了ascii，这是一个定长的字符集。当我们将的字符集修改成utf8时，结果就会不一样了。

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

• 当两个定长遇到一起时，最终字段的数据肯定是定长的；
• 当一个定长一个变长遇到一起时，由于有一个变长的不确定因素，最终字段的数据长度为不确定，因此会被加到变长字段长度列表中；
• 当两个变长遇到一起时，最终字段的数据肯定是变长的。

值得注意的是，对于定长字段，不管有没有存放值都会占用指定字节的大小，这是因为在更新该列的值的字节长度大于原有值的字节长度而小于10个字节时，可以在该记录处直接更新，而不是在存储空间中重新分配一个新的记录空间，导致原有的记录空间成为所谓的碎片。

3.4、行溢出

MySQL 对一条记录占用的最大储存空间是有限制的，除了 BLOB 和 TEXT 类型之外，其他所有列 (不包括隐藏列和记录头信息) 占用的字节长度不能超过 65535 个字节，当记录长度超过限制时，MySQL 会建议使用 TEXT 或 BLOB 类型。

我们先分析一下每一行的数据占用情况，以VARCHAR类型为例：

• 如果允许存在NULL，那么将会有额外信息的三个内容的数据长度都需要加上去；
• 如果存在NOT NULL约束，那么将不会存在NULL值列表，但需要加上其他两个内容的数据长度。

总而言之，65535的长度并不是单指我们存放进去的数据，还得加上一些MySQL给我们自动添加上的数据。

与此同时，一个页的大小一般是16kb，转换成字节数为16384，而一个VARCHAR类型的列最多可以存储65532个字节，这样就可能造成一个页存放不了一条记录的尴尬场景。

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

以上分析的场景便是我们需要介绍的行溢出：一个页一般是16KB，当记录中的数据太多，当前页放不下的时候，会把多余的数据存储到其他页中。

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