一条 SQL 的一生：从执行流程到 redo-log 与 binlog 两阶段提交实战

一条 SQL 的一生从执行流程到 redo-log 与 binlog 两阶段提交实战关键词SQL 执行流程、redo log、binlog、两阶段提交、WAL、双 1、crash-safe、MySQL 45 讲实验环境真实云服务器 Ubuntu 24.04 MySQL 8.0.46所有输出均为实机回显IP 已脱敏为121.36.***.***。一、引子面试里那段“经典八股”到底在考什么面试官笑着问“你说一条UPDATE语句在 MySQL 里是怎么执行的redo log 和 binlog 谁先写如果写到一半机器宕机了数据库怎么保证不丢数据、主从不串”这道题是丁奇《MySQL 实战 45 讲》第 1、2、23 讲的核心。很多人背过“两阶段提交”四个字但一到追问就露怯redo log 是干嘛的为什么有了它还要 binlog两阶段提交到底是哪两阶段prepare / commit 落到哪两个日志上为什么说innodb_flush_log_at_trx_commit1和sync_binlog1俗称“双 1”是数据安全的底线又为什么性能最差本文我在真实云服务器上把“一条 SQL 的一生”从连接器一路跑到 InnoDB 的 redo log 和 binlog再用 sysbench 把“双 1”的性能代价量出来全部是实机回显不靠脑补。二、环境说明真实机器配置实验在一台 8C / 14G 的云服务器上完成公网 IP 脱敏为121.36.***.***系统信息如下$uname-aLinux ecs-fb52-00016.8.0-106-generic#106-Ubuntu SMP PREEMPT_DYNAMIC Fri Mar 6 07:58:08 UTC 2026 x86_64 x86_64 x86_64 GNU/Linux$cat/etc/os-release|head-3PRETTY_NAMEUbuntu 24.04.4 LTSVERSION_ID24.04$ nproc8$free-htotal usedfreeMem: 14Gi 478Mi 14Gi $ mysql--versionmysql Ver8.0.46-0ubuntu0.24.04.3forLinux on x86_64((Ubuntu))MySQL 用 Ubuntu 仓库默认装的mysql-server8.0.46。root 账号在 Ubuntu 上默认走auth_socket插件通过本地 socket 免密登录所以实验里所有命令都是mysql -uroot连接走/var/run/mysqld/mysqld.sock不需要密码。下面是与本主题最相关的几个全局参数全部是实机SELECT的真实返回值mysqlSELECTinnodb_buffer_pool_size,innodb_buffer_pool_sizeUNIONALLSELECTinnodb_flush_log_at_trx_commit,innodb_flush_log_at_trx_commitUNIONALLSELECTsync_binlog,sync_binlogUNIONALLSELECTinnodb_redo_log_capacity,innodb_redo_log_capacityUNIONALLSELECTlog_bin,log_binUNIONALLSELECTbinlog_format,binlog_formatUNIONALLSELECTtransaction_isolation,transaction_isolation;-------------------------------------------------------------------|innodb_buffer_pool_size|134217728|-- 128M|innodb_flush_log_at_trx_commit|1||sync_binlog|1||innodb_redo_log_capacity|104857600|-- 100M|log_bin|1|-- binlog 已开启|binlog_format|ROW||transaction_isolation|REPEATABLE-READ|-------------------------------------------------------------------注意默认innodb_flush_log_at_trx_commit1、sync_binlog1也就是“双 1”全开这是最安全、也是本实验后文要对比的基线。三、原理讲解一条 SQL 在 MySQL 里走了哪几站3.1 执行流程Server 层 存储引擎层MySQL 是“插件式存储引擎”架构一条 SQL 要经过Server 层和存储引擎层本文是 InnoDB两大部分客户端 (mysql / 应用驱动) │ ▼ ┌───────────────┐ │ 连接器 │ 管理连接、权限校验、维持连接池 └───────┬───────┘ ▼ ┌───────────────┐ │ 查询缓存(8.0移除)│ MySQL 8.0 已彻底删除 query cache └───────┬───────┘ ▼ ┌───────────────┐ │ 分析器 │ 词法分析 语法分析识别 SQL 语义 └───────┬───────┘ ▼ ┌───────────────┐ │ 优化器 │ 决定索引、join 顺序、执行计划 └───────┬───────┘ ▼ ┌───────────────┐ │ 执行器 │ 调用存储引擎接口逐行取/写 └───────┬───────┘ ▼ ┌───────────────┐ │ InnoDB 引擎 │ buffer pool / redo log / undo log / 索引 └───────────────┘我用一张极简表把流程跑出来看实况。建表插数mysqlDROPDATABASEIFEXISTStest;CREATEDATABASEtest;USEtest;mysqlCREATETABLET(idintprimarykey,cint);mysqlINSERTINTOTVALUES(1,1),(2,2),(3,3),(4,4),(5,5);mysqlSELECT*FROMT;-------|id|c|-------|1|1||2|2||3|3||4|4||5|5|-------对c这个没有索引的列做EXPLAIN看到的是全表扫描mysqlEXPLAINSELECT*FROMTWHEREc3;-----------------------------------------------------------------------------------------|id|select_type|table|type|possible_keys|key|key_len|ref|rows|filtered|Extra|-----------------------------------------------------------------------------------------|1|SIMPLE|T|ALL|NULL|NULL|NULL|NULL|5|20.00|Usingwhere|-----------------------------------------------------------------------------------------typeALL、keyNULL引擎层只能把整张表 5 行都扫一遍。这说明执行器在“取数”时要反复调 InnoDB 接口——而接口背后就是 buffer pool、undo、redo 那一套。3.2 为什么需要两套日志redo log 与 binlog这是面试最爱追问的点必须分清“谁”为什么存在redo logInnoDB 独有物理日志记录“某个数据页做了什么改动”循环写、固定大小。它的存在是为了实现WALWrite-Ahead Logging——脏页先写日志再落盘这样即使脏页还没刷盘宕机后也能靠 redo log 把已提交的事务恢复出来即crash-safe。redo log 是“引擎层的重做日志”。binlogServer 层逻辑日志记录“对哪个表哪行做了什么操作”ROW 格式下是行的前镜像/后镜像追加写、不覆盖。它用于主从复制和按时间点恢复PITR。binlog 是“Server 层的归档日志”。因为一个事务既要写 InnoDB 的 redo又要写 Server 的 binlog而两者是独立的日志系统如果“先写 A 再写 B”中途宕机两份日志就会不一致redo 说提交了、binlog 没记录主库有、从库没有或者反过来。于是 MySQL 用**两阶段提交2PC**把这两步绑定成原子操作。3.3 两阶段提交流程核心当事务提交时InnoDB 与 Server 层的协作如下InnoDB redo log Server binlog ─────────────────────── ───────────────────────────── 事务执行生成 redo处于 prepare 状态前 ① prepare 阶段 ──────────────► 将 redo 标记为 PREPARE并 fsync 落盘 ② 写 binlog ─────────────────────────────► 写入 binlog并按 sync_binlog 决定是否 fsync ③ commit 阶段 ──────────────► 将 redo 标记为 COMMIT写入 commit 标记事务对外可见崩溃恢复时的判定规则面试高频如果崩溃在①之前redo 没有 prepare、binlog 没有记录 → 事务回滚。如果崩溃在①②之间redo 是 prepare、binlog 没有完整记录 → 回滚。如果崩溃在②③之间redo 是 prepare、binlog 已完整写入 →重做commit因为 binlog 已经能喂给从库了必须让主库也提交保证主从一致。如果崩溃在③之后redo 已是 commit → 正常提交。InnoDB 在恢复时会扫描 prepare 状态的 redo拿着事务 XID 去 binlog 里找有没有对应的完整事务有就提交、没有就回滚。这就是“两阶段提交保证两份日志逻辑一致”的本质。四、分步实操真实命令 真实回显4.1 用 general_log 抓一条 SQL 的“执行痕迹”为了看 SQL 真正经过哪些接口我临时打开通用查询日志跑一条SELECT再抓日志内容然后关闭mysqlSETGLOBALgeneral_log1;mysqlUSEtest;SELECT*FROMTWHEREid1;-------|id|c|-------|1|1|-------general_log 文件落盘在/var/lib/mysql/ecs-fb52-0001.log尾部实况Time Id Command Argument 2026-07-29T03:47:41.209481Z 15 Connect rootlocalhost on using Socket 2026-07-29T03:47:41.209593Z 15 Query select version_comment limit 1 2026-07-29T03:47:41.209733Z 15 Init DB test 2026-07-29T03:47:41.209787Z 15 Query SELECT * FROM T WHERE id1 2026-07-29T03:47:41.209963Z 15 Quit可以看到一条查询在协议层被拆成了Connect → Query(version_comment) → Init DB → Query(SELECT) → Quit这正是“连接器 / 执行器 / 存储引擎”协作的真实痕迹。验证完立刻SET GLOBAL general_log0;关掉避免日志膨胀。4.2 版本演进素材query cache 在 8.0 已死mysqlSHOWVARIABLESLIKEquery_cache%;Emptyset(0.00sec)空空集。MySQL 8.0 直接移除了查询缓存——因为只要有写整个表的缓存就失效高并发下反而成锁瓶颈。这是面试官喜欢顺带问的“版本演进坑”。4.3 binlog 状态查看的版本坑真实报错我想确认当前 binlog 位点先试了 8.4 才引入的写法mysqlSHOWBINARYLOGSTATUS;ERROR1064(42000)at line1: You have an errorinSQLsyntax;checkthe manual that correspondstoyour MySQL server versionfortherightsyntaxtousenearLOG STATUSat line1踩坑实录SHOW BINARY LOG STATUS是 MySQL 8.4 起才有的语法在 8.0.46 上直接报 1064 语法错。8.0 上应当用老写法SHOW MASTER STATUS或SHOW BINARY LOGS看文件清单mysqlSHOWMASTERSTATUS;-------------------------------------------------------------------------|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|-------------------------------------------------------------------------|binlog.000003|1043||||-------------------------------------------------------------------------所以本文所有实验都在 8.0 上用SHOW MASTER STATUS取位点避免踩这个新老版本不一致的坑。4.4 用 mysqlbinlog 解析 ROW 格式的真实改动我执行一条UPDATE后用mysqlbinlog --base64-outputdecode-rows -v把最新 binlog 解码binlog_formatROW所以看到的是行的前/后镜像而不是 SQL 文本# at 368 #260729 11:47:44 server id 1 end_log_pos 422 CRC32 0x301582fc Update_rows: table id 85 flags: STMT_END_F ### UPDATE test.T ### WHERE ### 11 -- 第1列(id) 旧值 ### 21 -- 第2列(c) 旧值 ### SET ### 11 ### 22 -- 第2列(c) 新值1 - 2 ... # at 664 #260729 11:48:28 server id 1 end_log_pos 718 CRC32 0xb543ecf4 Update_rows: table id 85 flags: STMT_END_F ### UPDATE test.T ### WHERE ### 12 ### 22 ### SET ### 12 ### 23 -- id2 的行c 从 2 - 3这就是 ROW 格式 binlog 的威力主从复制时从库直接拿到“id1 这行 c 由 1 改成 2”的精确前后镜像不依赖从库重放 SQL也不会因为函数如NOW()、UUID()产生主从不一致。4.5 看 InnoDB redo log 的真实状态SHOW ENGINE INNODB STATUS的 LOG 段截取几个关键字段LOG --- Log capacity 104857600 Log capacity used 104857600 Log sequence number 19439493 Log buffer assigned up to 19439493 Log buffer completed up to 19439493 Log written up to 19439493 Log flushed up to 19439493 Added dirty pages up to 19439493 Pages flushed up to 19438377 Last checkpoint at 19438377 Log minimum file id is 5 Log maximum file id is 5 69 log i/os done, 1.38 log i/os/second要点Log sequence number (LSN)当前日志序列号代表“已经写到 redo 的逻辑位置”。Log flushed up to已经刷到磁盘的 redo 位置。LSN flushed说明刚才的写入已经落盘本机 flush 参数1。Last checkpoint at上一次检查点小于 LSN 的部分是可以被覆盖的重做区checkpoint 越落后崩溃恢复要回放的 redo 越多。五、sysbench 实测“双 1”到底有多慢光讲原理不够我把innodb_flush_log_at_trx_commit简称 f和sync_binlog简称 s四个组合各跑一轮oltp_read_write8 线程、每轮 30 秒、10 万行单表真实数字如下组合innodb_flush_log_at_trx_commitsync_binlogTPSQPSavg 延迟95% 延迟双 1最安全11981.8719637.338.15 ms11.24 msf1,s0101388.6527772.965.76 ms8.28 msf2,s1211748.2434965.404.57 ms6.67 ms全 0最快203561.4671229.682.25 ms3.82 ms准备阶段实机回显$ sysbench oltp_read_write prepare --mysql-socket/var/run/mysqld/mysqld.sock --mysql-userroot --db-drivermysql--tables1--table-size100000sysbench1.0.20(using system LuaJIT2.1.0-beta3)Creating tablesbtest1... Inserting100000records intosbtest1Creating a secondary index onsbtest1...“双 1”那一轮的真实尾部[ 10s ] thds: 8 tps: 939.16 qps: 18798.36 (r/w/o: 13159.41/3759.83/1879.12) lat (ms,95%): 11.87 [ 20s ] thds: 8 tps: 967.80 qps: 19355.73 (r/w/o: 13549.22/3870.91/1935.60) lat (ms,95%): 11.45 [ 30s ] thds: 8 tps: 1038.70 qps: 20771.74 (r/w/o: 14540.36/4153.99/2077.39) lat (ms,95%): 10.46 SQL statistics: transactions: 29465 (981.87 per sec.) queries: 589300 (19637.33 per sec.) Latency (ms): min: 3.23 avg: 8.15 max: 39.49 95th percentile: 11.24“全 0”那一轮性能天花板但最不安全[ 10s ] thds: 8 tps: 3522.97 qps: 70469.12 (r/w/o: 49330.19/14092.18/7046.74) lat (ms,95%): 3.82 [ 20s ] thds: 8 tps: 3579.06 qps: 71580.23 (r/w/o: 50105.86/14316.15/7158.22) lat (ms,95%): 3.82 SQL statistics: transactions: 106853 (3561.46 per sec.) queries: 2137077 (71229.68 per sec.) Latency (ms): avg: 2.25 95th percentile: 3.82结论真实数据支撑最严格的“双 1”TPS 只有981.87而放开成f2,s0后 TPS 飙升到3561.46是 3.6 倍差距——延迟从 8.15 ms 降到 2.25 ms。f2事务提交只写 redo 到 OS buffer不强制 fsync比f1提升明显sync_binlog0binlog 靠 OS 刷盘不每次 fsync同样带来巨大提升。取舍很清晰金融/核心交易走双 1宁可慢也要不丢日志/统计类可从性能角度放松。这也是两阶段提交“安全 vs 性能”矛盾的最直观体现双 1 让两阶段里每一次 fsync 都落盘自然最慢但最稳。六、踩坑记录真实报错SHOW BINARY LOG STATUS在 8.0 报 1064这是 8.4 的新语法8.0 必须用SHOW MASTER STATUS。本文因此改用老写法并保留报错作为版本坑素材。Ubuntu 上 root 免密登录默认auth_socket用密码连反而失败实验统一走本地 socketmysql -uroot。query cache 返回空集8.0 已移除别再调query_cache_size之类的参数。EXPLAIN 显示全表扫描对无索引列c过滤必然typeALL真实业务里该建索引这也提醒我们执行器取数要走引擎接口索引缺失会放大 redo/IO 压力。sysbench 必须建库oltp_read_write prepare默认库名sbtest先CREATE DATABASE IF NOT EXISTS sbtest;再跑避免Unknown database。七、面试高频问答一条 UPDATE 语句在 MySQL 里怎么执行连接器鉴权 → 查询缓存8.0 已移除→ 分析器词法/语法→ 优化器选索引/执行计划→ 执行器调用 InnoDB 接口 → InnoDB 在 buffer pool 改数据、写 undo、写 redoprepare→ Server 写 binlog → InnoDB 写 redocommit。redo log 和 binlog 有什么区别为什么都要redo 是 InnoDB 物理日志、循环写、用于 crash-safeWALbinlog 是 Server 逻辑日志、追加写、用于主从复制和按时间点恢复。两者职责不同必须共存。两阶段提交是哪两阶段崩溃了怎么办prepareredo 置 PREPARE 并 fsync→ 写 binlog → commitredo 置 COMMIT。崩溃恢复时扫描 prepare 的 redo用 XID 去 binlog 找完整事务有则提交无则回滚保证两日志一致。什么是“双 1”为什么最安全也最慢innodb_flush_log_at_trx_commit1sync_binlog1每次事务提交都 fsync 落盘掉电不丢。代价是每次提交都有磁盘 fsync本文实测 TPS 仅为“全 0”的约 1/3.6。binlog 的 ROW 格式有什么好处记录行的前/后镜像主从复制精确、不受NOW()/UUID()等不确定性函数影响也不会因从库索引不同导致结果不同。缺点是日志量较大binlog_row_image可调。innodb_flush_log_at_trx_commit 取 2 和取 1 差在哪1 每次提交 redo fsync 到盘2 只写 OS buffer、靠每秒刷盘线程落盘。2 在 OS 不崩、仅 MySQL 崩时可恢复但整机掉电可能丢最后 1 秒事务。checkpoint 和 LSN 是什么LSN 是 redo 的写入位点checkpoint 是“已安全落盘、redo 可覆盖”的位点。LSN 与 checkpoint 的差距越大崩溃恢复需回放的 redo 越多。七又二分之一、补充深挖把原理再往下凿三层补充 Abinlog 三种格式怎么选binlog_format可取STATEMENT、ROW、MIXED三种STATEMENT记录 SQL 原文日志量最小但遇到NOW()、UUID()、带非确定函数的LIMIT时从库重放结果可能和主库不一致在生产中已逐渐被淘汰。ROW本文实验所用的格式记录每一行的前镜像与后镜像主从结果绝对一致代价是批量DELETE/UPDATE影响行很多时日志会变大可用binlog_row_imageMINIMAL只记必要列来优化体积。MIXED默认按 STATEMENT 记仅在判断可能不一致时自动切到 ROW是折中方案。生产环境强烈建议用 ROW。本文 4.4 里解析出来的Update_rows事件WHERE 21→SET 22就是 ROW 格式最直观的证据从库拿到的是精确的行改动而不是要重新执行的 SQL。补充 Bredo log 的写入细节与 group commit组提交redo log 并不是每次改动都立刻写盘。InnoDB 先把日志写进内存里的log buffer再由innodb_flush_log_at_trx_commit决定何时 fsync 到磁盘1事务提交时必须 fsync 到磁盘掉电也不丢最安全本文基线。2只写进操作系统 page cache依赖后台每秒一次的 fsync只有当操作系统也崩溃时才可能丢这一秒。0每秒写一次崩溃可能丢失最多 1 秒的事务。因为 fsync 是昂贵且串行的系统调用InnoDB 引入了group commit组提交多个并发事务的 redo 写入可以合并成一次 fsync 批量落盘从而在高并发下把 TPS 抬上去。本文 sysbench 在 8 线程下能跑出近千 TPS“双 1”那一轮也能稳住背后就有组提交的功劳。要分清两阶段提交解决的是 redo 与 binlog 的一致性group commit 解决的是多次 fsync 合并为一次的性能优化两者是正交的两件事。补充 C崩溃恢复的完整对账流程数据库启动、或者崩溃重启时InnoDB 按下面四步做恢复从最后一个 checkpoint 开始把 redo 里所有事务无论提交与否重放到内存页。对仍处于prepare状态的事务取出它的 XID。拿 XID 去 binlog 里搜索对应事件若 binlog 中该事务完整存在有对应的 COMMIT 事件或 GTID则对该事务执行 redo commit使其对外可见。若 binlog 中找不到该 XID说明 binlog 没写完就崩了则回滚该事务。这套“prepare → 查 binlog → 决定提交还是回滚”正是两阶段提交在恢复侧的落地。它保证了两个不变量只要 binlog 落了主库就一定提交只要主库提交了binlog 一定在。主库与从库由此永不分裂。补充 DWAL、刷脏页与 checkpoint 的协作redo log 的价值在于让“脏页可以晚点刷盘”但脏页终究要写回.ibd数据文件否则 redo 会无限膨胀。InnoDB 的后台线程按checkpoint推进把“已经持久化到数据文件的 LSN”记下来本文实机里Last checkpoint at 19438377。LSN 与 checkpoint 之间的距离就是“还留在 redo 里、尚未落盘的改动量”。当 redo 容量快写满时会触发sharp checkpoint强制刷脏带来可感知的性能抖动——所以innodb_redo_log_capacity本文 100M要按机器内存与写入量合理配置太小会频繁刷脏拖慢写入太大则崩溃恢复时要回放的 redo 变多、重启变慢。补充 E云数据库与“双 1”的工程实践在云数据库如 RDS里“双 1”几乎是无脑默认值因为云厂商用本地 SSD 主从同步把 fsync 代价摊薄了但自建库在机械盘或网络盘上往往会在“性能与一致性”间妥协日志类、计数类、可重算的业务会把sync_binlog设为 0 或 100把innodb_flush_log_at_trx_commit设为 2换来数倍吞吐。取舍原则只有一条——丢这条数据会不会造成不可挽回的业务损失。不会就放松会就老老实实双 1。八、总结一条UPDATE的一生从连接器走到 InnoDB靠redo log 保证 crash-safe、binlog 保证可复制二者通过两阶段提交绑定成原子操作任何一阶段崩溃都能靠 XID 对账恢复一致。而“双 1”正是这两套日志在事务提交时都强制 fsync 的代名词——安全但有性能代价。本文用真实云服务器跑出的981.87 → 1388.65 → 1748.24 → 3561.46TPS 梯度把“安全与性能如何取舍”量化了出来又用补充章节把 binlog 格式、group commit、崩溃对账、checkpoint 与云上实践一层层凿开。理解这一条“SQL 的一生”你就掌握了 MySQL 持久化与高可用最底层的那根主线。补充 F执行器如何“调”存储引擎——Handler 接口视角很多人把“执行器”和“存储引擎”想成黑盒其实中间隔着一套标准的Handler API。执行器不关心数据存在哪、索引怎么组织它只向引擎要“下一行”“按索引找键”“更新这行”。对应到SHOW STATUS LIKE Handler_%里的Handler_read_rnd_next全表扫一行行取、Handler_read_key走索引定位等计数器正是执行器与引擎交互的次数。本文 3.1 里EXPLAIN出现typeALL、rows5意味着执行器要走Handler_read_rnd_next把整张表 5 行依次拉出来再过滤——若表有 500 万行就是 500 万次随机取行性能灾难。这也解释了为什么“建对索引”是 SQL 优化的第一杠杆它把引擎接口调用从“顺序扫全表”变成“索引定位”redo/undo 的写入压力也随之下降。补充 G再举一例说清“物理日志”与“逻辑日志”redo log物理“把表空间 5 号页、偏移 1024 处的 8 字节改成 0x03”。它和具体 SQL 无关哪怕你是用UPDATE还是LOAD DATA触发的落盘格式一样——所以 redo 极小、极快只服务“崩溃后把页恢复成正确字节”。binlog逻辑“把test.T第 1 行c列从 1 改成 2”。它和存储结构无关从库拿去重放就能得到同样结果所以 binlog 能跨引擎、跨版本用于复制和时间点恢复。正因为一个是“页字节级”、一个是“行语义级”两者谁都替代不了谁才必须用两阶段提交绑在一起。理解这一层面试题里“能不能只用 binlog 做崩溃恢复”就有答案了不能因为 binlog 是追加写、没有“页已刷盘”的 checkpoint 概念恢复时无法判断哪些脏页已经落盘而 redo 的 checkpoint LSN 正是为这个设计的。补充 Hbuffer pool 大小对“SQL 一生”的隐秘影响本文实验机的innodb_buffer_pool_size只有默认的 128M134217728 字节这是 MySQL 出厂最保守值。buffer pool 是 InnoDB 的“内存数据页缓存”几乎所有读都先到这里找找不到才去磁盘并把页读进来。它直接决定了一条 SQL 在“执行器调引擎”这一环是走内存还是走 IO池子够大热点数据常驻内存redo/undo 的读写也都是内存操作TPS 自然高池子太小频繁缺页触发磁盘读延迟飙升。生产库一般把 buffer pool 配到物理内存的 60%~75%。本机只跑了 10 万行的 sysbench128M 绰绰有余所以“双 1”性能差异主要来自 fsync 而非缺页——这一点在解读第五节曲线时要心里有数若把数据量放大到上亿行、buffer pool 又偏小瓶颈会从“提交 fsync”转移到“缓存缺页”优化方向就完全不同了。本文实验均在真实云服务器完成输出为实机回显。