MySQL存储引擎内部之：`InnoDB`的`记录`格式：`Compact`、`Dynamic`和`Redundant`格式的详细区别。

MySQL InnoDB 记录格式深度剖析：Compact、Dynamic 和 Redundant

大家好，今天我们来深入探讨 MySQL InnoDB 存储引擎中记录格式的奥秘，重点关注 Compact、Dynamic 和 Redundant 这三种格式的区别。理解这些格式对于优化数据库性能、更好地利用存储空间至关重要。

一、InnoDB 记录结构概览

在深入了解具体格式之前，我们先对 InnoDB 记录的整体结构有个基本的认识。InnoDB 使用 B+ 树来组织数据，每个节点包含多个记录，这些记录按照主键排序。一条 InnoDB 记录主要包含以下几个部分：

• 记录头信息 (Record Header): 包含一些控制信息，如记录的删除状态、next_record 指针等。
• 真实数据 (Actual Data): 存储记录的实际数据，包括所有用户定义的列的值。
• 其他信息: 可能包含一些辅助信息，例如 NULL 值列表（用于处理允许为 NULL 的列）和变长字段长度列表（用于处理 VARCHAR、TEXT、BLOB 等变长类型）。

二、Redundant 记录格式

Redundant 是 MySQL 5.0 及更早版本的默认记录格式，现在已经很少使用，但了解它有助于我们理解后续格式的改进。

1. 记录头信息： Redundant 格式的记录头信息占用 6 字节，包含以下内容：

   • delete_mask: 1 bit，标记记录是否被删除。
   • min_rec_mask: 1 bit，标记是否为 B+ 树的最小记录。
   • n_owned: 4 bits，表示该记录拥有的记录数（仅在页面目录槽中使用）。
   • heap_no: 13 bits，表示记录在堆中的位置（0 表示未使用，1 表示页目录，2 表示最小记录，3 表示最大记录）。
   • record_type: 3 bits，表示记录类型（0 表示普通记录，1 表示 B+ 树非叶子节点记录，2 表示最小记录，3 表示最大记录）。
   • next_record: 16 bits，指向下一条记录的偏移量。

2. 真实数据： 真实数据区域存储了所有列的值。Redundant 格式会显式存储每个字段的长度，即使是固定长度的字段。对于变长字段，会使用 2 字节存储长度信息。如果字段允许为 NULL，则会用一个单独的 NULL 值列表来标记哪些字段为 NULL。

3. NULL 值列表： 如果表中有允许为 NULL 的列，Redundant 格式会在真实数据之前添加一个 NULL 值列表。每个允许为 NULL 的列在列表中占据一位，如果该位为 1，则表示该列的值为 NULL。NULL 值列表的长度取决于表中允许为 NULL 的列的数量。

4. 示例：

   假设我们有以下表结构：

   CREATE TABLE redundant_table (
       id INT PRIMARY KEY,
       name VARCHAR(50),
       age INT NULL,
       email VARCHAR(100) NULL
   ) ROW_FORMAT=REDUNDANT;

   插入一条数据：

   INSERT INTO redundant_table (id, name, age, email) VALUES (1, 'Alice', 30, '[email protected]');

   在 Redundant 格式下，这条记录的存储结构大致如下（仅为示意，实际存储更复杂）：

   [记录头信息 (6 bytes)] [NULL 值列表 (1 byte)] [id (4 bytes)] [name 长度 (2 bytes)] [name (5 bytes)] [age (4 bytes)] [email 长度 (2 bytes)] [email (17 bytes)]

   如果 age 和 email 都为 NULL：

   [记录头信息 (6 bytes)] [NULL 值列表 (1 byte)] [id (4 bytes)] [name 长度 (2 bytes)] [name (5 bytes)]

   NULL值列表会显示age和email都为NULL。

三、Compact 记录格式

Compact 格式是 MySQL 5.1 引入的，并在 MySQL 5.5 成为默认格式。它主要针对 Redundant 格式的缺点进行了优化，减少了存储空间。

1. 记录头信息： Compact 格式的记录头信息占用 5 字节，比 Redundant 格式少 1 字节。内容与 Redundant 类似，但 heap_no 字段只有 12 位，而 next_record 字段扩展到 16 位，更加灵活。

2. 真实数据： Compact 格式在存储真实数据时，主要有以下改进：

   • 变长字段长度列表： Compact 格式不再显式存储固定长度字段的长度。对于变长字段，它使用一个变长字段长度列表来存储长度信息。这个列表位于记录头信息之后，真实数据之前。每个变长字段的长度占用 1 或 2 字节，取决于字段的最大长度。如果所有变长字段的总长度小于 256 字节，则每个长度占用 1 字节；否则，占用 2 字节。
   • NULL 值列表： 与 Redundant 格式相同，如果表中有允许为 NULL 的列，Compact 格式也会使用 NULL 值列表。

3. 变长字段长度列表： 这是一个关键的优化点。假设一个表有多个 VARCHAR 类型的列，Redundant 格式会为每个 VARCHAR 列都存储一个 2 字节的长度信息，即使实际存储的字符串很短。Compact 格式则将所有 VARCHAR 列的长度信息集中存储在一个列表中，并且根据实际长度使用 1 或 2 字节，更有效地利用了存储空间。

4. 示例：

   假设我们使用与之前相同的表结构，但指定 ROW_FORMAT=COMPACT：

   CREATE TABLE compact_table (
       id INT PRIMARY KEY,
       name VARCHAR(50),
       age INT NULL,
       email VARCHAR(100) NULL
   ) ROW_FORMAT=COMPACT;

   插入相同的数据：

   INSERT INTO compact_table (id, name, age, email) VALUES (1, 'Alice', 30, '[email protected]');

   在 Compact 格式下，这条记录的存储结构大致如下：

   [记录头信息 (5 bytes)] [变长字段长度列表 (3 bytes)] [NULL 值列表 (1 byte)] [id (4 bytes)] [name (5 bytes)] [age (4 bytes)] [email (17 bytes)]

   变长字段长度列表会存储name和email字段的长度。

   如果 age 和 email 都为 NULL：

   [记录头信息 (5 bytes)] [变长字段长度列表 (1 bytes)] [NULL 值列表 (1 byte)] [id (4 bytes)] [name (5 bytes)]

   变长字段长度列表会存储name字段的长度。

四、Dynamic 记录格式

Dynamic 格式是 MySQL 5.6 引入的，并成为默认格式。它在 Compact 格式的基础上，进一步优化了对大文本（TEXT、BLOB）字段的处理。

1. 记录头信息： Dynamic 格式的记录头信息与 Compact 格式相同，占用 5 字节。

2. 真实数据： Dynamic 格式的关键改进在于对大文本字段的处理方式。如果一个记录包含 TEXT 或 BLOB 类型的列，并且这些列的长度超过一定阈值（默认为 40 字节），那么 InnoDB 会将这些列的数据存储在单独的溢出页 (overflow page) 中，而不是直接存储在记录本身。记录中只存储指向溢出页的指针。

3. 溢出页： 溢出页是一个单独的存储区域，用于存储大文本字段的实际数据。InnoDB 使用链表的方式将多个溢出页连接起来，以便存储超过单个页面的数据。

4. 优势： Dynamic 格式的优势在于，它可以避免在一个 B+ 树节点中存储过多的数据，从而提高 B+ 树的性能。当查询只需要访问记录的其他列，而不需要访问大文本字段时，InnoDB 可以直接从 B+ 树节点读取数据，而不需要访问溢出页，从而减少了 I/O 操作。

5. 示例：

   假设我们有以下表结构：

   CREATE TABLE dynamic_table (
       id INT PRIMARY KEY,
       name VARCHAR(50),
       content TEXT
   ) ROW_FORMAT=DYNAMIC;

   插入一条数据，其中 content 字段包含大量文本：

   INSERT INTO dynamic_table (id, name, content) VALUES (1, 'Article', REPEAT('A', 1000));

   在 Dynamic 格式下，这条记录的存储结构大致如下：

   [记录头信息 (5 bytes)] [变长字段长度列表 (1 byte)] [id (4 bytes)] [name (5 bytes)] [content 指针 (20 bytes)] ...溢出页...

   content 字段的数据不会直接存储在记录中，而是存储在单独的溢出页中，记录中只包含指向溢出页的指针。

   如果 content 字段的长度很小（例如，小于 40 字节），则会直接存储在记录中，而不会使用溢出页。

五、三种记录格式的对比

特性	Redundant	Compact	Dynamic
记录头信息长度	6 bytes	5 bytes	5 bytes
变长字段长度存储	每个字段单独存储	变长字段长度列表	变长字段长度列表
NULL 值列表	有 NULL 列则存在	有 NULL 列则存在	有 NULL 列则存在
大文本字段处理	直接存储	直接存储	超过阈值则存储在溢出页
空间利用率	较低	较高	较高
性能	较低	较高	较高

六、如何选择记录格式

选择合适的记录格式取决于你的应用场景和数据特点。

• Redundant: 已经过时，不建议使用。
• Compact: 适合大多数场景，尤其是在没有大量 TEXT/BLOB 字段的情况下。
• Dynamic: 适合包含大量 TEXT/BLOB 字段的表，可以提高查询性能。

你可以使用以下 SQL 语句来查看表的记录格式：

SHOW TABLE STATUS LIKE 'table_name'G

结果中的 Row_format 字段会显示表的记录格式。

可以使用以下语句修改记录格式：

ALTER TABLE table_name ROW_FORMAT=COMPACT;
ALTER TABLE table_name ROW_FORMAT=DYNAMIC;

七、注意事项

• 修改表的记录格式会重建表，这可能需要花费一些时间，尤其是对于大型表。
• 在选择记录格式时，需要考虑存储空间和性能之间的平衡。Dynamic 格式虽然可以提高查询性能，但会增加 I/O 操作，因此需要根据实际情况进行选择。
• 不同的 MySQL 版本可能对记录格式的支持有所不同，建议查阅官方文档以获取最新的信息。

八、代码示例：创建不同记录格式的表

-- 创建 Redundant 格式的表 (不推荐)
CREATE TABLE redundant_example (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ROW_FORMAT=REDUNDANT;

-- 创建 Compact 格式的表
CREATE TABLE compact_example (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ROW_FORMAT=COMPACT;

-- 创建 Dynamic 格式的表
CREATE TABLE dynamic_example (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    content TEXT
) ROW_FORMAT=DYNAMIC;

-- 查看表的记录格式
SHOW TABLE STATUS LIKE 'redundant_example'G
SHOW TABLE STATUS LIKE 'compact_example'G
SHOW TABLE STATUS LIKE 'dynamic_example'G

-- 修改表的记录格式
ALTER TABLE redundant_example ROW_FORMAT=COMPACT;

九、深入理解带来的好处

理解 InnoDB 的记录格式对于数据库管理员和开发人员来说至关重要。它可以帮助我们更好地设计表结构、优化查询性能，并有效地利用存储空间，从而构建更高效、更可靠的数据库系统。选择合适的记录格式，能让数据库在特定场景下发挥最佳性能。