MySQL 行格式详解

在 MySQL 中，表的行格式（Row Format）决定了其数据行的物理存储方式。这种存储方式会影响查询和数据操作语句（DML）的性能。当更多数据行能够存储在单个磁盘页上时，查询和索引查找可以运行得更快，缓冲池（Buffer Pool）需要更少的缓存内存，且更新数据所需的输入/输出（I/O）也会相应减少。

每个表中的数据被划分为多个页面，组成表的页面以 B-tree 索引的树状数据结构排列。表数据和二级索引都使用这种结构。代表整个表的 B-tree 索引被称为聚簇索引（Clustered Index），它按主键列进行组织。聚簇索引数据结构的节点包含了行中所有列的值。而二级索引结构的节点则包含索引列和主键列的值。

对于可变长列来说，情况略有不同：过长的可变长列值无法存储在 B-tree 页面上，会被存放到独立分配的磁盘页面，称为溢出页面（Overflow Pages）。这些列被称为离页列（Off-page Columns）。离页列的值存储在溢出页面的单向链表中，每个此类列都有自己的一个或多个溢出页面的链表。根据列长度的不同，所有内容或部分前缀可能会存储在 B-tree 中，以避免浪费存储空间并减少读取单独页面的需求。

InnoDB 存储引擎支持四种行格式：REDUNDANT、COMPACT、DYNAMIC 和 COMPRESSED。

行格式概览

行格式	紧凑存储特性	变长列增强存储	大索引键前缀支持	压缩支持	支持的表空间类型
REDUNDANT	否	否	否	否	system, file-per-table, general
COMPACT	是	否	否	否	system, file-per-table, general
DYNAMIC	是	是	是	否	system, file-per-table, general
COMPRESSED	是	是	是	是	file-per-table, general

以下是关于行格式存储特性的描述以及如何定义和确定表的行格式。

紧凑存储特性

紧凑存储特性（Compact Storage Characteristics）是指 InnoDB 表的行格式能够更有效地利用存储空间，减少每一行数据所占用的字节数。这主要通过优化记录格式和减少元数据的存储需求来实现。InnoDB 引擎做了以下几点改进以节省存储空间：

1. 非空变量长度字段的前缀：仅当非空的可变长度字段（如VARCHAR）需要时才存储长度信息，并且采用最少的字节数来表示长度，例如，较短的字符串长度可以用一个字节表示，而不是固定用两个或更多字节。

2. NULL值的存储优化：COMPACT行格式采用位图来标记列是否为NULL，相比为每个可能为NULL的列分配一个完整的字节，这种方式更为节省空间，特别是在列较多且含有大量NULL值的情况下。

3. 变长字段头信息的紧凑编码：对于VARCHAR和VARBINARY这类变长字段，其长度信息的头部编码更加高效，对于较短的内容使用较少的字节编码长度信息，进一步节约存储空间。

4. 列的有序存储：数据行中的列值按照类型和大小进行排序，这有助于减少数据间的空白区域（内部碎片），使得存储更加紧凑，提高了空间利用率。

变长列增强存储

变长列增强存储是指在 MySQL 的 InnoDB 存储引擎中，对于可变长度数据（如 VARCHAR、VARBINARY、BLOB 和 TEXT 类型）的高效存储方案。这种增强存储主要针对 DYNAMIC 和 COMPRESSED 行格式，它们能够更好地处理较长的可变长度字段。InnoDB 引擎做了以下几点优化变长列存储

1. 内联存储: 对于较短的可变长度列值，它们会被直接存储在 B-tree 索引的页面中，这意味着可以快速访问这些值而不需要额外的磁盘 I/O。
2. 离页存储: 当列值较长且无法在页面中完整存储时，DYNAMIC 和 COMPRESSED 行格式优先将长字段的值完全存放在溢出页面（Overflow Pages）上，索引记录中只保留一个指向该溢出页面的指针。这减少了聚簇索引记录的大小，使得聚簇索引中可以存储更多行，从而提高了空间利用率和查询性能。

大索引键前缀支持

大索引键前缀支持是指数据库能够为索引创建较长的列值前缀。在 MySQL 中，对于 B-tree 索引，有一个最大键长度限制，这意味着索引的每个条目不能超过一定大小。对于早期的行格式（如 REDUNDANT 和 COMPACT），InnoDB 限制索引键前缀的最大长度为 767 字节。

但是，在某些应用场景中，可能需要对较长文本字段进行索引，例如长 VARCHAR、VARBINARY 或 BLOB/TEXT 列。因此，MySQL 引入了 DYNAMIC 行格式和 COMPRESSED 行格式，这两种格式支持更大的索引键前缀。MySQL 8.0之前可以启用 innodb_large_prefix 选项，DYNAMIC 和 COMPRESSED 行格式可以支持最大 3072 字节的索引键前缀。

大索引键前缀支持允许用户创建更宽的索引，使得对长文本数据的搜索更加高效，尤其是当需要使用部分值匹配或范围查询时。还要注意的是，这种支持与字符集有关，因为不同的字符集占用的字节数可能不同，比如 utf8mb4 每个字符最多占用 4 个字节，因此索引键前缀的实际字符数会受到影响。

REDUNDANT 行格式

REDUNDANT 格式与旧版本的 MySQL 兼容。使用 REDUNDANT 行格式的表将可变长列值的前 768 字节存储在 B-tree 节点内的索引记录中，剩余部分存储在溢出页面上。大于或等于 768 字节的固定长度列编码为可变长度列，可能存储在离页位置。例如，在 utf8mb4 编码下，CHAR(255) 列可能超过 768 字节。

如果列值不超过 768 字节，则不使用溢出页面，这可能会减少 I/O 开销，因为该值完全存储在 B-tree 节点中。这适用于较短的 BLOB 列值，但可能会导致 B-tree 节点填满数据而非关键值，降低效率。拥有许多 BLOB 列的表可能导致 B-tree 节点过度充满，包含的行数太少，使整个索引的效率低于行更短或者将列值存储在离页位置的情况。

COMPACT 行格式

与 REDUNDANT 行格式相比，COMPACT 行格式减少了约 20% 的行存储空间，但某些操作的 CPU 使用量会增加。如果负载受缓存命中率和磁盘速度限制，COMPACT 格式可能会更快。如果负载受 CPU 速度限制，COMPACT 格式可能会更慢。

COMPACT 行格式同样将可变长列值的前 768 字节存储在 B-tree 节点内的索引记录中，剩余部分存储在溢出页面上。固定长度大于或等于 768 字节的列编码为可变长度列，可能存储在离页位置。

DYNAMIC 行格式

DYNAMIC 行格式提供与 COMPACT 类似的存储特性，但对长可变长度列增加了增强的存储能力，并支持大型索引键前缀。

当表创建时指定 ROW_FORMAT=DYNAMIC，InnoDB 将长可变长度列值（VARCHAR、VARBINARY、BLOB 和 TEXT 类型）完全存储在离页位置，聚簇索引记录仅包含指向溢出页面的 20 字节指针。固定长度大于或等于 768 字节的字段被编码为可变长度字段。

是否将列存储在离页位置取决于页面大小和行的总大小。如果行太长，最长的列被选为离页存储，直到聚簇索引记录适合 B-tree 页面。小于或等于 40 字节的 TEXT 和 BLOB 列被内联存储。

DYNAMIC 行格式通过尽可能保留较短的列在 B-tree 节点内，减少了所需溢出页面的数量，从而保持了存储效率。

COMPRESSED 行格式

COMPRESSED 行格式提供与 DYNAMIC 行格式相同的存储特性和功能，但增加了对表和索引数据压缩的支持。

定义表的行格式

InnoDB 表的默认行格式由 innodb_default_row_format 变量定义，默认值为 DYNAMIC。未明确定义 ROW_FORMAT 表选项或指定 ROW_FORMAT=DEFAULT 时，将使用默认行格式。

可以使用 CREATE TABLE 或 ALTER TABLE 语句中的 ROW_FORMAT 表选项显式地定义表的行格式。例如：

CREATE TABLE t1 (c1 INT) ROW_FORMAT=DYNAMIC;

显式定义的 ROW_FORMAT 设置会覆盖默认行格式。指定 ROW_FORMAT=DEFAULT 相当于使用隐式默认值。

确定表的行格式

要确定表的行格式，可以使用 SHOW TABLE STATUS：

mysql> SHOW TABLE STATUS LIKE 't1'\G

或者查询 Information Schema 中的 INNODB_TABLES 表：

mysql> SELECT NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';

以上内容简述了 MySQL 中的各种行格式，包括它们的存储特性，如何定义和确定表的行格式，以及各个行格式之间的差异。在数据库设计和优化时，合理选择行格式对于提升系统性能具有重要意义。