MySQL 一行记录是怎么存储的

文章目录

- 1. 文件存放目录 && 组织
- 2. 表空间文件的结构
- 3. InnoDB 行格式
- 4. Compact 行格式
- - 记录的额外信息
  - - 1. 变长字段长度列表
    - 2. NULL 值列表
    - 3. 记录头信息
  - 记录的真实数据
  - - 1. 定义的表字段
    - 2. 三个隐藏字段
- 5. varchar(n) 中 n 最大取值为多少？
- 6. 行溢出后，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 可用字节

文件名	存储内容	文件名后缀全称
`db.opt`	配置（默认字符集和字符校验规则）	Option
`dept.frm`	表结构定义	Format
`dept.ibd`	表数据	InnoDB Data

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操作，提升查询效率。