14.11 InnoDB 行格式
table 的行格式决定了其行的物理存储方式,进而会影响查询和 DML 操作的性能。随着更多的行适合单个磁盘页面,查询和索引查找可以更快地工作,缓冲池中需要的缓存内存更少,并且写出更新值所需的 I/O 更少。
每个 table 中的数据分为几页。构成每个 table 的页面以称为 B 树索引的树数据结构排列。table 数据和二级索引都使用这种类型的结构。table 示整个 table 的 B 树索引称为聚 Cluster 索引,该聚 Cluster 索引是根据主键列进行组织的。聚集索引数据结构的节点包含该行中所有列的值。次要索引结构的节点包含索引列和主键列的值。
可变长度列是将列值存储在 B 树索引节点中的规则的 exception。太长而无法容纳在 B 树页面上的变长列存储在单独分配的磁盘页面上,这些页面称为溢出页面。这些列称为页外列。页面外列的值存储在溢出页面的单链接列 table 中,每个这样的列都有其自己的一个或多个溢出页面的列 table。根据列的长度,所有或可变长度列值的前缀都存储在 B 树中,以避免浪费存储空间并不得不读取单独的页面。
InnoDB
存储引擎支持四种行格式:REDUNDANT
,COMPACT
,DYNAMIC
和COMPRESSED
。
table14.9 InnoDB 行格式概述
Row Format | 紧凑的存储特性 | 增强的可变长度色谱柱存储 | 大索引键前缀支持 | Compression Support | 支持的 table 空间类型 | 所需文件格式 |
---|---|---|---|---|---|---|
REDUNDANT | No | No | No | No | 系统,每 table 文件,常规 | 羚羊或梭子鱼 |
COMPACT | Yes | No | No | No | 系统,每 table 文件,常规 | 羚羊或梭子鱼 |
DYNAMIC | Yes | Yes | Yes | No | 系统,每 table 文件,常规 | Barracuda |
COMPRESSED | Yes | Yes | Yes | Yes | file-per-table, general | Barracuda |
后面的主题描述行格式存储 Feature 以及如何定义和确定 table 的行格式。
冗余行格式
REDUNDANT
格式与旧版本的 MySQL 兼容。
InnoDB
文件格式(Antelope
和Barracuda
)都支持REDUNDANT
行格式。有关更多信息,请参见第 14.10 节“ InnoDB 文件格式 Management”。
使用REDUNDANT
行格式的 table 将前 768 个字节的变长列值(VARCHAR,VARBINARY以及BLOB和TEXT类型)存储在 B 树节点的索引 Logging,其余的存储在溢出页上。大于或等于 768 字节的固定长度列被编码为可变长度列,可以在页面外存储。例如,如果字符集的最大字节长度大于 3,则CHAR(255)
列可以超过 768 字节,就像utf8mb4
一样。
如果列的值小于或等于 768 个字节,则不使用溢出页,并且由于该值完全存储在 B 树节点中,因此可以节省一些 I/O。这对于较短的BLOB
列值效果很好,但可能导致 B 树节点填充数据而不是键值,从而降低了效率。具有许多BLOB
列的 table 可能会导致 B 树节点变得太满,并且包含的行太少,这使得整个索引的效率低于行较短或列值存储在页面外的情况。
冗余行格式存储 Feature
REDUNDANT
行格式具有以下存储 Feature:
-
每个索引记录包含一个 6 字节的 Headers。Headers 用于将连续的记录链接在一起,并用于行级锁定。
-
聚集索引中的记录包含所有用户定义列的字段。此外,还有一个 6 字节的 TransactionID 字段和一个 7 字节的滚动指针字段。
-
如果没有为 table 定义主键,则每个聚集索引记录还包含一个 6 字节的行 ID 字段。
-
每个二级索引记录包含不在二级索引中的,为聚 Cluster 索引键定义的所有主键列。
-
记录包含指向记录每个字段的指针。如果 Logging 字段的总长度小于 128 个字节,则指针为 1 个字节;否则为 0.否则为两个字节。指针数组称为记录目录。指针指向的区域是记录的数据部分。
-
大于或等于 768 字节的固定长度列被编码为可变长度列,可以在页面外存储。例如,如果字符集的最大字节长度大于 3,则
CHAR(255)
列可以超过 768 个字节,就像utf8mb4
一样。 -
SQL
NULL
值在记录目录中保留一个或两个字节。如果存储在可变长度列中,则 SQLNULL
值在记录的数据部分中保留零字节。对于固定长度的列,该列的固定长度保留在记录的数据部分中。保留NULL
值的固定空间允许将列从NULL
值更新为非NULL
值,而不会导致索引页碎片。
COMPACT 行格式
与REDUNDANT
行格式相比,COMPACT
行格式将行存储空间减少了约 20%,但代价是增加了某些操作的 CPU 使用率。如果您的工作量是典型的工作,受缓存命中率和磁盘速度的限制,那么COMPACT
格式可能会更快。如果工作量受 CPU 速度限制,则紧凑格式可能会变慢。
InnoDB
文件格式(Antelope
和Barracuda
)都支持COMPACT
行格式。有关更多信息,请参见第 14.10 节“ InnoDB 文件格式 Management”。
使用COMPACT
行格式的 table 将前 768 个字节的变长列值(VARCHAR,VARBINARY以及BLOB和TEXT类型)存储在B-tree节点内的索引 Logging,其余的存储在溢出页面上。大于或等于 768 字节的固定长度列被编码为可变长度列,可以在页面外存储。例如,如果字符集的最大字节长度大于 3,则CHAR(255)
列可以超过 768 个字节,就像utf8mb4
一样。
如果列的值小于或等于 768 个字节,则不使用溢出页,并且由于该值完全存储在 B 树节点中,因此可以节省一些 I/O。这对于较短的BLOB
列值效果很好,但可能导致 B 树节点填充数据而不是键值,从而降低了效率。具有许多BLOB
列的 table 可能会导致 B 树节点变得太满,并且包含的行太少,这使得整个索引的效率低于行较短或列值存储在页面外的情况。
COMPACT 行格式存储 Feature
COMPACT
行格式具有以下存储 Feature:
-
每个索引记录都包含一个 5 字节的 Headers,该 Headers 之前可以有一个可变长度的 Headers。Headers 用于将连续的记录链接在一起,并用于行级锁定。
-
记录头的可变长度部分包含用于指示
NULL
列的位向量。如果索引中可以是NULL
的列数是*N
*,则位向量占用CEILING(N/8)
个字节。 (例如,如果有 9 到 16 列可以是NULL
,则位向量使用两个字节.)NULL
的列除了占用此向量中的位之外,不占用空间。Headers 的可变长度部分还包含可变长度列的长度。每个长度占用一个或两个字节,具体取决于列的最大长度。如果索引中的所有列均为NOT NULL
且具有固定长度,则记录头不包含可变长度部分。 -
对于每个非
NULL
可变长度字段,记录头均以一或两个字节包含列的长度。仅当列的一部分存储在外部溢出页面中或最大长度超过 255 个字节且实际长度超过 127 个字节时,才需要两个字节。对于外部存储的列,2 字节的长度 table 示内部存储部分的长度加上指向外部存储部分的 20 字节指针。内部部分为 768 字节,因此长度为 768+20。20 字节的指针存储列的真实长度。 -
记录头后跟非
NULL
列的数据内容。 -
聚集索引中的记录包含所有用户定义列的字段。此外,还有一个 6 字节的 TransactionID 字段和一个 7 字节的滚动指针字段。
-
如果没有为 table 定义主键,则每个聚集索引记录还包含一个 6 字节的行 ID 字段。
-
每个二级索引记录包含不在二级索引中的,为聚 Cluster 索引键定义的所有主键列。如果任何主键列的长度是可变的,则即使在固定长度的列上定义了辅助索引,每个辅助索引的记录头都具有可变长度的部分来记录其长度。
-
在内部,对于非可变长度字符集,固定长度字符列(例如CHAR(10))以固定长度格式存储。
尾随空格不会从VARCHAR列中截断。
- 在内部,对于诸如
utf8mb3
和utf8mb4
之类的可变长度字符集,InnoDB
试图通过修剪尾随空格来将CHAR(N)存储在*N
字节中。如果CHAR(N)列值的字节长度超过N
字节,则尾随空格会被修剪为该列值字节长度的最小值。 CHAR(N)列的最大长度是最大字符字节长度×N
*。
为CHAR(N)保留至少* N
个字节。在许多情况下,保留最小空间 N
可使列更新就地完成而不会导致索引页碎片。相比之下,使用REDUNDANT
行格式时,CHAR(N)列占据最大字符字节长度× N
*。
大于或等于 768 字节的固定长度列被编码为可变长度字段,可以在页面外存储。例如,如果字符集的最大字节长度大于 3,则CHAR(255)
列可以超过 768 个字节,就像utf8mb4
一样。
动态行格式
DYNAMIC
行格式提供与COMPACT
行格式相同的存储特性,但是增加了对长可变长度列的增强的存储功能,并支持大索引键前缀。
梭子鱼文件格式支持DYNAMIC
行格式。参见第 14.10 节“ InnoDB 文件格式 Management”。
使用ROW_FORMAT=DYNAMIC
创建 table 时,InnoDB
可以完全不在页面上存储长可变长度列值(对于VARCHAR,VARBINARY以及BLOB和TEXT类型),而聚集索引记录仅包含指向溢出页面的 20 字节指针。大于或等于 768 字节的固定长度字段被编码为可变长度字段。例如,如果字符集的最大字节长度大于 3,则CHAR(255)
列可以超过 768 个字节,就像utf8mb4
一样。
列是否存储在页面外取决于页面大小和行的总大小。当一行太长时,将选择最长的列进行页外存储,直到聚集的索引记录适合B-tree页。小于或等于 40 个字节的TEXT和BLOB列存储在行中。
DYNAMIC
行格式保持了将整个行存储在索引节点中的效率(如COMPACT
和REDUNDANT
格式一样),但是DYNAMIC
行格式避免了用大量数据字节填充 B 树节点的问题。长列。 DYNAMIC
行格式基于以下思想:如果将长数据值的一部分存储在页外,则通常最有效地将整个值存储在页外。使用DYNAMIC
格式,较短的列可能会保留在 B 树节点中,从而最大程度地减少了给定行所需的溢出页数。
DYNAMIC
行格式支持最多 3072 个字节的索引键前缀。此功能由innodb_large_prefix变量控制,该变量默认情况下处于启用状态。有关更多信息,请参见innodb_large_prefix变量说明。
使用DYNAMIC
行格式的 table 可以存储在系统 table 空间,每 table 文件 table 空间和常规 table 空间中。要将DYNAMIC
table 存储在系统 table 空间中,请禁用innodb_file_per_table并使用常规CREATE TABLE
或ALTER TABLE
语句,或者将TABLESPACE [=] innodb_system
table 选项与CREATE TABLE
或ALTER TABLE
一起使用。 innodb_file_per_table和innodb_file_format变量不适用于常规 table 空间,使用TABLESPACE [=] innodb_system
table 选项在系统 table 空间中存储DYNAMIC
table 时,它们也不适用。
动态行格式存储 Feature
DYNAMIC
行格式是COMPACT
行格式的变体。有关存储特性,请参见COMPACT 行格式存储 Feature。
COMPRESSED 行格式
COMPRESSED
行格式提供的存储特性和功能与DYNAMIC
行格式相同,但是增加了对 table 和索引数据压缩的支持。
梭子鱼文件格式支持COMPRESSED
行格式。参见第 14.10 节“ InnoDB 文件格式 Management”。
COMPRESSED
行格式使用了与DYNAMIC
行格式类似的内部详细信息进行页外存储,并从 table 和索引数据中压缩并使用较小的页面大小来增加存储和性能方面的考虑。使用COMPRESSED
行格式时,KEY_BLOCK_SIZE
选项可控制在聚 Cluster 索引中存储多少列数据,以及在溢出页面上放置多少列数据。有关COMPRESSED
行格式的更多信息,请参见第 14.9 节“ InnoDBtable 和页面压缩”。
COMPRESSED
行格式支持最多 3072 个字节的索引键前缀。此功能由innodb_large_prefix变量控制,该变量默认情况下处于启用状态。有关更多信息,请参见innodb_large_prefix变量说明。
可以在每 table 文件 table 空间或常规 table 空间中创建使用COMPRESSED
行格式的 table。系统 table 空间不支持COMPRESSED
行格式。要将COMPRESSED
table 存储在每 table 文件 table 空间中,必须启用innodb_file_per_table变量,并且必须将innodb_file_format设置为Barracuda
。 innodb_file_per_table和innodb_file_format变量不适用于常规 table 空间。常规 table 空间支持所有行格式,但要注意的是,由于物理页大小不同,压缩 table 和未压缩 table 不能在同一常规 table 空间中共存。有关更多信息,请参见第 14.6.3.3 节“常规 table 空间”。
压缩的行格式存储 Feature
COMPRESSED
行格式是COMPACT
行格式的变体。有关存储特性,请参见COMPACT 行格式存储 Feature。
定义 table 格的行格式
InnoDB
table 的默认行格式由innodb_default_row_format变量定义,该变量的默认值为DYNAMIC
。当未明确定义ROW_FORMAT
table 选项或指定ROW_FORMAT=DEFAULT
时,将使用默认行格式。
可以使用CREATE TABLE或ALTER TABLE语句中的ROW_FORMAT
table 选项显式定义 table 的行格式。例如:
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
行格式定义为系统 table 空间中的默认格式。它只能在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
时,新创建的 table 将使用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
时,用于重建 table 的操作会将 table 的行格式更改为innodb_default_row_format变量定义的格式。
table 重建操作包括ALTER TABLE使用ALGORITHM=COPY
或ALGORITHM=INPLACE
的操作,其中需要重建 table。有关更多信息,请参见第 14.13.1 节“在线 DDL 操作”。 OPTIMIZE TABLE也是 table 重建操作。
下面的示例演示一个 table 重建操作,该操作以静默方式更改没有显式定义的行格式的情况下创建的 table 的行格式。
mysql> SELECT @@innodb_default_row_format;
+-----------------------------+
| @@innodb_default_row_format |
+-----------------------------+
| dynamic |
+-----------------------------+
mysql> CREATE TABLE t1 (c1 INT);
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1' \G
*************************** 1. row ***************************
TABLE_ID: 54
NAME: test/t1
FLAG: 33
N_COLS: 4
SPACE: 35
FILE_FORMAT: Barracuda
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_SYS_TABLES WHERE NAME LIKE 'test/t1' \G
*************************** 1. row ***************************
TABLE_ID: 55
NAME: test/t1
FLAG: 1
N_COLS: 5
SPACE: 36
FILE_FORMAT: Antelope
ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
在将现有 table 的行格式从REDUNDANT
或COMPACT
更改为DYNAMIC
之前,请考虑以下潜在问题。
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)));
有关相关信息,请参见第 14.23 节“ InnoDB 限制”。
- 如果源服务器上的innodb_default_row_format设置与目标服务器上的设置不同,则导入未明确定义行格式的 table 会导致架构不匹配错误。有关更多信息,第 14.6.1.3 节“导入 InnoDBtable”。
确定 table 的行格式
要确定 table 的行格式,请使用显示 table 格状态:
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: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
或者,查询INFORMATION_SCHEMA.INNODB_SYS_TABLEStable:
mysql> SELECT NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test1/t1';
+----------+------------+
| NAME | ROW_FORMAT |
+----------+------------+
| test1/t1 | Dynamic |
+----------+------------+