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

好的，我们今天来深入探讨MySQL InnoDB存储引擎中非常重要的一个概念：Record Format，特别是Compact、Dynamic和Redundant这三种格式。理解这些格式如何存储数据，对于优化数据库性能至关重要。

一、Record Format：数据在磁盘上的组织方式

Record Format定义了InnoDB在磁盘上如何组织和存储表数据。不同的Record Format在存储效率、兼容性和功能性方面有所差异。选择合适的Record Format可以显著影响数据库的性能，包括查询速度、存储空间占用和事务处理效率。

二、Redundant：最古老的格式

Redundant是MySQL 5.0版本之前默认的格式，也是最古老的一种。它相对简单，但存储效率较低。

• 结构:

  • 记录头（Record Header）: 包含记录的元数据信息，例如记录的删除标志、记录的长度等。
  • 字段数据: 包含记录的实际字段数据。

• 特点:

  • 定长字段存储: 所有字段都按照定义的长度存储，即使字段实际值长度小于定义长度，也会用空格填充。
  • 溢出页支持有限: 虽然支持BLOB和TEXT类型，但处理溢出页的效率不高。
  • 不支持压缩: 不支持压缩存储。

• 适用场景:

  • 已经非常罕见，通常只存在于老版本的数据库升级迁移。
  • 不建议在新表中使用。

• 缺陷:

  • 存储空间浪费: 定长字段的存储方式导致空间浪费。
  • 性能较差: 溢出页处理效率低。

让我们通过一个例子来说明 Redundant 格式的存储方式。 假设我们有一个表 users，定义如下：

CREATE TABLE users (
    id INT,
    username VARCHAR(20),
    email VARCHAR(50),
    PRIMARY KEY (id)
) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;

假设我们插入一条记录：

INSERT INTO users (id, username, email) VALUES (1, 'test', '[email protected]');

在 Redundant 格式下， username 字段会占用 20 个字节，即使实际存储的字符串 ‘test’ 只有 4 个字节。剩余的 16 个字节会被空格填充。 email字段会占用50个字节，即使实际存储的字符串’[email protected]’只有17个字节，剩余的33个字节会被空格填充。

三、Compact：更高效的存储

Compact 是 MySQL 5.0 引入的，并且是 MySQL 5.1 及之后版本的默认格式（早期版本）。 它旨在减少存储空间的占用并提高性能。

• 结构:

  • 记录头（Record Header）: 包含记录的元数据信息。
  • 变长字段长度列表: 存储变长字段的实际长度。
  • NULL值标志位: 使用一个位来标识字段是否为NULL，减少NULL值占用的空间。
  • 字段数据: 包含记录的实际字段数据。

• 特点:

  • 变长字段压缩存储: 只存储实际的数据长度，避免空间浪费。
  • NULL值优化: 使用 NULL 值标志位，而不是实际存储 NULL 值。
  • 溢出页支持: 支持将 BLOB 和 TEXT 等大型字段存储到溢出页中。
  • 更紧凑的存储: 相比 Redundant 格式，减少了存储空间占用。

• 适用场景:

  • 大多数场景。
  • 对存储空间有较高要求的场景。

• 优势:

  • 节省存储空间: 变长字段的压缩存储和 NULL 值优化减少了空间占用。
  • 性能提升: 更紧凑的存储减少了 I/O 操作。

同样使用上面的 users 表和数据，如果使用 Compact 格式：

CREATE TABLE users (
    id INT,
    username VARCHAR(20),
    email VARCHAR(50),
    PRIMARY KEY (id)
) ENGINE=InnoDB ROW_FORMAT=COMPACT;

INSERT INTO users (id, username, email) VALUES (1, 'test', '[email protected]');

username 字段只会存储 4 个字节的数据 ‘test’，再加上一个或多个字节来表示该字段的长度（取决于最大长度，小于 255 字节通常只需要 1 个字节）。 email 字段只会存储 17 个字节的数据’[email protected]’，再加上一个或多个字节来表示该字段的长度。 这显著减少了存储空间的占用。

代码示例：模拟 Compact 格式的存储（简化版）

以下是一个 Python 代码示例，用于模拟 Compact 格式的存储方式（简化版，不包含所有细节）。

class CompactRecord:
    def __init__(self, data_types, values):
        self.data_types = data_types
        self.values = values
        self.data = bytearray()  # 使用 bytearray 存储数据

    def serialize(self):
        """序列化数据为 Compact 格式"""
        # 1. NULL 值标志位 (简化：假设所有字段都不为 NULL)
        null_flags = 0  # 假设没有 NULL 值
        # self.data.append(null_flags) # 实际情况需要根据 NULL 值设置标志位

        # 2. 变长字段长度列表
        variable_length_fields = []
        for i, data_type in enumerate(self.data_types):
            if data_type.startswith("VARCHAR") or data_type.startswith("TEXT") or data_type.startswith("BLOB"):
                variable_length_fields.append((i, len(str(self.values[i])))) # 保存字段索引和长度

        # 先存储变长字段的长度
        for index, length in variable_length_fields:
            self.data.extend(length.to_bytes(1, 'big')) # 假设长度小于 256

        # 3. 字段数据
        for i, value in enumerate(self.values):
            data_type = self.data_types[i]
            if data_type == "INT":
                self.data.extend(value.to_bytes(4, 'big'))  # 假设 INT 是 4 字节
            elif data_type.startswith("VARCHAR") or data_type.startswith("TEXT") or data_type.startswith("BLOB"):
                self.data.extend(str(value).encode('utf-8')) # 存储字符串的 UTF-8 编码
            else:
                raise ValueError(f"Unsupported data type: {data_type}")

        return bytes(self.data) # 返回字节数据

    def deserialize(self, data):
        """从 Compact 格式的数据反序列化"""
        # (简化版本，仅演示基本思路)
        # 注意：实际反序列化需要根据 data_types 和数据长度进行解析
        # 这里仅打印数据
        print("Raw data:", data)
        # TODO: 实现反序列化逻辑

# 示例用法
data_types = ["INT", "VARCHAR(20)", "VARCHAR(50)"]
values = [1, "test", "[email protected]"]

record = CompactRecord(data_types, values)
serialized_data = record.serialize()

print("Serialized data:", serialized_data)

record.deserialize(serialized_data) # 简单演示

这个例子演示了如何将数据序列化为 Compact 格式的基本思路。实际的 InnoDB 实现远比这复杂，但它展示了变长字段长度列表和字段数据是如何组织的。

四、Dynamic：BLOB 和 TEXT 字段的优化

Dynamic 是 MySQL 5.1 引入的，并成为 MySQL 5.7 及之后版本的默认格式。 它是对 Compact 格式的进一步优化，尤其是在处理 BLOB 和 TEXT 等大型字段时。

• 结构:

  与 Compact 格式类似，但对 BLOB 和 TEXT 字段的处理方式不同。

• 特点:

  • BLOB 和 TEXT 字段的完全行溢出: 当 BLOB 和 TEXT 字段的大小超过一定阈值时，InnoDB 会将整个字段的数据存储到单独的溢出页中，只在数据页中保留一个 20 字节的指针指向溢出页。
  • 减少数据页的碎片: 由于 BLOB 和 TEXT 字段的数据不在数据页中存储，可以减少数据页的碎片，提高查询效率。
  • 更高效的 BLOB/TEXT 处理: 更适合存储大型文本和二进制数据。

• 适用场景:

  • 包含大量 BLOB 和 TEXT 字段的表。
  • 对 BLOB 和 TEXT 字段的读写性能有较高要求的场景。

• 优势:

  • 提高 BLOB/TEXT 字段的读写性能: 通过将 BLOB/TEXT 字段存储到溢出页，减少了数据页的 I/O 操作。
  • 减少数据页碎片: 提高了查询效率。

继续使用上面的 users 表，但这次我们添加一个 profile 字段，用于存储用户的个人简介（TEXT 类型）：

CREATE TABLE users (
    id INT,
    username VARCHAR(20),
    email VARCHAR(50),
    profile TEXT,
    PRIMARY KEY (id)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

INSERT INTO users (id, username, email, profile) VALUES (1, 'test', '[email protected]', 'This is a long profile text...');

如果 profile 字段的内容非常长，超过了 InnoDB 的阈值（通常是大约半个数据页），Dynamic 格式会将 profile 字段的数据存储到单独的溢出页中。 在 users 表的数据页中，只会存储一个指向溢出页的指针。 这样可以避免 profile 字段占用过多的数据页空间，提高其他字段的查询效率。

五、对比总结

特性	Redundant	Compact	Dynamic
字段存储	定长存储	变长字段压缩存储	变长字段压缩存储，BLOB/TEXT 字段完全行溢出
NULL 值处理	占用空间	NULL 值标志位	NULL 值标志位
适用场景	遗留系统	大多数场景	包含大量 BLOB/TEXT 字段的表
空间占用	高	较低	更低（特别是包含大量 BLOB/TEXT 字段时）
性能	较低	较高	最高（特别是对 BLOB/TEXT 字段的读写性能）
默认格式	MySQL 5.0 之前	MySQL 5.1 – 5.6 (早期版本)	MySQL 5.7 及之后

六、如何选择合适的 Record Format

选择合适的 Record Format 需要根据具体的应用场景和数据特点进行权衡。

• 如果数据库中包含大量的 BLOB 和 TEXT 字段，并且对这些字段的读写性能有较高要求，那么 Dynamic 格式是最佳选择。
• 如果数据库中没有大量的 BLOB 和 TEXT 字段，或者对存储空间有较高要求，那么 Compact 格式是一个不错的选择。
• 除非是遗留系统，否则不建议使用 Redundant 格式。

七、查看和修改 Record Format

可以使用以下 SQL 语句查看表的 Record Format：

SHOW TABLE STATUS LIKE 'table_name'G

在结果中，Row_format 字段显示了表的 Record Format。

可以使用以下 SQL 语句修改表的 Record Format：

ALTER TABLE table_name ROW_FORMAT=format_name;

其中，table_name 是要修改的表名，format_name 是要设置的 Record Format，可以是 REDUNDANT、COMPACT 或 DYNAMIC。

八、Record Format与Page Size 的关系

InnoDB的Page Size默认是16KB，Record Format的选择会影响每个Page能存储的记录数量。更紧凑的Record Format（如Compact和Dynamic）通常意味着每个Page可以存储更多的记录，从而减少I/O操作，提高查询效率。

九、深入理解行溢出

Dynamic格式的核心优势在于对BLOB和TEXT类型字段的行溢出处理。当这些字段的数据量超过一定阈值时，InnoDB会将数据存储在单独的溢出页中，并在主数据页中只保留一个指向溢出页的指针。 这种机制避免了大型字段占据过多的主数据页空间，减少了数据页的碎片，从而提高了查询性能。 理解行溢出对于优化包含大型文本或二进制数据的表至关重要。

十、总结：选择合适的存储格式，提升数据库性能

理解InnoDB的Record Format是数据库优化的关键一步。Compact和Dynamic格式通过更紧凑的存储方式和行溢出处理，显著提高了存储效率和查询性能。根据实际应用场景选择合适的Record Format，能够最大限度地发挥InnoDB存储引擎的优势，优化数据库性能。