InnoDB行存储和行格式
本节讨论InnoDB的特性，如表压缩、可变长度列值的页外存储和大索引键前缀(innodb_large_prefix)是如何由InnoDB表的行格式控制的。它还讨论了选择正确行格式的注意事项，以及MySQL版本之间行格式的兼容性。
InnoDB行存储概述
行和关联列的存储影响查询和DML操作的性能。由于单个磁盘页面中容纳了更多的行，查询和索引查找可以更快地工作，InnoDB缓冲池中所需的缓存内存更少，为数字和短字符串列写更新值所需的I/O也更少。
每个InnoDB表中的数据被划分为多个页。组成每个表的页面排列在称为b-树索引的树状数据结构中。表数据和二级索引都使用这种类型的结构。表示整个表的b树索引称为聚集索引，它是根据主键列组织的。索引数据结构的节点包含该行中所有列的值(对于聚集索引)或索引列和主键列的值(对于辅助索引)。
变长列是此规则的例外。像BLOB和VARCHAR这样的列太长而不能放在b树页上，它们存储在单独分配的磁盘页上，称为溢出页。我们称这样的列为页外列。这些列的值存储在溢出页的单链表中，每个这样的列都有自己的一个或多个溢出页列表。在某些情况下，长列值的全部或前缀存储在b树中，以避免浪费存储空间并消除读取单独页面的需要。
下面的章节描述了如何配置InnoDB表的行格式来控制如何存储可变长度列的值。行格式配置还决定了表压缩特性和大索引键前缀特性(innodb_large_prefix)的可用性。
指定表的行格式
默认的行格式由innodb_default_row_format定义，默认值为DYNAMIC。当没有显式定义ROW_FORMAT表选项或指定ROW_FORMAT= default时，使用默认行格式。
mysql> show variables like 'innodb_default_row_format'; +---------------------------+---------+ | Variable_name | Value | +---------------------------+---------+ | innodb_default_row_format | dynamic | +---------------------------+---------+ 1 row in set (0.02 sec)
表的行格式可以在CREATE table或ALTER table语句中使用ROW_FORMAT表选项显式定义。例如:
mysql> CREATE TABLE t9 (c1 INT) ROW_FORMAT=DYNAMIC; Query OK, 0 rows affected (0.03 sec)
显式定义的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定义的行格式。
mysql> CREATE TABLE t10 (c1 INT);
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE t11 (c1 INT) ROW_FORMAT=DEFAULT;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'innodb_default_row_format';
+---------------------------+---------+
| Variable_name | Value |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+
1 row in set (0.02 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='undo/t10'\G
*************************** 1. row ***************************
TABLE_ID: 498
NAME: undo/t10
FLAG: 33
N_COLS: 4
SPACE: 500
FILE_FORMAT: Barracuda
ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
1 row in set (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='undo/t11'\G
*************************** 1. row ***************************
TABLE_ID: 499
NAME: undo/t11
FLAG: 33
N_COLS: 4
SPACE: 501
FILE_FORMAT: Barracuda
ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
1 row in set (0.00 sec)
当没有显式指定ROW_FORMAT选项或使用ROW_FORMAT=DEFAULT时，任何重建表的操作都会将表的行格式静默地更改为innodb_default_row_format定义的格式。
表重建操作包括使用ALGORITHM=COPY的ALTER TABLE操作和使用ALGORITHM= INPLACE的ALTER TABLE操作。OPTIMIZE TABLE也是一个表重建操作。
下面的示例演示了一个表重建操作，该操作静默地更改在没有显式定义行格式的情况下创建的表的行格式。
mysql> SELECT @@innodb_default_row_format;
+-----------------------------+
| @@innodb_default_row_format |
+-----------------------------+
| dynamic |
+-----------------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE t12 (c1 INT);
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'undo/t12' \G
*************************** 1. row ***************************
TABLE_ID: 500
NAME: undo/t12
FLAG: 33
N_COLS: 4
SPACE: 502
FILE_FORMAT: Barracuda
ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
1 row in set (0.01 sec)
mysql> SET GLOBAL innodb_default_row_format=COMPACT;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE t12 ADD COLUMN (c2 INT);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'undo/t12' \G
*************************** 1. row ***************************
TABLE_ID: 501
NAME: undo/t12
FLAG: 1
N_COLS: 5
SPACE: 503
FILE_FORMAT: Antelope
ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
1 row in set (0.00 sec)
在将现有表的行格式从REDUNDANT或COMPACT更改为DYNAMIC之前，请考虑以下潜在问题。
.如果启用了innodb_large_prefix配置选项，则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)));
.导入一个没有显式定义行格式的表，如果源服务器上的innodb_default_row_format设置与目标服务器上的设置不同，则会导致模式不匹配错误。
要查看表的行格式，可以发出SHOW TABLE STATUS语句或查询INFORMATION_SCHEMA.TABLES。
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'undo/t12' \G
*************************** 1. row ***************************
TABLE_ID: 501
NAME: undo/t12
FLAG: 1
N_COLS: 5
SPACE: 503
FILE_FORMAT: Antelope
ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
1 row in set (0.00 sec)
mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
Name: big_table
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 3370592
Avg_row_length: 205
Data_length: 694140928
Max_data_length: 0
Index_length: 0
Data_free: 3145728
Auto_increment: 3326977
Create_time: 2023-07-06 18:05:18
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
InnoDB表的行格式决定了它的物理行结构。
DYNAMIC和COMPRESSED行格式
当创建一个ROW_FORMAT=DYNAMIC或ROW_FORMAT=COMPRESSED的表时，InnoDB可以存储长可变长度的列值(对于VARCHAR, VARBINARY, BLOB和TEXT类型)完全溢出页，集群索引记录只包含一个指向溢出页的20字节指针。InnoDB也将长度大于或等于768字节的固定长度字段编码为可变长度字段。例如，如果字符集的最大字节长度大于3，那么CHAR(255)列可以超过768字节，就像utf8mb4一样。
是否有列存储在页外取决于页大小和行的总大小。当行太长时，InnoDB选择最长的列作为页外存储，直到聚集索引记录适合B-tree页。小于或等于40字节的TEXT和BLOB列始终内联存储。
如果适合的话，DYNAMIC行格式保持了在索引节点中存储整行的效率(就像COMPACT和REDUNDANT格式一样)，但是DYNAMIC行格式避免了用长列的大量数据字节填充b树节点的问题。DYNAMIC格式基于这样的思想:如果长数据值的一部分存储在页外，那么将所有值存储在页外通常是最有效的。使用DYNAMIC格式，较短的列可能会保留在B-tree节点中，从而最大限度地减少任何给定行所需的溢出页数。
对于页外存储，COMPRESSED行格式使用了与DYNAMIC行格式类似的内部细节，同时对表和索引数据进行了额外的存储和性能考虑，并使用了更小的页面大小。对于COMPRESSED行格式，KEY_BLOCK_SIZE选项控制在聚集索引中存储多少列数据，以及在溢出页上放置多少列数据。
DYNAMIC和COMPRESSED行格式都支持最多3072字节的索引键前缀。该特性由innodb_large_prefix配置选项控制，默认情况下是启用的。
mysql> select @@innodb_large_prefix; +-----------------------+ | @@innodb_large_prefix | +-----------------------+ | 1 | +-----------------------+ 1 row in set (0.00 sec) mysql> show variables like 'innodb_large_prefix'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | innodb_large_prefix | ON | +---------------------+-------+ 1 row in set (0.01 sec)
使用COMPRESSED行格式的表可以在file-per-table表空间或一般表空间中创建。system表空间不支持COMPRESSED行格式。为了在file-per-table表空间中存储压缩表，innodb_file_per_table必须启用，innodb_file_format必须设置为Barracuda。innodb_file_per_table和innodb_file_format配置选项不适用于一般表空间。一般表空间支持所有的行格式，但需要注意的是，由于物理页大小不同，压缩表和未压缩表不能在同一通用表空间
中共存。
mysql> show variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'innodb_file_format'; +--------------------+-----------+ | Variable_name | Value | +--------------------+-----------+ | innodb_file_format | Barracuda | +--------------------+-----------+ 1 row in set (0.01 sec)
DYNAMIC表可以存储在file-per-table表空间、通用表空间和系统表空间中。要在系统表空间中存储DYNAMIC表，必须使用CREATE TABLE或ALTER TABLE语句并使用选项tablespace [=] innodb_system。innodb_file_per_table和innodb_file_format配置选项不适用于一般表空间，也不适用于使用TABLESPACE [=] innodb_system表选项在system表空间中存储DYNAMIC表。
可以通过禁用innodb_file_per_table并使用常规的CREATE TABLE或ALTER TABLE语句向系统表空间添加一个DYNAMIC表。innodb_file_format设置将被忽略。DYNAMIC表总是使用Barracuda文件格式。
DYNAMIC和COMPRESSED行格式是COMPACT行格式的变体，因此处理CHAR存储的方式与COMPACT行格式相同。
COMPACT and REDUNDANT行格式
早期版本的InnoDB使用一种未命名的文件格式(现在称为Antelope)来处理数据库文件。对于这种文件格式，表定义为ROW_FORMAT=COMPACT或ROW_FORMAT=REDUNDANT。使用这些行格式，InnoDB将可变长度列(VARCHAR, VARBINARY, BLOB和TEXT类型)的前768字节存储在b树节点的索引记录中，其余的存储在溢出页上。InnoDB还将长度大于或等于768字节的固定长度字段编码为可变长度字段，可以存储在页外。例如，如果字符集的最大字节长度大于3，那么CHAR(255)列可以超过768字节，就像utf8mb4一样。
对于Antelope文件格式，如果列的值为768字节或更少，则不需要溢出页，并且可能会节省一些I/O，因为该值位于b树节点中。这对于相对较短的blob很有效，但可能导致B树节点填充数据而不是键值，从而降低了它们的效率。具有许多BLOB列的表可能会导致B树节点的数据太满，而包含的行太少，从而使整个索引的效率低于行更短或列值存储在页外的情况。