InnoDB行格式

vonlinee

已于 2024-09-25 21:04:07 修改

阅读量1k

点赞数 12

分类专栏： MySQL 文章标签： mysql

于 2024-09-17 21:30:38 首次发布

本文链接：https://blog.csdn.net/qq_40926260/article/details/142315614

版权

MySQL 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

行大小限制
- 行溢出
行格式
指定行格式
查看行格式

表的行格式决定了其行的物理存储方式，反过来又会影响查询和DML操作的性能。随着单个磁盘Page中可以容纳更多的行，查询和索引查找可以更快地工作，缓冲池中所需的缓存更少，写入更新值所需的I/O也更少。

每个表中的数据分布在多个Page中。构成每个表的所有Page以B+树数据结构进行排列。表数据和辅助索引都使用这种结构。表示整个表的B树索引称为聚集索引，它根据主键列进行组织。聚集索引的节点包含行中所有列的值，而辅助索引ss的节点包含索引列和主键列的值。

行大小限制

给定表的最大行大小由几个因素决定：

MySQL表的行最大只能占65535字节，这个限制是和存储引擎无关的，即使存储引擎能够支持更大的行。BLOB和TEXT列只会占行大小限制的9到12个字节，因为它们的内容与行的其余部分分开存储。

# InnoDB表
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;

# MyISAM表
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g VARCHAR(6000)) ENGINE=MyISAM CHARACTER SET latin1;

这两条sql都会报错：ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

解决办法错误信息中也提到了：使用TEXT或者BLOB数据类型

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g TEXT(6000)) ENGINE=MyISAM CHARACTER SET latin1;

存储可变长度的列时还需要存储长度信息，比如VARCHAR 的最大字节长度为 65,535 字节（约 64 KB），包括存储长度的前缀。这个长度包括字符数据本身和用于存储该长度的字节数。如果字符数据长度小于 255 字节，长度前缀占用 1 个字节。如果字符数据长度大于等于 255 字节，长度前缀占用 2 个字节。
例如VARCHAR(255) CHARACTER SET utf8mb3 会使用2个字节存储长度，所以每列的大小占用的空间最大为 255 * 3 + 2 = 767 字节（长度最多255个字符，utf8mb3编码一个字符占3个字节）

下面这个创建表的语句会失败，因为尽管列长度在65535字节的最大长度范围内，但需要另外两个字节来记录长度，这导致行大小超过65535字节

mysql> CREATE TABLE t2
       (c1 VARCHAR(65535) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;

InnoDB表也有最大行大小限制，针对存储在Page中的数据，对于4KB、8KB、16KB和32KB的innodb_page_size设置，行大小的最大值略小于半个Page的大小。例如Page大小是16KB，则行大小的最大值略小于8KB。而对于64KB的页面，有点特殊，最大行大小略小于16KB，不是略小于一半。如果行包含了可变长度列，并且该行的长度超过InnoDB的最大行大小，InnoDB会选择将可变长度列存储在另外的Page中，直到该行符合InnoDB行大小限制。对于这些列中，具体有多少数据存储在当前这个Page，多少存储在页外，这个因行格式而异。

mysql> CREATE TABLE t4 (
       c1 CHAR(255),c2 CHAR(255),c3 CHAR(255),
       c4 CHAR(255),c5 CHAR(255),c6 CHAR(255),
       c7 CHAR(255),c8 CHAR(255),c9 CHAR(255),
       c10 CHAR(255),c11 CHAR(255),c12 CHAR(255),
       c13 CHAR(255),c14 CHAR(255),c15 CHAR(255),
       c16 CHAR(255),c17 CHAR(255),c18 CHAR(255),
       c19 CHAR(255),c20 CHAR(255),c21 CHAR(255),
       c22 CHAR(255),c23 CHAR(255),c24 CHAR(255),
       c25 CHAR(255),c26 CHAR(255),c27 CHAR(255),
       c28 CHAR(255),c29 CHAR(255),c30 CHAR(255),
       c31 CHAR(255),c32 CHAR(255),c33 CHAR(255)
       ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET latin1;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help.
In current row format, BLOB prefix of 0 bytes is stored inline.

行溢出

行溢出是指当某个记录的某个字段(varchar、text、blob等类型)的值长度过长、数据量过大，会导致一个页中放不下一条记录，为此在compact、redundant行格式中，如果该记录某字段中数据量过多时，则在该记录的数据内容的相应字段处只存储该字段值前768个字节的数据和一个指向存储剩余数据的其他页的地址，该地址通常占用20个字节

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

行格式

InnoDB支持4种行格式
在这里插入图片描述

不同的存储格式使用不同数量的Page Header和Page Tailer数据，这会影响行的可用存储量。因为一个Page中除了存储行数据，还有其他一些元数据信息

REDUNDANT

对于可变长度列，会存储开始的768字节在索引的Page节点上，剩下的存储在overflow page中。对于固定长度的列，只要超过了768字节，也会以这种方式存储。如果少于768字节，那么不会使用overflow page，这样可以减少IO次数，因为所有数据都在B+树节点上，这适用于相对较短的BLOB列值，但可能会导致B+树节点填充数据而不是key，从而降低其效率。具有许多BLOB列的表可能会导致B树节点变得太满，并且包含的行太少，使整个索引的效率低于行较短或列值off-page存储的情况。

每个索引记录包含6字节的header，用于连接记录以及行锁
聚簇索引包含所有用户定义的列，除此之外，还有6字节的事务ID字段，7字节的回滚指针（用于事务回滚）
如果没有为表定义主键，则每个聚集索引记录还包含一个6字节的行ID字段
每个辅助索引记录都包含为聚集索引键定义的所有主键列，这些主键列不在辅助索引中
NULL值处理

Record Directory

每条记录都有一个记录目录，其中包含有助于管理记录的元数据，包括有关NULL值的信息。此元数据允许系统快速检查值是否为NULL，而无需扫描实际数据。

记录目录是一个指针数组，包含指向记录每个字段的指针。如果记录中字段的总长度小于128字节，则指针为1字节；否则为两个字节。指针指向的区域是记录的数据部分。这是数据库用于高效管理记录的开销的一部分，无论列是可变长度还是固定长度。

可变长度列NULL值处理

当列的长度可变时，存储引擎仅根据实际存储的数据分配空间。对于NULL值，记录的数据部分没有预留空间（即它在实际数据部分占用0个字节）。由于可变长度列已经具有跟踪数据长度的机制（如长度前缀），因此NULL只是意味着没有数据要存储。NULL值可以通过记录目录中的标志有效地处理，因此不需要在数据部分分配额外的空间。这种方法最大限度地减少了可变长度列中NULL值的浪费空间，提高了存储效率。

固定长度列NULL值处理

对于固定长度的列，MySQL保留列的完整固定大小，即使该值为NULL。例如，如果CHAR(10)列为NULL，则记录的数据部分仍将保留10个字节。

为什么在固定长度列中为NULL值保留空间？

避免页分裂：通过保留全部空间，系统可以就地将NULL值更新为非NULL值（无需移动或重新排序数据），不会改变行或页的结构。这避免了额外的操作，如移动数据或重新定位记录，否则会导致索引页碎片化。
数据一致性：固定长度列是为可预测、一致的数据结构而设计的。为NULL值保留空间可确保每一行占用相同的空间，这允许快速直接的数据访问，特别是在性能至关重要的系统中。这要求在设计之初就需要根据实际情况确定是否应该选择固定长度列

固定长度字符列如CHAR(10)以固定长度格式存储，并且VARCHAR列中的尾随空格不会被截断。

COMPACT

COMPACT 行格式与REDUNDANT行格式相比，行存储空间减少了大约 20%，但代价是某些操作的 CPU 使用量有所增加。如果你的工作负载主要受到缓存命中率和磁盘速度的限制，COMPACT 格式可能会更快。如果工作负载受限于 CPU 速度，COMPACT 格式可能会较慢。

1.每个索引记录包含一个 5 字节的头部，可能还会有一个可变长度的头部。这个头部用于将连续的记录连接在一起，同时也可用于行锁。
2.如果包含NULL列，那么记录的头部有个可变长度部分。它包含一个bit矢量(即一个bit数组)来表示是否包含NULL列。索引中有N列可以为NULL，那么该bit矢量占用CEILING(N/8)字节，例如如果有9~16列可以为NULL，那么该bit矢量占用2字节。NULL列不占用此向量位以外的空间。除此之外可变长度部分还包含列的长度信息，占1或2字节，视列的最大长度而定。
在这里插入图片描述
对于不为NULL的可变长度列，不仅需要存储该列的实际数据，还需要额外存储该列数据的长度信息(即占用的字节数)，因此对于每个非NULL可变长度字段，记录头包含一个或两个字节的列长度。只有当列的一部分存储在溢出页中的外部，或者最大长度超过255个字节，实际长度超过127个字节时，才需要两个字节。
对于外部存储的列，2字节长度表示内部存储部分的长度加上指向外部存储部分的20字节指针。内部为768字节，因此长度为768+20。20字节指针存储列的真实长度。
3.record header后面是非NULL列的数据内容
4.和REDUNDANT格式一样，聚簇索引包含所有用户定义的列，除此之外，还有6字节的事务ID字段，7字节的回滚指针（用于事务回滚）。如果没有为表定义主键，则每个聚集索引记录还包含一个6字节的行ID字段
如果所有列都是NOT NULL的并且有固定长度，则记录的头部没有可变长度部分，如下图所示：
在这里插入图片描述

行溢出处理

使用 COMPACT 行格式的表会将可变长度列的前 768 字节存储在 B 树节点的索引记录中，其余部分存储在溢出页上。长度大于或等于 768 字节的定长列会被编码为可变长度列，并可以存储在页外。如果列的值小于或等于 768 字节，则不会使用溢出页，这可能会带来一些 I/O 节省，因为该值完全存储在 B 树节点中。这对于相对较短的 BLOB 列值效果很好，但可能会导致 B 树节点充满数据而非键值，降低其效率。拥有许多 BLOB 列的表可能会导致 B 树节点过于饱满，包含的行数太少，从而使整个索引的效率低于行较短或列值存储在页外的情况。

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

比如

CREATE TABLE t (
	a VARCHAR ( 36 ), b CHAR ( 5 ),
	PRIMARY KEY (a ), INDEX idx_b (b)
) ENGINE = INNODB CHARACTER SET latin1;

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

DYNAMIC

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

当使用ROW_FORMAT=DYNAMIC创建表时，InnoDB可以完全页外存储长可变长度列值（用于VARCHAR、VARBINARY、BLOB和TEXT类型），聚集索引记录中仅包含指向溢出页的20字节指针。大于或等于768字节的固定长度字段被编码为可变长度字段。例如，如果字符集的最大字节长度大于3，则CHAR(255)列可以超过768个字节，就像utf8mb4一样。

列是否存储在页外取决于Page大小和行的总大小。当一行太长时，会选择最长的列进行页外存储，直到聚集索引记录适合B树的Page。小于或等于40字节的TEXT和BLOB列存储在行中。

DYNAMIC行格式保持了在索引节点中存储整行的效率（如COMPACT和REDUNDANT格式），但DYNAMIC行格式化避免了用大量长列数据字节填充B树节点的问题。DYNAMIC行格式基于这样一种思想，即如果长数据值的一部分存储在页外，那么将整个值存储在Page下通常是最有效的。使用DYNAMIC格式，较短的列可能会保留在B树节点中，从而最大限度地减少给定行所需的溢出页的数量。

DYNAMIC行格式支持最多3072字节的index key prefix

使用DYNAMIC行格式的表可以存储在系统表空间、file-per-table表空间和general表空间中。要在系统表空间中存储DYNAMIC表，需禁用innodb_file_per_table并使用常规的CREATE table或ALTER table语句，或者将tablespace[=]innodb_system表选项与CREATE table或ALTER table一起使用。innodb_file_per_table变量不适用于general表空间，也不适用于使用TABLESPACE[=]innodb_system table选项在系统表空间中存储DYNAMIC表的情况。

COMPRESSED

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

COMPRESSED行格式使用与DYNAMIC行格式类似的页外存储方式，并从压缩的表和索引数据以及使用较小的页大小中考虑额外的存储和性能因素。使用COMPRESSED行格式时，KEY_BLOCK_SIZE选项控制聚集索引中存储的列数据量，以及溢出页上放置的数据量。

https://dev.mysql.com/doc/refman/8.0/en/innodb-compression.html

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

使用COMPRESSED的表可以创建在file-per-table tablespaces或者general tablespaces.system表空间不支持不支持COMPRESSED行格式。存储COMPRESSED表在file-per-table表空间,，必须开启innodb_file_per_table 。

innodb_file_per_table变量不适用于general表空间。general表空间支持所有行格式，但需要注意的是，由于Page大小不同，压缩和未压缩的表不能在同一个general表空间中共存。

指定行格式

-- 创建数据表时,显示指定行格式
CREATE TABLE t1 (c1 INT) ROW_FORMAT=DYNAMIC;
-- 修改行格式
ALTER TABLE 表名 ROW_FORMAT=行格式名称;

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

CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT) ROW_FORMAT=DEFAULT;

默认行格式由变量innodb_default_row_format控制，默认值是DYNAMIC。可以修改默认行格式

mysql> SET GLOBAL innodb_default_row_format=DYNAMIC;

可设置的值只有3种：DYNAMIC、COMPACT和REDUNDANT。由于COMPRESSED行格式不支持在系统表空间中使用，不能定义为默认值，它只能在CREATE TABLE或者ALTER TABLE语句中使用。

注意事项

REDUNDANT和COMPACT 行格式支持index key prefix最大长度为767字节，而DYNAMIC和COMPRESSED支持最大3072字节

在主从复制场景中，如果innodb_default_row_format变量在源数据库上设置为DYNAMIC，在副本上设置为COMPACT，则以下DDL语句在源上成功，但在副本上失败：

CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 VARCHAR(5000), KEY i1(c2(3070))); # 未显式定义行格式

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

查看行格式

通过SHOW TABLE STATUS语句进行查看：SHOW TABLE STATUS IN ${数据库名} LIKE '表名pattern'\G，详细使用参考：https://dev.mysql.com/doc/refman/8.0/en/show-table-status.html

mysql> SHOW TABLE STATUS IN mysql_learn LIKE 't'\G;
*************************** 1. row ***************************
           Name: t
         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: NULL
    Create_time: 2024-09-17 18:12:21
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

ERROR:
No query specified

或者查看INFORMATION_SCHEMA.INNODB_TABLES表

mysql> SELECT NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='mysql_learn/t';
+---------------+------------+
| NAME          | ROW_FORMAT |
+---------------+------------+
| mysql_learn/t | Dynamic    |
+---------------+------------+
1 row in set (0.00 sec)