表的行格式决定了其行的物理存储方式,这反过来又会影响查询和 DML 操作的性能。随着更多行适合单个磁盘页面,查询和索引查找可以更快地工作,缓冲池中需要更少的缓存内存,并且需要更少的 I/O 来写出更新的值。
每个表中的数据被分成页。组成每个表的页面排列在称为 B 树索引的树数据结构中。表数据和二级索引都使用这种类型的结构。表示整个表的 B 树索引称为聚簇索引,它根据主键列进行组织。聚簇索引数据结构的节点包含行中所有列的值。二级索引结构的节点包含索引列和主键列的值。
可变长度列是列值存储在 B 树索引节点中的规则的一个例外。太长而无法放入 B 树页面的可变长度列存储在称为溢出页面的单独分配的磁盘页面上。此类列称为页外列。离页列的值存储在溢出页的单链表中,每个这样的列都有自己的一个或多个溢出页列表。根据列的长度,所有或可变长度列值的前缀都存储在 B 树中,以避免浪费存储空间和不得不读取单独的页面。
存储引擎支持四种行InnoDB
格式:REDUNDANT
、COMPACT
、
DYNAMIC
和COMPRESSED
。
表 14.7 InnoDB 行格式概述
行格式 | 紧凑的存储特性 | 增强的可变长度列存储 | 大型索引键前缀支持 | 压缩支持 | 支持的表空间类型 | 所需文件格式 |
---|---|---|---|---|---|---|
REDUNDANT |
不 | 不 | 不 | 不 | 系统,文件每表 | 羚羊或梭鱼 |
COMPACT |
是的 | 不 | 不 | 不 | 系统,文件每表 | 羚羊或梭鱼 |
DYNAMIC |
是的 | 是的 | 是的 | 不 | 每表文件 | 梭子鱼 |
COMPRESSED |
是的 | 是的 | 是的 | 是的 | 每表文件 | 梭子鱼 |
以下主题描述行格式存储特征以及如何定义和确定表的行格式。
该REDUNDANT
格式提供与旧版本 MySQL 的兼容性。
两种文件格式(和)REDUNDANT
都支持行
格式。有关更多信息,请参阅第 14.10 节,“InnoDB 文件格式管理”。
InnoDB
Antelope
Barracuda
使用REDUNDANT
行格式的表将可变长度列值(VARCHAR
、
VARBINARY
和
类型)的前 768 个字节存储在 B 树节点内的索引记录中BLOB
,
TEXT
其余部分存储在溢出页上。大于或等于 768 字节的固定长度列被编码为可变长度列,可以跨页存储。例如,CHAR(255)
如果字符集的最大字节长度大于 3,则列可以超过 768 个字节,就像utf8mb4
.
如果列的值是 768 字节或更少,则不会使用溢出页,并且可能会节省一些 I/O,因为该值完全存储在 B 树节点中。这适用于相对较短的BLOB
列值,但可能会导致 B 树节点填充数据而不是键值,从而降低其效率。具有许多BLOB
列的表可能会导致 B 树节点变得太满,并且包含的行太少,从而使整个索引的效率低于行较短或列值存储在页外的情况。
冗余行格式存储特性
行REDUNDANT
格式具有以下存储特性:
每个索引记录包含一个 6 字节的标头。标头用于将连续的记录链接在一起,并用于行级锁定。
聚集索引中的记录包含所有用户定义列的字段。此外,还有一个 6 字节的事务 ID 字段和一个 7 字节的滚动指针字段。
如果没有为表定义主键,则每个聚簇索引记录还包含一个 6 字节的行 ID 字段。
每个二级索引记录包含为聚集索引键定义的所有主键列,这些主键列不在二级索引中。
记录包含指向记录的每个字段的指针。如果一条记录中字段的总长度小于128字节,则指针为1字节;否则,两个字节。指针数组称为记录目录。指针指向的区域是记录的数据部分。
大于或等于 768 字节的固定长度列被编码为可变长度列,可以跨页存储。例如,
CHAR(255)
如果字符集的最大字节长度大于 3,则列可以超过 768 个字节,就像utf8mb4
.SQL
NULL
值在记录目录中保留一个或两个字节。如果存储在可变长度列中,则SQLNULL
值会在记录的数据部分保留零字节。对于定长列,列的固定长度保留在记录的数据部分。为值保留固定空间NULL
允许将列更新NULL
为非NULL
值,而不会导致索引页碎片。
与COMPACT
行格式相比,行格式减少了大约 20% 的行存储空间,REDUNDANT
代价是增加了某些操作的 CPU 使用率。如果您的工作负载是受缓存命中率和磁盘速度限制的典型工作负载,COMPACT
格式化可能会更快。如果工作负载受 CPU 速度限制,紧凑格式可能会更慢。
两种文件格式(和)COMPACT
都支持行
格式。有关更多信息,请参阅第 14.10 节,“InnoDB 文件格式管理”。
InnoDB
Antelope
Barracuda
使用COMPACT
行格式的表将可变长度列值(VARCHAR
、
VARBINARY
和
类型)的前 768 个字节存储在B 树节点内的索引记录中BLOB
,
其余部分存储在溢出页上。大于或等于 768 字节的固定长度列被编码为可变长度列,可以跨页存储。例如,
如果字符集的最大字节长度大于 3,则列可以超过 768 个字节,就像.
TEXT
CHAR(255)
utf8mb4
如果列的值是 768 字节或更少,则不会使用溢出页,并且可能会节省一些 I/O,因为该值完全存储在 B 树节点中。这适用于相对较短的BLOB
列值,但可能会导致 B 树节点填充数据而不是键值,从而降低其效率。具有许多BLOB
列的表可能会导致 B 树节点变得太满,并且包含的行太少,从而使整个索引的效率低于行较短或列值存储在页外的情况。
COMPACT 行格式存储特性
行COMPACT
格式具有以下存储特性:
每个索引记录包含一个 5 字节的标头,其前面可能有一个可变长度的标头。标头用于将连续的记录链接在一起,并用于行级锁定。
记录头的可变长度部分包含一个用于指示
NULL
列的位向量。若索引中可列数NULL
为N
,则位向量占用 字节数。(例如,如果有 9 到 16 列可以是,则位向量使用两个字节。)除了此向量中的位之外,是的列不占用空间。标头的可变长度部分还包含可变长度列的长度。每个长度占用一个或两个字节,具体取决于列的最大长度。如果索引中的所有列都是CEILING(
N
/8)NULL
NULL
NOT NULL
并且具有固定长度,记录头没有可变长度部分。对于每个非
NULL
可变长度字段,记录头包含一或两个字节的列长度。仅当部分列存储在溢出页外部或最大长度超过 255 字节且实际长度超过 127 字节时,才需要两个字节。对于外部存储的列,2字节长度表示内部存储部分的长度加上指向外部存储部分的20字节指针。内部部分是768字节,所以长度是768+20。20 字节的指针存储列的真实长度。记录头之后是非
NULL
列的数据内容。聚集索引中的记录包含所有用户定义列的字段。此外,还有一个 6 字节的事务 ID 字段和一个 7 字节的滚动指针字段。
如果没有为表定义主键,则每个聚簇索引记录还包含一个 6 字节的行 ID 字段。
每个二级索引记录包含为聚集索引键定义的所有主键列,这些主键列不在二级索引中。如果任何主键列是可变长度的,则每个二级索引的记录头都有一个可变长度部分来记录它们的长度,即使二级索引是在固定长度列上定义的。
在内部,对于非可变长度字符集,固定长度字符列如 以
CHAR(10)
固定长度格式存储。VARCHAR
不会从列 中截断尾随空格 。在内部,对于诸如
utf8mb3
and之类的可变长度字符集utf8mb4
,InnoDB
尝试 通过修剪尾随空格来存储字节 。如果 列值的字节长度超过字节,则将尾随空格修剪为列值字节长度的最小值。列的最大长度 是最大字符字节长度× 。CHAR(
N
)N
CHAR(
N
)N
CHAR(
N
)N
N
为 保留 最少的字节。在许多情况下,保留最小空间可以使列更新就地完成,而不会导致索引页碎片化。相比之下, 使用 行格式 时, 列占用最大字符字节长度× 。CHAR(
N
)N
CHAR(
N
)N
REDUNDANT
大于或等于 768 字节的固定长度列被编码为可变长度字段,可以跨页存储。例如,
CHAR(255)
如果字符集的最大字节长度大于 3,则列可以超过 768 个字节,就像utf8mb4
.
行格式提供与DYNAMIC
行格式相同的存储特性,COMPACT
但增加了对长可变长度列的增强存储功能,并支持大索引键前缀。
DYNAMIC
文件格式支持行
格式Barracuda
。有关更多信息,请参阅第 14.10 节,“InnoDB 文件格式管理”。要创建使用DYNAMIC
行格式的
表,innodb_file_format
必须将变量设置为Barracuda
,并且
innodb_file_per_table
必须启用该变量。
当使用 创建表时
ROW_FORMAT=DYNAMIC
,InnoDB
可以完全在页外存储长可变长度列值(对于
VARCHAR
、
VARBINARY
和
类型) BLOB
,
TEXT
聚簇索引记录仅包含指向溢出页的 20 字节指针。大于或等于 768 字节的固定长度字段被编码为可变长度字段。例如,
CHAR(255)
如果字符集的最大字节长度大于 3,则列可以超过 768 个字节,就像utf8mb4
.
列是否存储在页外取决于页大小和行的总大小。当一行太长时,选择最长的列进行页外存储,直到聚集索引记录适合B 树页。
小于等于40字节的列按行存储
TEXT
。
BLOB
行格式保持了在索引节点合适的DYNAMIC
情况下存储整行的效率(
COMPACT
和REDUNDANT
格式也是如此),但是DYNAMIC
行格式避免了长列的大量数据字节填充B树节点的问题。行DYNAMIC
格式基于这样的想法,即如果长数据值的一部分存储在页外,通常将整个值存储在页外是最有效的。使用DYNAMIC
格式,较短的列可能会保留在 B 树节点中,从而最大限度地减少给定行所需的溢出页数。
DYNAMIC
行格式支持最多 3072 字节的索引键前缀
。此功能由
innodb_large_prefix
变量控制,默认情况下禁用。有关详细信息,请参阅
innodb_large_prefix
变量说明。
动态行格式存储特性
DYNAMIC
行格式是行格式的变
体COMPACT
。有关存储特性,请参阅
COMPACT 行格式存储特性。
行格式提供与COMPRESSED
行格式相同的存储特性和功能,
DYNAMIC
但增加了对表和索引数据压缩的支持。
COMPRESSED
文件格式支持行
格式Barracuda
。有关更多信息,请参阅第 14.10 节,“InnoDB 文件格式管理”。要创建使用COMPRESSED
行格式的
表,innodb_file_format
必须将变量设置为Barracuda
,并且
innodb_file_per_table
必须启用该变量。
COMPRESSED
行格式使用与行格式类似的页外存储内部细节,
并DYNAMIC
从表和索引数据中压缩额外的存储和性能考虑,并使用更小的页面大小。对于COMPRESSED
行格式,该
KEY_BLOCK_SIZE
选项控制在聚集索引中存储多少列数据,以及将多少列数据放置在溢出页上。有关
COMPRESSED
行格式的更多信息,请参阅
InnoDB 表压缩。
COMPRESSED
行格式支持最多 3072 字节的索引键前缀
。此功能由
innodb_large_prefix
变量控制,默认情况下禁用。有关详细信息,请参阅
innodb_large_prefix
变量说明。
压缩行格式存储特性
COMPRESSED
行格式是行格式的变
体COMPACT
。有关存储特性,请参阅
COMPACT 行格式存储特性。
表的默认行格式InnoDB
是
COMPACT
.
可以使用or
语句ROW_FORMAT
中的表选项
显式定义表的行格式
。例如:
CREATE TABLE
ALTER TABLE
CREATE TABLE t1 (c1 INT) ROW_FORMAT=COMPACT;
ROW_FORMAT
选项包括
REDUNDANT
、COMPACT
、
DYNAMIC
和COMPRESSED
。
要创建使用DYNAMIC
或
COMPRESSED
行格式的
表,innodb_file_format
必须将变量设置为Barracuda
,并且
innodb_file_per_table
必须启用该变量。否则,如果
innodb_strict_mode
未启用,InnoDB
将使用默认COMPACT
行格式创建表。
Oracle 建议
在和
语句
中innodb_strict_mode
使用
ROW_FORMAT
表选项
时启用。CREATE TABLE
ALTER TABLE
要确定表的行格式,请使用
SHOW TABLE STATUS
:
mysql> SHOW TABLE STATUS IN test1\G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 1
Create_time: 2014-10-31 16:02:01
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
或者,查询
INFORMATION_SCHEMA.INNODB_SYS_TABLES
表:
mysql> SELECT NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test1/t1';
+----------+------------+
| NAME | ROW_FORMAT |
+----------+------------+
| test1/t1 | Dynamic |
+----------+------------+