ozine's book library

主题

MySQL：沿着一次提交，认识 InnoDB、日志与复制 ​

应用收到“提交成功”后，数据页可能还没全部写回磁盘，副本也可能还查不到这笔数据。这两件事并不矛盾：InnoDB 通过日志恢复尚未写回的数据，而普通异步复制允许副本晚一些应用变化。

理解 MySQL 时，先把本机事务、日志持久化和副本进度拆开。本文固定讨论 MySQL 8.4 的 InnoDB；内部提交路径还核对了官方 mysql-8.4.0 tag 的源码。当前环境没有 MySQL 服务，因此本文没有 MySQL 实测输出，不用 PostgreSQL 或 SQLite 的结果代替。文末给出完整的只读检查 SQL 和临时表实验，明确列出要验证的现象。

MySQL Server 与 InnoDB 分别做什么 ​

MySQL Server 负责连接、SQL 解析、优化与执行协调，也管理 binlog。InnoDB 是存储引擎，负责页、索引、行版本、事务锁以及 redo/undo 等。执行器通过存储引擎接口访问记录，所以“SQL 很慢”可能慢在优化器选择、锁等待、页读取、日志刷盘或返回结果，不能只盯一个模块。[1][2]

正在绘制图示…

可切换适合窗口或原始尺寸
查看图源代码
flowchart TB
    A["客户端 SQL / COMMIT"] --> S["MySQL Server<br/>解析、优化、执行、事务协调"]
    S --> I["InnoDB<br/>索引访问、MVCC、锁"]
    S --> B["binlog<br/>服务器层变更事件"]
    I --> P["Buffer Pool<br/>缓存数据页与索引页"]
    I --> L["Log Buffer → redo log"]
    I --> U["undo<br/>撤销与旧版本信息"]
    P -->|后台刷脏页| F["表空间文件"]
    B --> R["复制 / 时间点恢复"]
放大图示

原始尺寸下可横向或纵向滚动；按 Escape 关闭。

正在绘制图示…

查看图源代码
flowchart TB
    A["客户端 SQL / COMMIT"] --> S["MySQL Server<br/>解析、优化、执行、事务协调"]
    S --> I["InnoDB<br/>索引访问、MVCC、锁"]
    S --> B["binlog<br/>服务器层变更事件"]
    I --> P["Buffer Pool<br/>缓存数据页与索引页"]
    I --> L["Log Buffer → redo log"]
    I --> U["undo<br/>撤销与旧版本信息"]
    P -->|后台刷脏页| F["表空间文件"]
    B --> R["复制 / 时间点恢复"]

Buffer Pool 是页缓存，缓存内容也包括索引页。读不到缓存时需要读存储；修改时通常先改内存页，把它标为脏页，再在适当时机写回。事务提交不等于把该事务碰到的全部页立刻随机写一遍，否则大量小事务会被数据页 I/O 拖慢。[3]

组成主要职责常见观察或代价
Buffer Pool保留常用数据/索引页，承接修改命中率、脏页、内存占用；越大不等于可以挤掉系统所需内存
Redo Log Buffer / redo 文件记录足以重做的变化，支持崩溃恢复日志空间、刷盘延迟、日志等待
Undo 记录与 purge撤销变化，支持旧快照读取，清理不再需要的历史长事务可拖住清理；历史积压影响空间与访问
Checkpoint 与后台刷脏推进已安全落到数据文件的恢复边界刷写跟不上时会施加压力，日志容量不能无限掩盖慢盘
Doublewrite降低数据页写到一半造成损坏的风险增加写入工作；实际行为与模式、底层原子写能力有关

Checkpoint 不是“所有脏页清空”。InnoDB 使用模糊检查点，分批刷新页；推进恢复起点后，较旧的 redo 空间才可复用。Doublewrite 处理的是页写入完整性问题，不能与“事务写两份日志”混为一谈。[4][5]

redo、undo、binlog 不可互相替代 ​

日志/记录由谁管理主要内容与用途不能据此承诺什么
redoInnoDB重做页级或底层结构变化，让恢复追上已记录状态；可概括为偏物理的重做信息，但不是整页原样备份不能单靠它任意回滚已提交业务或替代完整备份
undoInnoDB撤销事务修改，也为一致性读重建旧版本提供信息不保证永久保存历史，purge 会清理
binlogMySQL Server记录数据变更事件，供复制与时间点恢复不是通用 undo，也不会让外部消费者天然 exactly-once

Redo 属于 WAL 思路：相关日志必须在相应脏数据页持久化之前满足规定的持久化要求，崩溃后才能补做。Redo 空间会在安全后复用，不是无限追加的业务历史档案。Undo 的保留时间取决于事务和读取需要，也不能当审计仓库。[6][7]

Binlog 根据配置记录行变更或语句等事件。MySQL 8.4 默认采用 ROW 格式；ROW 的优点是让复制更直接地跟随行变化，代价是批量改动可能产生大量日志。STATEMENT 记录语句，体积可能小，但非确定性等问题需要处理；MIXED 在两种方式间选择。具体记录格式与支持/弃用状态应按正在使用的 8.4 补丁文档核对，不能把 binlog 永远理解成一行行原始 SQL。[8][9]

时间点恢复通常是先恢复一份合适的全量备份，再按正确起点重放后续 binlog，停在目标位置或时刻。只有 binlog 没有基线，或中间日志已被清理，都不能凭空还原数据库。复制还会传播误删，所以有副本不等于有备份。[10]

为什么要协调 redo 与 binlog ​

假设 InnoDB 已经认为某事务提交，但 binlog 中没有对应事务。主库保留了修改，重放 binlog 的副本却没有；反过来，binlog 宣告了事务而引擎撤销，也会产生分歧。内部两阶段提交解决的是服务器事务协调器与支持事务的引擎之间的决定一致性。

在开启 binlog、参与者满足相应条件的普通事务路径中，可把过程理解为下图。它表达逻辑依赖，不是一张逐次 fsync 的时序清单：

正在绘制图示…

可切换适合窗口或原始尺寸
查看图源代码
flowchart LR
    M["执行修改<br/>产生 redo / undo / binlog cache"] --> P["准备阶段<br/>引擎达到可决定提交的状态"]
    P --> G["组提交协调<br/>引擎日志与 binlog 按要求写入/同步"]
    G --> C["提交阶段<br/>引擎完成提交"]
    C --> A["向客户端返回结果"]
    G -. "崩溃恢复时核对决定" .-> R["恢复尚处于 prepared 状态的事务"]
放大图示

原始尺寸下可横向或纵向滚动；按 Escape 关闭。

正在绘制图示…

查看图源代码
flowchart LR
    M["执行修改<br/>产生 redo / undo / binlog cache"] --> P["准备阶段<br/>引擎达到可决定提交的状态"]
    P --> G["组提交协调<br/>引擎日志与 binlog 按要求写入/同步"]
    G --> C["提交阶段<br/>引擎完成提交"]
    C --> A["向客户端返回结果"]
    G -. "崩溃恢复时核对决定" .-> R["恢复尚处于 prepared 状态的事务"]

官方 mysql-8.4.0 的 MYSQL_BIN_LOG::prepare 调用 ha_prepare_low。源码还说明，prepare 时可以暂不单独刷引擎日志，而在后续组提交阶段一起处理。ordered_commit 具有 flush、sync、commit 阶段。因此“每个事务先同步刷一次 redo，再同步刷一次 binlog，再同步刷一次 redo”的固定三次刷盘图，掩盖了组提交和配置差异。[11]

组提交把多个并发事务共享部分日志写入/同步成本，吞吐可能因此提高，但排队策略也会影响延迟。读源码是为了理解为什么不能把逻辑步骤等同于磁盘调用次数；本文没有测量其吞吐提升。

崩溃恢复需要根据持久的事务状态与协调信息，决定 prepared 事务是完成提交还是撤销。这里的两阶段提交不等于你的“订单服务 + 支付服务”已自动纳入一个分布式事务；外部 HTTP 服务没有因为调用了 MySQL 就成为这个内部协议的参与者。外部 XA 也是另一层需要专门设计的机制。[2:1][12]

“提交成功后会不会丢”，必须把配置说出来 ​

设置常见取值与意义取舍
innodb_flush_log_at_trx_commit=1按事务提交要求写并刷 redo，内部仍可利用组提交更强的本机持久化要求，需要存储提供相应保证
=2提交时把日志交到文件系统，通常周期性刷盘OS 或掉电故障可能丢最近事务；不应把“一秒”当严格上限
=0写与刷通常按周期执行mysqld 进程故障也可能丢尚未写出的日志
sync_binlog=1按 binlog 提交组要求同步 binlog常与上面的 redo=1 搭配，减少两份持久信息不一致的风险
sync_binlog=0 或较大 N由系统决定或每 N 个提交组同步降低部分同步成本，但扩大故障时日志丢失窗口

这些参数也不能消除坏盘、存储虚报持久化、错误操作等风险。官方建议的强持久组合有其前提，依然需要备份、恢复演练以及与业务目标匹配的复制方案。不要为了基准更好看先改成弱持久，再把测试结论当作同等可靠性的优化。[13][14]

查看配置可以先执行以下只读查询，完整脚本在文末；本次未运行，不附伪造结果：

sql
SELECT @@global.innodb_flush_log_at_trx_commit AS redo_flush,
       @@global.sync_binlog AS binlog_sync,
       @@global.log_bin AS binlog_enabled;

拿到结果后，再回答业务需要的 RPO：最多允许丢多少已确认数据；以及 RTO：故障后允许多久恢复。只谈“有复制”不足以回答这两个问题。

从 source 的 binlog 到 replica 的可读数据 ​

传统资料常用“主从”，MySQL 8.4 文档使用 source/replica。异步复制至少有“源端产生”“副本收到”“副本执行”几个不同进度：

正在绘制图示…

可切换适合窗口或原始尺寸
查看图源代码
sequenceDiagram
    participant App as 应用
    participant S as Source
    participant R as Replica 接收线程
    participant A as Replica 应用线程/工作线程
    App->>S: 写入并提交
    S-->>App: 返回成功
    S->>R: 发送 binlog 事件
    R->>R: 写 relay log
    R->>A: 提供待应用事务
    A->>A: 执行并提交
    Note over App,A: 直到应用完成，副本查询才可能看到这次变化
放大图示

原始尺寸下可横向或纵向滚动；按 Escape 关闭。

正在绘制图示…

查看图源代码
sequenceDiagram
    participant App as 应用
    participant S as Source
    participant R as Replica 接收线程
    participant A as Replica 应用线程/工作线程
    App->>S: 写入并提交
    S-->>App: 返回成功
    S->>R: 发送 binlog 事件
    R->>R: 写 relay log
    R->>A: 提供待应用事务
    A->>A: 执行并提交
    Note over App,A: 直到应用完成，副本查询才可能看到这次变化

这是概念时序，网络发送可能与其他步骤交叠，但普通异步复制不要求应用成功回包必须等到副本执行完成。一个“大事务”在源端执行了很久，副本随后还要处理它；网络慢、工作线程冲突、缺索引、磁盘慢，都能造成不同阶段的落后。[15]

GTID 为事务提供跨复制拓扑的标识，便于自动定位已执行与未执行的事务。它能帮助避免原生复制重复执行同一已记录事务，但不能保证任意 CDC 消费者的外部写入只发生一次。消费者如果先写目标系统、再记消费位置，中间崩溃会重放；先记位置再写目标，又可能漏做。需要在目标端设计事务化位点、幂等键或可去重的业务更新。[16]

半同步复制进一步让源端等到满足配置数量的副本确认收到并持久记录事件，再按其等待点继续。确认接收不等于副本已经应用，超时还可能退回异步模式，所以必须同时监控半同步是否仍生效。它减少某些故障下的数据丢失风险，但不能拿来保证“任意副本立刻读到刚写入的值”。[17]

需求先采取的措施新成本或限制
写完立即查到自己的写入关键读走 source，或按可验证的复制进度等待source 读压力；等待时间与失败处理
一般浏览可容忍轻微延迟读 replica，定义过旧时的降级规则用户可能短暂看到旧值
故障切换少丢数据检查持久化与候选副本实际进度，设计隔离旧主的流程更多协调与运维工作，可能牺牲可用性
稳定恢复到误操作前合适的备份、连续 binlog 与恢复演练存储、保留期和恢复成本

SHOW REPLICA STATUS 的 Seconds_Behind_Source 只是一个观察角度。值为零不能孤立地证明“源端没有任何尚未收到的数据”，NULL 也要结合复制线程状态解释。还应看接收/应用错误、GTID 集合、relay log 和工作线程状态。[18]

慢查询优化：先找到最值得改的，再解释为什么慢 ​

一条执行 5 秒、一天一次的查询，未必比每秒执行几千次的 5 毫秒查询更值得优先处理。先按业务尾延迟、总耗时、频次、错误和资源占用找目标，再拿实际参数与数据分布做计划分析。

步骤具体动作避免的误判
找目标慢日志记录达到阈值的语句；Performance Schema 按 digest 汇总同类语句只看到某次最慢，没有看到高频总成本
分清等待和工作结合行锁等待、metadata lock、I/O、CPU 与连接池等待把全部延迟都归因于没建索引
看执行计划EXPLAIN FORMAT=TREE；测试环境用 EXPLAIN ANALYZE只看 key 字段，不看扫描量和循环次数
降低工作量改谓词、联合索引、避免不必要列和 N+1、调整分页与批次用更多连接掩盖慢 SQL
复测与观察副作用同样数据、相近冷热状态、代表参数与并发；看写入和复制延迟一个查询变快，却拖慢写入和副本

慢日志并非整条请求链的秒表。MySQL 对记录时机、锁获取计时和日志过滤有自己的规则；应用端还可能在数据库执行前等待连接。记录日志也有 I/O 和敏感参数泄露风险，应配置合适的收集、权限和保留策略。[19]

Performance Schema 的 events_statements_summary_by_digest 能给出次数、累计/平均耗时、扫描行数和返回行数。本篇完整检查脚本按总耗时排序，并把皮秒计时单位转换为秒；读两个时点的增量更适合观察一个特定窗口，累计值可能跨很长运行时间。[20]

假设订单查询是“某租户最近一段时间的金额”，候选索引 (tenant_id, created_at, amount_cents) 可能同时减少扫描并覆盖返回列。InnoDB 二级索引还带主键，因此返回 id 未必需要额外添加一个 id 列。代价是每笔订单写入维护更宽的索引；应由测试确认它比现有索引划算，而不是直接复制到生产。[21]

文末临时表实验先建 1,000 行，再比较加索引前后的计划。按固定输入计算，查询应匹配 902、912、…、992 共 10 行，金额和为 9,470 分；这是从造数规则推导的校验目标，尚非 MySQL 实测结果。具体会选哪个计划、耗时多少，本文不知道，也不预填。

本篇的验证边界 ​

已核对 8.4 官方文档，以及官方 mysql-8.4.0 源码中 prepare、组提交阶段和引擎协调入口。没有在 MySQL 进程中执行文末脚本，没有做崩溃恢复、主从复制、半同步超时或性能基准。源码入口能支持机制解释，不能替代对运行配置和故障行为的实验。

要继续验证，先在隔离的 MySQL 8.4 测试库记录实际补丁版本及持久化参数，跑临时表 SQL，再另行准备复制和受控故障环境。不要把杀生产进程当作验证教程。

参考资料 ​

完整验证脚本（本次未运行） ​

需要 MySQL 8.4 客户端、已经创建的隔离测试数据库，以及对应权限。通过本机客户端的安全登录机制提供凭据，不把密码写在命令行或本文脚本里。inspect.sql 只读，但 Performance Schema、InnoDB 状态和复制状态可能需要额外权限；没有复制配置时复制状态可能为空。plan_lab.sql 只建临时表，不修改全局参数，不配置复制。

sh
mysql --login-path=lab --database=your_disposable_test_db < inspect.sql
mysql --login-path=lab --database=your_disposable_test_db < plan_lab.sql

上面的 lab 登录配置和数据库应事先由使用者准备。本文未创建账号、保存凭据或配置服务。不要为了让检查成功而给线上账号随意加管理员权限。

inspect.sql ​

sql
-- MySQL 8.4 read-only inspection. NOT executed in the article environment.
SELECT VERSION() AS version, @@version_comment AS distribution;
SELECT @@global.innodb_flush_log_at_trx_commit AS redo_flush,
       @@global.sync_binlog AS binlog_sync,
       @@global.log_bin AS binlog_enabled,
       @@global.binlog_format AS binlog_format,
       @@session.transaction_isolation AS session_isolation;
SHOW GLOBAL STATUS WHERE Variable_name IN (
 'Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads',
 'Innodb_buffer_pool_pages_dirty','Innodb_log_waits');
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,
       ROUND(SUM_TIMER_WAIT/1000000000000,3) AS total_seconds,
       ROUND(AVG_TIMER_WAIT/1000000000000,6) AS mean_seconds,
       SUM_ROWS_EXAMINED,SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
SELECT * FROM performance_schema.data_lock_waits;
SHOW ENGINE INNODB STATUS;
SHOW REPLICA STATUS;

plan_lab.sql ​

sql
-- MySQL 8.4 disposable test database only. NOT executed here.
-- Requires CREATE TEMPORARY TABLES privilege and an already selected database.
CREATE TEMPORARY TABLE lab_orders (
 id bigint NOT NULL PRIMARY KEY,
 tenant_id int NOT NULL,
 created_at datetime NOT NULL,
 amount_cents int NOT NULL,
 note varchar(100) NOT NULL
) ENGINE=InnoDB;
INSERT INTO lab_orders
WITH RECURSIVE n AS (
 SELECT 1 AS n
 UNION ALL SELECT n+1 FROM n WHERE n<1000
)
SELECT n,MOD(n,10),TIMESTAMPADD(SECOND,n,'2026-01-01 00:00:00'),
       MOD(n,10000),REPEAT('x',100) FROM n;
EXPLAIN FORMAT=TREE
SELECT id,amount_cents FROM lab_orders
WHERE tenant_id=2 AND created_at>='2026-01-01 00:15:00';
ALTER TABLE lab_orders ADD INDEX idx_tenant_time_amount(tenant_id,created_at,amount_cents);
ANALYZE TABLE lab_orders;
EXPLAIN FORMAT=TREE
SELECT id,amount_cents FROM lab_orders
WHERE tenant_id=2 AND created_at>='2026-01-01 00:15:00';
-- ANALYZE really executes the SELECT. Safe here only because this is toy data.
EXPLAIN ANALYZE
SELECT id,amount_cents FROM lab_orders
WHERE tenant_id=2 AND created_at>='2026-01-01 00:15:00';
SELECT COUNT(*) AS matched_rows,SUM(amount_cents) AS total_cents
FROM lab_orders
WHERE tenant_id=2 AND created_at>='2026-01-01 00:15:00';
DROP TEMPORARY TABLE lab_orders;

  1. MySQL 8.4 InnoDB Architecture。 ↩︎

  2. 官方 mysql-8.4.0 handler.cc，事务与两阶段提交说明、ha_prepare_low。 ↩︎ ↩︎

  3. MySQL 8.4 Buffer Pool。 ↩︎

  4. MySQL 8.4 InnoDB Checkpoints。 ↩︎

  5. MySQL 8.4 Doublewrite Buffer。 ↩︎

  6. MySQL 8.4 Redo Log。 ↩︎

  7. MySQL 8.4 Undo Logs。 ↩︎

  8. MySQL 8.4 Binary Log。 ↩︎

  9. MySQL 8.4 Replication Formats。 ↩︎

  10. MySQL 8.4 Point-in-Time Recovery。 ↩︎

  11. 官方 mysql-8.4.0 binlog.cc，MYSQL_BIN_LOG::prepare 约 8130 行、ordered_commit 约 8974 行及 flush/sync/commit 阶段。 ↩︎

  12. MySQL 8.4 XA Transactions。 ↩︎

  13. MySQL 8.4 innodb_flush_log_at_trx_commit。 ↩︎

  14. MySQL 8.4 sync_binlog。 ↩︎

  15. MySQL 8.4 Replication Threads。 ↩︎

  16. MySQL 8.4 GTID Format and Storage。 ↩︎

  17. MySQL 8.4 Semisynchronous Replication。 ↩︎

  18. MySQL 8.4 SHOW REPLICA STATUS。 ↩︎

  19. MySQL 8.4 Slow Query Log。 ↩︎

  20. MySQL 8.4 Statement Summary Tables。 ↩︎

  21. MySQL 8.4 Clustered and Secondary Indexes。 ↩︎

保留各书来源与许可 · 示例运行条件以正文为准