MySQL的InnoDB的行格式：Compact、Dynamic与Compressed的差异及LOB数据的存储方式 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

好的，我们开始吧。

MySQL InnoDB 行格式深度解析：Compact、Dynamic、Compressed 与 LOB 数据存储

大家好！今天我们来深入探讨 MySQL InnoDB 存储引擎中的行格式，重点分析 Compact、Dynamic 和 Compressed 这三种行格式的差异，以及它们对 LOB（Large Object）数据存储的影响。理解这些概念对于优化数据库性能至关重要。

1. InnoDB 行格式概览

InnoDB 存储引擎提供了多种行格式，用于组织和存储表中的数据。行格式的选择会影响存储空间利用率、I/O 性能以及对大字段（如 TEXT、BLOB）的处理方式。主要有以下几种：

Redundant: MySQL 5.0 之前使用的旧格式，现在已不推荐使用。
Compact: InnoDB 的默认行格式之一，设计目标是紧凑，节省存储空间。
Dynamic: InnoDB 的另一个默认行格式，与 Compact 类似，但在 LOB 数据处理上有所不同。
Compressed: 在 Dynamic 的基础上增加了压缩功能，进一步节省存储空间。
Row_format=PAGE: mysql 8.0的新特性，数据页内以链表形式组织数据，节省空间。
Row_format=JSON: mysql 8.0的新特性，优化JSON类型的存储和查询。

我们重点关注 Compact、Dynamic 和 Compressed 这三种。

2. Compact 行格式

Compact 行格式的设计目标是减少每行数据占用的空间，从而提高数据密度和 I/O 效率。

行格式结构：

Compact 行格式的结构如下：

记录头（Record Header）列长度列表（Variable Length Field Length） NULL 标志位（NULL Flags）数据（Data）
- 记录头（Record Header）： 占用 5 字节，包含一些控制信息，如：
  - delete_mask: 标记记录是否被删除。
  - next_record: 指向下一条记录的偏移量，用于链接数据页内的记录。
  - 其他标志位，如记录类型等。
- 列长度列表（Variable Length Field Length）： 存储变长列（如 VARCHAR、TEXT、BLOB）的长度。如果所有变长列都不超过 255 字节，则每个长度占用 1 字节；否则，占用 2 字节。
- NULL 标志位（NULL Flags）： 用于标记哪些列的值为 NULL。每个 NULL 值占用 1 位。
- 数据（Data）： 存储实际的列数据。
LOB 数据存储：

Compact 行格式对 LOB 数据的处理方式是：
- 如果 LOB 数据较小（小于某个阈值，通常是 40 字节），则直接存储在数据行中。
- 如果 LOB 数据较大，则只在数据行中存储 LOB 数据的指针（20字节），指向一个单独的 LOB 数据页。这个指针包含了 LOB 数据的页号和偏移量。
优点：
- 存储空间利用率高。
- 适用于大多数场景，特别是数据行较小的情况。
缺点：
- 对于包含大量 LOB 数据的表，可能会导致频繁的 I/O 操作，因为 LOB 数据需要单独读取。

3. Dynamic 行格式

Dynamic 行格式是对 Compact 行格式的改进，主要体现在对 LOB 数据的处理方式上。

行格式结构：

Dynamic 行格式的结构与 Compact 行格式基本相同，也是：

记录头（Record Header）列长度列表（Variable Length Field Length） NULL 标志位（NULL Flags）数据（Data）
LOB 数据存储：

Dynamic 行格式对 LOB 数据的处理方式是：
- 无论 LOB 数据的大小，都只在数据行中存储 LOB 数据的指针（20字节），指向一个单独的 LOB 数据页。
优点：
- 减少了数据页的大小，提高了缓存命中率。
- 避免了因 LOB 数据过大导致的数据页分裂。
缺点：
- 读取 LOB 数据时，总是需要进行额外的 I/O 操作。
- 相比 Compact，如果很多 LOB 数据都很小，可能会略微增加存储空间开销。

4. Compressed 行格式

Compressed 行格式是在 Dynamic 行格式的基础上增加了压缩功能，进一步节省存储空间。

行格式结构：

Compressed 行格式的结构与 Dynamic 行格式相同。
LOB 数据存储：

Compressed 行格式对 LOB 数据的处理方式与 Dynamic 行格式相同，都是只存储 LOB 数据的指针。
压缩方式：

InnoDB 使用 zlib 算法对数据页进行压缩。
优点：
- 极大地节省了存储空间，特别是对于包含大量重复数据的表。
- 减少了 I/O 操作，因为压缩后的数据更小。
缺点：
- 压缩和解压缩需要消耗 CPU 资源。
- 可能会增加 CPU 负载。

5. 行格式选择的策略

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

数据行的大小： 如果数据行较小，且 LOB 数据不多，可以选择 Compact 行格式。
LOB 数据的大小和数量： 如果包含大量 LOB 数据，或者 LOB 数据很大，建议选择 Dynamic 或 Compressed 行格式。
CPU 资源： 如果 CPU 资源充足，可以选择 Compressed 行格式，以节省存储空间和 I/O 操作。
I/O 性能： 如果 I/O 性能是瓶颈，可以尝试 Dynamic 或 Compressed 行格式，以减少数据页的大小和 I/O 操作。

6. 修改行格式

可以使用 ALTER TABLE 语句修改表的行格式。例如：

-- 修改为 Dynamic 行格式
ALTER TABLE my_table ROW_FORMAT=DYNAMIC;

-- 修改为 Compressed 行格式
ALTER TABLE my_table ROW_FORMAT=COMPRESSED;

修改行格式会重建表，因此需要谨慎操作，特别是在生产环境中。

7. 查看行格式

可以使用 SHOW TABLE STATUS 语句查看表的行格式。例如：

SHOW TABLE STATUS LIKE 'my_table'G

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

8. LOB 数据存储的底层实现

InnoDB 使用一种称为 "Uncompressed BLOB Page" 的特殊页类型来存储 LOB 数据。每个 LOB 数据页的大小通常是 16KB（与普通数据页相同）。

LOB 数据页的组织方式：

LOB 数据页以链表的形式组织，每个 LOB 数据页包含指向下一个 LOB 数据页的指针。
LOB 数据的读取：

读取 LOB 数据时，InnoDB 首先从数据行中获取 LOB 数据的指针，然后根据指针找到第一个 LOB 数据页，并按照链表顺序读取后续的 LOB 数据页，直到读取完整个 LOB 数据。
LOB 数据的更新：

更新 LOB 数据时，InnoDB 会根据 LOB 数据的大小和位置，可能需要创建新的 LOB 数据页，或者修改现有的 LOB 数据页。

9. 代码示例

下面是一些代码示例，演示如何使用不同的行格式创建表，以及如何插入和查询 LOB 数据。

-- 创建 Compact 行格式的表
CREATE TABLE compact_table (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    content TEXT
) ROW_FORMAT=COMPACT;

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

-- 创建 Compressed 行格式的表
CREATE TABLE compressed_table (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    content TEXT
) ROW_FORMAT=COMPRESSED;

-- 插入 LOB 数据
INSERT INTO compact_table (id, name, content) VALUES (1, 'Compact', REPEAT('A', 10000));
INSERT INTO dynamic_table (id, name, content) VALUES (1, 'Dynamic', REPEAT('B', 10000));
INSERT INTO compressed_table (id, name, content) VALUES (1, 'Compressed', REPEAT('C', 10000));

-- 查询 LOB 数据
SELECT id, name, LENGTH(content) FROM compact_table;
SELECT id, name, LENGTH(content) FROM dynamic_table;
SELECT id, name, LENGTH(content) FROM compressed_table;

10. 性能测试与分析

为了更直观地了解不同行格式的性能差异，可以进行一些简单的性能测试。例如，可以创建一个包含大量 LOB 数据的表，并分别使用 Compact、Dynamic 和 Compressed 行格式进行插入、查询和更新操作，然后比较它们的执行时间。

在进行性能测试时，需要注意以下几点：

数据量： 确保数据量足够大，以便能够反映出不同行格式的性能差异。
硬件环境： 在相同的硬件环境下进行测试，以避免硬件因素的影响。
缓存： 在测试前，需要清空缓存，以避免缓存的影响。
监控： 在测试过程中，需要监控 CPU、内存和 I/O 等资源的使用情况，以便更好地了解不同行格式的性能特点。

示例测试脚本

-- 测试环境准备
DROP TABLE IF EXISTS test_table;

-- 创建表函数
CREATE PROCEDURE create_test_table(IN row_format_type VARCHAR(20))
BEGIN
    SET @sql_text = CONCAT('CREATE TABLE test_table (id INT PRIMARY KEY, content LONGTEXT) ROW_FORMAT=', row_format_type);
    PREPARE stmt FROM @sql_text;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;

-- 插入数据函数
CREATE PROCEDURE insert_test_data(IN data_size INT)
BEGIN
    SET @i = 0;
    WHILE @i < 1000 DO
        INSERT INTO test_table (id, content) VALUES (@i, REPEAT('A', data_size));
        SET @i = @i + 1;
    END WHILE;
END;

-- 测试流程
CALL create_test_table('COMPACT');
CALL insert_test_data(1024); -- 1KB
SELECT row_format, round(((data_length + index_length) / 1024 / 1024), 2) AS "Size in MB" FROM information_schema.TABLES WHERE table_name = 'test_table';
DROP TABLE IF EXISTS test_table;

CALL create_test_table('DYNAMIC');
CALL insert_test_data(1024); -- 1KB
SELECT row_format, round(((data_length + index_length) / 1024 / 1024), 2) AS "Size in MB" FROM information_schema.TABLES WHERE table_name = 'test_table';
DROP TABLE IF EXISTS test_table;

CALL create_test_table('COMPRESSED');
CALL insert_test_data(1024); -- 1KB
SELECT row_format, round(((data_length + index_length) / 1024 / 1024), 2) AS "Size in MB" FROM information_schema.TABLES WHERE table_name = 'test_table';
DROP TABLE IF EXISTS test_table;

这个脚本创建了一个简单的表，使用三种不同的行格式，并插入数据，最后计算空间占用。请注意，实际测试中需要统计插入、查询的时间，并适当增加数据量，这里只是演示思路。

11. 总结一下主要差异

Compact 节省空间，小LOB直接存；Dynamic 指针全分离，读取总I/O；Compressed 压缩数据页，空间更节省。

发表回复 取消回复

发表回复取消回复