【MySQL精通之路】InnoDB(10)-行格式

目录

1.表数据结构

1.1 聚集索引数据结构

1.2 辅助索引数据结构

2.行格式

2.1 REDUNDANT行格式

2.2 REDUNDANT存储特性

2.3 COMPACT行格式

2.4 COMPACT存储特性

2.5 DYNAMIC行格式

2.6 DYNAMIC存储特性

2.7 COMPRESSED行格式

2.8 COMPRESSED存储特性

3.定义表格的行格式

4.确定表的行格式

5.总结

---

1.表数据结构

表的行格式决定了其行的物理存储方式，这反过来又会影响查询和DML操作的性能。

多个行数据在单个磁盘页面时，查询和索引查找可以更快地工作，缓冲池中所需的缓存更少，写入更新值所需的I/O也更少。

博主PS:

为了减少对磁盘的IO，数据库加载数据的时候都是以页为单位加载数据的，一般情况，页的大小是16KB，比如有一行数据在某一页中，就算这一行数据只有1B，那么数据库也会将这一页16KB加载到内存中来。

每个表中的数据被划分为多页。组成每个表的页面被排列在一个称为B树（Innodb中为B+树）索引的树数据结构中。表数据和二级索引都使用这种类型的结构。

表示整个表的B树索引称为聚集索引，它是根据主键列组织的。

博主PS:

也就是说很多行数据和和很多索引key共同存在一个页上，这样的一堆页又共同组成了一张表。

1.1 聚集索引数据结构

聚集索引数据结构的节点包含行中所有列的值。

1.2 辅助索引数据结构

辅助索引数据结构的节点包含索引列和聚集索引列的值。

可变长度列是列值存储在B树索引节点规则的例外。

太长而无法容纳在B树页面上的可变长度列存储在单独分配的称为溢出页面的磁盘页面上。这样的列被称为页外列。页外列的值存储在溢出页的单链接列表中，每个这样的列都有自己的一个或多个溢出页的列表。根据列长度的不同，可变长度列值的全部或一个前缀都存储在B树中，以避免浪费存储空间而不得不读取单独的页面。

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

REDUNDANT、COMPACT、DYNAMIC和COMPRESED。

Table 17.15 InnoDB Row Format Overview

 这里两种索引的数据结构有所不同，比如聚簇索引，他的结构是由一堆主键构成一颗树，然后叶子节点上放置了每行的数据，（当然也有指向下一页的指针。别忘了Innodb中页是顺序存储，且头尾相连的一个链表结构）

2.行格式

2.1 REDUNDANT行格式

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

使用REDUNDANT行格式的表将可变长度列值

（VARCHAR、VARBINARY、BLOB和TEXT类型）的前768个字节存储在B树节点内的索引记录中，其余部分存储在溢出页上。

大于或等于768字节的固定长度列被编码为可变长度列，可变长度列可以页外存储。

例如：

如果字符集的最大字节长度大于3，则CHAR（255）列可以超过768字节，就像utf8mb4一样。

如果列的值为768字节或更少，则不使用溢出页，并且可能会节省一些I/O，因为该值完全存储在B树节点中。

这适用于相对较短的BLOB列值，但可能会导致B树节点填充数据而不是键值，从而降低其效率。具有许多BLOB列的表可能会导致B树节点过满，并且包含的行过少，从而使整个索引的效率低于行较短或列值存储在页外的情况。

---

2.2 REDUNDANT存储特性

REDUNDANT行格式具有以下存储特性：

每个索引记录都包含一个6字节的头。

头用于将连续记录链接在一起，并用于行级锁定。

聚集索引中的记录包含所有用户定义列的字段。

此外，还有一个6字节的事务ID字段和一个7字节的回滚指针字段。这个回滚指针主要用在MVCC中

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

每个辅助索引记录都包含为聚集索引键定义的、不在辅助索引中的所有主键列。

记录包含指向该记录的每个字段的指针(pointer)。如果记录中字段的总长度小于128字节，则指针为一个字节；否则为两个字节。指针数组称为记录目录。指针指向的区域是记录的数据部分。

在内部，固定长度字符列（如CHAR（10））以固定长度格式存储。VARCHAR列的尾部空格不会被截断。

大于或等于768字节的固定长度列被编码为可变长度列，可变长度列可以页外存储。

例如，如果字符集的最大字节长度大于3，则CHAR（255）列可以超过768字节，就像utf8mb4一样。

SQL NULL值在记录目录中保留一个或两个字节。
如果存储在可变长度列中，SQL NULL值将在记录的数据部分保留零个字节。
对于固定长度的列，该null列的固定长度保留在记录的数据部分中。

为NULL值保留固定空间允许将列从NULL值更新为非NULL值，而不会导致索引页碎片。

---

博主来画图你们就懂了：

这是根据官方文档的解释画出的图。与其他博客有差异的地方在于记录目录，其他博客说的是偏移量，官方文档说的是指针。这里存在争议。而且很多博客抄来抄去存在错误。这里希望大家仔细甄别

2.3 COMPACT行格式

COMPACT行格式与REDUNDANT行格式相比，以增加某些操作的CPU使用为代价，将行存储空间减少了约20%。

如果您的工作负载是受缓存命中率和磁盘速度限制的典型工作负载，COMPACT格式可能会更快。如果工作负载受到CPU速度的限制，那么紧凑格式可能会更慢。

使用COMPACT行格式的表将可变长度列值

（VARCHAR、VARBINARY、BLOB和TEXT类型）

的前768个字节存储在B树节点内的索引记录中，其余部分存储在溢出页上。

大于或等于768字节的固定长度列被编码为可变长度列，可变长度列可以页外存储。

例如，如果字符集的最大字节长度大于3，则CHAR（255）列可以超过768字节，就像utf8mb4一样。

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

2.4 COMPACT存储特性

COMPACT行格式具有以下存储特性：

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

记录头的可变长度部分包含一个用于指示NULL列的位向量。

如果索引中可以为NULL的列数为N，则位向量占用CEILING（N/8）字节

（例如，如果有9到16列中的任何一列可以为NULL，则位向量使用两个字节。）

为NULL的列不占用此向量中位以外的空间。

标头的可变长度部分还包含可变长度列的长度。

每个长度占用一个或两个字节，具体取决于列的最大长度。如果索引中的所有列都不是NULL并且具有固定长度，则记录头没有可变长度部分。

对于每个非NULL可变长度字段，记录头包含一个或两个字节的列长度。只有当列的一部分存储在溢出页的外部，或者最大长度超过255字节而实际长度超过127字节时，才需要两个字节。

对于外部存储的列，2字节长度表示内部存储部分的长度加上指向外部存储部分的20字节指针。内部是768字节，所以长度是768+20。20字节的指针存储列的真实长度。

记录头后面跟着非NULL列的数据内容。

聚集索引中的记录包含所有用户定义列的字段。此外，还有一个6字节的事务ID字段和一个7字节的回滚指针字段。用于MVCC版本链回滚

MVCC：

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

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

在内部，对于非可变长度字符集，固定长度字符列（如CHAR（10））以固定长度格式存储。
VARCHAR列的尾部空格不会被截断。

在内部，对于utf8mb3和utf8mb4等可变长度字符集，InnoDB试图通过修剪尾部空格将CHAR（N）存储在N个字节中。如果CHAR（N）列值的字节长度超过N个字节，则尾部空格将修剪为列值字节长度的最大值。CHAR（N）列的最大长度是最大字符字节长度×N。

至少为CHAR（N）保留N个字节。在许多情况下，保留最小空间N可以在不导致索引页碎片的情况下就地执行列更新。相比之下，当使用REDUNDANT行格式时，CHAR（N）列占据最大字符字节长度×N。

大于或等于768字节的固定长度列被编码为可变长度字段，这些字段可以页外存储。例如，如果字符集的最大字节长度大于3，则CHAR（255）列可以超过768字节，就像utf8mb4一样。

2.5 DYNAMIC行格式

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

当用ROW_FORMAT=DYNAMIC创建表时，InnoDB可以完全离页存储长可变长度列值（对于VARCHAR、VARBINARY、BLOB和TEXT类型），聚集索引记录只包含一个指向溢出页的20字节指针。

大于或等于768字节的固定长度字段被编码为可变长度字段。例如，如果字符集的最大字节长度大于3，则CHAR（255）列可以超过768字节，就像utf8mb4一样。

列是否存储在页外取决于页大小和行的总大小。

当一行太长时，会选择最长的列进行页外存储，直到聚集索引记录适合B树页。

小于或等于40字节的TEXT和BLOB列存储在行中。

如果适合的话，DYNAMIC行格式可以保持将整行存储在索引节点中的效率（COMPACT和REDUNDANT格式也是如此），但DYNAMIC列格式避免了用大量长列数据字节填充B树节点的问题。

DYNAMIC行格式基于这样一种思想，即如果长数据值的一部分存储在页外，则通常最有效的方法是将整个值存储在页内。

使用DYNAMIC格式，较短的列可能会保留在B树节点中，从而最大限度地减少给定行所需的溢出页数。

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

使用DYNAMIC行格式的表可以存储在system表空间、file-per-table表空间和general表空间中

要在系统表空间中存储DYNAMIC表，请禁用innodb_file_per_table并使用常规的CREATE TABLE或ALTER TABLE语句，或者将tablespace[=]innodb_system table选项与CREATE TABLE或ALTERTABLE一起使用。

innodb_file_per_table变量不适用于general表空间

TABLESPACE[=]innodb_system table选项在系统表空间中存储DYNAMIC表时也不适用。

2.6 DYNAMIC存储特性

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

2.7 COMPRESSED行格式

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

COMPRESSED行格式使用与DYNAMIC行格式类似的页外存储内部细节，表和索引数据被压缩

并使用较小的页大小，因此需要考虑额外的存储和性能因素。

对于COMPRESSED行格式，KEY_BLOCK_SIZE选项控制聚集索引中存储了多少列数据，以及在溢出页上放置了多少。

有关COMPRESSED行格式的更多信息，请参阅第17.9节“InnoDB表和页面压缩”

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

使用COMPRESSED行格式的表可以在file-per-table表空间和general表空间创建创建。

system表空间不支持COMPRESED行格式。

要在每个表表空间的文件中存储COMPRESED表，必须启用innodb_file_per_table变

innodb_file_per_table变量不适用于常规表空间。

通用表空间支持所有行格式，但需要注意的是，由于物理页面大小不同，压缩表和未压缩表不能共存于同一通用表空间中。

有关更多信息，请参阅第17.6.3.3节“通用表空间”。

2.8 COMPRESSED存储特性

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

3.定义表格的行格式

InnoDB表的默认行格式由InnoDB_default_row_format变量定义，该变量的默认值为DYNAMIC。

当row_format表选项未明确定义或指定row_format=default时，将使用默认行格式。

可以使用CREATE table或ALTER 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选项包括DYNAMIC、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变量定义的行格式。例如，以下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。

有关更多信息，请参阅第17.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

在将现有表的行格式从REDUNDANT或COMPACT更改为DYNAMIC之前，请考虑以下潜在问题。

REDUNDANT和COMPACT行格式支持767字节的最大索引关键字前缀长度，而DYNAMIC和COMPRESED行格式支持3072字节的索引关键字前缀长度。

在复制环境中，如果innodb_default_row_format变量在源上设置为DYNAMIC，在副本上设置为COMPACT，则以下DDL语句（未明确定义行格式）在源上成功，但在副本上失败：

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

有关相关信息，请参阅“InnoDB限制”。ps 这里可以催更

如果源服务器上的innodb_default_row_format设置与目标服务器上的设置不同，则导入未明确定义行格式的表会导致架构不匹配错误。

有关更多信息，请参阅“导入InnoDB表”。

4.确定表的行格式

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

5.总结

说了一大堆，很难记住。

基本上可以确定行的结构由下面几个基本元素组成：

头，行ID，事务ID，回滚指针，用户数据。

至于列的指针数组，或者头部可变长区域，不一定一定存在于结构中，会根据你是什么行结构而变化。

关于溢出的数据页后面再补充。本博客本质是对官方文档的详读，后面会细化每个知识点的内容，评论可以催更。