Documentation Home
MySQL 8.0 参考手册  / 第 15 章 InnoDB 存储引擎  /  15.10 InnoDB 行格式

15.10 InnoDB 行格式

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

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

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

存储引擎支持四种行InnoDB格式:REDUNDANTCOMPACTDYNAMICCOMPRESSED

表 15.15 InnoDB 行格式概述

行格式 紧凑的存储特性 增强的可变长度列存储 大型索引键前缀支持 压缩支持 支持的表空间类型
REDUNDANT 系统,每个表的文件,一般
COMPACT 是的 系统,每个表的文件,一般
DYNAMIC 是的 是的 是的 系统,每个表的文件,一般
COMPRESSED 是的 是的 是的 是的 文件每表,一般

以下主题描述行格式存储特征以及如何定义和确定表的行格式。

冗余行格式

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

使用REDUNDANT行格式的表将可变长度列值(VARCHARVARBINARY和 类型)的前 768 个字节存储在 B 树节点内的索引记录中BLOBTEXT其余部分存储在溢出页上。大于或等于 768 字节的固定长度列被编码为可变长度列,可以跨页存储。例如,CHAR(255)如果字符集的最大字节长度大于 3,则列可以超过 768 个字节,就像utf8mb4.

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

冗余行格式存储特性

REDUNDANT格式具有以下存储特性:

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

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

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

  • 每个二级索引记录包含为聚集索引键定义的所有主键列,这些主键列不在二级索引中。

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

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

  • 大于或等于 768 字节的固定长度列被编码为可变长度列,可以跨页存储。例如,CHAR(255)如果字符集的最大字节长度大于 3,则列可以超过 768 个字节,就像 utf8mb4.

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

紧凑行格式

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

使用COMPACT行格式的表将可变长度列值(VARCHARVARBINARY和 类型)的前 768 个字节存储在B 树节点内的索引记录中BLOB, 其余部分存储在溢出页上。大于或等于 768 字节的固定长度列被编码为可变长度列,可以跨页存储。例如, 如果字符集的最大字节长度大于 3,则列可以超过 768 个字节,就像. TEXTCHAR(255)utf8mb4

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

COMPACT 行格式存储特性

COMPACT格式具有以下存储特性:

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

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

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

  • 记录头之后是非NULL列的数据内容。

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

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

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

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

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

  • 在内部,对于诸如 utf8mb3and之类的可变长度字符集utf8mb4InnoDB尝试 通过修剪尾随空格来存储字节 。如果 列值的字节长度超过字节,则将尾随空格修剪为列值字节长度的最小值。列的最大长度 是最大字符字节长度× 。 CHAR(N)NCHAR(N)NCHAR(N)N

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

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

动态行格式

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

当使用 创建表时 ROW_FORMAT=DYNAMICInnoDB 可以完全在页外存储长可变长度列值(对于 VARCHARVARBINARY和 类型) BLOBTEXT聚簇索引记录仅包含指向溢出页的 20 字节指针。大于或等于 768 字节的固定长度字段被编码为可变长度字段。例如, CHAR(255)如果字符集的最大字节长度大于 3,则列可以超过 768 个字节,就像utf8mb4.

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

行格式保持了在索引节点合适的DYNAMIC情况下存储整行的效率( COMPACTREDUNDANT 格式也是如此),但是DYNAMIC行格式避免了长列的大量数据字节填充B树节点的问题。行DYNAMIC格式基于这样的想法,即如果长数据值的一部分存储在页外,通常将整个值存储在页外是最有效的。使用DYNAMIC格式,较短的列可能会保留在 B 树节点中,从而最大限度地减少给定行所需的溢出页数。

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

使用DYNAMIC行格式的表可以存储在系统表空间、file-per-table 表空间和通用表空间中。要将DYNAMIC表存储在系统表空间中,请禁用 innodb_file_per_table并使用常规CREATE TABLEorALTER TABLE语句,或者将TABLESPACE [=] innodb_system表选项与CREATE TABLEor一起使用ALTER TABLE。该 innodb_file_per_table变量不适用于一般表空间,也不适用于使用TABLESPACE [=] innodb_systemtable选项DYNAMIC在系统表空间中存储表时。

动态行格式存储特性

DYNAMIC行格式是行格式的变 体COMPACT。有关存储特性,请参阅 COMPACT 行格式存储特性

压缩行格式

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

COMPRESSED行格式使用与行格式类似的页外存储内部细节, 并DYNAMIC从表和索引数据中压缩额外的存储和性能考虑,并使用更小的页面大小。对于COMPRESSED行格式,该 KEY_BLOCK_SIZE选项控制在聚集索引中存储多少列数据,以及将多少列数据放置在溢出页上。有关 COMPRESSED行格式的更多信息,请参阅 第 15.9 节,“InnoDB 表和页面压缩”

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

COMPRESSED可以在 file-per-table 表空间或通用表空间中创建 使用行格式的表。系统表空间不支持 COMPRESSED行格式。要将 COMPRESSED表存储在 file-per-table 表空间中, innodb_file_per_table必须启用该变量。该 innodb_file_per_table变量不适用于一般表空间。通用表空间支持所有行格式,但需要注意的是,由于物理页面大小不同,压缩表和未压缩表不能共存于同一通用表空间中。有关详细信息,请参阅 第 15.6.3.3 节,“通用表空间”

压缩行格式存储特性

COMPRESSED行格式是行格式的变 体COMPACT。有关存储特性,请参阅 COMPACT 行格式存储特性

定义表格的行格式

表的默认行格式InnoDBinnodb_default_row_format 变量定义,其默认值为DYNAMIC. 当 ROW_FORMAT表选项未明确定义或ROW_FORMAT=DEFAULT指定时,将使用默认行格式。

可以使用or 语句ROW_FORMAT中的表选项 显式定义表的行格式 。例如: CREATE TABLEALTER 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 选项包括DYNAMICCOMPACTREDUNDANT。不支持在系统表空间中使用的 COMPRESSED行格式不能定义为默认值。它只能在 CREATE TABLEor 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'

当未明确指定选项或使用选项 innodb_default_row_format 时,新创建的表使用变量 定义的行格式 。例如,以下语句使用 变量 定义的行格式 。ROW_FORMATROW_FORMAT=DEFAULTCREATE TABLEinnodb_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=COPYALGORITHM=INPLACE需要重建表的操作。有关详细信息,请参阅第 15.12.1 节,“在线 DDL 操作”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

在将现有表的行格式从REDUNDANTCOMPACT更改为 之前,请考虑以下潜在问题DYNAMIC

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

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

    有关相关信息,请参阅第 15.22 节,“InnoDB 限制”

  • innodb_default_row_format 如果源服务器上的设置与目标服务器上的设置不同,则 导入未明确定义行格式的表会导致模式不匹配错误 。有关详细信息,请参阅 第 15.6.1.3 节,“导入 InnoDB 表”

确定表格的行格式

要确定表的行格式,请使用 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    |
+----------+------------+