MySQL的InnoDB的行格式：Compact、Dynamic、Redundant与Compressed的差异，以及LOB数据在其中的存储方式

InnoDB 行格式详解：Compact, Dynamic, Redundant, Compressed

大家好，今天我们来深入探讨 MySQL InnoDB 存储引擎中的行格式。InnoDB 作为 MySQL 默认的存储引擎，其行格式直接影响着数据的存储效率、空间利用率和性能。我们将详细讲解四种主要的行格式：Compact、Dynamic、Redundant 和 Compressed，并重点分析 LOB (Large Objects) 数据在这些格式中的存储方式。

1. 行格式概述

在 InnoDB 中，每一行数据都以特定的格式存储在磁盘上。行格式决定了数据记录的物理布局，包括字段的存储顺序、NULL 值的处理、变长字段的处理、以及 LOB 数据的存储方式。选择合适的行格式可以显著提高数据库的性能和存储效率。

InnoDB 的行格式主要有以下几种：

• Redundant: MySQL 5.0 及之前版本的默认行格式，相对陈旧，空间利用率较低。
• Compact: MySQL 5.1 引入，旨在提高空间利用率，是目前推荐的行格式之一。
• Dynamic: MySQL 5.1 引入，与 Compact 类似，但在处理 LOB 数据上有差异，也是目前推荐的行格式之一。
• Compressed: MySQL 5.1 引入，支持对数据进行压缩，进一步节省存储空间。

接下来，我们将逐一深入分析这些行格式的结构和特点。

2. Redundant 行格式

Redundant 行格式是 InnoDB 的早期版本中使用的行格式。其结构相对简单，但空间利用率较低。

结构特点：

• 记录头 (Record Header): 包含记录的元数据信息，如删除标记、记录类型、下一条记录的指针等。
• 字段数据 (Field Data): 按照表定义中的字段顺序存储数据。
• NULL 值标记: 使用一个 NULL 标志位数组来标记 NULL 值。如果某个字段为 NULL，则对应的标志位会被设置。
• 变长字段长度: 对于 VARCHAR、TEXT 等变长字段，存储其长度信息。

NULL 值的处理：

Redundant 行格式使用独立的 NULL 标志位数组来标记 NULL 值。每个字段对应一个标志位，如果字段为 NULL，则标志位被设置。

变长字段的处理：

对于 VARCHAR、TEXT 等变长字段，Redundant 行格式会存储字段的实际长度。

LOB 数据的存储：

LOB 数据 (如 BLOB、TEXT) 直接存储在数据页中。如果 LOB 数据过大，可能会导致数据页非常庞大，影响性能。

Redundant 行格式示例：

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

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

插入一条数据：

INSERT INTO redundant_example (id, name, content) VALUES (1, 'test', 'This is a test content.');

Redundant 行格式的存储结构大致如下：

[Record Header] [NULL Flags] [id] [name length] [name] [content length] [content]

缺点：

• 空间利用率较低，特别是当存在大量 NULL 值时。
• LOB 数据直接存储在数据页中，可能导致数据页过大，影响性能。

3. Compact 行格式

Compact 行格式是 InnoDB 为了提高空间利用率而引入的。它对 Redundant 行格式进行了优化，特别是在处理 NULL 值和变长字段方面。

结构特点：

• 记录头 (Record Header): 与 Redundant 行格式类似，包含记录的元数据信息。
• 变长字段长度列表 (Variable-Length Column Length List): 存储变长字段的长度信息。
• NULL 值标志位 (NULL Flags): 存储 NULL 值的标志位。
• 字段数据 (Field Data): 按照表定义中的字段顺序存储数据。

NULL 值处理：

Compact 行格式将 NULL 值标志位集中存储在 NULL Flags 部分，而不是像 Redundant 行格式那样为每个字段单独存储一个标志位。这可以节省空间，特别是当存在大量 NULL 值时。

变长字段处理：

Compact 行格式使用变长字段长度列表来存储变长字段的长度信息。长度列表位于记录头之后，NULL 标志位之前。

LOB 数据的存储：

Compact 行格式对于 LOB 数据的处理方式与 Redundant 行格式类似，直接将 LOB 数据存储在数据页中。如果 LOB 数据过大，同样会导致数据页过大，影响性能。

Compact 行格式示例：

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

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

插入一条数据：

INSERT INTO compact_example (id, name, content) VALUES (1, 'test', 'This is a test content.');

Compact 行格式的存储结构大致如下：

[Record Header] [Variable-Length Column Length List] [NULL Flags] [id] [name] [content]

优点：

• 空间利用率比 Redundant 行格式更高，特别是在处理 NULL 值和变长字段方面。

缺点：

• LOB 数据的处理方式与 Redundant 行格式类似，可能导致数据页过大，影响性能。

4. Dynamic 行格式

Dynamic 行格式是 MySQL 5.1 引入的，旨在解决 Compact 行格式在处理 LOB 数据时的性能问题。Dynamic 行格式与 Compact 行格式非常相似，但在 LOB 数据的存储方式上有所不同。

结构特点：

Dynamic 行格式的结构与 Compact 行格式基本相同，包括：

• 记录头 (Record Header)
• 变长字段长度列表 (Variable-Length Column Length List)
• NULL 值标志位 (NULL Flags)
• 字段数据 (Field Data)

NULL 值处理：

Dynamic 行格式的 NULL 值处理方式与 Compact 行格式相同，使用 NULL Flags 来集中存储 NULL 值的标志位。

变长字段处理：

Dynamic 行格式的变长字段处理方式与 Compact 行格式相同，使用变长字段长度列表来存储变长字段的长度信息。

LOB 数据的存储：

Dynamic 行格式对于 LOB 数据的处理方式与 Compact 和 Redundant 行格式不同。它并不直接将 LOB 数据存储在数据页中，而是将 LOB 数据存储在独立的溢出页 (overflow page) 中，只在数据页中存储一个指向溢出页的指针。

LOB 数据溢出页：

当 LOB 数据的大小超过一定阈值时，InnoDB 会将 LOB 数据存储在独立的溢出页中。溢出页是专门用于存储 LOB 数据的页面，可以避免数据页过大。数据页中只存储一个 20 字节的指针，指向存储 LOB 数据的溢出页。这个指针包含了 LOB 数据在溢出页中的位置和长度信息。

Dynamic 行格式示例：

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

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

插入一条包含较大 LOB 数据的数据：

INSERT INTO dynamic_example (id, name, content) VALUES (1, 'test', REPEAT('A', 10000));

Dynamic 行格式的存储结构大致如下：

• 数据页: [Record Header] [Variable-Length Column Length List] [NULL Flags] [id] [name] [content pointer] 其中 content pointer 指向存储 LOB 数据的溢出页。
• 溢出页: 存储实际的 LOB 数据。

优点：

• 解决了 Compact 和 Redundant 行格式在处理 LOB 数据时可能导致的数据页过大的问题，提高了性能。
• 空间利用率较高。

缺点：

• 读取 LOB 数据时需要访问溢出页，可能会增加 I/O 操作。

5. Compressed 行格式

Compressed 行格式是在 Dynamic 行格式的基础上引入的，它支持对数据进行压缩，进一步节省存储空间。

结构特点：

Compressed 行格式的结构与 Dynamic 行格式基本相同，包括：

• 记录头 (Record Header)
• 变长字段长度列表 (Variable-Length Column Length List)
• NULL 值标志位 (NULL Flags)
• 字段数据 (Field Data) (压缩后)
• LOB 数据指针 (如果 LOB 数据溢出)

NULL 值处理：

Compressed 行格式的 NULL 值处理方式与 Dynamic 行格式相同，使用 NULL Flags 来集中存储 NULL 值的标志位。

变长字段处理：

Compressed 行格式的变长字段处理方式与 Dynamic 行格式相同，使用变长字段长度列表来存储变长字段的长度信息。

数据压缩：

Compressed 行格式会对数据进行压缩，以减少存储空间。压缩算法是可配置的，可以使用 zlib 等算法。

LOB 数据的存储：

Compressed 行格式对于 LOB 数据的处理方式与 Dynamic 行格式相同，将 LOB 数据存储在独立的溢出页中，并在数据页中存储一个指向溢出页的指针。LOB 数据在存储到溢出页之前也会被压缩。

Compressed 行格式示例：

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

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

插入一条包含较大 LOB 数据的数据：

INSERT INTO compressed_example (id, name, content) VALUES (1, 'test', REPEAT('A', 10000));

Compressed 行格式的存储结构大致如下：

• 数据页: [Record Header] [Variable-Length Column Length List] [NULL Flags] [Compressed Data (id, name)] [content pointer] 其中 content pointer 指向存储 LOB 数据的溢出页。
• 溢出页: 存储压缩后的 LOB 数据。

优点：

• 进一步节省存储空间，特别是在存储大量文本数据时。
• 解决了 Compact 和 Redundant 行格式在处理 LOB 数据时可能导致的数据页过大的问题，提高了性能。

缺点：

• 数据压缩和解压缩会消耗额外的 CPU 资源。
• 读取 LOB 数据时需要访问溢出页，可能会增加 I/O 操作。

6. 行格式选择的考量因素

选择合适的行格式需要综合考虑以下因素：

• 空间利用率： Compressed > Dynamic > Compact > Redundant
• 性能： Dynamic 和 Compact 通常比 Redundant 更好，Compressed 可能会带来额外的 CPU 消耗。
• LOB 数据的处理： Dynamic 和 Compressed 更适合存储大量的 LOB 数据。
• CPU 资源： Compressed 会消耗额外的 CPU 资源进行压缩和解压缩。

建议：

• 对于大多数应用，建议使用 Dynamic 或 Compact 行格式。
• 如果需要存储大量的 LOB 数据，建议使用 Dynamic 或 Compressed 行格式。
• 如果对存储空间有较高要求，并且 CPU 资源充足，可以考虑使用 Compressed 行格式。
• Redundant 行格式已经过时，不建议使用。

7. 代码示例：查看和修改行格式

可以使用以下 SQL 语句查看表的行格式：

SHOW TABLE STATUS LIKE 'your_table_name'G

在输出结果中，可以找到 Row_format 字段，它表示表的行格式。

可以使用以下 SQL 语句修改表的行格式：

ALTER TABLE your_table_name ROW_FORMAT=DYNAMIC;

将 your_table_name 替换为你的表名，DYNAMIC 替换为你想要设置的行格式。

示例：

-- 查看表 compact_example 的行格式
SHOW TABLE STATUS LIKE 'compact_example'G

-- 修改表 compact_example 的行格式为 dynamic
ALTER TABLE compact_example ROW_FORMAT=DYNAMIC;

-- 再次查看表 compact_example 的行格式
SHOW TABLE STATUS LIKE 'compact_example'G

8. 表格总结不同行格式的特性

特性	Redundant	Compact	Dynamic	Compressed
空间利用率	低	较高	较高	最高
LOB 数据处理	直接存储	直接存储	溢出页存储	溢出页存储
NULL 值处理	独立标志位	集中标志位	集中标志位	集中标志位
变长字段处理	存储长度	存储长度	存储长度	存储长度
压缩	不支持	不支持	不支持	支持
CPU 消耗	低	低	低	高
适用场景	已过时	一般应用	LOB 数据较多	空间敏感型

9. 影响数据存储和检索的关键因素

理解了 InnoDB 的四种行格式之后，我们就能更好地根据实际应用场景选择最合适的行格式，有效地提升数据库的性能。掌握这些知识对于优化数据库存储和检索至关重要。