MYSQL存储引擎InnoDB（六十五）：InnoDB行格式

科技 09-03 来源：泡泡研究笔记

表的行格式决定了其行的物理存储方式，这反过来又会影响查询和 DML 操作的性能。随着更多的行适合单个磁盘页面，查询和索引查找可以更快地工作，缓冲池中需要更少的高速缓存，并且需要更少的 I/O 来写出更新的值。

每个表中的数据被分成页。构成每个表的页面排列在称为 B 树索引的树数据结构中。表数据和二级索引都使用这种类型的结构。代表整个表的 B 树索引称为聚集索引，它是根据主键列组织的。聚集索引数据结构的节点包含行中所有列的值。二级索引结构的节点包含索引列和主键列的值。

可变长度列是列值存储在 B 树索引节点中的规则的一个例外。太长而无法放入 B 树页面的可变长度列存储在单独分配的磁盘页面上，称为溢出页面。此类列称为离页列。页外列的值存储在溢出页的单链表中，每个这样的列都有自己的一个或多个溢出页的列表。根据列长度，所有或可变长度列值的前缀都存储在 B 树中，以避免浪费存储空间并不得不读取单独的页面。

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

REDUNDANT行格式

REDUNDANT格式提供与旧版本 MySQL 的兼容性。

使用REDUNDANT行格式的表将可变长度列值的前 768 个字节（VARCHAR、 VARBINARY和、BLOB和 TEXT类型）存储在 B 树节点内的索引记录中，其余的存储在溢出页上。大于或等于 768 字节的固定长度列被编码为可变长度列，可以在页外存储。例如，如果字符集的最大字节长度大于 3，则CHAR(255)列可以超过 768 个字节。

如果列的值是 768 字节或更少，则不使用溢出页面，并且可能会节省一些 I/O，因为该值完全存储在 B 树节点中。这适用于相对较短的BLOB列值，但可能会导致 B-tree 节点填充数据而不是键值，从而降低其效率。具有许多BLOB列的表可能会导致 B 树节点变得太满，并且包含的行太少，从而使整个索引的效率低于行较短或列值存储在页外的情况。

REDUNDANT行格式具有以下存储特性：

1、每个索引记录包含一个 6 字节的标头。标头用于将连续记录链接在一起，并用于行级锁定。

2、聚集索引中的记录包含所有用户定义列的字段。此外，还有一个 6 字节的事务 ID 字段和一个 7 字节的滚动指针字段。

3、如果没有为表定义主键，则每个聚集索引记录还包含一个 6 字节的行 ID 字段。

4、每个二级索引记录包含为聚集索引键定义但不在二级索引中的所有主键列。

5、记录包含指向记录的每个字段的指针。如果一条记录的字段总长度小于 128 字节，则指针为 1 字节；否则，两个字节。指针数组称为记录目录。指针指向的区域是记录的数据部分。

6、在内部，固定长度的字符列，例如 CHAR(10)以固定长度格式存储。尾随空格不会从 VARCHAR列中截断。

7、大于或等于 768 字节的固定长度列被编码为可变长度列，可以在页外存储。例如，如果字符集的最大字节长度大于 3，则CHAR(255)列可以超过 768 个字节。

8、NULL值在记录目录中保留一个或两个字节。如果存储在可变长度列中， NULL值会在记录的数据部分保留零字节。对于定长列，该列的定长保留在记录的数据部分。为NULL值保留固定空间允许将列从原位更新 NULL为非NULL值，而不会导致索引页碎片。

COMPACT行格式

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

使用COMPACT行格式的表将可变长度列值的前 768 个字节（VARCHAR、 VARBINARY、 BLOB和TEXT类型）存储在B 树节点内的索引记录中，其余的存储在溢出页上。大于或等于 768 字节的固定长度列被编码为可变长度列，可以在页外存储。例如，如果字符集的最大字节长度大于 3，则CHAR(255)列可以超过 768 个字节。

COMPACT行格式具有以下存储特性：

1、每个索引记录都包含一个 5 字节的标头，其前面可能是一个可变长度的标头。标头用于将连续记录链接在一起，并用于行级锁定。

2、记录头的可变长度部分包含一个用于指示NULL列的位向量。如果索引中可以为NULL的列数为N，则位向量占用 CEILING(N/8)字节。（例如，如果有 9 到 16 列可以是NULL，则位向量使用两个字节。）除了此向量中的位之外，NULL不占用空间。标题的可变长度部分还包含可变长度列的长度。每个长度占用一个或两个字节，具体取决于列的最大长度。如果索引中的所有列都是NOT NULL并且具有固定长度，记录头没有可变长度部分。

3、对于每个非NULL可变长度字段，记录头包含一或两个字节的列长度。仅当列的一部分在溢出页中外部存储或最大长度超过 255 字节且实际长度超过 127 字节时才需要两个字节。对于外部存储的列，2 字节长度表示内部存储部分的长度加上指向外部存储部分的 20 字节指针。内部部分为768字节，所以长度为768+20。20 字节指针存储列的真实长度。

4、记录头后面是非NULL列的数据内容。

5、聚集索引中的记录包含所有用户定义列的字段。此外，还有一个 6 字节的事务 ID 字段和一个 7 字节的滚动指针字段。

6、如果没有为表定义主键，则每个聚集索引记录还包含一个 6 字节的行 ID 字段。

7、每个二级索引记录包含为聚集索引键定义但不在二级索引中的所有主键列。如果任何主键列是可变长度的，则每个二级索引的记录头都有一个可变长度部分来记录它们的长度，即使二级索引是在固定长度列上定义的。

8、在内部，对于非可变长度字符集，固定长度字符列，例如以 CHAR(10)固定长度格式存储。

尾随空格不会从 VARCHAR列中截断。

9、在内部，对于可变长度字符集， InnoDB尝试通过修剪尾随空格来存储CHAR(N)字节。如果CHAR(N)列值的字节长度超过字节N，则将尾随空格修剪为CHAR(N)列值字节长度的最小值N。列的最大长度是最大字符字节长度 × N。

COMPACT行格式为CHAR(N)保留最少的字节N。在许多情况下，保留最小空间N可以使列更新就地完成，而不会导致索引页碎片。相比之下，当使用REDUNDANT行格式时，CHAR(N) 列占用最大字符字节长度 × N。

大于或等于 768 字节的固定长度列被编码为可变长度字段，可以在页外存储。例如，如果字符集的最大字节长度大于 3，则CHAR(255)列可以超过 768 个字节。

DYNAMIC行格式

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

当使用ROW_FORMAT=DYNAMIC创建表时，InnoDB 可以完全离页存储长可变长度列值（对于 VARCHAR、 VARBINARY、BLOB和TEXT类型），聚集索引记录仅包含指向溢出页的 20 字节指针。大于或等于 768 字节的固定长度字段被编码为可变长度字段。例如，如果字符集的最大字节长度大于 3，则CHAR(255)列可以超过 768 个字节。

列是否存储在页外取决于页面大小和行的总大小。当一行太长时，选择最长的列进行页外存储，直到聚集索引记录适合B 树页面。小于或等于 40 字节的TEXT、BLOB列存储在行中。

如果合适的话，DYNAMIC行格式保持了将整行存储在索引节点中的效率（就像 COMPACT和REDUNDANT 格式一样），但是DYNAMIC行格式避免了用长列的大量数据字节填充B树节点的问题。DYNAMIC行格式基于这样一种思想，即如果长数据值的一部分存储在页外，则将整个值存储在页外通常是最有效的。使用DYNAMIC格式，较短的列可能会保留在 B 树节点中，从而最大限度地减少给定行所需的溢出页数。

DYNAMIC行格式支持最多 3072 字节的索引键前缀。

使用DYNAMIC行格式的表可以存储在系统表空间、独立表空间和通用表空间中。要将DYNAMIC表存储在系统表空间中，请禁用 innodb_file_per_table并使用常规CREATE TABLE或ALTER TABLE语句，或者将TABLESPACE [=] innodb_system选项与CREATE TABLE或ALTER TABLE一起使用。innodb_file_per_table变量不适用于通用表空间，也不适用于使用TABLESPACE [=] innodb_system选项将DYNAMIC表存储在系统表空间中。

DYNAMIC行格式是COMPACT行格式的一种变体。

COMPRESSED行格式

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

COMPRESSED行格式对页外存储使用与DYNAMIC行格式类似的内部细节，但表和索引数据被压缩并使用更小的页面大小需要额外的存储和性能考虑。使用COMPRESSED行格式，该KEY_BLOCK_SIZE选项控制在聚集索引中存储多少列数据，以及在溢出页上放置多少。

COMPRESSED行格式支持最多 3072 字节的索引键前缀。

可以在独立表空间或通用表空间中创建使用COMPRESSED行格式的表。系统表空间不支持 COMPRESSED行格式。要将 COMPRESSED表存储在独立表空间中，必须启用innodb_file_per_table变量。 innodb_file_per_table变量不适用于通用表空间。通用表空间支持所有行格式，但需要注意的是，由于物理页大小不同，压缩表和未压缩表不能在同一个通用表空间中共存。

COMPRESSED行格式是COMPACT行格式的一种变体。

定义表格的行格式

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等同于使用隐式默认值。

innodb_default_row_format 变量可以动态设置：

mysql> SET GLOBAL innodb_default_row_format=DYNAMIC;

有效innodb_default_row_format 选项包括DYNAMIC、 COMPACT和REDUNDANT。不支持在系统表空间中使用的 COMPRESSED行格式不能定义为默认值。它只能在 CREATE TABLE或 ALTER TABLE语句中显式指定。尝试将 innodb_default_row_format 变量设置为COMPRESSED会返回错误：

mysql> SET GLOBAL innodb_default_row_format=COMPRESSED;
ERROR 1231 (42000): Variable 'innodb_default_row_format'
can't be set to the value of 'COMPRESSED'

当未明确指定ROW_FORMAT选项或使用ROW_FORMAT=DEFAULT时，新创建的表使用innodb_default_row_format 变量定义的行格式。例如，以下CREATE TABLE语句使用 innodb_default_row_format变量定义的行格式。

CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT) ROW_FORMAT=DEFAULT;

如果ROW_FORMAT未明确指定选项，或使用ROW_FORMAT=DEFAULT时，重建表的操作会静默地将表的行格式更改为 innodb_default_row_format 变量定义的格式。

表重建操作包括使用ALTER TABLE （ALGORITHM=COPY或 ALGORITHM=INPLACE）需要重建表的操作。OPTIMIZE TABLE也是一个表重建操作。

下面的示例演示了一个表重建操作，该操作以静默方式更改在没有明确定义的行格式的情况下创建的表的行格式。

mysql> SELECT @@innodb_default_row_format;
+-----------------------------------+
| @@innodb_default_row_format |
+-----------------------------------+
| dynamic                                       |
+-----------------------------------+

mysql> CREATE TABLE t1 (c1 INT);

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE 'test/t1' \G
*************************** 1. row ***************************
TABLE_ID: 54
NAME: test/t1
FLAG: 33
N_COLS: 4
SPACE: 35
ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single

mysql> SET GLOBAL innodb_default_row_format=COMPACT;

mysql> ALTER TABLE t1 ADD COLUMN (c2 INT);

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE 'test/t1' \G
*************************** 1. row ***************************
TABLE_ID: 55
NAME: test/t1
FLAG: 1
N_COLS: 5
SPACE: 36
ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single

在将现有表的行格式从REDUNDANT或 COMPACT更改为DYNAMIC之前，请考虑以下潜在问题。

1、REDUNDANT和 COMPACT行格式支持最大 767 字节的索引键前缀长度，而DYNAMIC和COMPRESSED 行格式支持 3072 字节的索引键前缀长度。在复制环境中，如果 innodb_default_row_format 变量在源上设置为DYNAMIC，在副本上设置为COMPACT，则以下 DDL 语句（未显式定义行格式）在源上成功，但在副本上失败：

CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 VARCHAR(5000), KEY i1(c2(3070)));

2、如果源服务器上的innodb_default_row_format 设置与目标服务器上的设置不同，则导入未明确定义行格式的表会导致架构不匹配错误。

确定表的行格式

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

mysql> SHOW TABLE STATUS IN test1\G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 1
Create_time: 2016-09-14 16:29:38
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:

或者，查询 INFORMATION_SCHEMA.INNODB_TABLES 表：

mysql> SELECT NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test1/t1';
+----------+-----------------+
| NAME      | ROW_FORMAT |
+----------+-----------------+
| test1/t1    | Dynamic           |
+----------+-----------------+