MySQL行格式（row format）

MySQL行格式（row format）

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

每个表中的数据都分为页。组成每个表的页被排列在称为 B 树索引的树数据结构中。表数据和二级索引都采用这种类型的结构。表示整个表的 B 树索引称为聚集索引，它根据主键列进行组织。聚集索引数据结构的节点包含行中所有列的值。二级索引结构的节点包含索引列和主键列的值。

可变长度列是列值存储在 B 树索引节点中的规则的一个例外。太长而无法放入 B 树页面的可变长度列存储在单独分配的磁盘页面（称为溢出页面）上。此类列称为页外列。页外列的值存储在溢出页的单链接列表中，每个此类列都有其自己的一个或多个溢出页列表。根据列长度，可变长度列值的全部或前缀存储在 B 树中，以避免浪费存储空间和必须读取单独的页面。

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

REDUNDANT 格式

REDUNDANT 是 InnoDB 最早的行格式，主要用于早期版本的 MySQL。这种格式的特点是存储更多的元数据，如额外的系统信息和字段的长度信息，使得每行数据占用更多的空间。虽然 REDUNDANT 行格式提供了良好的向后兼容性，但它的存储效率低下。在这种行格式中，即使字段内容为空，也会为其分配存储空间。尽管现在已经不再推荐使用此格式，但了解其历史意义对理解 MySQL 的发展仍然有一定价值。

COMPACT 格式

COMPACT 行格式是对 REDUNDANT 的改进，它减少了每行的元数据量，并优化了空间使用。在 COMPACT 格式中，NULL值的列不会占用存储空间，且对可变长度字段的处理更加高效。这种行格式通过不保存前缀长度来减少存储需求，并且仅在必要时存储变长字段的实际长度。因此，COMPACT 格式适用于包含许多可变长度列（如 VARCHAR、TEXT 或 BLOB 类型）的表，能显著提高数据的存储效率和访问速度。

DYNAMIC 格式

DYNAMIC 行格式是 InnoDB 的一种更加灵活的行格式，特别适用于包含大量变长字段的表。与 COMPACT 格式相比，DYNAMIC 格式可以更高效地处理长文本和二进制数据，因为它将过长的数据存储到专门的溢出页中。这种设计允许基本记录页面更紧凑，从而减少了因为频繁访问大数据而导致的性能下降。因此，对于那些包含大字段或者需要频繁更新这些字段的应用，DYNAMIC 行格式提供了更高的效率和更好的性能。

COMPRESSED 格式

COMPRESSED 行格式结合了 DYNAMIC 格式的特性，并引入了数据压缩功能，旨在进一步减少磁盘占用和提高IO效率。这种格式特别适合那些磁盘空间较为昂贵或I/O速度较慢的环境。通过压缩数据，可以在磁盘上存储更多的数据，同时由于读取的数据量减少，可以提高数据访问速度。不过，需要注意的是，数据的压缩和解压缩会消耗额外的CPU资源，因此在CPU受限的环境下使用 COMPRESSED 格式可能会对性能产生负面影响。

QA

如何查看表的行格式？

要查看 MySQL 中表的行格式，你可以使用 SQL 查询来获取相关信息。以下是几种常用的方法：

1. 使用 SHOW TABLE STATUS 命令:
   这个命令可以提供包括行格式在内的表的详细状态信息。例如，要查看名为 mytable 的表的行格式，你可以执行：

   SHOW TABLE STATUS LIKE 'mytable';
   

   在返回的结果中，会有一个 Row_format 的列显示该表的行格式。

2. 查询 information_schema.tables:
   INFORMATION_SCHEMA 是 MySQL 提供的一个数据库，其中包含了关于其他所有数据库的元数据。你可以从这里查询特定表的行格式：

   SELECT TABLE_NAME, ROW_FORMAT 
   FROM information_schema.tables 
   WHERE table_schema = 'your_database_name' AND table_name = 'your_table_name';
   

   将 your_database_name 和 your_table_name 替换为你的数据库名和表名。

3. 使用 SHOW CREATE TABLE 命令:
   这个命令会显示用于创建指定表的完整 SQL 语句，其中包括行格式的信息。执行：

   SHOW CREATE TABLE your_table_name;
   

   输出结果中将包括行格式设置，如果在创建表时指定了行格式的话。

如何定义表的行格式？

在 MySQL 中，你可以在创建表时或者修改现有表的定义来指定行格式。这可以通过 ROW_FORMAT 选项实现，这个选项可以在 CREATE TABLE 或 ALTER TABLE 语句中使用。

创建表时定义行格式

当你创建一个新表时，可以直接在 CREATE TABLE 语句中指定行格式。以下是一个示例，展示如何在创建表时设置行格式为 DYNAMIC：

CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(255)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

在这个例子中，example 表将使用 InnoDB 存储引擎和 DYNAMIC 行格式。你可以根据需要将 ROW_FORMAT 的值改为 COMPACT, REDUNDANT, COMPRESSED 等。

修改现有表的行格式

如果你想改变一个已经存在的表的行格式，可以使用 ALTER TABLE 语句。以下是如何将一个现有表的行格式改为 COMPRESSED 的示例：

ALTER TABLE example ROW_FORMAT=COMPRESSED;

这条语句会将 example 表的行格式改为 COMPRESSED。修改表的行格式可能会触发表的重建，这可能需要一定的时间完成，特别是对于数据量大的表。

注意事项

• 在选择行格式时，需要确保你的 MySQL 服务器支持你选择的行格式。某些较旧的 MySQL 版本可能不支持所有现代行格式。
• 行格式的选择应考虑到数据的特点和访问模式，以最大化查询性能和存储效率。
• 修改大表的行格式可能会导致较长时间的服务中断，因为修改过程中表可能会不可用。
• 使用 COMPRESSED 行格式时，还可以结合使用 KEY_BLOCK_SIZE 参数来指定压缩的粒度。

MySQL 默认的行格式是什么？

从 MySQL 5.7 版本开始，InnoDB 的默认行格式是 DYNAMIC。这一变化旨在优化数据存储的灵活性和效率，特别是对于包含大量可变长度字段的表。DYNAMIC 格式能够有效地处理较长的文本和二进制数据，并将过长的字段值存储在外部页中，从而提高页的空间利用率和整体性能。

行格式和存储引擎有关系吗？具体是什么关系？

行格式和存储引擎之间的关系非常紧密，因为行格式是由存储引擎决定并实现的，它影响了数据的物理存储方式、访问速度以及索引的结构和效率。

在 MySQL 中，不同的存储引擎如 InnoDB、MyISAM、MEMORY 等支持不同的行格式，且每种存储引擎的实现和优化目标不同，因此它们提供的行格式也各不相同。例如：

• InnoDB：这是 MySQL 默认的存储引擎，支持 REDUNDANT、COMPACT、DYNAMIC 和 COMPRESSED 等行格式。InnoDB 优化了事务处理、数据完整性和并发控制，其行格式选择能够影响数据的压缩度、存储效率以及事务处理性能。

• MyISAM：另一种较早的存储引擎，主要支持固定（FIXED）和动态（DYNAMIC）行格式。固定行格式在处理静态数据时非常高效，因为所有记录占用相同的字节数，直接读取特定记录变得非常快速。而动态行格式则允许记录占用变化的字节数，更适合存储有大量可变长度字段的数据。

行格式对性能的影响主要体现在数据的读写效率、存储空间的使用以及维护数据完整性和一致性的方式上。选择正确的行格式可以优化存储空间的使用、加速查询响应时间和提升事务处理性能，但这也需要考虑具体的应用场景和数据特性。

因此，行格式和存储引擎的关系基本上是存储引擎定义了支持哪些行格式，并通过行格式的具体实现来达到其性能和功能目标。在选择存储引擎和行格式时，开发者和数据库管理员需要根据具体需求做出选择，以实现最佳的性能和存储效率。

参考链接

• InnoDB存储引擎：MySQL 官方文档
• 行格式说明：MySQL 行格式详解
• 数据压缩技术：InnoDB压缩技术