当前位置：首页 > news >正文

MySQL数据存储详解

news 来源：原创 2025/7/31 23:38:46

1. MySQL 的数据存放位置

当我们在 MySQL 数据库中创建一个表时，MySQL 会在数据库对应的文件夹下生成三个文件：

1.1 `db.opt` 文件

是什么：
每个数据库都有一个 db.opt 文件，这个文件保存了数据库的配置信息。
作用：
记录数据库的字符集（character set）和排序规则（collation）。
位置：
位于数据库对应的目录中，比如 data/your_database/。
内容举例：
假如你的数据库字符集是 utf8mb4，db.opt 文件的内容可能是：

default-character-set=utf8mb4
default-collation=utf8mb4_general_ci

1.2 `t_order.frm` 文件

是什么：
frm 文件是表定义文件，保存了表的元数据（也就是表的结构信息）。
作用：
描述表的结构，例如表名、字段名、字段类型、索引等信息。
注意：
在 MySQL 8.0 及以后版本中，frm 文件被数据字典取代，表结构信息直接存储在系统表中。
举例：
如果我们创建表 t_order：

CREATE TABLE t_order (order_id INT PRIMARY KEY,order_date DATE
);

t_order.frm 文件会包含 order_id 是主键，order_date 是日期类型等结构信息。

1.3 `t_order.ibd` 文件

是什么：
ibd 文件是 InnoDB 存储引擎的表空间文件，用于存储表的数据和索引。
作用：
保存了表的实际数据（行记录）和相关索引。
特点：
- 每个表对应一个独立的 ibd 文件，称为独立表空间模式。
- 表空间文件包含了表的数据和所有索引。
- 默认情况下，ibd 文件位于与数据库同名的目录中，比如：data/your_database/t_order.ibd。

2. 表空间文件的结构

接下来，表空间文件（如 t_order.ibd）的存储结构是一个重点，它是由段（segment）、区（extent）、页（page） 和 行（row） 组成的。我们会按以下顺序逐步展开说明：

段（Segment）
区（Extent）
页（Page）
行（Row）

2.1 段（Segment）

是什么：

段是表空间中存储数据的基本单位。

每个表和索引在表空间中都有对应的段。
一个段可以看作是为了存储某一类数据（比如表的数据、索引、undo 日志等）而分配的逻辑存储结构。

类型：

段主要分为三种类型：

数据段（Data Segment）： 存储表的行数据。
索引段（Index Segment）： 存储表中索引的数据。
回滚段（Rollback Segment）： 存储事务中的 undo 日志，用于回滚或 MVCC（多版本并发控制）。

特点：

一个段是由多个 区（Extent） 组成的。
InnoDB 会动态地为段分配更多的区来扩展存储。

运作流程：

当一个表被创建时，InnoDB 会自动为该表分配一个初始的段。
如果表的数据量增大且需要更多空间，InnoDB 会自动为段分配更多的区。

2.2 区（Extent）

是什么：

区是段中的次级逻辑分区，用于存储页（Page）。

每个区的大小是固定的，等于 64 页。
默认情况下，每页大小为 16KB，因此一个区的大小为 64 * 16KB = 1MB。

作用：

作为 InnoDB 中分配存储的最小单位。
区的存在使得段可以按需动态扩展，而不是一次性占用大量磁盘空间。

分类：

用户数据区： 存储表的行数据。
系统区： 存储系统数据，例如数据字典、系统事务信息等。

分配策略：

InnoDB 表采用线性分配和按需分配策略：
1. 初始分配一个区。
2. 当一个区的存储用完后，会再分配一个新的区。
3. 如果表增长特别快，可能一次分配多个区。

2.3 页（Page）

是什么：

页是 InnoDB 中存储的最小单位。

每个页的默认大小为 16KB，也可以通过修改配置调整为 8KB 或 4KB。
页在区中顺序排列，用于存储实际的数据内容或辅助信息。

类型：

页可以存储不同类型的数据，主要包括：

数据页（Data Page）： 用于存储表中的行记录。
索引页（Index Page）： 用于存储 B+ 树的索引数据。
Undo 页： 用于事务的回滚操作。
系统页： 存储系统相关信息。
LOB 页： 用于存储大对象（如文本或二进制数据）。

页的作用：

每一页中都包含一个页头，用于记录该页的元信息（如页类型、下一个页的位置等）。
页中剩余部分用于存储实际的数据。

2.4 行（Row）

是什么：

行是 MySQL 中存储的最小数据单位，表示表中的一条记录。

存储方式：

每一行记录以行格式的形式存储在页中。
InnoDB 支持以下行格式：
1. Compact 格式： 紧凑存储，使用较少的空间。
2. Redundant 格式： 旧版本支持的格式，效率较低。
3. Dynamic 格式： 用于大对象存储，数据可能存放在外部页中。
4. Compressed 格式： 支持压缩存储，节省空间。

行的结构：

一行记录一般包括：

固定部分：元信息（如事务 ID、回滚指针等）。
可变部分：实际的列值。

行的特点：

行记录的长度可能因列的内容不同而变化。
每一行有一个唯一的行 ID（如果未设置主键，InnoDB 会自动生成一个隐式主键）。

总结与逻辑关系：

表空间 是一个文件（如 t_order.ibd），它包含了段。
段是逻辑存储的分区，按需扩展，由多个区组成。
区是存储分配的基本单位，固定大小为 1MB（64 页）。
页是存储的最小单位，默认大小为 16KB。
行是表中存储的基本数据记录，存储在页中。

3. InnoDB 行格式概述

InnoDB 支持以下 4 种行格式：

Redundant 行格式（冗余格式）：
- 最早期的行格式，数据存储相对冗余。
- 每列都存储长度信息，无论字段是否变长，占用更多空间。
Compact 行格式（紧凑格式）：
- 默认行格式，优化了空间利用率。
- 使用变长字段长度列表和 NULL 值列表，更高效存储变长字段和 NULL 值。
Dynamic 行格式（动态格式）：
- 主要用于存储大对象（如 TEXT、BLOB 类型）。
- 当列内容过大时，存储在外部页中，仅保留指针在主页。
Compressed 行格式（压缩格式）：
- 基于 Dynamic 格式，支持页级压缩，节省磁盘空间。
- 压缩的页无法直接访问，需要解压后再操作。

接下来重点介绍 Compact 行格式 的结构。

4. Compact 行格式详解

4.1 Compact 行格式的组成

Compact 行格式的每条记录分为两部分：

记录的额外信息
包括：
- 变长字段长度列表
- NULL 值列表
- 记录头信息
记录的真实数据
包括：
- 定义的字段值
- 三个隐藏字段（row_id、trx_id、roll_pointer）

4.2 记录的额外信息

（1）变长字段长度列表

是什么：
用于存储表中变长字段（如 VARCHAR、TEXT）的长度。
- 每个变长字段的实际长度需要通过这个列表进行记录。
- 长度值以字节为单位记录，并按照字段在表中的顺序逆序存放。
为什么要逆序存放？
- 原因 1：更高效地解析数据。
  在存储引擎从页中读取记录时，从右往左读取变长字段长度，可以在解析字段时更快获取完整信息。
- 原因 2：减少偏移计算。
  变长字段通常存储在记录的尾部，逆序存放长度值后，解析字段时不需要跳转大量位置，优化了解析性能。
每个表都有「变长字段长度列表」吗？
- 不一定有。
  如果一个表中没有变长字段（如全是 INT 或 CHAR 类型），则不会存储此列表。

（2）NULL 值列表

是什么：
用于表示记录中哪些字段的值是 NULL。
- 以比特位的形式记录，1 表示 NULL，0 表示非 NULL。
- 比特位排列顺序与表中字段定义的顺序一致。
每个表都有「NULL 值列表」吗？
- 不一定有。
  只有表中至少存在一个可以为 NULL 的字段时，才会有 NULL 值列表。
「NULL 值列表」是否固定占用 1 字节？
- 不是。
  - NULL 列表的大小取决于字段数。
  - 1 字节可存储 8 个字段的 NULL 信息。
  - 如果字段数超过 8 个，需要多字节存储。
举例：
如果一个表中有 9 个字段，且允许为 NULL 的字段全部为 NULL，那么：
- NULL 列表需要 2 字节：
  - 第 1 字节记录前 8 个字段的 NULL 信息。
  - 第 2 字节记录第 9 个字段的 NULL 信息。

（3）记录头信息

是什么：
每条记录都包含一段元信息，用于管理记录本身。
- 比如记录的删除标记、下一条记录的指针等。
主要字段：
- 记录类型：标识该记录是普通记录还是特殊标记（如主键记录）。
- 下一条记录指针：实现记录之间的链式存储。
- 删除标记：标识该记录是否被标记为删除。

4.3 记录的真实数据

记录真实数据部分除了我们定义的字段外，还有以下三个隐藏字段：

（1）row_id

是什么：
- 隐藏的自增主键，仅在表没有显式定义主键时自动生成。
作用：
唯一标识每条记录，作为隐式主键参与索引。

（2）trx_id

是什么：
- 表示最近一次修改记录的事务 ID。
作用：
用于 MVCC（多版本并发控制）和事务回滚。

（3）roll_pointer

是什么：
- 指向 undo 日志的指针，记录了当前记录之前的版本信息。
作用：
在事务回滚时，InnoDB 根据此指针定位到之前的版本，从而恢复数据。

4.4 总结

Compact 行格式通过紧凑的记录额外信息（变长字段长度列表、NULL 值列表、记录头信息）和高效的数据组织方式（包含隐藏字段），实现了更高的空间利用率和读写性能。

5. varchar(n) 的最大取值

在 MySQL 中，varchar(n) 的 n 最大取值受到 行记录总长度和 字符集的限制。MySQL 规定除了 TEXT 和 BLOB 这种大对象类型外，表中的所有列（不包括隐藏列和记录头信息）占用的字节总长度不能超过 65535 个字节。

5.1 单字段情况下

如果表中只有一个 varchar(n) 字段，可以直接计算 n 的最大值。在这个过程中，需要扣除一些额外字节：

变长字段长度列表的字节数：
- 每个变长字段的长度信息占 2 个字节。
NULL 值列表的字节数：
- 表中至少有一个字段时，NULL 列表占 1 个字节（不足 8 个字段仍占 1 字节）。

公式为：

1. ASCII 字符集
在 ASCII 字符集中，varchar 的每个字符占用 1 字节。

最大存储长度为：65535 - 2 - 1 = 65532。
即，n 的最大取值为：65532。

2. UTF-8 字符集
在 UTF-8 字符集中，一个字符最多占 3 字节（标准 UTF-8 的基础平面字符）。

最大存储长度为：65535 - 2 - 1 = 65532。
字符个数 n 的最大值为：

即，n 的最大取值为：21844。

5.2 多字段情况下

当表中有多个字段时，所有字段的存储长度需要满足以下条件：
所有字段长度总和+变长字段长度列表字节数+NULL 值列表字节数≤65535

变长字段长度列表字节数：
- 每个变长字段占用 2 字节，总字节数为字段数 ×2。
NULL 值列表字节数：
- NULL 值列表的大小为 ⌈字段数/8⌉字节。

举例：
假设一个表有两个 varchar(n) 字段，字符集为 ASCII，那么：

每个字段的存储长度为 n。
总长度需满足：

对于多字段表的计算逻辑更复杂，因为需要考虑字段的具体类型和字符集。

5.3 总结

单字段 varchar(n) 的 n 最大取值：
- ASCII 字符集：65532
- UTF-8 字符集：21844
多字段的情况下，需要考虑所有字段的总长度、变长字段长度列表字节数和 NULL 值列表字节数是否超过 65535 的限制。

6. 行溢出与处理方式

在 MySQL 的 InnoDB 存储引擎中，如果一条记录的数据量过大，无法完全存储在单个页（Page）中，就会发生 行溢出。这种情况下，超出的部分数据会存储到 溢出页（Overflow Page） 中，而行记录的主存储页中只保存指向这些溢出页的指针。

6.1 什么情况下会发生行溢出？

行溢出通常发生在以下情况中：

字段类型过大：
- 例如 TEXT 或 BLOB 类型的数据，可能会占用几 KB 或更大的空间。
字段数量过多：
- 如果表中包含多个大字段，整体记录的大小可能会超出页的限制。
表字符集不同：
- 在多字节字符集（如 UTF-8 或 UTF-16）中，每个字符占用的字节数比单字节字符集（如 ASCII）多，更容易触发溢出。

InnoDB 默认使用的页大小为 16KB，因此，单条记录的大小不能超过 16KB 减去记录头和其他系统信息的空间。

6.2 行溢出的处理机制

当发生行溢出时，InnoDB 会将超出部分的数据存放到「溢出页」中，并在原始页中存储一个指针指向溢出页。以下是具体处理方式：

2.1 长度较短的字段：保留部分内容

对于 TEXT 和 BLOB 类型的字段，InnoDB 并不会直接将整个字段移到溢出页，而是会尝试将字段的前 768 字节存储在主存储页中，其余部分存储在溢出页。

为什么存储 768 字节？
这是为了在查询时能够快速定位到字段的部分内容，减少对溢出页的读取需求，提升性能。

2.2 溢出页结构：链式存储

溢出页之间是通过指针串联起来的，形成一个链表结构。当需要访问溢出部分的数据时，InnoDB 会沿着指针逐页读取。

每个溢出页也为 16KB，并按照页结构存储数据。

6.3 行溢出的访问性能影响

行溢出的处理虽然解决了大数据量记录存储的问题，但也会对性能造成一定的影响：

增加随机 I/O
- 读取一条完整记录时，除了主存储页，还需要访问多个溢出页，会引发多次随机磁盘 I/O，增加了读取的开销。
内存缓存受限
- InnoDB 的缓冲池缓存的是整个页的数据。当溢出页过多时，会占用更多的缓冲池空间，可能导致缓存效率下降。
写操作复杂度提升
- 更新溢出字段时，不仅要修改主存储页，还需要更新溢出页的内容，增加了写操作的复杂性。

6.4 如何避免行溢出？

选择合适的字段类型：
- 如果数据大小可控，尽量避免使用 TEXT 或 BLOB 类型，改用 VARCHAR 或其他固定长度的字段类型。
拆分大字段：
- 将表中可能导致溢出的字段单独拆分到另一张表中，通过外键关联，减少主表的行记录大小。
优化页大小：
- 如果数据库支持更大的页大小（如 32KB 或 64KB），可以通过调整 innodb_page_size 参数来减少行溢出的概率。
字符集优化：
- 使用占用空间较小的字符集（如 latin1）来存储文本数据，也能有效减小字段大小。

6.5 总结

行溢出是 MySQL 在应对大数据量存储时的一种机制。它通过将超出的数据移到溢出页，确保主存储页的记录不会超过页大小限制。但行溢出的代价是性能的下降，因此在设计表结构时，应尽量避免行溢出现象的发生。

1. MySQL 的数据存放位置

1.1 db.opt 文件

1.2 t_order.frm 文件

1.3 t_order.ibd 文件

2. 表空间文件的结构

2.1 段（Segment）

2.2 区（Extent）

2.3 页（Page）

2.4 行（Row）

总结与逻辑关系：

3. InnoDB 行格式概述

4. Compact 行格式详解

4.1 Compact 行格式的组成

4.2 记录的额外信息

4.3 记录的真实数据

4.4 总结

5. varchar(n) 的最大取值

5.1 单字段情况下

5.2 多字段情况下

5.3 总结

6. 行溢出与处理方式

6.1 什么情况下会发生行溢出？

6.2 行溢出的处理机制

6.3 行溢出的访问性能影响

6.4 如何避免行溢出？

6.5 总结

相关文章：

1.1 `db.opt` 文件

1.2 `t_order.frm` 文件

1.3 `t_order.ibd` 文件