REDUNDANT 格式提供与旧版本 MySQL 的兼容性。The REDUNDANT format provides compatibility with older versions of MySQL.

使用 REDUNDANT 行格式的表在 B 树节点的索引记录中存储可变长度列值的前 768 个字节（VARCHAR、VARBINARY 和 BLOB 以及 TEXT 类型），其余部分存储在溢出页上。大于或等于 768 字节的固定长度列会被编码为可变长度列，可以存储在页外。例如，如果字符集的最大字节长度大于 3（如 utf8mb4），则 CHAR(255) 列的大小可以超过 768 字节。Tables that use the REDUNDANT row format store the first 768 bytes of variable-length column values (VARCHAR, VARBINARY, and BLOB and TEXT types) in the index record within the B-tree node, with the remainder stored on overflow pages. Fixed-length columns greater than or equal to 768 bytes are encoded as variable-length columns, which can be stored off-page. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4.

如果列的值为 768 字节或更少，则不会使用溢出页，这可能会节省一些 I/O 操作，因为值完全存储在 B 树节点中。这对于比较短的BLOB列值效果很好，但可能导致 B 树节点被数据而非键值填满，从而降低其效率。拥有许多BLOB列的表可能会导致 B 树节点过于饱满，包含的行数过少，使整个索引的效率比行更短或列值存储在页外时更低。If the value of a column is 768 bytes or less, an overflow page is not used, and some savings in I/O may result, since the value is stored entirely in the B-tree node. This works well for relatively short BLOB column values, but may cause B-tree nodes to fill with data rather than key values, reducing their efficiency. Tables with many BLOB columns could cause B-tree nodes to become too full, and contain too few rows, making the entire index less efficient than if rows were shorter or column values were stored off-page.

REDUNDANT 行格式存储特性REDUNDANT Row Format Storage Characteristics

REDUNDANT行格式具有以下存储特性：The REDUNDANT row format has the following storage characteristics:

COMPACT 行格式相比 REDUNDANT 行格式能减少大约 20% 的行存储空间，但代价是某些操作的 CPU 使用增加。如果您的工作负载是典型的、受缓存命中率和磁盘速度限制的，COMPACT 格式可能会更快。如果工作负载受 CPU 速度限制，紧凑格式可能会更慢。The COMPACT row format reduces row storage space by about 20% compared to the REDUNDANT row format, at the cost of increasing CPU use for some operations. If your workload is a typical one that is limited by cache hit rates and disk speed, COMPACT format is likely to be faster. If the workload is limited by CPU speed, compact format might be slower.

使用 COMPACT 行格式的表在 VARCHAR、VARBINARY 和 BLOB 以及 TEXT 类型的可变长度列值的前 768 字节存储在 B 树 节点的索引记录中，其余部分存储在溢出页上。固定长度列大于或等于 768 字节时将被编码为可变长度列，可以存储在页外。例如，如果字符集的最大字节长度大于 3（如 utf8mb4），则 CHAR(255) 列可能会超过 768 字节。Tables that use the COMPACT row format store the first 768 bytes of variable-length column values (VARCHAR, VARBINARY, and BLOB and TEXT types) in the index record within the B-tree node, with the remainder stored on overflow pages. Fixed-length columns greater than or equal to 768 bytes are encoded as variable-length columns, which can be stored off-page. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4.

如果列的值为 768 字节或更少，则不会使用溢出页，这可能会节省一些 I/O 操作，因为值完全存储在 B 树节点中。这对于比较短的BLOB列值效果很好，但可能导致 B 树节点被数据而非键值填满，从而降低其效率。拥有许多BLOB列的表可能会导致 B 树节点过于饱满，包含的行数过少，使整个索引的效率比行更短或列值存储在页外时更低。If the value of a column is 768 bytes or less, an overflow page is not used, and some savings in I/O may result, since the value is stored entirely in the B-tree node. This works well for relatively short BLOB column values, but may cause B-tree nodes to fill with data rather than key values, reducing their efficiency. Tables with many BLOB columns could cause B-tree nodes to become too full, and contain too few rows, making the entire index less efficient than if rows were shorter or column values were stored off-page.

COMPACT 行格式存储特性COMPACT Row Format Storage Characteristics

COMPACT 行格式具有以下存储特性：The COMPACT row format has the following storage characteristics:

DYNAMIC行格式提供与COMPACT行格式相同的存储特性，但增加了对长可变长度列的增强存储能力，并支持大索引键前缀。The DYNAMIC row format offers the same storage characteristics as the COMPACT row format but adds enhanced storage capabilities for long variable-length columns and supports large index key prefixes.

当使用 ROW_FORMAT=DYNAMIC 创建表时，InnoDB 可以将长可变长度列的值（对于 VARCHAR、VARBINARY 和 BLOB 以及 TEXT 类型）完全存储在页外，聚簇索引记录只包含一个指向溢出页的 20 字节指针。大于或等于 768 字节的固定长度字段会被编码为可变长度字段。例如，如果字符集的最大字节长度大于 3（如 utf8mb4），CHAR(255) 列的大小可能超过 768 字节。When a table is created with ROW_FORMAT=DYNAMIC, InnoDB can store long variable-length column values (for VARCHAR, VARBINARY, and BLOB and TEXT types) fully off-page, with the clustered index record containing only a 20-byte pointer to the overflow page. Fixed-length fields greater than or equal to 768 bytes are encoded as variable-length fields. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4.

列是否存储在页外取决于页大小和行的总大小。当行过长时，最长的列会被选择存储在页外，直到聚集索引记录适合在B 树页面上。小于或等于 40 字节的TEXT和BLOB列是存储在行内的。Whether columns are stored off-page depends on the page size and the total size of the row. When a row is too long, the longest columns are chosen for off-page storage until the clustered index record fits on the B-tree page. TEXT and BLOB columns that are less than or equal to 40 bytes are stored in line.

DYNAMIC 行格式保持了在索引节点中存储整个行的效率（就像 COMPACT 和 REDUNDANT 格式一样），但 DYNAMIC 行格式避免了用长列的大量数据字节填充 B 树节点的问题。DYNAMIC 行格式的理念是，如果长数据值的一部分存储在页外，通常将整个值存储在页外是最有效的。使用 DYNAMIC 格式时，较短的列可能会保留在 B 树节点中，从而最小化给定行所需的溢出页数。The DYNAMIC row format maintains the efficiency of storing the entire row in the index node if it fits (as do the COMPACT and REDUNDANT formats), but the DYNAMIC row format avoids the problem of filling B-tree nodes with a large number of data bytes of long columns. The DYNAMIC row format is based on the idea that if a portion of a long data value is stored off-page, it is usually most efficient to store the entire value off-page. With DYNAMIC format, shorter columns are likely to remain in the B-tree node, minimizing the number of overflow pages required for a given row.

DYNAMIC 行格式支持最多 3072 字节的索引键前缀。The DYNAMIC row format supports index key prefixes up to 3072 bytes.

使用DYNAMIC行格式的表可以存储在系统表空间、每表文件表空间和通用表空间中。要将DYNAMIC表存储在系统表空间中，可以禁用innodb_file_per_table并使用常规的CREATE TABLE或ALTER TABLE语句，或者在ALTER TABLE或TABLESPACE [=] innodb_system中使用TABLESPACE [=] innodb_system表选项。innodb_file_per_table变量不适用于通用表空间，也不适用于使用CREATE TABLE表选项将DYNAMIC表存储在系统表空间时。Tables that use the DYNAMIC row format can be stored in the system tablespace, file-per-table tablespaces, and general tablespaces. To store DYNAMIC tables in the system tablespace, either disable innodb_file_per_table and use a regular CREATE TABLE or ALTER TABLE statement, or use the TABLESPACE [=] innodb_system table option with CREATE TABLE or ALTER TABLE. The innodb_file_per_table variable is not applicable to general tablespaces, nor is it applicable when using the TABLESPACE [=] innodb_system table option to store DYNAMIC tables in the system tablespace.

DYNAMIC 行格式存储特性DYNAMIC Row Format Storage Characteristics

DYNAMIC 行格式是 COMPACT 行格式的一种变体。关于存储特性，请参见 COMPACT 行格式存储特性。The DYNAMIC row format is a variation of the COMPACT row format. For storage characteristics, see COMPACT Row Format Storage Characteristics.

COMPRESSED 行格式提供与 DYNAMIC 行格式相同的存储特性和能力，但增加了对表和索引数据压缩的支持。The COMPRESSED row format offers the same storage characteristics and capabilities as the DYNAMIC row format but adds support for table and index data compression.

COMPRESSED 行格式在页外存储的内部细节上与 DYNAMIC 行格式类似，但由于表和索引数据被压缩并使用更小的页尺寸，因此在存储和性能上有额外的考虑。使用 COMPRESSED 行格式时，KEY_BLOCK_SIZE 选项控制多少列数据存储在聚簇索引中，以及多少数据存放在溢出页上。有关 COMPRESSED 行格式的更多信息，请参见 第 17.9 节，“InnoDB 表和页压缩”。The COMPRESSED row format uses similar internal details for off-page storage as the DYNAMIC row format, with additional storage and performance considerations from the table and index data being compressed and using smaller page sizes. With the COMPRESSED row format, the KEY_BLOCK_SIZE option controls how much column data is stored in the clustered index, and how much is placed on overflow pages. For more information about the COMPRESSED row format, see Section 17.9, “InnoDB Table and Page Compression”.

COMPRESSED 行格式支持最大 3072 字节的索引键前缀。The COMPRESSED row format supports index key prefixes up to 3072 bytes.

使用 COMPRESSED 行格式的表可以在每表文件表空间或通用表空间中创建。系统表空间不支持 COMPRESSED 行格式。要在每表文件表空间中存储 COMPRESSED 表，必须启用 innodb_file_per_table 变量。innodb_file_per_table 变量不适用于通用表空间。通用表空间支持所有行格式，但需要注意，由于物理页大小不同，压缩表和非压缩表不能共存于同一通用表空间。更多信息，请参见 第 17.6.3.3 节，“通用表空间”。Tables that use the COMPRESSED row format can be created in file-per-table tablespaces or general tablespaces. The system tablespace does not support the COMPRESSED row format. To store a COMPRESSED table in a file-per-table tablespace, the innodb_file_per_table variable must be enabled. The innodb_file_per_table variable is not applicable to general tablespaces. General tablespaces support all row formats with the caveat that compressed and uncompressed tables cannot coexist in the same general tablespace due to different physical page sizes. For more information, see Section 17.6.3.3, “General Tablespaces”.

压缩行格式存储特性Compressed Row Format Storage Characteristics

COMPRESSED 行格式是 COMPACT 行格式的一种变体。有关存储特性，请参见 COMPACT 行格式存储特性。The COMPRESSED row format is a variation of the COMPACT row format. For storage characteristics, see COMPACT Row Format Storage Characteristics.

InnoDB 表的默认行格式由 innodb_default_row_format 变量定义，默认值为 DYNAMIC。当未显式定义 ROW_FORMAT 表选项或指定 ROW_FORMAT=DEFAULT 时，会使用默认行格式。The default row format for InnoDB tables is defined by innodb_default_row_format variable, which has a default value of DYNAMIC. The default row format is used when the ROW_FORMAT table option is not defined explicitly or when ROW_FORMAT=DEFAULT is specified.

可以在 CREATE TABLE 或 ALTER TABLE 语句中使用 ROW_FORMAT 表选项显式定义表的行格式。例如：The row format of a table can be defined explicitly using the ROW_FORMAT table option in a CREATE TABLE or ALTER TABLE statement. For example:

CREATE TABLE t1 (c1 INT) ROW_FORMAT=DYNAMIC;

显式定义的 ROW_FORMAT 设置会覆盖默认的行格式。指定 ROW_FORMAT=DEFAULT 相当于使用隐式默认值。An explicitly defined ROW_FORMAT setting overrides the default row format. Specifying ROW_FORMAT=DEFAULT is equivalent to using the implicit default.

innodb_default_row_format 变量可以动态设置：The innodb_default_row_format variable can be set dynamically:

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 会返回错误：Valid innodb_default_row_format options include DYNAMIC, COMPACT, and REDUNDANT. The COMPRESSED row format, which is not supported for use in the system tablespace, cannot be defined as the default. It can only be specified explicitly in a CREATE TABLE or ALTER TABLE statement. Attempting to set the innodb_default_row_format variable to COMPRESSED returns an error:

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 变量定义的行格式。Newly created tables use the row format defined by the innodb_default_row_format variable when a ROW_FORMAT option is not specified explicitly, or when ROW_FORMAT=DEFAULT is used. For example, the following CREATE TABLE statements use the row format defined by the innodb_default_row_format variable.

CREATE TABLE t1 (c1 INT);

CREATE TABLE t2 (c1 INT) ROW_FORMAT=DEFAULT;

当没有明确指定 ROW_FORMAT 选项，或者使用 ROW_FORMAT=DEFAULT 时，重建表的操作会悄悄地将表的行格式更改为由 innodb_default_row_format 变量定义的格式。When a ROW_FORMAT option is not specified explicitly, or when ROW_FORMAT=DEFAULT is used, an operation that rebuilds a table silently changes the row format of the table to the format defined by the innodb_default_row_format variable.

表重建操作包括使用 ALGORITHM=COPY 或 ALGORITHM=INPLACE 且需要重建表的 ALTER TABLE 操作。有关更多信息，请参见 第 17.12.1 节，“在线 DDL 操作”。OPTIMIZE TABLE 也是一种表重建操作。Table-rebuilding operations include ALTER TABLE operations that use ALGORITHM=COPY or ALGORITHM=INPLACE where table rebuilding is required. See Section 17.12.1, “Online DDL Operations” for more information. OPTIMIZE TABLE is also a table-rebuilding operation.

下面的示例演示了一个表重建操作，该操作悄无声息地更改了未明确定义行格式的表的行格式。The following example demonstrates a table-rebuilding operation that silently changes the row format of a table created without an explicitly defined row format.

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 之前，请考虑以下潜在问题。Consider the following potential issues before changing the row format of existing tables from REDUNDANT or COMPACT to DYNAMIC.

要确定表的行格式，请使用 SHOW TABLE STATUS：To determine the row format of a table, use 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:

或者，查询信息模式 INNODB_TABLES 表：Alternatively, query the Information Schema INNODB_TABLES table:

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