MySQL存储引擎之：`InnoDB`的`Record Format`：`Compact`、`Dynamic`、`Redundant`的字段存储格式。

MySQL InnoDB Record Format：Compact、Dynamic、Redundant详解

大家好！今天我们来深入探讨MySQL InnoDB存储引擎中非常重要的一个概念：Record Format。Record Format定义了InnoDB如何物理地存储表中的数据行，直接影响着存储空间利用率、性能和兼容性。我们将会重点介绍三种主要的Record Format：Compact、Dynamic、和Redundant，并通过具体的例子和代码来加深理解。

1. Record Format概述

Record Format是InnoDB存储引擎用于在磁盘上组织和存储表数据的格式。不同的Record Format在存储结构、空间利用率和对长数据列的处理方式上有所不同。选择合适的Record Format可以优化存储空间，提高查询效率，并确保与旧版本MySQL的兼容性。

2. Redundant Record Format

Redundant是MySQL 5.0及更早版本中使用的默认Record Format。它以简单直接的方式存储数据，但空间利用率相对较低。

2.1 Redundant的存储结构

Redundant Format 主要由以下几个部分构成：

• 记录头 (Record Header): 记录头包含一些元数据，如记录的删除标记、记录长度等。
• 字段数据 (Field Data): 实际的字段数据，按照字段的顺序依次存储。

2.2 Redundant的特点

• 固定长度字段直接存储: 固定长度的字段直接存储其值。
• 变长字段长度信息: 变长字段（如VARCHAR、TEXT、BLOB）会存储一个长度信息，用于指示字段的实际长度。长度信息直接存储在字段数据之前，占用1或2个字节。如果字段的最大长度小于256字节，使用1个字节存储长度；否则，使用2个字节。
• NULL值的处理: NULL值使用一个特殊的标记位来表示。
• 空间浪费: Redundant Format的主要缺点是空间浪费。即使变长字段的值很短，它仍然会预留最大长度的空间。

2.3 Redundant的例子

假设我们有如下的表结构：

CREATE TABLE redundant_example (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    age INT
) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;

插入一条数据：

INSERT INTO redundant_example (id, name, age) VALUES (1, 'John', 30);

在Redundant Format下，name字段（VARCHAR(20)）即使只存储了"John"（4个字符），仍然会预留20个字节的空间。

2.4 Redundant的适用场景

由于空间利用率较低，Redundant Format 现在已经很少使用。它主要用于兼容旧版本的MySQL。

3. Compact Record Format

Compact Record Format 在MySQL 5.0中引入，并在MySQL 5.1中成为默认的Record Format。相比Redundant，Compact Format 显著提高了存储空间利用率。

3.1 Compact的存储结构

Compact Format的存储结构更加紧凑，主要由以下几个部分组成：

• 记录头 (Record Header): 与Redundant类似，包含元数据。
• NULL标志位 (NULL Flags): 一个单独的区域，用于标记哪些字段为NULL。
• 变长字段长度列表 (Variable-Length Field Length List): 存储变长字段的长度信息。
• 字段数据 (Field Data): 实际的字段数据。

3.2 Compact的特点

• NULL标志位: 使用一个单独的区域来存储NULL标志位，而不是像Redundant那样在字段数据中存储NULL标记。这节省了空间，特别是当表中有多个允许为NULL的字段时。
• 变长字段长度列表: 变长字段的长度信息存储在一个单独的列表中，而不是像Redundant那样存储在每个变长字段的前面。这个列表按照字段在表定义中的顺序排列。
• 紧凑存储: Compact Format 尽可能地减少了空间浪费，只存储实际的数据。

3.3 Compact的例子

继续使用上面的表结构，但是将ROW_FORMAT改为COMPACT：

CREATE TABLE compact_example (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    age INT
) ENGINE=InnoDB ROW_FORMAT=COMPACT;

插入相同的数据：

INSERT INTO compact_example (id, name, age) VALUES (1, 'John', 30);

在Compact Format下，name字段只会存储实际的长度（4个字节）加上长度信息所占用的字节数。不会预留20个字节的空间。

3.4 Compact的适用场景

Compact Format 适合大多数场景，因为它在空间利用率和性能之间取得了很好的平衡。它是许多MySQL版本的默认Record Format。

4. Dynamic Record Format

Dynamic Record Format 在MySQL 5.1中引入，旨在更好地处理长数据列（如TEXT和BLOB）。

4.1 Dynamic的存储结构

Dynamic Format的存储结构与Compact类似，但它对长数据列的处理方式不同：

• 记录头 (Record Header)
• NULL标志位 (NULL Flags)
• 变长字段长度列表 (Variable-Length Field Length List)
• 字段数据 (Field Data)
• 溢出页指针 (Overflow Page Pointers)：这是Dynamic和Compact的主要区别。

4.2 Dynamic的特点

• 长数据列的溢出存储: 当长数据列的值超过一定长度时，Dynamic Format 会将数据存储在单独的溢出页中，只在记录中存储一个指向溢出页的指针。
• 减少主页面的大小: 通过将长数据列存储在溢出页中，Dynamic Format 可以减少主页面的大小，提高查询性能。
• 与Compact的相似性: 对于非长数据列，Dynamic Format 的存储方式与 Compact Format 几乎相同。

4.3 Dynamic的例子

修改表结构，添加一个TEXT字段：

CREATE TABLE dynamic_example (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    age INT,
    content TEXT
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

插入一条包含较长TEXT数据的数据：

INSERT INTO dynamic_example (id, name, age, content) VALUES (1, 'John', 30, REPEAT('A', 20000));

在Dynamic Format下，content字段的数据如果超过InnoDB页面大小的一半，就会被存储在溢出页中。主页面中只存储一个指向溢出页的指针。

4.4 Dynamic的适用场景

Dynamic Format 适合包含大量长数据列的表。它可以有效地减少主页面大小，提高查询效率。

5. 溢出页 (Overflow Pages)

溢出页是InnoDB用于存储长数据列的数据块。当长数据列的值超过InnoDB页面大小的一半时，InnoDB会将数据存储在溢出页中，并在主页面中存储一个指向溢出页的指针。

5.1 溢出页的类型

InnoDB有两种类型的溢出页：

• BLOB页 (BLOB Pages): 用于存储BLOB数据。
• Uncompressed BLOB页 (Uncompressed BLOB Pages): 用于存储未压缩的BLOB数据。

5.2 溢出页的组织方式

溢出页可以以链表的形式组织，也可以以B-tree的形式组织。链表形式的溢出页更容易实现，但查询效率较低。B-tree形式的溢出页查询效率较高，但实现起来更复杂。

6. Record Format的选择

选择合适的Record Format 取决于表的数据类型和查询模式。

• Redundant: 仅在需要兼容旧版本MySQL时使用。
• Compact: 适合大多数场景，特别是当表不包含大量长数据列时。
• Dynamic: 适合包含大量长数据列的表。

7. 修改Record Format

可以使用ALTER TABLE语句修改Record Format：

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

修改Record Format 会导致表重建，因此需要谨慎操作。

8. 代码示例

为了更直观地理解Record Format，我们可以通过MySQL的INFORMATION_SCHEMA库查看表的ROW_FORMAT信息：

SELECT TABLE_NAME, ROW_FORMAT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME IN ('redundant_example', 'compact_example', 'dynamic_example');

将your_database_name替换为你的数据库名称。执行这个查询，你将会看到每个表的ROW_FORMAT。

此外，可以使用SHOW TABLE STATUS 命令查看更详细的表信息：

SHOW TABLE STATUS LIKE 'table_name'G

将table_name替换为你要查询的表名。输出结果中会包含Row_format字段，显示了表的Record Format。

为了演示Dynamic Format的溢出页效果，可以创建一个包含长TEXT字段的表，并插入大量数据。然后，使用SHOW TABLE STATUS命令查看Data_length和Index_length，比较在Compact和Dynamic Format下的差异。 在Dynamic Format下，Data_length可能会小于实际的数据大小，因为长数据被存储在溢出页中。

9. 一些需要注意的点

• 页面大小: InnoDB的页面大小会影响长数据列是否会被存储在溢出页中。默认的页面大小是16KB。
• 压缩: 可以对InnoDB表进行压缩，以进一步减少存储空间。压缩算法会影响Record Format的存储方式。
• 性能测试: 在选择Record Format之前，最好进行性能测试，以确定哪种格式最适合你的应用场景。

10.总结： 理解Record Format 优化数据存储

今天我们深入探讨了InnoDB存储引擎的三种主要Record Format：Compact、Dynamic和Redundant。理解这些格式的存储结构和特点，可以帮助我们更好地选择合适的Record Format，优化数据库存储空间，提高查询性能，并确保与旧版本MySQL的兼容性。记住，根据你的数据类型和查询模式进行选择，并进行性能测试，以找到最佳的配置。