MySQL 一行记录是怎么存储的

1. 文件存放目录 && 组织

MySQL 数据库的文件存放目录：

mysql> show variables like 'datadir';
+---------------+----------------------------------+
| Variable_name | Value                            |
+---------------+----------------------------------+
| datadir       | D:\Softwares\mysql\install\Data\ |
+---------------+----------------------------------+
1 row in set, 1 warning (0.01 sec)

数据库名：db01, 表名：dept

D:\Softwares\mysql\install\Data>cd db01

D:\Softwares\mysql\install\Data\db01>dir
 驱动器 D 中的卷是 Data
 卷的序列号是 B89F-809E

 D:\Softwares\mysql\install\Data\db01 的目录

2024/04/27  16:57    <DIR>          .
2024/07/13  00:30    <DIR>          ..
2024/04/25  19:43                61 db.opt
2024/04/27  16:57             8,636 dept.frm
2024/04/27  16:58            98,304 dept.ibd
               3 个文件        107,001 字节
               2 个目录 79,156,989,952 可用字节

2. 表空间文件的结构

表空间由段、区、页、行组成

段，segment：包括索引段（B+树非叶子节点）、数据段（也字节带你）和回滚段（回滚数据的区的集合）

区，extent：索引分配空间单位，默认区大小为 1MB（方便B+树双向链表相邻的页物理位置也相邻，使用顺序IO避免随机IO）

页，page：InnoDB 的数据读写单位，默认页大小为 16KB

行，row：数据库中的记录按行存放

3. InnoDB 行格式

• Redundant：MySQL 5.0 之前，不紧凑

• Compact：MySQL 5.0 之后，紧凑（一页可以存放更多记录行）

• Dynamic：MySQL 5.1 之后，基于 Compact 稍作改进

• Compressed：基于 Compact 稍作改进

4. Compact 行格式

记录的额外信息

1. 变长字段长度列表

• varchar

• 逆序

• 不是必须的

2. NULL 值列表

• 以字节为单位，但每一位记录一个 NULL 列（1）

• 逆序

• 不是必须的

3. 记录头信息

• delete_mask: 1/0, 标记数据行是否被删除

• next_record: 下一条记录的位置，记录之间通过链表组织

• record_type: 记录类型，0=普通记录（B+树叶子节点记录），1=B+树非叶子节点记录，2=最小记录，3=最大记录

• …

记录的真实数据

1. 定义的表字段

2. 三个隐藏字段

• row_id

  • 非必须，若建表指定主键或唯一约束列，则无此字段

  • 6 字节

• trx_id

  • 事务 id，表示数据记录对应的生成事务编号

  • 必须的

  • 6 字节

• roll_pointer

  • 上一版本的指针，与 trx_id 用于 MVCC 机制

  • 必须的

  • 7 字节

5. varchar(n) 中 n 最大取值为多少？

MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节。

• 这里所有的列包括记录的额外信息（变长字段长度列表、NULL 值列表）

• ascii 字符集， 1 个字符占用 1 字节

• UTF-8 字符集下，一个字符最多需要三个字节

• 分单字段和多字段讨论

6. 行溢出后，MySQL 是怎么处理的？

• Situation

  MySQL 中磁盘和内存交互的基本单位是页，一个页的大小一般是 16KB，也就是 16384字节，而一个 varchar(n) 类型的列最多可以存储 65532字节，一些大对象如 TEXT、BLOB 可能存储更多的数据，这时一个页可能就存不了一条记录。这个时候就会发生行溢出，多的数据就会存到另外的「溢出页」中。

• Task & Action

  • Compact 格式

    在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。

  • Dynamic / Compressed 格式

    采用完全的行溢出方式，记录的真实数据处不会存储该列的一部分数据，只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中

• 思考

  • 性能和空间的权衡

  • 数据的随机访问优化：将大对象分片存储在溢出页中，可以在需要时进行局部读取，而不必每次都读取整个大对象，这样可以减少I/O操作，提升查询效率。

参考

MySQL 一行记录是怎么存储的？ | 小林coding