MySQL 三大日志讲透：redo log、undo log、binlog 与两阶段提交

个人主页 for_ever_love__ 欢迎各位大佬莅临其他栏目: 大模型开发从0到1 其他栏目: iOS项目总结大全 其他栏目: 我想学python了 其他栏目: iOS UI 文章目录MySQL 三大日志讲透redo log、undo log、binlog 与两阶段提交一、先搞清楚为什么需要三份日志二、redo log用顺序写换掉随机写2.1 WAL 思想2.2 redo log 是怎么写的2.3 redo log 文件环形复用 checkpoint2.4 redo log 该设多大附测量方法2.5 崩溃恢复怎么跑三、undo log回滚和 MVCC 的共同载体3.1 它是反向操作记录3.2 两类 undoinsert undo 与 update undo3.3 版本链与 MVCC串起隔离级别3.4 DELETE 是两步慢动作3.5 undo 的存放与运维四、binlogServer 层的逻辑日志4.1 它是干什么的4.2 三种格式4.3 binlog 的刷盘策略五、三者对比总表六、关键难点两阶段提交七、双一的性能代价与优化手段八、常见误区九、小结MySQL 三大日志讲透redo log、undo log、binlog 与两阶段提交为什么断电重启后已提交的事务一条不少、没提交的一条不留为什么主从同步要靠 binlog 而不是 redo log答案全在这三份日志里。本篇把它们各自的职责、写入时机、刷盘策略讲清楚最后落到一个必须搞懂的问题两阶段提交。一、先搞清楚为什么需要三份日志设想这条 UPDATE 执行到一半机房断电UPDATEaccountSETbalancebalance-100WHEREid1;脏页还在 Buffer Pool 里没落盘。重启后怎么保证数据正确三种情况分别要处理情况需求谁来负责事务已提交脏页还没刷盘必须把修改补回来持久性 Dredo log事务没提交但已经改了数据页必须回滚原子性 Aundo log要恢复历史数据 / 做主从复制 / 同步给下游需要一份做过什么的记录binlog不同的诉求注定了三份日志的性质完全不同日志所属层日志类型主要作用redo logInnoDB 存储引擎层物理日志哪个页改了哪个字节崩溃恢复保证持久性undo logInnoDB 存储引擎层逻辑日志反向操作回滚 MVCC保证原子性binlogMySQL Server 层逻辑日志SQL 或行前后镜像主从复制、数据恢复、CDC注意 binlog 在Server 层这就是它和 redo log 分工的根本原因Redo log 是 InnoDB 私有的换 MyISAM 就没有而 binlog 面向所有引擎、面向外部世界。二、redo log用顺序写换掉随机写2.1 WAL 思想如果每次提交都要求把修改的数据页刷到磁盘那一次 UPDATE 可能触发多个随机 IO数据页在表空间文件里散落各处性能没法看。InnoDB 的解法是WALWrite-Ahead Logging预写日志先把改了什么顺序写到 redo log 里数据页留在内存慢慢刷。顺序写 vs 随机写在 HDD 上差两个数量级在 SSD 上依然差好几倍。只要 redo log 落盘了即使数据页没刷崩溃后也能重放出来——持久性就有了保障。2.2 redo log 是怎么写的事务修改数据 │ ▼ ① 改内存中的 Buffer Pool 页变成脏页同时写 redo log buffer │ ▼ ② redo log buffer → write() 到 page cacheOS 缓存 │ ▼ ③ fsync() 刷到磁盘上的 redo log 文件 ← 只有这一步完成事务才算真正安全第 ②③ 步的时机由参数innodb_flush_log_at_trx_commit控制值commit 时做什么崩溃风险适用1默认write fsync每次提交都刷盘不丢已提交事务金融 / 交易等不能丢数据的场景2只 write 到 OS 缓存每秒 fsync 一次宕机丢最后 1 秒OS 崩溃也丢高并发、可容忍少量丢失0什么都不做由后台线程每秒 write fsync宕机丢最后 1 秒测试环境生产不建议SELECTinnodb_flush_log_at_trx_commit;-- 生产请确认是 12.3 redo log 文件环形复用 checkpointredo log 不是无限增长的它在磁盘上是一组固定大小的文件循环覆盖写┌───────────────────────────────────────────┐ │ ib_logfile0 │ ib_logfile1 │ ib_logfile2 │ ... │ └───────────────────────────────────────────┘ ▲ write pos写指针不停往后追加 │ ▼ checkpoint已刷脏页的位置之前的日志可被覆盖这里有个关键关系redo log 满了就必须强制刷脏页把 checkpoint 往前推腾出空间。所以 redo log 太小会导致checkpoint 频繁触发脏页被迫同步刷盘写入出现卡顿时快时慢的锯齿每次追上 checkpoint 就抖一下2.4 redo log 该设多大附测量方法MySQL 5.7用这两个参数SHOWVARIABLESLIKEinnodb_log_file%;-- innodb_log_file_size 单个文件大小-- innodb_log_files_in_group 文件个数默认 2-- 总容量 size × files_in_groupMySQL 8.0.30 之后改成了单一参数innodb_redo_log_capacity默认约 100MB旧的innodb_log_file_size/innodb_log_files_in_group已被标记废弃SELECTinnodb_redo_log_capacity;-- 单位字节8.0.30SETGLOBALinnodb_redo_log_capacity2147483648;-- 动态调整为 2G大小怎么定别拍脑袋测一下-- 记录 60 秒内 redo log 的写入量SHOWGLOBALSTATUSLIKEInnodb_os_log_written;-- 等 60 秒业务正常运行时SHOWGLOBALSTATUSLIKEInnodb_os_log_written;-- 计算差值 / 60 每秒写入字节数-- 经验值让 redo log 能容纳 **1 小时** 的写入量或不低于 30 分钟举个数60 秒写了 240MB即 4MB/s那么 1 小时是 14.4GB——这说明你的库写压力很大redo log 至少给到几个 G反之如果 1 小时才 200MB给 2GB 已经绰绰有余。生产常见区间是 1G ~ 4G。2.5 崩溃恢复怎么跑重启时 InnoDB 做的事找到 redo log 里的 checkpoint 位置从 checkpoint 开始重放 redo包括未提交事务的 redo 也重放先全量前滚扫描 undo 的回滚段找出所有处于ACTIVE状态的事务用 undo log 把这些未提交事务回滚掉也就是说 InnoDB 走的是先重做所有再回滚未提交的这种账要两步算完的路线。这也是为什么即使你从没 ROLLBACK 过undo log 依然是崩溃恢复的必需品。三、undo log回滚和 MVCC 的共同载体3.1 它是反向操作记录undo log 记的是逻辑反向操作非常朴素操作undo log 记什么回滚时做什么INSERT记下这条记录的主键值按主键 DELETE 掉DELETE记下整行内容重新 INSERT 回去UPDATE不更新主键记下被改列的旧值把旧值 UPDATE 回去UPDATE更新主键拆成 delete mark insert 两条反向的两个操作注意查询SELECT不产生 undo log——只有改数据的操作才需要后悔药。3.2 两类 undoinsert undo 与 update undo这是理解 undo 生命周期的关键分界类型产生自事务提交后insert undoINSERT以及因更新主键产生的 insert可以直接丢弃没人需要看到它update undoDELETE / UPDATE不能丢MVCC 的快照读要用它读历史版本所以 update undo 会被挂到一个叫history list的链表上等到「没有任何事务需要读这个旧版本」时才由后台的purge 线程真正清理掉。3.3 版本链与 MVCC串起隔离级别每行数据有两个隐藏列DB_TRX_ID最后修改这行的事务 IDDB_ROLL_PTR回滚指针指向 undo log 里的上一个版本多次修改会串成一条链快照读顺着DB_ROLL_PTR往回找直到找到一个自己看得见的版本。这一部分在事务隔离级别那篇里展开过这里只需要记住一个因果因为 MVCC 依赖 update undo所以只要有一个长事务一直不走它需要的旧版本就必须保留history list 就会持续变长undo 表空间就会膨胀。这是长事务危害的物理根源下一篇详聊。监控它SHOWENGINEINNODBSTATUS\G-- 关注这一段-- History list length 8 ← 越大说明待 purge 的版本越多3.4 DELETE 是两步慢动作InnoDB 里的 DELETE 不是立刻抹掉数据delete mark把记录的删除标记置 1逻辑上不可见但物理上还在页里purge事务提交后由后台 purge 线程把它从记录链表移走并挂到页内的空闲链表上空间可被后续插入复用这个延迟删除正是为了服务 MVCC别的会话可能还在读这个旧版本不能马上抹掉。推论频繁 DELETE 之后表文件大小不会立刻下降这是 InnoDB 的正常行为要回收空间得OPTIMIZE TABLE重建表。3.5 undo 的存放与运维undo 保存在undo tablespaceMySQL 8.0 默认是 2 个独立 undo 表空间文件undo_001、undo_002老版本在系统表空间ibdata1里独立 undo 表空间的优点可以自动 truncate 回收而 ibdata1 只增不减相关参数innodb_undo_tablespaces、innodb_undo_log_truncateON、innodb_max_undo_log_size超限才触发回收purge 线程数innodb_purge_threads默认 4写压力大的库可以加到 8/16SHOWVARIABLESLIKEinnodb_undo%;SHOWVARIABLESLIKEinnodb_purge%;⚠️ 8.0 里 undo 表空间一旦创建基本不可削减配置初始化时别给太小的磁盘目录。四、binlogServer 层的逻辑日志4.1 它是干什么的redo log 只管让本机崩溃后能恢复它做不到这三件事主从复制redo log 是引擎私有格式、且循环覆盖会丢历史按时间点恢复 PITR需要一份持续追加的完整变更历史把变更同步给 Kafka / 数仓 / 订阅系统CDCbinlog 是追加写、不覆盖的因此才适合做这些事。4.2 三种格式格式记录内容优点缺点ROW5.7.7 起默认每一行变更前后的行镜像复制绝对安全随机函数、触发器、非确定性语句都不怕日志体积大改 10 万行记 10 万条STATEMENT原始 SQL 语句体积小有不安全语句问题UUID()、NOW()、LIMIT无 ORDER BY 等可能导致主从不一致MIXED多数用 STATEMENT不安全时自动切 ROW折中判断逻辑复杂出问题不好排查SELECTbinlog_format,binlog_row_image;-- binlog_row_image FULL默认/ MINIMAL / NOBLOB生产建议ROW FULL最安全。如果磁盘吃紧可以改成MINIMAL只记录必要的列但要知道这会让某些下游工具的语义变差。4.3 binlog 的刷盘策略SELECTsync_binlog;值行为风险1推荐每提交一次事务就 fsync binlog不丢N 1攒够 N 次提交才 fsync宕机丢最后 N 个事务的 binlog主从可能不一致0交给 OS 调度丢失不可控SELECTbinlog_expire_logs_seconds;-- 8.0 用这个单位秒-- 5.7 用 expire_logs_days8.0 中已废弃保留期别设太短至少覆盖「一次全量备份 追binlog」的窗口常见 7 天。五、三者对比总表对比项redo logundo logbinlog所属层InnoDBInnoDBMySQL Server日志类型物理页级逻辑反向操作逻辑SQL / 行镜像解决什么持久性 D原子性 A MVCC复制 / 恢复 / 归档写入方式循环覆盖写大小固定追加purge 后回收追加写文件轮转是否可被替代否否可以关掉但等于放弃复制和恢复能否直接读懂不能物理格式不能能mysqlbinlog解析相关线程log writer / flusherpurge threaddump thread主从顺带提一句常被排进同一张表的relay log中继日志它是从库上的东西IO 线程把主库 binlog 拉回来先落地成 relay logSQL 线程再回放。它本质上就是主从链路的中间缓冲不参与本机事务。六、关键难点两阶段提交现在的问题是redo log 和 binlog 是两份独立的日志如果它们不一致就会出现主库有这条数据从库没有——最严重的事故之一。考虑这个顺序① 写 redo log标记事务已提交 ② 写 binlog ↑ 如果在这里崩溃 → redo 有、binlog 没有 → 本机恢复后数据存在但从库永远少了这条 → 主从不一致反之先写 binlog 再写 redo崩溃时会出现从库有、主库没有。两种都不能接受。解法是把 redo log 的提交拆成两步中间夹着 binlog┌──────────────────────────────┐ 第 1 步 │ redo log: prepare 状态 │ ← fsync 落盘 └──────────────────────────────┘ │ ┌──────────────────────────────┐ 第 2 步 │ 写 binlogfsync 落盘 │ └──────────────────────────────┘ │ ┌──────────────────────────────┐ 第 3 步 │ redo log: 标记 commit │ └──────────────────────────────┘崩溃恢复时的判定规则非常干净崩溃时 redo 状态binlog 中该事务的 XID恢复动作prepare完整存在提交第 3 步补上prepare不存在 / 不完整回滚commit必然完整什么都不用做也就是说只要 binlog 完整落盘了事务就必须算提交成功。这就是为什么sync_binlog1和innodb_flush_log_at_trx_commit1俗称双一是金融级场景的标配——少任何一个都有丢失或不一致的窗口。⚠️ 也正是因为这套机制在主从架构下把sync_binlog设成 0 或 N 特别危险主库崩溃后那些已提交但 binlog 没刷盘的事务本机靠 redo 恢复了从库却永远收不到业务上会表现为用户看到下单成功但后台/报表查不到这笔。七、双一的性能代价与优化手段每次事务提交要两次 fsyncredo 一次、binlog 一次确实贵。三种常见缓解方式1. 组提交group commit——MySQL 原生支持无需配置。多个并发事务会把各自的 redo/binlog 攒在一起做一次 fsyncTPS 越高单次 fsync 的开销摊得越薄。可以通过下面两个参数让它凑得更积极SELECTbinlog_group_commit_sync_delay,-- 微秒级延迟故意等一等凑更多事务binlog_group_commit_sync_no_delay_count;-- 攒够 N 个就不等了2. 把 binlog 和 redo log 放到不同的物理磁盘让两个 fsync 并行减少磁头竞争。3. 用更好的磁盘NVMe SSD 的 fsync 延迟比机械盘低两个数量级很多时候双一太慢的根因是还在用机械盘或网络盘。不建议的做法为了性能把innodb_flush_log_at_trx_commit改成 2 或 0。如果业务确实允许丢失那应该做的是明确记录这个决策和它的后果而不是悄悄改掉。八、常见误区误区 1redo log 记录了没提交的事务恢复时会把未提交的也提交——不会。恢复分两步先 redo 前滚再用 undo 回滚所有ACTIVE事务。这也是 undo 存在的第二个理由。误区 2有了 redo log 就不需要 binlog——redo 是物理格式、循环覆盖、引擎私有。binlog 能做复制、PITR、CDCredo 全做不到。误区 3binlog 可以随便开 ROW 格式没代价——ROW 会让日志膨胀明显。一条UPDATE ... WHERE 条件命中 10 万行的 SQL在 STATEMENT 下几十字节在 ROW 下是 10 万条行镜像可能几十 MB。批量更新记得拆小批次顺便防止大事务。误区 4事务提交后就一定已经把数据写到磁盘的数据文件了——不一定也不必。数据页可能还在 Buffer Pool 里等着刷真正落盘的只是 redo log。这也是为什么突然 kill -9 进程不会丢数据但直接删ibd文件会灾难。误区 5undo 只在 ROLLBACK 时有用——它还支撑 MVCC 的快照读和崩溃恢复的未提交事务回滚。很多时候 undo 暴涨不是因为有人 ROLLBACK而是因为有长事务卡着不放。误区 6MySQL 8.0 调 redo log 还是改innodb_log_file_size——8.0.30 起请用innodb_redo_log_capacity旧参数已废弃。九、小结三份日志分工明确redo 管持久性物理、循环写undo 管原子性 MVCC逻辑、反向操作binlog 管复制与恢复逻辑、追加写redo log 靠WAL 顺序写把随机 IO 变成顺序 IO太小会导致 checkpoint 频繁刷脏页写性能锯齿抖动redo 大小按实测的每小时写入量估算生产常见 1G~4G8.0.30 起用innodb_redo_log_capacityundo 分insert undo提交即可弃和update undo要服务 MVCC靠 purge 线程回收DELETE 是delete mark purge两步所以删完表文件不会立刻变小binlog 推荐ROW FULL刷盘用sync_binlog1两阶段提交是 redo 与 binlog 一致性的保证prepare → 写 binlog → commit恢复时binlog 完整就提交否则回滚金融级配置俗称双一innodb_flush_log_at_trx_commit1sync_binlog1性能问题优先用组提交、磁盘分离、SSD 解决不要直接降级安全性History list length是观察 undo 堆积最直观的指标下一篇进入 InnoDB 的内存世界Buffer Pool 怎么管理這块最贵的内存LRU 链表怎么防止全表扫描污染缓存以及脏页是怎么被刷出去的。