InnoDB 记录存储结构

InnoDB 是 MySQL 默认的存储引擎，也是最常用的存储引擎，本章节就来介绍使用 InnoDB 作为存储引擎的记录存储结构。

InnoDB 简介

InnoDB 是一个将表中数据存储到磁盘上的存储引擎，即使关闭或重启服务器，数据依旧存在。但是数据的处理是发生在内存中，所以使用时需要把磁盘上的数据加载到内存中进行操作。由于磁盘和内存的 IO 速度存在巨大的差距，所以为了避免频繁 IO 操作，InnoDB 采用的方式是将数据划分为若干个页，以页作为磁盘和内存数据交互的基本单位。InnoDB 中页的大小一般默认为 16KB。

系统变量 innodb_page_size 表明了页大小，默认为 16384 个字节，也就是 16KB。该变量只能在第一次初始化 mysql 数据目录时指定，之后再也不能更改了。

InnoDB 行格式

平时插入数据是我们都是以行（记录）为单位进行插入，这些数据在磁盘上的存放形式也被称为行格式（记录格式）。迄今为止，MySQL 中共计有 4 种不同类型的行格式，分别是 COMPACT、REDUNDANT、DYNAMIC、COMPRESSED。

指定行格式的语法：

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

COMPACT 行格式

COMPACT 行格式

从图中可以看到，一条完整的记录其实可以分为额外信息和真实数据两部分。

记录的额外信息

为了更好的管理记录而不得不添加的一些额外信息。这些信息分为一下 3 部分。

  1. 变长字段长度列表：

    MySQL 支持一些变长的数据类型，比如 VARCHAR(M)、VARBINARY(M)、各种 TEXT 类型、各种 BLOB 类型。这些存储数据不固定的类型，称为变长字段。在存储真实数据时顺便把数据占用的字节数页存起来，变长字段占用的存储空间分为 真正的数据内容、数据占用的字节数。

    在 COMPACT 行格式中，所有变长字段的真实数据都会存放在记录开头位置，从而形成一个变长字段长度列表，== 各变长字段的真实数据占用的字节数按照列的数据逆序存放 ==。

    并不是所有记录都有这个变长字段长度列表的部份，如果表中所有的列都不是变长字段数据类型或者所有列的值都是 NULL 的话，就不需要变长字段长度列表。

  2. NULL 值列表：

    一条记录在某些列中可能存储 NULL 值，如果把 NULL 值都放到记录的真实数据中会很占用空间，所以把一条记录中的 NULL 的列统一管理起来，存储到 NULL 值列表中。

    • 首先统计表中允许存储 NULL 的列。不允许的列统计时不会把这些列算进去。
    • 将每个允许 NULL 的列对应一个二进制位，1 代表为 NULL，0 代表不为 NULL。
    • NULL 值列表必须用整数字节的位表示，不足则在字节高位补 0。

  3. 记录头信息：

    记录头信息由固定的 5 字节组成，用于描述记录的一些属性。共计 40 位，每一位代表的意思如下：

    COMPACT 记录头信息
    名称大小（位）描述
    预留位11没有使用
    预留位21没有使用
    delete_flag1标记该记录是否删除
    min_rec_flag1B+数的每层非叶子节点中最小的记录都会添加该标记
    n_owned4一个页面中的记录会被分为若干个组，每组中有一个记录是大哥，其余的记录都是小弟，带头大哥的n_owned值代表该组中有多少个记录，小弟的n_owned值都为0
    heap_no13表示当前记录在页面堆中的相对位置
    record_type3表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点目录项记录，2表示Infimum记录，3表示Supremum记录
    next_record16表示下一条记录的相对位置

记录的真实数据

记录的真实数据除了我们自己定义的列的数据外，MySQL还会为每个记录默默的添加一些列（隐藏列），具体如下所示：

列名是否必须占用空间描述
DB_ROW_IDfalse6字节行ID，唯一标识一条记录
DB_TRX_IDtrue6字节事务ID
DB_ROLL_PTRtrue7字节回滚指针

InnoDB主键生成策略：
优先使用用户自定义的主键，如果用户没有自定义主键，则选取一个不允许存NULL值的UNIQUE键作为主键。
如果不存在一个不允许存NULL值的UNIQUE键，则InnoDB会为表生成一个名为DB_ROW_ID的隐藏列作为主键。

CHAR(M) 列的存储格式

上面讲到在COMPACT行格式下，变长字段长度列表只是用来存放一条记录中各个变长字段的值占用的字节长度。但是对于CHAR(M)这种类型，是不属于变长字段的。但是这只是建立在表采用ASCII字符集的情况下，即采用定长编码字符集，如果采用变长编码字符集（如GBK，UTF8等），虽然类型是CHAR(M)，但是COMPACT行格式规定，此时该列的值占用的字节数也会被存储到变长字段长度列表中。

采用变长编码字符集的CHAR(M)类型的列要求至少占用M个字节，而VARCHAR(M)没有这个要求。例如使用UTF-8字符集，字段类型为CHAR(10)时，即使我们存储一个空串也会占用10个字节，主要是希望在将来更新该列时，在新值大于旧值的长度但不大于10字节时，可以在该记录处直接更新，而不是重新分配一个新的记录空间，导致原有记录空间变为碎片。

REDUNDANT行格式

REDUNDANT行格式

从图中可以看到，一条完整的记录其实可以分为额外信息和真实数据两部分。

记录的额外信息

相比COMPACT行格式，REDUNDANT行格式记录的额外信息只有两部分。

  1. 字段长度偏移列表：

    与COMPACT行格式的变长字段长度列表相比有两处不同：

    • 没有了“变长”，意味着REDUNDANT行格式会把该条记录的所有列（包括隐藏列）的长度信息都按照逆序存储到字段长度偏移列表中。
    • 多了个“偏移”，意味着计算列值长度的方式与COMPACT行格式那么直观，它采用了两个相邻偏移量的差值来计算各个列值的长度。

  2. 记录头信息：

    REDUNDANT记录头信息
    名称大小（位）描述
    预留位 11没有使用
    预留位 21没有使用
    delete_flag1标记该记录是否删除
    min_rec_flag1B+ 数的每层非叶子节点中最小的记录都会添加该标记
    n_owned4一个页面中的记录会被分为若干个组，每组中有一个记录是大哥，其余的记录都是小弟，带头大哥的 n_owned 值代表该组中有多少个记录，小弟的 n_owned 值都为 0
    heap_no13表示当前记录在页面堆中的相对位置
    n_field10表示记录中列的数量
    1 byte_offs_flag1标记字段长度偏移列表中每个列对应的偏移量是使用 1 字节还是 2 字节表示的
    next_record16表示下一条记录的相对位置

记录头信息中 1 byte_offs_flag 的值是怎么选择的？

字段长度偏移列表存储的偏移量是每个列的值占用的空间在记录的真实数据处结束的位置。比如第一个列的值长度是 6 字节，那么它对应的偏移量就是 0x06，第二个列的值长度为 3 字节，那么它对应的偏移量就是 0x09，以此类推。

在字段长度偏移列表中，每个列对应的偏移量可以用 1 字节或者 2 字节来存储，那么什么时候用 1 字节、什么适合用 2 字节呢？

  • 当整条记录的真实数据占用字节数不大于 127(二进制 0111 1111) 时，每个列对应的偏移量占用 1 字节。
  • 当整条记录的真实数据占用字节数大于 127，但不大于 32767(二进制 0111 1111 1111 1111) 时，每个列对应的偏移量占用 2 字节。
  • 当整条记录的真实数据占用字节数大于 32767 时，会将记录的一部分存放在所谓的溢出页中，在本页中只保留前 768 字节和 20 字节的溢出页面地址，这种情况下每个列对应的偏移量也占用 2 字节。

当 1 byte_offs_flag 为 1 时，表明使用 1 字节存储偏移量。

当 1 byte_offs_flag 为 0 时，表明使用 2 字节存储偏移量。

NULL 值处理

由于 REDUNDANT 行格式没有 NULL 值列表，所以设计 REDUNDANT 行格式时在字段长度偏移列表中对各列对应的偏移量做了一些特殊处理——将列对应的偏移量值得第一个比特位作为 NULL 的依据，该比特位也被称为 NULL 比特位。如果该列对应的偏移量的 NULL 比特位为 1，那么该列的值就是 NULL，反之，则不是 NULL。

这也就解释了为什么 1 byte_offs_flag 为什么 1 字节和 2 字节的划分为 127，而不是 255(因为首位用来做 NULL 值判断)。

  • 如果存储 NULL 值的字段是定长类型，比如 CHAR(M)类型，则 NULL 值也将占用记录的真实数据部分，并把该字段对应的真实数据用 0 填充。
  • 如果存储 NULL 值的字段是变长类型，比如 VARCHAR 类型，则不在记录的真实数据部分占用任何存储空间。

CHAR(M) 列的存储格式

在使用 COMPACT 行格式是，CHAR(M) 类型的列使用的字符集不同，该列的真实数据具体存储方式也不同。

而 REDUNADANT 行格式则十分干脆，不管该列使用的字符集是什么，只要使用 CHAR(M)类型，该列的真实数据占用的存储空间大小就是该字符集单个字符占用最多的字节数 × M。如果使用 UTF8 字符集的 CHAR(10) 类型，则占用存储空间始终为 30 字节；如果使用 GBK 字符集的 CHAR(10) 类型，则占用存储空间始终为 20 字节。

溢出列

🏷️前情提要：InnoDB 中磁盘和内存交互的基本单位是页，而一个页的大小一般为 16KB，也就是 16384 字节，而面对一条大记录（如 REDUNDANT 行格式下大于 32767 字节的记录），显然一页存不下此条记录。

什么是溢出列？

在 COMPACT 和 REDUNDANT 行格式中，对于占用存储空间非常多的列，在记录的真实数据处只会存储该列的一部分数据，而把剩余的数据分散存储在几个其他页中，然后在记录的真实数据处用 20 字节存储指向这些页的地址，从而找到剩余数据所在的页。

COMPACT 溢出列

上面展示的是 COMPACT 行格式的存储方式，可以看到在本记录的真实数据处只会存储前 768 个字节的数据以及一个指向其他页的地址，然后把剩下的数据存放到其他页中。这些 768 个字节之外的数据页面也称为溢出页。某条记录中的某一列需要使用溢出页来存储时，我们称该列为溢出列（off_page），不只是 VARCHAR(M)类型的列可能成为溢出列，像 TEXT、BLOB 这些类型的列在存储数据过多时也会成为溢出列。

溢出页的临界点

一个列在存储了多少字节后会变为溢出列呢？

MySQL 规定在一个页中至少要存放两行记录。每条记录最少需要包含多少字节的数据才会需要溢出列呢？以下来分析页中的空间都是

  • 每个页除了存放我们的记录之外，也需要存一些额外信息，这些信息加起来需要 132 字节的空间，其他的空间都可以被用来存储记录。
  • 每个记录需要的额外信息是 27 字节。这 27 字节包括下面这些内容：
    • 2 字节用来存储真实数据长度；
    • 1 字节用来存储列是否是 NULL 值；
    • 5 字节大小的头信息；
    • 6 字节的 row_id 列；
    • 6 字节的 trx_id 列；
    • 7 字节的 roll_pointer 列。

假设一个列的真实数据占用的字节数为 N，一个页中有 M 行，如果一个列想不发生溢出现象，那么就需要满足下面这个不等式：

132+M×(27+N)<16384132 + M \times (27 + N) < 16384

由于 MySQL 规定一个页中最少要放两行记录，所以 132 + 2 × (27 + N) < 16384，即 N < 8099，这是只针对于表中只有一个列所计算出的最大值。如果有多个列，则不等式和结论就需要修改了，但重点是：我们不用关注这个临界点是什么，只要知道如果一条记录的某个列中存储的数据占用的字节数非常多时，该列就可能成为溢出列。

DYNAMIC 行格式和 COMPRESSED 行

MySQL5.0 之后默认的行格式为 COMPACT，而在 5.7 版本之后默认行格式为 DYNAMIC。

DYNAMIC 行格式和 COMPRESSED 行格式和 COMPACT 行格式基本一致，唯一的区别是 DYNAMIC 和 COMPRESSED 行格式不会在记录的真实数据处存储该溢出列的前 768 个字节数据，而是把所有的真实数据都存储到溢出页中，只在记录的真实数据处存储 20 字节的溢出页地址。

DYNAMIC 和 COMPRESSED 行格式

COMPRESSED 行格式和 DYNAMIC 行格式的区别在于，COMPRESSED 行格式会采用压缩算法对页面进行压缩，以节省空间。