MySQL的InnoDB的行格式：Compact、Dynamic与Compressed的差异及LOB数据的存储方式

InnoDB 行格式：Compact、Dynamic 与 Compressed 的 LOB 数据存储

大家好，今天我们来深入探讨 MySQL InnoDB 存储引擎中的行格式，特别是 Compact、Dynamic 和 Compressed 这三种主要格式，以及它们对大对象（LOB）数据的存储方式的影响。理解这些行格式对于优化数据库性能至关重要，尤其是在处理包含大量文本或二进制数据的应用中。

1. InnoDB 行格式概述

InnoDB 存储引擎以页为单位存储数据，默认页大小为 16KB。每一页包含多个行，而行格式决定了行数据在页内的物理存储方式。不同的行格式在存储效率、性能和对 LOB 数据的处理上有显著差异。选择合适的行格式是数据库设计的重要环节。

InnoDB 主要支持以下几种行格式：

• Redundant: MySQL 5.0 之前的默认行格式，效率较低，很少使用。
• Compact: MySQL 5.0 及之后的默认行格式，空间利用率较高。
• Dynamic: MySQL 5.1 及之后引入，针对 LOB 数据优化，是 MySQL 5.7 及之后版本的默认行格式。
• Compressed: MySQL 5.1 及之后引入，支持数据压缩，可以进一步减少存储空间占用。
• ROW_FORMAT=DEFAULT: 始终使用当前 MySQL 版本定义的默认行格式。

我们可以通过以下 SQL 语句查看表的行格式：

SHOW TABLE STATUS LIKE 'your_table_name'G

或者：

SELECT table_name, row_format
FROM information_schema.tables
WHERE table_schema = 'your_database_name' AND table_name = 'your_table_name';

要修改表的行格式，可以使用 ALTER TABLE 语句：

ALTER TABLE your_table_name ROW_FORMAT=COMPACT;
ALTER TABLE your_table_name ROW_FORMAT=DYNAMIC;
ALTER TABLE your_table_name ROW_FORMAT=COMPRESSED;

接下来，我们将重点分析 Compact、Dynamic 和 Compressed 这三种行格式。

2. Compact 行格式

Compact 行格式旨在减少存储空间，它通过以下方式实现：

• 记录头信息: 每行记录都有一个记录头信息，用于存储行的一些元数据，如删除标记、记录类型、下一条记录的指针等。
• 可变长度字段长度列表: 存储可变长度字段（如 VARCHAR、VARBINARY、TEXT、BLOB）的长度。 如果表的所有字段都是固定长度，则没有这个列表。
• NULL 值标志位: 如果字段允许为 NULL，则使用一个 NULL 值标志位来标记该字段是否为 NULL。
• 实际数据: 存储实际的列数据。

Compact 行格式的存储结构示意图 (简化版):

| Record Header | Variable Length Field Length List | NULL Flags | Column 1 Data | Column 2 Data | ... |

LOB 数据的处理:

在 Compact 行格式下，LOB 数据的处理方式如下：

• 如果 LOB 数据小于 768 字节，则直接存储在行记录中。
• 如果 LOB 数据大于等于 768 字节，则只在行记录中存储 LOB 数据的前 20 字节（称为 LOB 描述符），以及指向 LOB 数据页面的指针。 实际的 LOB 数据存储在单独的 LOB 数据页面中。

这种方式称为 “溢出存储” (Off-page Storage)。 LOB 描述符中包含了 LOB 数据的长度信息和指向实际 LOB 数据页面的指针。

示例:

假设我们有一张表 articles，包含 id (INT), title (VARCHAR(255)) 和 content (TEXT) 字段，行格式为 Compact。

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT
) ROW_FORMAT=COMPACT;

INSERT INTO articles (id, title, content) VALUES (1, 'My Article', REPEAT('A', 1000));

在这个例子中，content 字段包含 1000 个字符的 ‘A’。 因为大于 768 字节，所以 content 的实际数据会被存储在单独的 LOB 数据页面中，而行记录中只包含 content 的 20 字节 LOB 描述符和指向 LOB 数据页面的指针。

代码示例 (模拟 LOB 描述符):

虽然我们无法直接访问 InnoDB 存储的 LOB 描述符，但可以模拟其结构：

class LOBDescriptor:
    def __init__(self, length, page_pointer):
        self.length = length  # LOB 数据的长度
        self.page_pointer = page_pointer  # 指向 LOB 数据页面的指针

# 假设 LOB 数据的长度为 1000 字节，存储在页面 12345
lob_descriptor = LOBDescriptor(1000, 12345)

print(f"LOB Length: {lob_descriptor.length}")
print(f"Page Pointer: {lob_descriptor.page_pointer}")

Compact 行格式的优点:

• 空间利用率相对较高，适合存储大量数据，特别是当大部分行数据都小于 768 字节时。

Compact 行格式的缺点:

• 对于频繁访问 LOB 数据的场景，性能可能较低，因为需要从单独的 LOB 数据页面读取数据。
• 所有 LOB 数据共享相同的存储方式，无法针对不同的 LOB 数据类型进行优化。

3. Dynamic 行格式

Dynamic 行格式是对 Compact 行格式的改进，主要针对 LOB 数据的存储进行了优化。 它是 MySQL 5.7 及之后版本的默认行格式。

Dynamic 行格式与 Compact 行格式的主要区别在于 LOB 数据的处理方式：

• 如果 LOB 数据小于 40 字节，则直接存储在行记录中。
• 如果 LOB 数据大于等于 40 字节，则只在行记录中存储指向 LOB 数据页面的指针。 实际的 LOB 数据存储在单独的 LOB 数据页面中。

注意：与 Compact 相比，Dynamic 行格式存储 LOB 数据的阈值从 768 字节降低到 40 字节。 这意味着更多 LOB 数据会采用溢出存储的方式。 这样做的好处是减少了行记录的大小，提高了查询性能，特别是对于包含大量 LOB 数据的表。

Dynamic 行格式的存储结构示意图 (简化版):

| Record Header | Variable Length Field Length List | NULL Flags | Column 1 Data | Column 2 Data | ... | LOB Pointer (if LOB data > 40 bytes) |

示例:

继续使用 articles 表，这次我们将其行格式改为 Dynamic。

ALTER TABLE articles ROW_FORMAT=DYNAMIC;

INSERT INTO articles (id, title, content) VALUES (2, 'Another Article', REPEAT('B', 500));

在这个例子中，content 字段包含 500 个字符的 ‘B’。 因为大于 40 字节，所以 content 的实际数据会被存储在单独的 LOB 数据页面中，而行记录中只包含指向 LOB 数据页面的指针。

代码示例 (模拟 LOB 指针):

class LOBPointer:
    def __init__(self, page_pointer):
        self.page_pointer = page_pointer  # 指向 LOB 数据页面的指针

# 假设 LOB 数据存储在页面 54321
lob_pointer = LOBPointer(54321)

print(f"LOB Page Pointer: {lob_pointer.page_pointer}")

Dynamic 行格式的优点:

• 减少了行记录的大小，提高了查询性能，特别是对于包含大量 LOB 数据的表。
• 更适合存储大型 LOB 数据，因为几乎所有的 LOB 数据都会采用溢出存储的方式。

Dynamic 行格式的缺点:

• 对于小于 40 字节的 LOB 数据，仍然会直接存储在行记录中，可能会增加行记录的大小。
• 频繁访问 LOB 数据仍然需要从单独的 LOB 数据页面读取数据，性能可能较低。

4. Compressed 行格式

Compressed 行格式是在 Dynamic 行格式的基础上增加了数据压缩功能。它使用 zlib 算法对行数据进行压缩，从而减少存储空间占用。

Compressed 行格式的存储结构与 Dynamic 行格式类似，只是在存储之前对数据进行了压缩。

Compressed 行格式的优点:

• 显著减少存储空间占用，可以节省大量的磁盘空间。
• 对于 I/O 密集型应用，可以提高性能，因为需要读取的数据量减少了。

Compressed 行格式的缺点:

• 需要额外的 CPU 资源进行数据压缩和解压缩，可能会增加 CPU 负载。
• 压缩比率取决于数据的可压缩性，对于已经高度压缩的数据，压缩效果可能不明显。

使用 Compressed 行格式的注意事项:

• 需要启用 innodb_file_per_table 参数，才能使用 Compressed 行格式。 建议始终启用该参数，因为它能带来更好的性能和管理便利性。
• 可以通过 KEY_BLOCK_SIZE 参数控制压缩页面的大小，默认为 8KB。 调整该参数可能会影响压缩比率和性能。

示例:

SET GLOBAL innodb_file_per_table=ON;  -- 确保启用 innodb_file_per_table

CREATE TABLE compressed_table (
    id INT PRIMARY KEY,
    data TEXT
) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

INSERT INTO compressed_table (id, data) VALUES (1, REPEAT('C', 2048));

代码示例 (模拟数据压缩):

import zlib

# 原始数据
original_data = "This is a string to be compressed." * 100

# 压缩数据
compressed_data = zlib.compress(original_data.encode('utf-8'))

# 解压缩数据
decompressed_data = zlib.decompress(compressed_data).decode('utf-8')

print(f"Original Size: {len(original_data)}")
print(f"Compressed Size: {len(compressed_data)}")
print(f"Decompressed Size: {len(decompressed_data)}")

# 验证数据是否一致
assert original_data == decompressed_data

5. LOB 数据的存储方式总结

行格式	LOB 数据小于阈值	LOB 数据大于等于阈值
Compact	直接存储	存储 20 字节描述符和指针
Dynamic	直接存储	存储指针
Compressed	(压缩后)直接存储	(压缩后)存储指针

各行格式 LOB 阈值:

行格式	LOB 阈值 (字节)
Compact	768
Dynamic	40
Compressed	40

选择哪种行格式取决于具体的应用场景和数据特征。

• Compact: 适合大部分行数据都较小的场景，LOB 数据较少或不频繁访问。
• Dynamic: 适合包含大量 LOB 数据，且 LOB 数据需要频繁访问的场景。
• Compressed: 适合对存储空间要求较高，且 CPU 资源相对充足的场景。

6. 优化建议

• 评估数据特征: 在选择行格式之前，务必评估表的数据特征，包括行的大小、LOB 数据的大小和访问频率等。
• 选择合适的行格式: 根据数据特征选择最合适的行格式，以达到最佳的性能和存储效率。
• 监控性能: 在修改行格式之后，务必监控数据库的性能，确保修改后的行格式能够带来预期的效果。
• 考虑数据压缩: 如果对存储空间要求较高，可以考虑使用 Compressed 行格式，但要注意评估 CPU 负载。
• 合理设计 LOB 数据: 尽量避免存储过大的 LOB 数据，可以考虑将 LOB 数据拆分成多个较小的块进行存储。
• 使用合适的索引: 对于包含 LOB 数据的表，可以使用全文索引或前缀索引来提高查询性能。

7. 案例分析

假设我们有一个存储博客文章的表 blog_posts，包含 id (INT), title (VARCHAR(255)), content (TEXT), author (VARCHAR(100)) 和 created_at (TIMESTAMP) 字段。

场景 1: 大部分文章内容较短，且不经常访问

在这种情况下，Compact 行格式可能是一个不错的选择，因为它可以有效地利用存储空间。

场景 2: 大部分文章内容较长，且经常访问

在这种情况下，Dynamic 行格式更适合，因为它可以减少行记录的大小，提高查询性能。

场景 3: 存储空间有限，需要存储大量的博客文章

在这种情况下，Compressed 行格式可以显著减少存储空间占用，但要注意评估 CPU 负载。

8. 使用场景与选择

使用场景	推荐行格式	备注
大部分行数据较小，LOB 数据少，不频繁访问	Compact	节省空间
包含大量 LOB 数据，LOB 数据需要频繁访问	Dynamic	减少行大小，提高查询效率
需要节省存储空间，可以容忍一定的 CPU 消耗	Compressed	使用 CPU 资源换取存储空间
存在大量的短文本 (小于 40 字节)	Compact	可以避免 Dynamic 将这些短文本存储到单独的页面

希望今天的分享能够帮助大家更好地理解 InnoDB 行格式，并能够在实际应用中做出正确的选择。

简要概括

总而言之，InnoDB 的 Compact、Dynamic 和 Compressed 行格式在 LOB 数据的存储方式上有所不同，选择合适的行格式需要根据具体的应用场景和数据特征进行评估。 理解这些差异有助于优化数据库的性能和存储效率。