InnoDB行格式
表的行格式决定了其行的物理存储方式,进而会影响查询和DML操作的性能。随着更多的行适合单个磁盘页面,查询和索引查找可以更快地工作,缓冲池中需要更少的缓存,并且写出更新后的值也需要更少的I / O。
每个表中的数据分为几页。构成每个表的页面以称为B树索引的树数据结构排列。表数据和二级索引都使用这种类型的结构。代表整个表的B树索引称为聚簇索引,它是根据主键列进行组织的。聚集索引数据结构的节点包含该行中所有列的值。二级索引结构的节点包含索引列和主键列的值。
可变长度列是将列值存储在B树索引节点中的规则的例外。太长而无法容纳在B树页面上的变长列存储在单独分配的磁盘页面上,这些磁盘页面称为溢出页面。这些列称为页外列。页面外列的值存储在溢出页面的单链接列表中,每个这样的列都有其自己的一个或多个溢出页面的列表。根据列的长度,所有或可变长度列值的前缀都存储在B树中,以避免浪费存储空间并不得不读取单独的页面。
该InnoDB
存储引擎支持四名的格式:REDUNDANT
,COMPACT
,DYNAMIC
,和COMPRESSED
。
InnoDB行格式概述
行格式 | 紧凑的存储特性 | 增强的可变长度色谱柱存储 | 大索引键前缀支持 | 压缩支持 | 支持的表空间类型 |
---|---|---|---|---|---|
REDUNDANT | 没有 | 没有 | 没有 | 没有 | 系统,每表文件,常规 |
COMPACT | 是 | 没有 | 没有 | 没有 | 系统,每表文件,常规 |
DYNAMIC | 是 | 是 | 是 | 没有 | 系统,每表文件,常规 |
COMPRESSED | 是 | 是 | 是 | 是 | 每表档案,一般 |
后面的主题描述行格式存储特征以及如何定义和确定表的行格式。
- 冗余行格式
- COMPACT行格式
- 动态行格式
- 压缩行格式
- 定义表格的行格式
- 确定表的行格式
冗余行格式
该REDUNDANT
格式提供了与旧版本MySQL的兼容性。
使用REDUNDANT
行格式的表将前768个字节的变长列值(VARCHAR
,VARBINARY
和BLOB
和TEXT
类型)存储在B树节点内的索引记录中,其余的存储在溢出页上。大于或等于768字节的固定长度列被编码为可变长度列,可以在页面外存储。例如,CHAR(255)
如果字符集的最大字节长度大于3,则该列可以超过768个字节(与相同)utf8mb4
。
如果列的值小于或等于768个字节,则不使用溢出页,并且由于该值完全存储在B树节点中,因此可以节省一些I / O。这对于相对较短的BLOB
列值很好用,但是可能导致B树节点填充数据而不是键值,从而降低了效率。具有许多BLOB
列的表可能导致B树节点变得太满,并且包含的行太少,这使得整个索引的效率低于行较短或列值存储在页面外的情况。
冗余行格式存储特征
该REDUNDANT
行格式有如下存储特性:
- 每个索引记录包含一个6字节的标头。标头用于将连续的记录链接在一起,并用于行级锁定。
- 聚集索引中的记录包含所有用户定义列的字段。此外,还有一个6字节的交易ID字段和一个7字节的滚动指针字段。
- 如果未为表定义主键,则每个聚集索引记录还包含一个6字节的行ID字段。
- 每个二级索引记录包含不在二级索引中的,为聚簇索引键定义的所有主键列。
- 一条记录包含一个指向该记录的每个字段的指针。如果记录中字段的总长度小于128个字节,则指针为1个字节;否则为0。否则为两个字节。指针数组称为记录目录。指针指向的区域是记录的数据部分。
- 在内部,定长字符列(例如
CHAR(10)
以定长格式存储)。尾随空格不会从VARCHAR
列中截断。 - 大于或等于768字节的固定长度列被编码为可变长度列,可以在页面外存储。例如,
CHAR(255)
如果字符集的最大字节长度大于3,则该列可以超过768个字节(与相同)utf8mb4
。 - 一个SQL
NULL
值在记录目录中保留一个或两个字节。NULL
如果存储在可变长度列中,则SQL 值在记录的数据部分保留零字节。对于固定长度的列,该列的固定长度保留在记录的数据部分中。保留NULL
值的固定空间允许将列从原位置更新NULL
为非NULL
值,而不会导致索引页碎片。
COMPACT行格式
与COMPACT
行格式相比,行格式将行存储空间减少了约20%REDUNDANT
,但代价是增加了某些操作的CPU使用率。如果您的工作量是典型的工作,并且受高速缓存命中率和磁盘速度的限制,那么COMPACT
格式可能会更快。如果工作量受CPU速度限制,则紧凑格式可能会变慢。
使用COMPACT
行格式的表将前768个字节的变长列值(VARCHAR
,VARBINARY
和BLOB
和TEXT
类型)存储在B树节点内的索引记录中,其余的存储在溢出页上。大于或等于768字节的固定长度列被编码为可变长度列,可以在页面外存储。例如,CHAR(255)
如果字符集的最大字节长度大于3,则该列可以超过768个字节(与相同)utf8mb4
。
如果列的值小于或等于768个字节,则不使用溢出页,并且由于该值完全存储在B树节点中,因此可以节省一些I / O。这对于相对较短的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
和)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
行格式,但增加了增强的长期可变长度列和支持大型索引键前缀的存储能力。
使用创建表时ROW_FORMAT=DYNAMIC
,InnoDB
可以完全在页外存储长的可变长度列值(针对VARCHAR
,VARBINARY
和BLOB
和TEXT
类型),而聚集索引记录仅包含指向溢出页的20字节指针。大于或等于768字节的固定长度字段被编码为可变长度字段。例如,CHAR(255)
如果字符集的最大字节长度大于3,则该列可以超过768个字节(与相同)utf8mb4
。
列是否存储在页面外取决于页面大小和行的总大小。当一行太长时,将选择最长的列进行页外存储,直到聚集的索引记录适合B树页面为止。TEXT
并且BLOB
是小于或等于40个字节的列被存储在线路。
如果DYNAMIC
行格式适合,行格式可以保持将整个行存储在索引节点中的效率(COMPACT
和和REDUNDANT
格式一样),但是DYNAMIC
行格式避免了用大量长列数据字节填充B树节点的问题。该DYNAMIC
行格式是基于这样的思想,如果一个长的数据值的一部分被存储关闭页,它通常是最有效的存储关闭页整个值。使用DYNAMIC
format时,较短的列可能会保留在B树节点中,从而最大程度地减少了给定行所需的溢出页数。
该DYNAMIC
行的格式支持索引键的前缀可达3072个字节。
使用DYNAMIC
行格式的表可以存储在系统表空间,每表文件表空间和常规表空间中。要将DYNAMIC
表存储在系统表空间中,请禁用innodb_file_per_table
并使用常规CREATE TABLE
或ALTER TABLE
语句,或者将TABLESPACE[=] innodb_system
表选项与CREATE TABLE
或一起使用ALTER TABLE
。该innodb_file_per_table
变量不适用于常规表空间,在使用TABLESPACE[=] innodb_system
table选项将DYNAMIC
表存储在系统表空间中时也不适用。
动态行格式存储特征
该DYNAMIC
行格式是一个偏差COMPACT
行格式。有关存储特征,请参见 COMPACT行格式存储特征。
压缩行格式
该COMPRESSED
行格式提供相同的存储特性和功能的DYNAMIC
行格式,但增加了对表和索引数据压缩的支持。
该COMPRESSED
行格式使用类似的内部细节关闭页存储为DYNAMIC
行格式,从表和索引数据的附加存储和性能的考虑被压缩,并使用较小的页大小。使用COMPRESSED
行格式时,该KEY_BLOCK_SIZE
选项控制在聚集索引中存储多少列数据,以及在溢出页面上放置多少列数据。有关COMPRESSED
行格式的更多信息,请参见“ InnoDB表和页面压缩”。
该COMPRESSED
行的格式支持索引键的前缀可达3072个字节。
COMPRESSED
可以在每表文件表空间或常规表空间中创建使用行格式的表。系统表空间不支持COMPRESSED
行格式。要将COMPRESSED
表存储在每表文件表空间中,innodb_file_per_table
必须启用该变量。该innodb_file_per_table
变量不适用于常规表空间。常规表空间支持所有行格式,但要注意的是,由于物理页大小不同,压缩表和未压缩表不能在同一常规表空间中共存。有关更多信息,请参见“通用表空间”。
压缩行格式存储特征
该COMPRESSED
行格式是一个偏差COMPACT
行格式。有关存储特征,请参见 COMPACT行格式存储特征。
定义表格的行格式
InnoDB
表的默认行格式由innodb_default_row_format
变量定义,其默认值为DYNAMIC
。ROW_FORMAT
未明确定义表选项或ROW_FORMAT=DEFAULT
指定表选项时,将使用默认行格式。
可以使用or 语句中的ROW_FORMAT
table选项显式定义表的行格式。例如:CREATE TABLE
ALTER 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
无法在系统表空间中使用的行格式不能定义为默认格式。它只能在CREATE TABLE
or 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_FORMAT
未明确指定选项或ROW_FORMAT=DEFAULT
使用选项时,新创建的表将使用变量定义的行格式。例如,以下CREATE TABLE
语句使用innodb_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=COPY
或ALGORITHM=INPLACE
需要表重建的操作。有关更多信息,请参见“在线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_TABLESWHERE 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 t1ADD COLUMN ( c2 INT); mysql>SELECT *FROM INFORMATION_SCHEMA . INNODB_TABLESWHERE 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个字节的索引关键字前缀长度。在复制环境中,如果将该innodb_default_row_format
变量DYNAMIC
在主服务器上设置为COMPACT
,在从属服务器上设置为,则以下未明确定义行格式的DDL语句在主服务器上成功但在从机上失败:CREATE TABLE t1 (c1 INTPRIMARY KEY , c2 VARCHAR(5000),KEY i1(c2(3070)));有关相关信息,请参见“ InnoDB限制”。
- 如果
innodb_default_row_format
源服务器上的设置与目标服务器上的设置不同,则导入未明确定义行格式的表会导致架构不匹配错误。有关更多信息,请参见“导入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_TABLESWHERE NAME ='test1/t1'; +---------- +------------ + | NAME | ROW_FORMAT | +---------- +------------ + | test1/t1 | Dynamic | +---------- +------------ +