15.10 InnoDB记录格式

一张表的记录格式决定了这张表的记录在物理上是怎样存储的，而后者又会影响查询和DML操作的性能。单个磁盘页面可以容纳的记录越多，查询和索引查找就会更快，buffer pool需要的缓冲区内存就会更少，写入被更新的记录所消耗的I/O也更少。

每张表中的数据按页存储。组成每张表的多个页被组织成两个一树形数据结构，被称为B+树索引。表数据和二级索引都使用这种类型的数据结构。表示整张表的B+树索引被称为聚簇索引，依照主键顺序组织。聚簇索引数据结构的节点包含了记录中的所有列的值。一个二级索引结构的节点中包含了索引列和主键列的值。

对于列的值存储在B+树索引中这个规则来说，变长字段是一个例外。过长的变长字段无法存储在B+树索引中，会被存储在独立分配的磁盘页面中（溢出页）。这种列被称为off-page列。off-page列的值被存储在一个单向链表中。每一个列有自己独立链表，包含一个或更多溢出页。整个变长列或者前缀被存储在B+树中，可以避免浪费存储空间，从而不用去读取溢出页，这取决于列的长度。

InnoDB存储引擎支持4种记录格式：REDUNDANT,COMPACT,DYNAMIC,和COMPRESSED。

表15.15 InnoDB记录格式概览

记录格式	紧凑存储	加强变长列存储	支持大索引键前缀	支持压缩	支持的表空间类型
REDUNDANT	否	否	否	否	系统，独立，通用
COMPACT	是	否	否	否	系统，独立，通用
DYNAMIC	是	是	是	否	系统，独立，通用
COMPRESSED	是	是	是	是	独立，通用

下面介绍记录格式存储特性，以及怎样定义和确定一张表的记录格式。

REDUNDANT Row Format
COMPACT Row Format
DYNAMIC Row Format
COMPRESSED Row Format
Defining the Row Format of a Table
Determining the Row Format of a Table

REDUNDANT Row Format #

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

使用REDUNDANT记录格式的表将变长字段（varchar,varbinary,以及blob和text）的前768个字节存储在B+树节点的索引记录中，剩余部分存储在溢出页中。长度达到或超过768字节的定长列被编码成变长列。例如，如果相关字符集每个字符的最大长度可以超过3，一个char(255)的列，可以存储的长度超过768个字节，比如utf8mb4。

如果一个列的值没有超过768个字节，就不会用到溢出页，也可以节省一些I/O（因为整个值被存储在B+树节点中）。对相对较短的BLOB列来说很好，但是可能会导致B+树节点被数据充满而不是键的值，这降低了性能。如果表中有很多BLOB列，会导致B+树节点变得太满，包含的记录却太少，和都是短记录的表或列值被存储在溢出页的表相比，这使得整个索引效率不高。

REDUNDANT记录格式的存储特性 #

REDUNDANT记录格式的存储特性如下：

每条索引记录都包含一个6字节长的header。这个header被用来连接相连的记录，以及用于记录锁。
聚簇索引中的记录包含所有用户定义的列。此外，含包含一个6字节的事务id和一个7字节的回滚指针。
如果一张表没有定义主键，每一个聚簇索引都会包含一个6字节的记录id字段。
每条二级索引记录包含所有的主键列。
每条记录包含一个指向这条记录中的字段的指针。如果这些字段的总长度不足128字节，这个指针是1字节长。否则，这个指针是2字节长。指针列表被称为记录目录。指针指向的区域被称为记录的数据部分。
在内部，定长列（比如char(10)）被按定长格式存储。而varchar列尾部的空余空间不会被清空（？？？）。
长度大于或等于768字节的定长列被编码成变长列，可以被存储在溢出页中。例如，如果相关字符集的一个字符的最大长度超过3，一个char(255)的列可以存储的长度超过768个字节，比如utf8mb4。
在记录目录中为NULL值保留一个或两个字节。如果被存储在变长列中，一个SQL中的NULL值在记录的数据部分占用0字节。对于一个定长列来说，这个列的固定长度被存储在记录的数据部分。位null值保留的固定空间使得当列被修改为non-null时，可以就地更新，而不会导致索引页碎片。

COMPACT Row Format #

和REDUNDANT记录格式相比，COMPACT记录格式占用的存储空间降低了约20%，代价是某些操作的CPU使用增加了。如果你的典型工作负载收到缓存命中率和磁盘速度的限制，COMPACT格式可能更快。如果典型工作负载受到CPU速度的限制，COMPACT记录格式可能会更慢。

使用COMPACT记录格式的表将变长列的前768字节存储在索引记录中，剩余部分存储在溢出页中。长度大于等于768字节的列会被编码成变长列。例如如果相关字符集中每个字符占用的最大字节数会超过3，一个char(255)的列可以存储的长度超过768字节，比如utf8mb4。

如果某个列的长度小于或等于768字节，不会用到溢出页，所有数据被存储在B+树节点中，可以节省一些I/O。这对短BLOB列来说没什么，但是会导致B+树节点充满数据，而不是键值，这会降低效率。使用很多BLOB列的表会导致B+树节点会导致B+树节点变得太满，包含的记录太少，和短记录或被存储在溢出页的表相比，这使得整个索引效率不高。

COMPACT记录格式的存储特性 #

COMPACT记录格式的存储特性如下：

每条索引记录包含一个5字节长的header，其前面可能有变长字段信息。这个header倍用来链接连续的记录，并用于记录锁。
记录header中的变长长度部分包含一个位向量，用来标识null列。如果索引中可以是null列的数量是N，那么这个位向量的长度是ceiling(N/8)字节（例如，如果有9-16列都可以是null，那么位向量使用两个字节）。除了位向量中的位，值是null的列不占用存储空间。header的变长字段长度部分也包含变长列的长度。每一个列的长度占1-2字节，具体值取决于这一列的最大长度。如果索引中的所有列都是not null并且是定长的，记录header就没有变长列长度部分。
对每一个non-null变长字段，记录的header中用1-2字节记录这一列的长度。只有当某一列被存储在溢出页中，或是最大长度超过255字节且实际长度超过127字节，才需要两个字节来记录长度。对于一个存储在溢出页中的列，2字节的长度用来记录页内存储的那一部分数据长度+20字节的指针（指向溢出页）。业内存储部分是768字节，所以长度是768+20.这20字节的指针中也存储这这一列的真实长度。
记录header后存储的是non-null列的数据内容。
聚簇索引中的记录包含所有用户定义的列。此外，还有一个6字节的事务id和7字节的回滚指针。
如果一张表没有定义主键，每一个聚簇索引记录会包含一个6字节的记录id字段。
每一个二级索引记录都包含所有的主键列。如果任意一个主键列是变长的，每条二级索引记录的header部分就会有一部分记录变长列的长度，即使这个二级索引中的列是定长列。
在内部，对于一个非变长结果集，定长字符列，比如char(10)，按照定长格式存储。变长列尾部的空间不会被清空（？？）。
在内部，对于诸如utf8mb3和utf8mb4一类的变长结果集，InnoDB会去除尾部的空间，尝试用N字节存储char(N)。如果一个char(N)列的值的长度超过N字节，尾部空白会被去除以使这一列占用的空间最小。一个char(N)列的最大长度是单个字符的最大字节数*N。
会为CHAR(N)列保留最少N字节。这可以在大多数场景中保证就地更新，避免产生索引页面碎片。与此形成对比的是，使用REDUNDANT格式时，一个CHAR(N)列占用的空间是字符最大长度*N。
长度大于或等于768字节的定长列被编码成变长列，可以存储在溢出页中。例如，如果使用的字符集中每个字符的最大长度大于3，比如utf8mb4，一个char(255)类型的列，存储长度可以超过768字节。

DYNAMIC Row Format #

DYNAMIC格式提供的存储特性和COMPACT格式相同，只是增加了对于长变长列的存储能力和存储大索引键前缀的能力。

如果创建一张表时指定的记录格式是DYNAMIC，InnoDB就可以讲长变长字段的所有内容都存储在溢出页中，聚簇索引中只包含20字节的指针。长度大于等于768字节的定长字段被编码成变长字段。例如，如果字符集中单个字符的最大长度大于3，比如utf8mb4，那么char(255)类型的列可以存储的长度超过768字节。

相关列是否被存储在溢出页中，取决于页大小和记录的总长度。如果一条记录过长，最长的列就会被存储在溢出页中，直到B+树索引页面可以容纳这条记录。长度小于或等于40字节的TEXT和BLOB列被存储在页内。

DYNAMIC格式保留了只要可以容纳，就将整条记录存储在索引节点中的高效，但是避免了B+树节点被大量长列的数据充满的问题。DYNAMIC记录格式基于的观点是：与其将一个长数据值存储在溢出页中，还不如将整个值存储在溢出页中，这样更高效。对于DYNAMIC格式，短列更有可能被存储在B+树节点中，需尽量减少一条记录所需的溢出页数量。

DYNAMIC格式支持的最大索引键长度是3072字节。

使用DYNAMIC格式的表可以被存储在系统表空间中，独立表空间中和通用表空间中。要将一张DYNAMIC表存储在系统表空间中，禁用innodb_file_per_table并使用一个常规的CREATE TABLE货ALTER TABLE语句，或在CREATE TABLE即ALTER TABLE语句后加上TABLESPACE [=] innodb_system选项。innodb_file_per_table变量不可用于通用表空间，当使用TABLESPACE [=] innodb_system选项来在系统表空间存储DYNAMIC表时，也无效。

DYNAMIC记录格式的存储特性 #

DYNAMIC格式是COMPACT格式的一个变种。要查看其存储特性，请查阅COMPACT记录格式的存储特性。

COMPRESSED Row Format #

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

COMPRESSED使用了和DYNAMIC相同的溢出页存储技术，只是出于对存储和性能考虑，增加了对表和索引数据压缩的功能，可以使用更小的页面。使用COMPRESSED记录格式，KEY_BLOCK_SIZE选项控制着多少数据被存储在聚簇索引中，多少数据被存储在溢出页页中。想了解更多关于COMPRESSED记录格式的信息，请查阅15.9,“InnoDB表和页面压缩”。

COMPRESSED格式支持的最大索引长度是3072字节。

使用COMPRESSED格式的表可以被存储在独立表空间或通用表空间中。系统表空间不支持COMPRESSED格式。要在独立表空间中存储一张COMPRESSED表，必须启用innodb_file_per_table变量。innodb_file_per_table对通用表空间无效。通用表空间支持所有记录格式，只是压缩表和非压缩表不能存在同一个通用表空间中，因为物理页大小不同。想了解更多信息，请查阅15.6.3.3，“通用表空间”。

COMPRESSED记录格式的存储特性 #

COMPRESSED格式是COMPACT格式的一个变种。要了解存储特性，请查阅COMPACT记录格式的存储特性。

定义一张表的行格式 #

InnoDB使用的默认行格式是由变量innodb_default_row_format定义，默认值是DYNAMIC。如果不明确指明行格式，或使用ROW_FORMAT=DEFAULT，就会使用默认行格式。

一张表的行格式可以在alter table或create 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可选项包括DYNAMI，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变量定义的格式。例如，先的语句使用innodb_default_row_format变量定义的行格式：

CREATE TABLE t1 (c1 INT);

CREATE TABLE t2 (c1 INT) ROW_FORMAT=DEFAULT;

如果没有显式指定行格式，或使用了ROW_FORMAT=DEFAULT，重建表操作会默认将表的行格式修改为默认行格式。

重建表操作包括使用ALGORITHM=COPY或ALGORITHM=INPLACE选项的ALTER TABLE操作。见章节15.2.1,“Online 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

在将一张格式为REDUNDANT或COMPACT的现有表更改为DYNAMIC格式之前，考虑一下潜在的问题：

REDUNDANT和COMPACT格式支持的最大索引键前缀是767字节，而DYNAMIC和COMPRESSED格式支持的最大索引键前缀是3072字节。

在主从复制场景中，如果源库上的默认行格式呗设置为DYNAMIC，而副本上的默认格式是COMPACT，下面的没有显示指定行格式的DDL语句，在源库上会执行成功，在副本上会执行失败：

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

想了解相关信息，请查阅章节[15.22，“InnoDB限制”]。

如果源库和目标库的默认行格式不一致，导入一张表时不显示置顶行格式，会导致数据库不匹配的错误。想了解更多信息，请查阅15.1.6.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.TABLES表：

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