什么是行格式（Compact / Redundant / Dynamic / Compressed）？长字段如何溢出存储？

考察要点

这道题面试官想看你是否深刻理解 InnoDB 的物理存储模型，即一条逻辑上的 SQL 记录是如何在物理的磁盘页上组织的。考察点主要包括：

1. 数据布局知识：你是否清楚不同行格式（特别是 Compact 和 Dynamic）在页内如何组织字段、处理 NULL 值和变长字段。
2. 性能影响分析：你是否明白行格式的选择，特别是长字段的溢出（Off-page）存储机制，如何直接影响 I/O 效率、缓存命中率和整体查询性能。
3. 版本演进和选型能力：你是否了解行格式随 MySQL 版本（5.7 vs 8.0）的演进，并能根据业务场景（如大字段存储、高并发读写）做出合理的技术选型。

核心概念

InnoDB 以页（Page）为单位管理磁盘空间，默认大小为 16KB。行格式（Row Format）定义了一行数据在数据页（Data Page）内部的物理存储方式。MySQL 8.0 默认行格式为 Dynamic。

1. 行格式的演进

2. Compact 行格式详解

Compact 是理解现代行格式的基础。其结构（不含事务ID和回滚指针等隐藏列）如下：

 1+----------------------+-----------------+------------------+----------------+
 2| 变长字段长度列表      | NULL 标志位      | 记录头信息 (5字节) |  列1数据 | 列2数据 | ... |
 3+----------------------+-----------------+------------------+----------------+
 4  (逆序存储)             (1 byte per 8 cols)

• 变长字段长度列表：对于 VARCHAR, VARBINARY, TEXT, BLOB 等变长类型，逆序存储其实际长度。若列长度小于等于255字节，用1字节表示；大于255字节，用2字节表示。定长字段（如 INT, CHAR）不在此处存储长度。
• NULL 标志位：将允许为 NULL 的列用一个比特位（bit）表示。1表示 NULL，0表示非 NULL。每 8 个可为 NULL 的列占用 1 个字节。如果表中没有允许为 NULL 的列，则此部分不存在。
• 记录头信息：固定 5 字节，包含记录类型、下一条记录的偏移量等元信息。

3. 长字段溢出存储（Row Overflow）

当单行数据太大无法完全放入一个 16KB 的页时，就会发生行溢出。InnoDB 规定，一个页至少要能存放两条记录，否则会报错。

数据示例： 假设有一张表 t_article，在 Compact 和 Dynamic 格式下，其长字段 content 的存储方式不同。

 1CREATE TABLE t_article (
 2    id INT PRIMARY KEY,
 3    title VARCHAR(255),
 4    content TEXT -- 假设 content 长度为 10KB
 5) ENGINE=InnoDB ROW_FORMAT=COMPACT; -- 或 DYNAMIC

Compact 的溢出机制

• 阈值：当单个变长列（如 TEXT, BLOB, VARCHAR）的存储长度超过 768 字节时，并且该列是该行中被选择用于页外存储的列。
• 行为：InnoDB 会在 B+ 树的叶子节点（数据页）中，只存储该列数据的前 768 字节，并在其后跟一个 20 字节的指针。这个指针指向一个或多个溢出页（Overflow Page），剩余的数据存放在这些溢出页中。

图示 (Compact)：

 1graph TD
 2    subgraph B+ Tree Leaf Page (Data Page)
 3        Row1[行记录1: id, title, content(前768字节) + 20字节指针]
 4    end
 5    Row1 --> OverflowPage1[溢出页1: content 剩余数据...]
 6    OverflowPage1 --> OverflowPage2[溢出页2: ...]

• 缺点：即使数据大部分在页外，数据页本身仍被这 768 字节的前缀数据占据，导致一个数据页能存放的行数减少。对于范围扫描等操作，这会增加需要扫描的物理页数，降低 I/O 效率和缓存命中率。

Dynamic 的溢出机制

• 行为：当发生行溢出时，Dynamic 格式采取“全列下放”策略。它不会在数据页中存储任何数据前缀，而是直接将整个列的数据存入溢出页，仅在数据页的行记录中保留一个 20 字节的指针。

图示 (Dynamic)：

 1graph TD
 2    subgraph B+ Tree Leaf Page (Data Page)
 3        Row1[行记录1: id, title, 20字节指针]
 4    end
 5    Row1 --> OverflowPage1[溢出页1: content 完整数据...]
 6    OverflowPage1 --> OverflowPage2[溢出页2: ...]

• 优点：数据页变得非常“瘦”，只包含主键、短字段和指向大字段的指针。这使得每个数据页可以容纳更多的行记录，极大地提高了 B+ 树的紧凑性。对于扫描操作，同样的内存可以缓存更多的行，I/O 效率更高。

4. Compressed 行格式

Compressed 格式在 Dynamic 的基础上，对存储在页中的数据使用 zlib 算法进行压缩。

• 优点：显著节省磁盘空间，对于 I/O 密集型且数据可压缩率高的场景（如大量文本、JSON），可以减少磁盘 I/O。
• 缺点：增加了 CPU 开销，因为每次读写页面都需要进行解压和压缩。需要通过 KEY_BLOCK_SIZE 参数权衡压缩率和性能。

常见低分回答

1. 混淆概念：将行格式与字符集（Character Set）或文件格式（File Format，如 Antelope/Barracuda）混为一谈。虽然它们相关（字符集影响字节长度，Barracuda 文件格式支持 Dynamic/Compressed），但行格式是更底层的物理布局定义。
2. 对溢出机制描述不清：只说“大字段会存到别的地方”，但说不出 Compact 和 Dynamic 的核心区别（768 字节前缀 vs 纯指针），也无法解释这种区别带来的性能影响。
3. 认为 VARCHAR(255) 就不会溢出：错误。行溢出不仅取决于单个列的长度，还取决于整行的总长度。如果一行中包含多个较宽的 VARCHAR 列，即使每个都小于 768 字节，当总长度超过页的存储极限时，InnoDB 仍然会选择其中最长的列进行页外存储。
4. 知识停留在老版本：仍然声称 Compact 是默认行格式，而没有提及 MySQL 8.0 已经将 Dynamic 作为默认。这会给面试官留下知识未更新的印象。
5. 对 Compressed 格式的理解片面：只说它能“省空间”，但没有提及它带来的 CPU 开销以及其适用场景（I/O 瓶颈而非 CPU 瓶颈）。

工程实践与优化

• 默认即最优：在 MySQL 8.0+ 中，通常保持默认的 ROW_FORMAT=DYNAMIC 就是最佳实践。它在处理各种数据类型（特别是包含 TEXT/BLOB/JSON 或长 VARCHAR 的表）时表现均衡且高效。
• 何时选择 Compressed：当存储成本是首要考虑因素，且服务器 CPU 资源充足时，可以考虑使用。典型场景是日志归档表、历史数据表。务必进行充分的基准测试，确认 CPU 开销不会成为新的瓶颈。
• 如何查看和修改行格式：
  • 查看：
    sql

    ⧉⛶

     1SHOW TABLE STATUS LIKE 'your_table_name'\G

    在输出中找到 Row_format 字段。
  • 修改：
    sql

    ⧉⛶

     1ALTER TABLE your_table_name ROW_FORMAT=DYNAMIC;

    注意：此操作会重建表（ALGORITHM=COPY），在生产环境的大表上执行会锁表并消耗大量 I/O 和时间，需要使用 pt-online-schema-change 或类似的工具。
• 索引与行溢出：如果一个查询可以被覆盖索引（Covering Index）满足，它只需扫描二级索引页。但如果查询需要访问一个已溢出的列，即使该列不在 WHERE 条件中，也必须回表到主键索引，并可能进一步读取溢出页，导致额外的随机 I/O，使覆盖索引的优势大打折扣。Dynamic 格式通过保持主键索引页的紧凑，间接提升了整体性能。

常见追问与延伸题目

1. 追问：VARCHAR(255)、VARCHAR(5000) 和 TEXT 在存储上有什么本质区别？

   • 回答要点：
     • 存储限制：VARCHAR 有最大长度限制（65535 字节，受字符集影响），而 TEXT 类型则更大（64KB）。
     • 存储方式：在行内存储时，VARCHAR 根据实际内容分配空间，并有 1-2 字节的长度前缀。TEXT 在行内只存一个指针（即使内容很短）。
     • 溢出行为：当数据非常长时，它们的溢出机制都遵循当前表的行格式规定（Compact 或 Dynamic）。从物理溢出角度看，一个足够长的 VARCHAR 和一个 TEXT 的处理方式是相同的。
     • 索引限制：VARCHAR 可以作为完整索引键（有长度限制，如 767 字节或 3072 字节），而 TEXT/BLOB 只能创建前缀索引。这是设计表结构时的一个关键差异。

2. 追问：行格式的选择如何影响覆盖索引的效率？

   • 回答要点：
     • 覆盖索引的本质是查询所需的所有列都存在于二级索引中，避免了回表查询主键索引。
     • 如果一个 TEXT 或溢出的 VARCHAR 列被包含在 SELECT 列表中（即使它不是查询条件），那么覆盖索引就无法生效，因为二级索引中不存储这些溢出列的完整数据。
     • Dynamic 格式通过将大字段完全移出主键索引的数据页，使得主键索引页更紧凑，能缓存更多行。这虽然不直接影响“是否能用上”覆盖索引，但它提升了回表（如果无法避免）的效率，因为找到主键索引对应页的概率更高，且该页更有可能已在缓冲池中。

3. 追问：如果我的表里全是 INT 和 DATE 这种定长的小字段，行格式的选择还重要吗？

   • 回答要点：
     • 不重要。对于只包含定长字段或总长度远小于页大小限制的短变长字段的表，行溢出机制根本不会被触发。
     • 在这种情况下，Compact 和 Dynamic 的性能差异微乎其微。它们在记录头和元信息上存在细微差别，但对整体空间和性能的影响可以忽略不计。
     • 尽管如此，在 MySQL 8.0+ 环境中，遵循默认使用 Dynamic 依然是最佳实践，因为它提供了对未来表结构变更（如增加长字段）的最好兼容性和性能保障。