MySQL 行格式详解

发布时间:2023年12月21日

在 MySQL 中,表的行格式(Row Format)决定了其数据行的物理存储方式。这种存储方式会影响查询和数据操作语句(DML)的性能。当更多数据行能够存储在单个磁盘页上时,查询和索引查找可以运行得更快,缓冲池(Buffer Pool)需要更少的缓存内存,且更新数据所需的输入/输出(I/O)也会相应减少。

每个表中的数据被划分为多个页面,组成表的页面以 B-tree 索引的树状数据结构排列。表数据和二级索引都使用这种结构。代表整个表的 B-tree 索引被称为聚簇索引(Clustered Index),它按主键列进行组织。聚簇索引数据结构的节点包含了行中所有列的值。而二级索引结构的节点则包含索引列和主键列的值。

对于可变长列来说,情况略有不同:过长的可变长列值无法存储在 B-tree 页面上,会被存放到独立分配的磁盘页面,称为溢出页面(Overflow Pages)。这些列被称为离页列(Off-page Columns)。离页列的值存储在溢出页面的单向链表中,每个此类列都有自己的一个或多个溢出页面的链表。根据列长度的不同,所有内容或部分前缀可能会存储在 B-tree 中,以避免浪费存储空间并减少读取单独页面的需求。

InnoDB 存储引擎支持四种行格式:REDUNDANT、COMPACT、DYNAMIC 和 COMPRESSED。

行格式概览

行格式紧凑存储特性变长列增强存储大索引键前缀支持压缩支持支持的表空间类型
REDUNDANTsystem, file-per-table, general
COMPACTsystem, file-per-table, general
DYNAMICsystem, file-per-table, general
COMPRESSEDfile-per-table, general

以下是关于行格式存储特性的描述以及如何定义和确定表的行格式。

REDUNDANT 行格式

REDUNDANT 格式与旧版本的 MySQL 兼容。使用 REDUNDANT 行格式的表将可变长列值的前 768 字节存储在 B-tree 节点内的索引记录中,剩余部分存储在溢出页面上。大于或等于 768 字节的固定长度列编码为可变长度列,可能存储在离页位置。例如,在 utf8mb4 编码下,CHAR(255) 列可能超过 768 字节。

如果列值不超过 768 字节,则不使用溢出页面,这可能会减少 I/O 开销,因为该值完全存储在 B-tree 节点中。这适用于较短的 BLOB 列值,但可能会导致 B-tree 节点填满数据而非关键值,降低效率。拥有许多 BLOB 列的表可能导致 B-tree 节点过度充满,包含的行数太少,使整个索引的效率低于行更短或者将列值存储在离页位置的情况。

COMPACT 行格式

与 REDUNDANT 行格式相比,COMPACT 行格式减少了约 20% 的行存储空间,但某些操作的 CPU 使用量会增加。如果负载受缓存命中率和磁盘速度限制,COMPACT 格式可能会更快。如果负载受 CPU 速度限制,COMPACT 格式可能会更慢。

COMPACT 行格式同样将可变长列值的前 768 字节存储在 B-tree 节点内的索引记录中,剩余部分存储在溢出页面上。固定长度大于或等于 768 字节的列编码为可变长度列,可能存储在离页位置。

DYNAMIC 行格式

DYNAMIC 行格式提供与 COMPACT 类似的存储特性,但对长可变长度列增加了增强的存储能力,并支持大型索引键前缀。

当表创建时指定 ROW_FORMAT=DYNAMIC,InnoDB 将长可变长度列值(VARCHAR、VARBINARY、BLOB 和 TEXT 类型)完全存储在离页位置,聚簇索引记录仅包含指向溢出页面的 20 字节指针。固定长度大于或等于 768 字节的字段被编码为可变长度字段。

是否将列存储在离页位置取决于页面大小和行的总大小。如果行太长,最长的列被选为离页存储,直到聚簇索引记录适合 B-tree 页面。小于或等于 40 字节的 TEXT 和 BLOB 列被内联存储。

DYNAMIC 行格式通过尽可能保留较短的列在 B-tree 节点内,减少了所需溢出页面的数量,从而保持了存储效率。

COMPRESSED 行格式

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

定义表的行格式

InnoDB 表的默认行格式由 innodb_default_row_format 变量定义,默认值为 DYNAMIC。未明确定义 ROW_FORMAT 表选项或指定 ROW_FORMAT=DEFAULT 时,将使用默认行格式。

可以使用 CREATE TABLEALTER TABLE 语句中的 ROW_FORMAT 表选项显式地定义表的行格式。例如:

CREATE TABLE t1 (c1 INT) ROW_FORMAT=DYNAMIC;

显式定义的 ROW_FORMAT 设置会覆盖默认行格式。指定 ROW_FORMAT=DEFAULT 相当于使用隐式默认值。

确定表的行格式

要确定表的行格式,可以使用 SHOW TABLE STATUS

mysql> SHOW TABLE STATUS LIKE 't1'\G

或者查询 Information Schema 中的 INNODB_TABLES 表:

mysql> SELECT NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1';

以上内容简述了 MySQL 中的各种行格式,包括它们的存储特性,如何定义和确定表的行格式,以及各个行格式之间的差异。在数据库设计和优化时,合理选择行格式对于提升系统性能具有重要意义。

文章来源:https://blog.csdn.net/jkzyx123/article/details/135132490
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。