外观
02 - 数据库为什么不能直接把数据写进普通文件
摘要与阅读定位
把订单逐行写入 JSON 文件,确实能保存数据。数据库通常也把数据写进文件,但还要解决四件事:快速找到记录、协调同时发生的修改、在故障后恢复,以及管理删除后留下的空间。为此,它在文件接口之上建立了数据组织方式和读写规则。本文以 InnoDB 为例,先理解记录、页和内存缓存,再解释为什么事务提交不必立即写回所有数据页。
- 前置:01 SQL 完整链路、A 实验与证据方法。
- 后续:11 B+ Tree;行宽应用:03 表设计;恢复细节:21 Redo/Undo/WAL。
- 基线:MySQL 8.0.16+ / 8.4、InnoDB(MySQL 中负责数据存取、事务和恢复的存储引擎)。SQL 示例给出推导预期;文件尺寸、缓存计数和空间变化取决于目标实例的版本、配置与运行状态。
初次先读第 1 节、第 2 节的页模型、第 4—5 节的读写路径,再做第 6 节五行验证。页内目录、区大小、1000 行空间观察和其他存储结构的对照可以第二遍再读。
1. 现象:write 返回,不代表订单可在断电后恢复
追加 JSON 文件可以满足低并发、顺序访问、可重建数据的需求。复杂性来自后续操作:按订单号查找时,需要知道记录在哪;两个请求同时修改余额时,需要防止修改互相覆盖;转账涉及两条记录时,需要保证扣款和入账一起成功或一起撤销。这种“一起成功或一起撤销”的要求称为原子性,事务用提交和回滚划定一组操作的边界。
文件接口不会自动实现这些业务规则。应用若直接管理文件,就需要自行补齐以下机制:
| 问题 | 单纯文件追加没有自动提供的协议 |
|---|---|
| 按订单 ID 查询 | 索引、偏移、记录边界和版本定位 |
| 两个写者修改余额 | 协调冲突,并维护余额等必须成立的业务规则 |
| 一笔操作修改两条记录 | 部分写入的识别、回滚或重放 |
| 写成功后断电 | 日志同步、存储持久化及恢复规则 |
| 删除后回收空间 | 旧版本保留、可复用页和文件收缩 |
| 在线备份 | 一致时间点、日志和文件的匹配关系 |
普通缓冲文件写入中,write 返回通常表示数据已交给操作系统,数据仍可能留在内存缓存中。若此时断电,尚未持久化的数据就可能丢失。持久化指数据进入故障后仍可恢复的存储;fsync 用于要求文件数据同步到持久存储,但其保证还依赖文件系统和存储控制器正确兑现刷新请求。数据库也受这些条件约束。
即使同步成功,也不能保证转账的两条记录一起生效,或协调并发更新。多个写者的内容是否交错,取决于追加语义、单次写入大小和同步方式;业务事务仍需要额外的协调与恢复规则。
2. 原理:先理解页,再理解空间分组
如果订单文件只是逐条追加,找订单 1001 可能要从头读取很多记录。索引是帮助按键值查找记录的数据结构。数据库借助索引缩小查找范围,再按块访问记录所在的位置,避免每次都把整张表读入内存。
记录是一行的物理表示;页是数据库组织和缓存这类数据的基本单位。例如一页可以放多条订单,也要留出管理记录位置的空间。查一条订单会访问它所在页,不是专门只从设备读取那一条行。
页中既有业务值,也有记录头、页目录和空闲区域,因此“100 条记录各 100 字节”不能推出“文件恰好 10000 字节”。删掉一条记录可能留出可复用位置,也不等于文件立即缩短。
2.1 页、区、段与表空间的关系
下面是管理层次,不是查询必须逐个经过的执行步骤:
text
记录 → 索引页 / 页外数据页
页在表空间中编号;段管理所属页与区;表空间由文件承载- 记录(Record):列值、记录头和版本相关字段等物理表示。
- 页(Page):有页号、类型、记录区、目录、空闲空间和校验的结构。
- 区(Extent):表空间内连续页的分配集合,方便管理批量空间。
- 段(Segment):管理某个索引叶子或非叶子所用的页与区,不是文件路径。
- 表空间(Tablespace):承载数据库对象与空间分配状态。每表独立、共享和通用表空间的文件生命周期不同。
这些层次解决不同问题:页用于组织和访问数据,区与段用于管理空间分配,表空间把这些结构承载在文件中。先理解这些职责,再看具体尺寸。KiB 是 1024 字节,MiB 是 1024 KiB。
在默认 16 KiB 页下,一个区为 64 个连续页、1 MiB。并非所有页大小都为 64 页一区:官方 8.4 手册规定 4/8 KiB 页的区仍是 1 MiB(256/128 页),32/64 KiB 页则是 2/4 MiB。小段可先按单页分配,不能把“每次申请一个区”当作实现规则。
sql
SELECT VERSION(),@@innodb_page_size,@@innodb_file_per_table;页大小在实例初始化时确定,本实验不改变它。16 KiB 是常用基线而非所有产品通则。数据库页也不等于操作系统页、磁盘扇区或一个 read 系统调用:预读、缓存、压缩及存储层合并会改变实际 I/O。访问三层树不等于三次磁盘读,详见 11。
3. 实现:页内查找、行宽与页外存储
InnoDB 常用 B+ Tree 组织索引:上层页帮助定位,叶子页是树的末端页,用来承载索引记录。主键对应的聚簇索引在叶子记录中组织行内数据;其他索引称为二级索引。
索引页内部还有记录头和页目录。目录中的槽指向一组记录,查找时可先缩小到相应组,再在组内继续查找。因此,页内记录并不是可以直接整体二分的连续行数组。叶子页按键值逻辑相邻,也不保证在文件或设备上物理连续。
页容量有限,行内记录越宽,同一页能容纳的记录通常越少;访问相同数量的记录就可能涉及更多页。不过,影响取决于实际值长度、行格式及页外存储。行格式规定记录如何布局,页外存储则把部分长列内容放到记录所在页以外。DYNAMIC 格式可把长变长列放到页外,行内保留 20 字节指针;是否外置由整行尺寸决定,不是所有 TEXT 一律外置。COMPACT 对外置长列保留的前缀规则不同。
讨论宽行前,还要确认表能否创建:MySQL 还有 65,535 字节逻辑行长限制,utf8mb4 按声明的最大字节数参与检查;失败可能在实际插入前发生。03将建表限制和运行时行宽分开验证。
冷热拆分是把常读字段与较少读取的大字段分开存储,例如把文章列表信息与正文分开。若列表不读正文,较窄的聚簇记录可能减少经常访问的页集合,这个集合称为工作集。若查询所需列已全部包含在一个较窄的索引中,也就是使用覆盖索引,未必需要拆表。若每次都读正文,拆表反而增加关联查询及一致性维护成本。先量化访问比例,再做 04 范式取舍。
插入新记录或扩大已有记录时,如果目标页空间不足,且页内整理等措施仍不能容纳修改,就可能需要页分裂:把记录分配到更多页中。随机键使插入位置更分散,可能扩大被修改页的工作集;顺序键让插入更集中,但也需要扩展和分裂。索引维护与最右端热点在 11展开。
4. 缓冲池:读路径和脏页路径要分开
缓冲池是 InnoDB 存放已读取数据页和索引页的内存区域。命中表示所需页已在其中;未命中通常需要从数据文件读取。修改页之后,内存中的页比文件里的版本新,这种还未写回的数据页称为脏页;“脏”不表示内容损坏。
text
访问页 → 缓冲池命中:在内存访问
→ 未命中:发起读取并等待可用缓冲页
修改页 → 变成脏页,登记刷新状态
后台刷新 / 淘汰压力 → 满足日志先行约束后写出脏页(见第 5 节)实际实现还要管理哪些页最近使用过、哪些页暂时不能被替换、哪些页正在读写,以及后台刷页任务,不是每次未命中都同步“先写一个脏页再读一个新页”。Buffer Pool 缓存页而不是 SQL 结果;即便页都命中,解析、过滤、连接、锁等待和传输仍可能慢。
sql
SELECT VARIABLE_NAME,VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
'Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads',
'Innodb_buffer_pool_pages_dirty','Innodb_buffer_pool_wait_free'
);在观测窗口开始和结束时各取一次采样。逻辑读是请求访问页,物理读是在缓存未命中时从数据文件取得页。以逻辑读请求增量为 L、物理读增量为 P,可用 1-P/L 近似观察缓存命中情况;L=0 时不计算。它们是实例级计数,包含其他请求和后台影响,不能把差值直接归因于一条 SQL。物理读次数也不是设备 read 调用次数或读取字节数。
即使命中率很高,少量未缓存的页仍可能让最慢的一部分请求耗时很长,这就是尾延迟问题。Innodb_buffer_pool_wait_free 表示等待可用缓冲页的次数,其增长也要结合刷页和 I/O 证据判断。排查方法见 24。
5. WAL:两个约束,不是一张绝对时序图
假设修改了余额,内存页已经变了,但尚未写回数据文件。如果此时掉电,恢复需要可靠记录说明哪些修改应重做、哪些未提交修改应撤销。Redo(重做日志)记录恢复时重新应用修改所需的信息;Undo(撤销日志)支持撤销修改和读取旧版本。WAL 是 Write-Ahead Logging,即日志先行:先让必要日志可靠落地,再让对应数据页写回。
这样可以避免每次提交都立即写完所有分散数据页,同时保留恢复依据。以下规则描述这条边界,具体恢复过程留到 21。
- 刷数据页之前,对应修改的 Redo 必须先满足日志持久化约束,避免数据页领先于可恢复日志。
- 宣称持久提交之前,提交所依赖的日志要达到所选择的耐久性级别;数据页不必全部刷完。
内存修改与 Redo 生成是受内部同步保护的操作,不应把教学箭头理解为“任何时刻都可先任意修改数据后再补日志”。Redo 也可能涵盖未提交事务的页修改;恢复需要结合 Undo 和事务状态,不能把 Redo 仅描述为“已提交事务的列表”。
sql
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'innodb_flush_log_at_timeout';
SHOW VARIABLES LIKE 'sync_binlog';MySQL 8.4 的 innodb_flush_log_at_trx_commit=1 在每次提交要求日志写入并刷新;组提交把多个事务的日志同步合并完成,不等于每个事务独占一次 fsync。值 2 在提交写日志但按周期刷新,值 0 按周期写入和刷新;周期还受配置和调度影响,不能承诺“最多丢一秒”。Binlog 是 MySQL Server 层的二进制日志,常用于复制和按时间点恢复;它的 sync_binlog 是另一项同步承诺,见 22。同步正确也依赖存储设备如实兑现刷新。
日志之外,数据页本身也需要保护。页校验用于检测部分损坏,但不能自行修复只写入了一部分的页。Doublewrite(双写)是针对这类部分页写入问题的独立保护机制。Checkpoint(检查点)标记恢复和日志复用的进度,不要求所有脏页都已写回。详细机制见 21。
6. 验证:逻辑字节、分配空间与删除后复用
6.1 基础验证:五行足以看清逻辑变化
在隔离实例、事务已结束的连接中运行。表名仅属于本文;已存在时先确认旧数据归属,不盲目覆盖:
sql
CREATE DATABASE IF NOT EXISTS kb_database_lab;
USE kb_database_lab;
SET autocommit = 1;
CREATE TABLE storage_small (
id BIGINT PRIMARY KEY,
payload VARCHAR(20) NOT NULL
) ENGINE = InnoDB;
INSERT INTO storage_small VALUES
(1, 'aaaa'), (2, 'bbbb'), (3, 'cccc'), (4, 'dddd'), (5, 'eeee');
SELECT COUNT(*) AS rows_count,
SUM(OCTET_LENGTH(payload)) AS payload_bytes
FROM storage_small;
DELETE FROM storage_small WHERE id <= 2;
SELECT COUNT(*) AS rows_count,
SUM(OCTET_LENGTH(payload)) AS payload_bytes
FROM storage_small;推导预期:先是 5 行、20 字节 payload,删除后是 3 行、12 字节。这里统计的是字段实际内容字节,不包含记录头、主键和页管理空间,不是文件尺寸。
这一步检查的是查询可见的记录变化;文件是否缩小、断电后能否恢复,需要不同的观察方法。理解逻辑变化并不要求查看操作系统文件。
6.2 可选观察:1000 行与引擎容量统计
以下操作限于独立隔离库中的 storage_* 表。脚本会删除已有的 storage_page,执行前确认其中没有需要保留的数据。先结束所有实验事务,记录 innodb_file_per_table(新建表是否默认使用每表独立表空间)和页大小;不要为对照在生产改全局参数、清空缓存或重启。
sql
CREATE DATABASE IF NOT EXISTS kb_database_lab;
USE kb_database_lab;
SET autocommit=1;
DROP TABLE IF EXISTS storage_page;
CREATE TABLE storage_page (
id BIGINT PRIMARY KEY,payload VARCHAR(1000) NOT NULL
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8mb4;
INSERT INTO storage_page
WITH RECURSIVE seq AS (
SELECT 1 AS num UNION ALL SELECT num+1 FROM seq WHERE num<1000
)
SELECT num,REPEAT('x',900) FROM seq;
SELECT COUNT(*) AS rows_count,SUM(OCTET_LENGTH(payload)) AS payload_bytes
FROM storage_page;
SELECT TABLE_NAME,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,DATA_FREE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='kb_database_lab' AND TABLE_NAME='storage_page';推导预期:1000 行、900000 字节 payload。TABLE_ROWS 及数据、索引空间字段是存储引擎统计口径,不是精确逻辑字节或每一行的账目。本例没有二级索引,不能据此把 INDEX_LENGTH 解释为“没有主键树”;主键树占 DATA_LENGTH 对应的聚簇存储。
依次记录初始化后、删除后、再填充后各自的统计和(有权限时)对应表空间文件尺寸:
sql
DELETE FROM storage_page WHERE id<=500;
SELECT COUNT(*) AS rows_count FROM storage_page;
INSERT INTO storage_page
WITH RECURSIVE seq AS (
SELECT 1 AS num UNION ALL SELECT num+1 FROM seq WHERE num<500
)
SELECT num,REPEAT('y',900) FROM seq;
SELECT COUNT(*) AS rows_count,SUM(OCTET_LENGTH(payload)) AS payload_bytes
FROM storage_page;预期中间 500 行、最后仍为 1000 行/900000 字节。不预设文件恰好缩小或完全不增长。DELETE 提交后,其他事务还可能需要读取旧版本,因此不能立刻清理所有旧内容。Purge 是清理不再需要的旧版本等内容的过程。清理后留下的页内空洞或空闲页可能供后续写入复用,但复用不等于把文件空间归还操作系统。逻辑删除、物理清理、空间复用和文件归还是不同阶段;Purge、段分配及文件配置共同决定最终表现。18 MVCC/Purge解释旧版本为什么暂不能清理。
若要验证持久化,应另建可丢弃实例,并在实例之外记录哪些事务已收到提交成功响应,再对照恢复后的数据。进程崩溃与机器断电影响的缓存层次不同,需要分别设计实验。复制正在运行的文件也不是一致备份,见 28。
7. 排障与工程取舍
| 现象 | 不能直接推出 | 下一步证据 |
|---|---|---|
| 小表文件比 payload 大 | 存储泄漏 | 页/段分配、统计口径和索引组成 |
| DELETE 后文件没缩 | 删除没生效 | COUNT、Purge、可复用空间与表空间类型 |
| 内存很多但读慢 | 必须继续加 Buffer Pool | 工作集、冷读增量、锁等待和 CPU |
| COMMIT 慢但读页命中 | 没有 I/O 瓶颈 | Redo/Binlog 同步等待与设备延迟 |
| checkpoint/刷脏压力 | 一定要放松持久化 | 写入速率、刷新能力、日志容量和恢复目标 |
可选对照:某些产品采用 LSM(Log-Structured Merge-tree,日志结构合并树)组织数据,先把写入放到内存结构,再形成有序文件,并通过后台合并控制文件数量与旧数据。其中,MemTable 是内存中的数据结构,SSTable 是有序数据文件,Compaction 是后台合并整理过程。它与 B+ Tree 的页修改和分裂不是同一实现;读取、写入和占用空间的额外成本依负载与实现而定,不能断言哪一种天然更快。具体实现和选型范围按 30核验。
清理时结束所有实验事务,在隔离库仅删除 storage_small、storage_page;不删除公共实验库。后续文章 11将页模型用于索引寻址,03用于字段宽度决策。
8. 名词解释
| 名词 | 含义 |
|---|---|
| 页 / 区 / 段 | 基本组织和缓存单位 / 连续页的分配集合 / 索引空间的管理集合 |
| 表空间 | 承载数据库对象及分配状态的存储空间,由文件承载 |
| 缓冲池 / 脏页 | 内存中的页缓存 / 已修改但尚未写回文件的页 |
| 逻辑读 / 物理读 | 请求访问缓存中的页 / 需要从数据文件取得页;后者计数不等于设备调用次数 |
| WAL / Redo / Undo | 日志先行规则 / 重做依据 / 撤销与旧版本依据 |
| Purge | 清理已不再需要的旧版本等内容,不等于立即缩小文件 |
| Checkpoint / Doublewrite | 恢复和日志复用的进度管理 / 防范部分数据页写入损坏的保护机制 |
| KiB / MiB | 分别为 1024 字节、1024 KiB,不混用十进制容量口径 |
参考资料
- MySQL 8.4 Reference Manual:File Space Management、InnoDB Row Formats、InnoDB Buffer Pool、innodb_flush_log_at_trx_commit。
- 适用条件:文件变化、缓存差值和崩溃恢复实验应在目标实例核验。