MySQL Binlog、Redo Log、Undo Log三大日志原理与协作机制详解：深入理解数据库事务持久化机制

一、MySQL 日志系统概述：理解数据库持久化的基石

1.1 为什么需要日志系统

MySQL 作为关系型数据库，需要保证数据的持久性和一致性。日志系统是 MySQL 实现这些特性的核心机制。当数据库发生故障时，通过日志可以恢复数据；在事务处理过程中，日志确保了事务的原子性。

MySQL 的日志系统主要包括三大日志：Binlog（二进制日志）、Redo Log（重做日志）和 Undo Log（回滚日志）。它们各自承担不同职责，共同构成了 MySQL 的事务处理和恢复机制。

1.2 三大日志的角色分工

• Binlog：记录所有对数据库的修改操作，主要用于主从复制和数据恢复
• Redo Log：记录事务对数据页的物理修改，用于崩溃恢复
• Undo Log：记录事务的回滚信息，用于事务回滚和 MVCC（多版本并发控制）

这三种日志协同工作，确保了 MySQL 的高可用性和数据一致性。

二、Binlog（二进制日志）：数据复制的核心

2.1 Binlog 的作用与类型

Binlog 是 MySQL 服务器层实现的日志，记录了所有对数据库的修改操作。它主要用于主从复制和数据恢复。Binlog 有三种格式：

• STATEMENT 格式：记录 SQL 语句
• ROW 格式：记录行的变化
• MIXED 格式：根据情况自动选择格式

#publish-mermaid-0-1791690443357{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-0-1791690443357 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-0-1791690443357 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-0-1791690443357 .error-icon{fill:#552222;}#publish-mermaid-0-1791690443357 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-0-1791690443357 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-0-1791690443357 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-0-1791690443357 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-0-1791690443357 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-0-1791690443357 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-0-1791690443357 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-0-1791690443357 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-0-1791690443357 .marker.cross{stroke:#333333;}#publish-mermaid-0-1791690443357 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-0-1791690443357 p{margin:0;}#publish-mermaid-0-1791690443357 .pieCircle{stroke:black;stroke-width:2px;opacity:0.7;}#publish-mermaid-0-1791690443357 .pieCircle.highlighted{scale:1.05;opacity:1;}#publish-mermaid-0-1791690443357 .pieCircle.highlightedOnHover:hover{transition-duration:250ms;scale:1.05;opacity:1;}#publish-mermaid-0-1791690443357 .pieOuterCircle{stroke:black;stroke-width:2px;fill:none;}#publish-mermaid-0-1791690443357 .pieTitleText{text-anchor:middle;font-size:25px;fill:black;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}#publish-mermaid-0-1791690443357 .slice{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;fill:#333;font-size:17px;}#publish-mermaid-0-1791690443357 .legend text{fill:black;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:17px;}#publish-mermaid-0-1791690443357 .node .neo-node{stroke:#9370DB;}#publish-mermaid-0-1791690443357 [data-look=\”neo\”].node rect,#publish-mermaid-0-1791690443357 [data-look=\”neo\”].cluster rect,#publish-mermaid-0-1791690443357 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-0-1791690443357 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-0-1791690443357 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-0-1791690443357 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-0-1791690443357 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-0-1791690443357 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-0-1791690443357 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-0-1791690443357 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-0-1791690443357 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-0-1791690443357 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}65%20%15%Binlog 格式使用比例ROW 格式STATEMENT 格式MIXED 格式

现代 MySQL 版本默认使用 ROW 格式，因为它能更准确地记录数据变化。

2.2 Binlog 的写入流程

Binlog 的写入流程包括以下几个步骤：

#publish-mermaid-1-1791690443365{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1-1791690443365 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1-1791690443365 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1-1791690443365 .error-icon{fill:#552222;}#publish-mermaid-1-1791690443365 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1-1791690443365 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1-1791690443365 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1-1791690443365 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1-1791690443365 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1-1791690443365 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1-1791690443365 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1-1791690443365 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1-1791690443365 .marker.cross{stroke:#333333;}#publish-mermaid-1-1791690443365 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1-1791690443365 p{margin:0;}#publish-mermaid-1-1791690443365 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1-1791690443365 .cluster-label text{fill:#333;}#publish-mermaid-1-1791690443365 .cluster-label span{color:#333;}#publish-mermaid-1-1791690443365 .cluster-label span p{background-color:transparent;}#publish-mermaid-1-1791690443365 .label text,#publish-mermaid-1-1791690443365 span{fill:#333;color:#333;}#publish-mermaid-1-1791690443365 .node rect,#publish-mermaid-1-1791690443365 .node circle,#publish-mermaid-1-1791690443365 .node ellipse,#publish-mermaid-1-1791690443365 .node polygon,#publish-mermaid-1-1791690443365 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1-1791690443365 .rough-node .label text,#publish-mermaid-1-1791690443365 .node .label text,#publish-mermaid-1-1791690443365 .image-shape .label,#publish-mermaid-1-1791690443365 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1-1791690443365 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1-1791690443365 .rough-node .label,#publish-mermaid-1-1791690443365 .node .label,#publish-mermaid-1-1791690443365 .image-shape .label,#publish-mermaid-1-1791690443365 .icon-shape .label{text-align:center;}#publish-mermaid-1-1791690443365 .node.clickable{cursor:pointer;}#publish-mermaid-1-1791690443365 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1-1791690443365 .arrowheadPath{fill:#333333;}#publish-mermaid-1-1791690443365 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1-1791690443365 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1-1791690443365 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1-1791690443365 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1-1791690443365 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1-1791690443365 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1-1791690443365 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1-1791690443365 .cluster text{fill:#333;}#publish-mermaid-1-1791690443365 .cluster span{color:#333;}#publish-mermaid-1-1791690443365 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1-1791690443365 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1-1791690443365 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1-1791690443365 .icon-shape,#publish-mermaid-1-1791690443365 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1-1791690443365 .icon-shape p,#publish-mermaid-1-1791690443365 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1-1791690443365 .icon-shape .label rect,#publish-mermaid-1-1791690443365 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1-1791690443365 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1-1791690443365 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1-1791690443365 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1-1791690443365 [data-look=\”neo\”].node rect,#publish-mermaid-1-1791690443365 [data-look=\”neo\”].cluster rect,#publish-mermaid-1-1791690443365 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1-1791690443365 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1-1791690443365 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1-1791690443365 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1-1791690443365 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1-1791690443365 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1-1791690443365 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1-1791690443365 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1-1791690443365 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1-1791690443365 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}是否

事务开始

执行 SQL 语句

生成 Binlog Event

写入 Binlog Cache

事务提交?

刷入磁盘

当事务提交时，Binlog Cache 中的内容会被刷入磁盘，确保 Binlog 的持久化。

2.3 Binlog 与 Redo Log 的协作

Binlog 和 Redo Log 在事务提交时协同工作。MySQL 采用"两阶段提交"机制确保两者的数据一致性。首先写入 Redo Log，然后写入 Binlog，最后提交事务。

BinlogRedo LogMySQLClientBinlogRedo LogMySQLClient#publish-mermaid-2-1791690443400{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-2-1791690443400 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-2-1791690443400 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-2-1791690443400 .error-icon{fill:#552222;}#publish-mermaid-2-1791690443400 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-2-1791690443400 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-2-1791690443400 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-2-1791690443400 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-2-1791690443400 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-2-1791690443400 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-2-1791690443400 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-2-1791690443400 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-2-1791690443400 .marker.cross{stroke:#333333;}#publish-mermaid-2-1791690443400 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-2-1791690443400 p{margin:0;}#publish-mermaid-2-1791690443400 .actor{stroke:#9370DB;fill:#ECECFF;stroke-width:1;}#publish-mermaid-2-1791690443400 rect.actor.outer-path[data-look=\”neo\”]{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-2-1791690443400 rect.note[data-look=\”neo\”]{stroke:#aaaa33;fill:#fff5ad;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-2-1791690443400 text.actor>tspan{fill:black;stroke:none;}#publish-mermaid-2-1791690443400 .actor-line{stroke:#9370DB;}#publish-mermaid-2-1791690443400 .innerArc{stroke-width:1.5;stroke-dasharray:none;}#publish-mermaid-2-1791690443400 .messageLine0{stroke-width:1.5;stroke-dasharray:none;stroke:#333;}#publish-mermaid-2-1791690443400 .messageLine1{stroke-width:1.5;stroke-dasharray:2,2;stroke:#333;}#publish-mermaid-2-1791690443400 [id$=\”-arrowhead\”] path{fill:#333;stroke:#333;}#publish-mermaid-2-1791690443400 .sequenceNumber{fill:white;}#publish-mermaid-2-1791690443400 [id$=\”-sequencenumber\”]{fill:#333;}#publish-mermaid-2-1791690443400 [id$=\”-crosshead\”] path{fill:#333;stroke:#333;}#publish-mermaid-2-1791690443400 .messageText{fill:#333;stroke:none;}#publish-mermaid-2-1791690443400 .labelBox{stroke:#9370DB;fill:#ECECFF;filter:none;}#publish-mermaid-2-1791690443400 .labelText,#publish-mermaid-2-1791690443400 .labelText>tspan{fill:black;stroke:none;}#publish-mermaid-2-1791690443400 .loopText,#publish-mermaid-2-1791690443400 .loopText>tspan{fill:black;stroke:none;}#publish-mermaid-2-1791690443400 .sectionTitle,#publish-mermaid-2-1791690443400 .sectionTitle>tspan{fill:black;stroke:none;}#publish-mermaid-2-1791690443400 .loopLine{stroke-width:2px;stroke-dasharray:2,2;stroke:#9370DB;fill:#9370DB;}#publish-mermaid-2-1791690443400 .note{stroke:#aaaa33;fill:#fff5ad;}#publish-mermaid-2-1791690443400 .noteText,#publish-mermaid-2-1791690443400 .noteText>tspan{fill:black;stroke:none;font-weight:normal;}#publish-mermaid-2-1791690443400 .activation0{fill:#f4f4f4;stroke:#666;}#publish-mermaid-2-1791690443400 .activation1{fill:#f4f4f4;stroke:#666;}#publish-mermaid-2-1791690443400 .activation2{fill:#f4f4f4;stroke:#666;}#publish-mermaid-2-1791690443400 .actorPopupMenu{position:absolute;}#publish-mermaid-2-1791690443400 .actorPopupMenuPanel{position:absolute;fill:#ECECFF;box-shadow:0px 8px 16px 0px rgba(0,0,0,0.2);filter:drop-shadow(3px 5px 2px rgb(0 0 0 / 0.4));}#publish-mermaid-2-1791690443400 .actor-man circle,#publish-mermaid-2-1791690443400 line{fill:#ECECFF;stroke-width:2px;}#publish-mermaid-2-1791690443400 g rect.rect{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));stroke:#9370DB;}#publish-mermaid-2-1791690443400 .node .neo-node{stroke:#9370DB;}#publish-mermaid-2-1791690443400 [data-look=\”neo\”].node rect,#publish-mermaid-2-1791690443400 [data-look=\”neo\”].cluster rect,#publish-mermaid-2-1791690443400 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-2-1791690443400 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-2-1791690443400 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-2-1791690443400 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-2-1791690443400 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-2-1791690443400 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-2-1791690443400 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-2-1791690443400 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-2-1791690443400 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-2-1791690443400 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}执行事务写入 Redo Log写入 Binlog返回提交结果

这种协作确保了主从复制的数据一致性。

三、Redo Log（重做日志）：崩溃恢复的关键

3.1 Redo Log 的作用与结构

Redo Log 是 InnoDB 存储引擎特有的日志，记录事务对数据页的物理修改。当数据库崩溃时，可以通过 Redo Log 恢复未持久化的数据。

Redo Log 采用循环写入的方式，由多个日志文件组成，形成日志组。InnoDB 使用"Write-Ahead Logging"策略，先写 Redo Log，再修改数据页。

3.2 Redo Log 的写入机制

Redo Log 的写入分为两个阶段：

#publish-mermaid-3-1791690443419{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-3-1791690443419 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-3-1791690443419 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-3-1791690443419 .error-icon{fill:#552222;}#publish-mermaid-3-1791690443419 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-3-1791690443419 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-3-1791690443419 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-3-1791690443419 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-3-1791690443419 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-3-1791690443419 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-3-1791690443419 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-3-1791690443419 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-3-1791690443419 .marker.cross{stroke:#333333;}#publish-mermaid-3-1791690443419 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-3-1791690443419 p{margin:0;}#publish-mermaid-3-1791690443419 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-3-1791690443419 .cluster-label text{fill:#333;}#publish-mermaid-3-1791690443419 .cluster-label span{color:#333;}#publish-mermaid-3-1791690443419 .cluster-label span p{background-color:transparent;}#publish-mermaid-3-1791690443419 .label text,#publish-mermaid-3-1791690443419 span{fill:#333;color:#333;}#publish-mermaid-3-1791690443419 .node rect,#publish-mermaid-3-1791690443419 .node circle,#publish-mermaid-3-1791690443419 .node ellipse,#publish-mermaid-3-1791690443419 .node polygon,#publish-mermaid-3-1791690443419 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-3-1791690443419 .rough-node .label text,#publish-mermaid-3-1791690443419 .node .label text,#publish-mermaid-3-1791690443419 .image-shape .label,#publish-mermaid-3-1791690443419 .icon-shape .label{text-anchor:middle;}#publish-mermaid-3-1791690443419 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-3-1791690443419 .rough-node .label,#publish-mermaid-3-1791690443419 .node .label,#publish-mermaid-3-1791690443419 .image-shape .label,#publish-mermaid-3-1791690443419 .icon-shape .label{text-align:center;}#publish-mermaid-3-1791690443419 .node.clickable{cursor:pointer;}#publish-mermaid-3-1791690443419 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-3-1791690443419 .arrowheadPath{fill:#333333;}#publish-mermaid-3-1791690443419 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-3-1791690443419 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-3-1791690443419 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-3-1791690443419 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-3-1791690443419 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-3-1791690443419 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-3-1791690443419 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-3-1791690443419 .cluster text{fill:#333;}#publish-mermaid-3-1791690443419 .cluster span{color:#333;}#publish-mermaid-3-1791690443419 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-3-1791690443419 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-3-1791690443419 rect.text{fill:none;stroke-width:0;}#publish-mermaid-3-1791690443419 .icon-shape,#publish-mermaid-3-1791690443419 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-3-1791690443419 .icon-shape p,#publish-mermaid-3-1791690443419 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-3-1791690443419 .icon-shape .label rect,#publish-mermaid-3-1791690443419 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-3-1791690443419 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-3-1791690443419 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-3-1791690443419 .node .neo-node{stroke:#9370DB;}#publish-mermaid-3-1791690443419 [data-look=\”neo\”].node rect,#publish-mermaid-3-1791690443419 [data-look=\”neo\”].cluster rect,#publish-mermaid-3-1791690443419 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-3-1791690443419 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-3-1791690443419 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-3-1791690443419 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-3-1791690443419 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-3-1791690443419 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-3-1791690443419 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-3-1791690443419 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-3-1791690443419 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-3-1791690443419 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}是否

事务修改数据

写入 Redo Log Buffer

缓冲区满或事务提交?

刷入 Redo Log File

InnoDB 通过 checkpoint 机制定期将 Redo Log 刷入磁盘，确保日志的持久性。

3.3 Redo Log 与 Undo Log 的协作

Redo Log 记录事务的"前像"，Undo Log 记录事务的"后像"。在崩溃恢复时，先通过 Redo Log 恢复已提交的事务，再通过 Undo Log 回滚未提交的事务。

#publish-mermaid-4-1791690443447{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-4-1791690443447 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-4-1791690443447 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-4-1791690443447 .error-icon{fill:#552222;}#publish-mermaid-4-1791690443447 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-4-1791690443447 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-4-1791690443447 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-4-1791690443447 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-4-1791690443447 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-4-1791690443447 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-4-1791690443447 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-4-1791690443447 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-4-1791690443447 .marker.cross{stroke:#333333;}#publish-mermaid-4-1791690443447 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-4-1791690443447 p{margin:0;}#publish-mermaid-4-1791690443447 defs [id$=\”-barbEnd\”]{fill:#333333;stroke:#333333;}#publish-mermaid-4-1791690443447 g.stateGroup text{fill:#9370DB;stroke:none;font-size:10px;}#publish-mermaid-4-1791690443447 g.stateGroup text{fill:#333;stroke:none;font-size:10px;}#publish-mermaid-4-1791690443447 g.stateGroup .state-title{font-weight:bolder;fill:#131300;}#publish-mermaid-4-1791690443447 g.stateGroup rect{fill:#ECECFF;stroke:#9370DB;}#publish-mermaid-4-1791690443447 g.stateGroup line{stroke:#333333;stroke-width:1;}#publish-mermaid-4-1791690443447 .transition{stroke:#333333;stroke-width:1;fill:none;}#publish-mermaid-4-1791690443447 .stateGroup .composit{fill:white;border-bottom:1px;}#publish-mermaid-4-1791690443447 .stateGroup .alt-composit{fill:#e0e0e0;border-bottom:1px;}#publish-mermaid-4-1791690443447 .state-note{stroke:#aaaa33;fill:#fff5ad;}#publish-mermaid-4-1791690443447 .state-note text{fill:black;stroke:none;font-size:10px;}#publish-mermaid-4-1791690443447 .stateLabel .box{stroke:none;stroke-width:0;fill:#ECECFF;opacity:0.5;}#publish-mermaid-4-1791690443447 .edgeLabel .label rect{fill:#ECECFF;opacity:0.5;}#publish-mermaid-4-1791690443447 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-4-1791690443447 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-4-1791690443447 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-4-1791690443447 .edgeLabel .label text{fill:#333;}#publish-mermaid-4-1791690443447 .label div .edgeLabel{color:#333;}#publish-mermaid-4-1791690443447 .stateLabel text{fill:#131300;font-size:10px;font-weight:bold;}#publish-mermaid-4-1791690443447 .node circle.state-start{fill:#333333;stroke:#333333;}#publish-mermaid-4-1791690443447 .node .fork-join{fill:#333333;stroke:#333333;}#publish-mermaid-4-1791690443447 .node circle.state-end{fill:#9370DB;stroke:white;stroke-width:1.5;}#publish-mermaid-4-1791690443447 .end-state-inner{fill:white;stroke-width:1.5;}#publish-mermaid-4-1791690443447 .node rect{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-4-1791690443447 .node polygon{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-4-1791690443447 [id$=\”-barbEnd\”]{fill:#333333;}#publish-mermaid-4-1791690443447 .statediagram-cluster rect{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-4-1791690443447 .cluster-label,#publish-mermaid-4-1791690443447 .nodeLabel{color:#131300;}#publish-mermaid-4-1791690443447 .statediagram-cluster rect.outer{rx:5px;ry:5px;}#publish-mermaid-4-1791690443447 .statediagram-state .divider{stroke:#9370DB;}#publish-mermaid-4-1791690443447 .statediagram-state .title-state{rx:5px;ry:5px;}#publish-mermaid-4-1791690443447 .statediagram-cluster.statediagram-cluster .inner{fill:white;}#publish-mermaid-4-1791690443447 .statediagram-cluster.statediagram-cluster-alt .inner{fill:#f0f0f0;}#publish-mermaid-4-1791690443447 .statediagram-cluster .inner{rx:0;ry:0;}#publish-mermaid-4-1791690443447 .statediagram-state rect.basic{rx:5px;ry:5px;}#publish-mermaid-4-1791690443447 .statediagram-state rect.divider{stroke-dasharray:10,10;fill:#f0f0f0;}#publish-mermaid-4-1791690443447 .note-edge{stroke-dasharray:5;}#publish-mermaid-4-1791690443447 .statediagram-note rect{fill:#fff5ad;stroke:#aaaa33;stroke-width:1px;rx:0;ry:0;}#publish-mermaid-4-1791690443447 .statediagram-note rect{fill:#fff5ad;stroke:#aaaa33;stroke-width:1px;rx:0;ry:0;}#publish-mermaid-4-1791690443447 .statediagram-note text{fill:black;}#publish-mermaid-4-1791690443447 .statediagram-note .nodeLabel{color:black;}#publish-mermaid-4-1791690443447 .statediagram .edgeLabel{color:red;}#publish-mermaid-4-1791690443447 [id$=\”-dependencyStart\”],#publish-mermaid-4-1791690443447 [id$=\”-dependencyEnd\”]{fill:#333333;stroke:#333333;stroke-width:1;}#publish-mermaid-4-1791690443447 .statediagramTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-4-1791690443447 [data-look=\”neo\”].statediagram-cluster rect{fill:#ECECFF;stroke:#9370DB;stroke-width:1;}#publish-mermaid-4-1791690443447 [data-look=\”neo\”].statediagram-cluster rect.outer{rx:5px;ry:5px;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-4-1791690443447 .node .neo-node{stroke:#9370DB;}#publish-mermaid-4-1791690443447 [data-look=\”neo\”].node rect,#publish-mermaid-4-1791690443447 [data-look=\”neo\”].cluster rect,#publish-mermaid-4-1791690443447 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-4-1791690443447 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-4-1791690443447 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-4-1791690443447 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-4-1791690443447 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-4-1791690443447 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-4-1791690443447 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-4-1791690443447 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-4-1791690443447 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-4-1791690443447 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

未提交事务

已提交事务：Redo

Log

恢复

已提交事务

：完成

回滚：Undo

回滚

四、Undo Log（回滚日志）：事务原子性的保障

4.1 Undo Log 的作用与类型

Undo Log 主要用于事务回滚和 MVCC。当事务需要回滚时，Undo Log 提供了回滚所需的信息。Undo Log 有两种类型：

• Insert Undo Log：记录插入操作的回滚信息
• Update Undo Log：记录更新操作的回滚信息

4.2 Undo Log 的生成与回收

Undo Log 在事务修改数据时生成，当事务提交或回滚时，Undo Log 可能被标记为可回收。InnoDB 通过 purge 线程定期清理已提交事务的 Undo Log。

#publish-mermaid-5-1791690443501{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-5-1791690443501 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-5-1791690443501 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-5-1791690443501 .error-icon{fill:#552222;}#publish-mermaid-5-1791690443501 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-5-1791690443501 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-5-1791690443501 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-5-1791690443501 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-5-1791690443501 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-5-1791690443501 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-5-1791690443501 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-5-1791690443501 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-5-1791690443501 .marker.cross{stroke:#333333;}#publish-mermaid-5-1791690443501 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-5-1791690443501 p{margin:0;}#publish-mermaid-5-1791690443501 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-5-1791690443501 .cluster-label text{fill:#333;}#publish-mermaid-5-1791690443501 .cluster-label span{color:#333;}#publish-mermaid-5-1791690443501 .cluster-label span p{background-color:transparent;}#publish-mermaid-5-1791690443501 .label text,#publish-mermaid-5-1791690443501 span{fill:#333;color:#333;}#publish-mermaid-5-1791690443501 .node rect,#publish-mermaid-5-1791690443501 .node circle,#publish-mermaid-5-1791690443501 .node ellipse,#publish-mermaid-5-1791690443501 .node polygon,#publish-mermaid-5-1791690443501 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-5-1791690443501 .rough-node .label text,#publish-mermaid-5-1791690443501 .node .label text,#publish-mermaid-5-1791690443501 .image-shape .label,#publish-mermaid-5-1791690443501 .icon-shape .label{text-anchor:middle;}#publish-mermaid-5-1791690443501 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-5-1791690443501 .rough-node .label,#publish-mermaid-5-1791690443501 .node .label,#publish-mermaid-5-1791690443501 .image-shape .label,#publish-mermaid-5-1791690443501 .icon-shape .label{text-align:center;}#publish-mermaid-5-1791690443501 .node.clickable{cursor:pointer;}#publish-mermaid-5-1791690443501 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-5-1791690443501 .arrowheadPath{fill:#333333;}#publish-mermaid-5-1791690443501 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-5-1791690443501 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-5-1791690443501 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-5-1791690443501 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-5-1791690443501 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-5-1791690443501 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-5-1791690443501 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-5-1791690443501 .cluster text{fill:#333;}#publish-mermaid-5-1791690443501 .cluster span{color:#333;}#publish-mermaid-5-1791690443501 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-5-1791690443501 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-5-1791690443501 rect.text{fill:none;stroke-width:0;}#publish-mermaid-5-1791690443501 .icon-shape,#publish-mermaid-5-1791690443501 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-5-1791690443501 .icon-shape p,#publish-mermaid-5-1791690443501 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-5-1791690443501 .icon-shape .label rect,#publish-mermaid-5-1791690443501 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-5-1791690443501 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-5-1791690443501 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-5-1791690443501 .node .neo-node{stroke:#9370DB;}#publish-mermaid-5-1791690443501 [data-look=\”neo\”].node rect,#publish-mermaid-5-1791690443501 [data-look=\”neo\”].cluster rect,#publish-mermaid-5-1791690443501 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-5-1791690443501 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-5-1791690443501 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-5-1791690443501 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-5-1791690443501 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-5-1791690443501 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-5-1791690443501 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-5-1791690443501 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-5-1791690443501 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-5-1791690443501 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}是否

事务开始

生成 Undo Log

事务提交?

标记 Undo Log 可回收

保留 Undo Log

Purge 线程清理

4.3 Undo Log 在 MVCC 中的作用

Undo Log 是 MVCC 的基础。当读取数据时，如果数据被其他事务修改，可以通过 Undo Log 获取历史版本。

Undo Log数据页事务B事务AUndo Log数据页事务B事务A#publish-mermaid-6-1791690443535{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-6-1791690443535 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-6-1791690443535 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-6-1791690443535 .error-icon{fill:#552222;}#publish-mermaid-6-1791690443535 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-6-1791690443535 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-6-1791690443535 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-6-1791690443535 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-6-1791690443535 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-6-1791690443535 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-6-1791690443535 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-6-1791690443535 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-6-1791690443535 .marker.cross{stroke:#333333;}#publish-mermaid-6-1791690443535 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-6-1791690443535 p{margin:0;}#publish-mermaid-6-1791690443535 .actor{stroke:#9370DB;fill:#ECECFF;stroke-width:1;}#publish-mermaid-6-1791690443535 rect.actor.outer-path[data-look=\”neo\”]{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-6-1791690443535 rect.note[data-look=\”neo\”]{stroke:#aaaa33;fill:#fff5ad;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-6-1791690443535 text.actor>tspan{fill:black;stroke:none;}#publish-mermaid-6-1791690443535 .actor-line{stroke:#9370DB;}#publish-mermaid-6-1791690443535 .innerArc{stroke-width:1.5;stroke-dasharray:none;}#publish-mermaid-6-1791690443535 .messageLine0{stroke-width:1.5;stroke-dasharray:none;stroke:#333;}#publish-mermaid-6-1791690443535 .messageLine1{stroke-width:1.5;stroke-dasharray:2,2;stroke:#333;}#publish-mermaid-6-1791690443535 [id$=\”-arrowhead\”] path{fill:#333;stroke:#333;}#publish-mermaid-6-1791690443535 .sequenceNumber{fill:white;}#publish-mermaid-6-1791690443535 [id$=\”-sequencenumber\”]{fill:#333;}#publish-mermaid-6-1791690443535 [id$=\”-crosshead\”] path{fill:#333;stroke:#333;}#publish-mermaid-6-1791690443535 .messageText{fill:#333;stroke:none;}#publish-mermaid-6-1791690443535 .labelBox{stroke:#9370DB;fill:#ECECFF;filter:none;}#publish-mermaid-6-1791690443535 .labelText,#publish-mermaid-6-1791690443535 .labelText>tspan{fill:black;stroke:none;}#publish-mermaid-6-1791690443535 .loopText,#publish-mermaid-6-1791690443535 .loopText>tspan{fill:black;stroke:none;}#publish-mermaid-6-1791690443535 .sectionTitle,#publish-mermaid-6-1791690443535 .sectionTitle>tspan{fill:black;stroke:none;}#publish-mermaid-6-1791690443535 .loopLine{stroke-width:2px;stroke-dasharray:2,2;stroke:#9370DB;fill:#9370DB;}#publish-mermaid-6-1791690443535 .note{stroke:#aaaa33;fill:#fff5ad;}#publish-mermaid-6-1791690443535 .noteText,#publish-mermaid-6-1791690443535 .noteText>tspan{fill:black;stroke:none;font-weight:normal;}#publish-mermaid-6-1791690443535 .activation0{fill:#f4f4f4;stroke:#666;}#publish-mermaid-6-1791690443535 .activation1{fill:#f4f4f4;stroke:#666;}#publish-mermaid-6-1791690443535 .activation2{fill:#f4f4f4;stroke:#666;}#publish-mermaid-6-1791690443535 .actorPopupMenu{position:absolute;}#publish-mermaid-6-1791690443535 .actorPopupMenuPanel{position:absolute;fill:#ECECFF;box-shadow:0px 8px 16px 0px rgba(0,0,0,0.2);filter:drop-shadow(3px 5px 2px rgb(0 0 0 / 0.4));}#publish-mermaid-6-1791690443535 .actor-man circle,#publish-mermaid-6-1791690443535 line{fill:#ECECFF;stroke-width:2px;}#publish-mermaid-6-1791690443535 g rect.rect{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));stroke:#9370DB;}#publish-mermaid-6-1791690443535 .node .neo-node{stroke:#9370DB;}#publish-mermaid-6-1791690443535 [data-look=\”neo\”].node rect,#publish-mermaid-6-1791690443535 [data-look=\”neo\”].cluster rect,#publish-mermaid-6-1791690443535 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-6-1791690443535 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-6-1791690443535 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-6-1791690443535 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-6-1791690443535 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-6-1791690443535 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-6-1791690443535 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-6-1791690443535 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-6-1791690443535 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-6-1791690443535 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}读取数据修改数据生成回滚信息获取历史版本返回历史数据

五、三大日志的协作机制：事务的生命周期

5.1 事务提交流程

一个完整的事务提交流程涉及三大日志的协同工作：

#publish-mermaid-7-1791690443554{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-7-1791690443554 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-7-1791690443554 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-7-1791690443554 .error-icon{fill:#552222;}#publish-mermaid-7-1791690443554 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-7-1791690443554 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-7-1791690443554 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-7-1791690443554 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-7-1791690443554 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-7-1791690443554 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-7-1791690443554 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-7-1791690443554 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-7-1791690443554 .marker.cross{stroke:#333333;}#publish-mermaid-7-1791690443554 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-7-1791690443554 p{margin:0;}#publish-mermaid-7-1791690443554 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-7-1791690443554 .cluster-label text{fill:#333;}#publish-mermaid-7-1791690443554 .cluster-label span{color:#333;}#publish-mermaid-7-1791690443554 .cluster-label span p{background-color:transparent;}#publish-mermaid-7-1791690443554 .label text,#publish-mermaid-7-1791690443554 span{fill:#333;color:#333;}#publish-mermaid-7-1791690443554 .node rect,#publish-mermaid-7-1791690443554 .node circle,#publish-mermaid-7-1791690443554 .node ellipse,#publish-mermaid-7-1791690443554 .node polygon,#publish-mermaid-7-1791690443554 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-7-1791690443554 .rough-node .label text,#publish-mermaid-7-1791690443554 .node .label text,#publish-mermaid-7-1791690443554 .image-shape .label,#publish-mermaid-7-1791690443554 .icon-shape .label{text-anchor:middle;}#publish-mermaid-7-1791690443554 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-7-1791690443554 .rough-node .label,#publish-mermaid-7-1791690443554 .node .label,#publish-mermaid-7-1791690443554 .image-shape .label,#publish-mermaid-7-1791690443554 .icon-shape .label{text-align:center;}#publish-mermaid-7-1791690443554 .node.clickable{cursor:pointer;}#publish-mermaid-7-1791690443554 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-7-1791690443554 .arrowheadPath{fill:#333333;}#publish-mermaid-7-1791690443554 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-7-1791690443554 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-7-1791690443554 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-7-1791690443554 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-7-1791690443554 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-7-1791690443554 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-7-1791690443554 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-7-1791690443554 .cluster text{fill:#333;}#publish-mermaid-7-1791690443554 .cluster span{color:#333;}#publish-mermaid-7-1791690443554 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-7-1791690443554 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-7-1791690443554 rect.text{fill:none;stroke-width:0;}#publish-mermaid-7-1791690443554 .icon-shape,#publish-mermaid-7-1791690443554 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-7-1791690443554 .icon-shape p,#publish-mermaid-7-1791690443554 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-7-1791690443554 .icon-shape .label rect,#publish-mermaid-7-1791690443554 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-7-1791690443554 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-7-1791690443554 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-7-1791690443554 .node .neo-node{stroke:#9370DB;}#publish-mermaid-7-1791690443554 [data-look=\”neo\”].node rect,#publish-mermaid-7-1791690443554 [data-look=\”neo\”].cluster rect,#publish-mermaid-7-1791690443554 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-7-1791690443554 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-7-1791690443554 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-7-1791690443554 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-7-1791690443554 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-7-1791690443554 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-7-1791690443554 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-7-1791690443554 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-7-1791690443554 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-7-1791690443554 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

开始事务

执行 SQL

生成 Undo Log

写入 Redo Log

写入 Binlog

提交事务

结束事务

5.2 崩溃恢复流程

当数据库崩溃后，恢复流程如下：

#publish-mermaid-8-1791690443592{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-8-1791690443592 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-8-1791690443592 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-8-1791690443592 .error-icon{fill:#552222;}#publish-mermaid-8-1791690443592 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-8-1791690443592 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-8-1791690443592 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-8-1791690443592 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-8-1791690443592 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-8-1791690443592 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-8-1791690443592 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-8-1791690443592 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-8-1791690443592 .marker.cross{stroke:#333333;}#publish-mermaid-8-1791690443592 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-8-1791690443592 p{margin:0;}#publish-mermaid-8-1791690443592 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-8-1791690443592 .cluster-label text{fill:#333;}#publish-mermaid-8-1791690443592 .cluster-label span{color:#333;}#publish-mermaid-8-1791690443592 .cluster-label span p{background-color:transparent;}#publish-mermaid-8-1791690443592 .label text,#publish-mermaid-8-1791690443592 span{fill:#333;color:#333;}#publish-mermaid-8-1791690443592 .node rect,#publish-mermaid-8-1791690443592 .node circle,#publish-mermaid-8-1791690443592 .node ellipse,#publish-mermaid-8-1791690443592 .node polygon,#publish-mermaid-8-1791690443592 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-8-1791690443592 .rough-node .label text,#publish-mermaid-8-1791690443592 .node .label text,#publish-mermaid-8-1791690443592 .image-shape .label,#publish-mermaid-8-1791690443592 .icon-shape .label{text-anchor:middle;}#publish-mermaid-8-1791690443592 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-8-1791690443592 .rough-node .label,#publish-mermaid-8-1791690443592 .node .label,#publish-mermaid-8-1791690443592 .image-shape .label,#publish-mermaid-8-1791690443592 .icon-shape .label{text-align:center;}#publish-mermaid-8-1791690443592 .node.clickable{cursor:pointer;}#publish-mermaid-8-1791690443592 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-8-1791690443592 .arrowheadPath{fill:#333333;}#publish-mermaid-8-1791690443592 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-8-1791690443592 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-8-1791690443592 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-8-1791690443592 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-8-1791690443592 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-8-1791690443592 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-8-1791690443592 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-8-1791690443592 .cluster text{fill:#333;}#publish-mermaid-8-1791690443592 .cluster span{color:#333;}#publish-mermaid-8-1791690443592 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-8-1791690443592 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-8-1791690443592 rect.text{fill:none;stroke-width:0;}#publish-mermaid-8-1791690443592 .icon-shape,#publish-mermaid-8-1791690443592 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-8-1791690443592 .icon-shape p,#publish-mermaid-8-1791690443592 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-8-1791690443592 .icon-shape .label rect,#publish-mermaid-8-1791690443592 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-8-1791690443592 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-8-1791690443592 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-8-1791690443592 .node .neo-node{stroke:#9370DB;}#publish-mermaid-8-1791690443592 [data-look=\”neo\”].node rect,#publish-mermaid-8-1791690443592 [data-look=\”neo\”].cluster rect,#publish-mermaid-8-1791690443592 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-8-1791690443592 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-8-1791690443592 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-8-1791690443592 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-8-1791690443592 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-8-1791690443592 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-8-1791690443592 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-8-1791690443592 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-8-1791690443592 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-8-1791690443592 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

数据库启动

检查 Redo Log

应用 Redo Log

应用 Undo Log

恢复完成

5.3 日志的持久化策略

MySQL 提供多种日志持久化策略，可以通过参数配置：

• sync_binlog：控制 Binlog 刷盘时机
• innodb_flush_log_at_trx_commit：控制 Redo Log 刷盘时机

#publish-mermaid-9-1791690443619{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-9-1791690443619 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-9-1791690443619 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-9-1791690443619 .error-icon{fill:#552222;}#publish-mermaid-9-1791690443619 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-9-1791690443619 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-9-1791690443619 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-9-1791690443619 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-9-1791690443619 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-9-1791690443619 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-9-1791690443619 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-9-1791690443619 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-9-1791690443619 .marker.cross{stroke:#333333;}#publish-mermaid-9-1791690443619 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-9-1791690443619 p{margin:0;}#publish-mermaid-9-1791690443619 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-9-1791690443619 .cluster-label text{fill:#333;}#publish-mermaid-9-1791690443619 .cluster-label span{color:#333;}#publish-mermaid-9-1791690443619 .cluster-label span p{background-color:transparent;}#publish-mermaid-9-1791690443619 .label text,#publish-mermaid-9-1791690443619 span{fill:#333;color:#333;}#publish-mermaid-9-1791690443619 .node rect,#publish-mermaid-9-1791690443619 .node circle,#publish-mermaid-9-1791690443619 .node ellipse,#publish-mermaid-9-1791690443619 .node polygon,#publish-mermaid-9-1791690443619 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-9-1791690443619 .rough-node .label text,#publish-mermaid-9-1791690443619 .node .label text,#publish-mermaid-9-1791690443619 .image-shape .label,#publish-mermaid-9-1791690443619 .icon-shape .label{text-anchor:middle;}#publish-mermaid-9-1791690443619 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-9-1791690443619 .rough-node .label,#publish-mermaid-9-1791690443619 .node .label,#publish-mermaid-9-1791690443619 .image-shape .label,#publish-mermaid-9-1791690443619 .icon-shape .label{text-align:center;}#publish-mermaid-9-1791690443619 .node.clickable{cursor:pointer;}#publish-mermaid-9-1791690443619 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-9-1791690443619 .arrowheadPath{fill:#333333;}#publish-mermaid-9-1791690443619 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-9-1791690443619 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-9-1791690443619 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-9-1791690443619 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-9-1791690443619 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-9-1791690443619 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-9-1791690443619 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-9-1791690443619 .cluster text{fill:#333;}#publish-mermaid-9-1791690443619 .cluster span{color:#333;}#publish-mermaid-9-1791690443619 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-9-1791690443619 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-9-1791690443619 rect.text{fill:none;stroke-width:0;}#publish-mermaid-9-1791690443619 .icon-shape,#publish-mermaid-9-1791690443619 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-9-1791690443619 .icon-shape p,#publish-mermaid-9-1791690443619 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-9-1791690443619 .icon-shape .label rect,#publish-mermaid-9-1791690443619 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-9-1791690443619 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-9-1791690443619 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-9-1791690443619 .node .neo-node{stroke:#9370DB;}#publish-mermaid-9-1791690443619 [data-look=\”neo\”].node rect,#publish-mermaid-9-1791690443619 [data-look=\”neo\”].cluster rect,#publish-mermaid-9-1791690443619 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-9-1791690443619 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-9-1791690443619 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-9-1791690443619 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-9-1791690443619 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-9-1791690443619 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-9-1791690443619 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-9-1791690443619 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-9-1791690443619 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-9-1791690443619 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}01N012

事务提交

sync_binlog 设置

不立即刷盘

立即刷盘

每 N 次事务刷盘

innodb_flush_log_at_trx_commit 设置

不立即刷盘

立即刷盘

每秒刷盘

六、性能优化与最佳实践：日志系统的调优

6.1 日志文件大小配置

合理配置日志文件大小可以平衡性能和恢复时间：

• binlog_size：控制 Binlog 文件大小
• innodb_log_file_size：控制 Redo Log 文件大小

6.2 日志刷盘策略优化

根据业务需求选择合适的刷盘策略：

• 高性能场景：适当降低刷盘频率
• 高可靠性场景：提高刷盘频率

6.3 日志清理与归档

定期清理和归档日志文件，避免磁盘空间不足：

• 自动清理：设置 expire_logs_days
• 手动归档：定期备份并删除旧日志

七、常见问题与解决方案：日志相关的故障排查

7.1 Binlog 丢失问题

Binlog 丢失可能导致主从数据不一致。解决方案包括：

• 配置双机热备
• 定期检查 Binlog 同步状态

7.2 Redo Log 溢出问题

Redo Log 溢出会导致数据库停机。解决方案：

• 增加日志文件大小
• 调整 checkpoint 频率

7.3 Undo Log 膨胀问题

Undo Log 膨胀会影响数据库性能。解决方案：

• 优化长事务
• 调整 purge 线程参数