快手面试题：一次Insert操作，MySQL的几种Log的写入顺序？

快手面试题：一次Insert操作，MySQL的几种Log的写入顺序？

在 MySQL InnoDB 存储引擎中，一次 INSERT 操作涉及 Undo Log（撤销日志）、Redo Log（重做日志）、Binlog（归档日志） 三种日志，它们的写入顺序如下：

日志作用对比

日志类型	作用	位置	适用场景
Undo Log	记录修改前的数据，用于回滚	InnoDB 内部	事务回滚、MVCC
Redo Log	记录数据修改，保证事务持久化	InnoDB 内部	崩溃恢复
Binlog	记录逻辑操作（SQL 语句或行变更）	MySQL Server 层	主从复制、数据恢复

1. INSERT 操作的日志写入顺序

Undo Log → Redo Log (prepare) → Binlog → Redo Log (commit)

执行流程

1. 写入 Undo Log
   • 记录修改前的数据（但 INSERT 操作不涉及回滚前的数据，因此 Undo Log 仅用于 MVCC）。
   • Undo Log 主要用于 事务回滚，以及支持 快照读（MVCC） 。
2. 写入 Redo Log（Prepare 阶段）
   • 记录本次事务的变更（但未提交）。
   • Redo Log 采用 WAL（Write-Ahead Logging） 机制，先记录日志，再修改数据。
   • 进入 prepare 状态，表示事务已经执行，但尚未提交。
3. 写入 Binlog
   • 记录 INSERT 语句或行数据到 Binlog（归档日志）。
   • Binlog 主要用于主从复制和数据恢复。
   • Binlog 以追加写（append-only） 方式记录，并最终刷盘。
4. 提交 Redo Log（Commit 阶段）
   • 事务正式提交，Redo Log 进入 commit 状态，标志事务完成。
   • MySQL 崩溃时，如果 Redo Log 处于 commit 状态，则事务可恢复，否则回滚。

2. 为什么采用这个顺序？

1. Undo Log 先写入
   • Undo Log 记录数据的 旧值，在事务回滚或 MVCC 需要时使用。
   • 必须在事务开始前写入，以便回滚时能正确恢复数据。
2. Redo Log 采用两阶段提交（2PC）
   • 先写 Redo Log（Prepare）：
     • 记录事务的物理变更，保证 MySQL 崩溃后数据可恢复。
     • 但事务尚未提交，避免未写入 Binlog 时崩溃导致数据不一致。
   • 再写 Binlog：
     • 先写 Binlog，确保所有变更都可用于 主从复制 和 灾难恢复。
     • 避免 Redo Log 提交后 MySQL 崩溃，导致主库和从库数据不一致。
   • 最后提交 Redo Log（Commit）：
     • 事务正式提交，确保数据最终一致。
3. 保证 Binlog 和 Redo Log 的一致性
   • 如果 Redo Log 先提交，再写 Binlog，MySQL 崩溃可能导致 主从数据不一致（主库事务提交成功，但 Binlog 丢失）。
   • 采用 两阶段提交（2PC） 确保 Redo Log 和 Binlog 同步。

3. 两阶段提交（2PC）

为了保证 Redo Log 和 Binlog 具有一致性，MySQL 采用两阶段提交（2PC，Two-Phase Commit） 机制：

阶段 1：Prepare

• 事务执行完毕后，先写 Redo Log，标记为 prepare 状态。
• 如果此时 MySQL 崩溃，事务可以回滚（因为 Redo Log 还未提交）。

阶段 2：Commit

• 事务提交时，先写 Binlog，然后提交 Redo Log。
• 如果 Binlog 写入失败，Redo Log 也不会提交，避免数据不一致。

✅ 两阶段提交示例

1. 写入 Undo Log
2. 写入 Redo Log（Prepare 阶段）
3. 写入 Binlog
4. 提交 Redo Log（Commit 阶段）

这种方式保证：

• Binlog 和 Redo Log 必须同时成功，才算事务提交。
• 事务提交前的崩溃可以回滚，确保数据一致性。

4. 实际执行示例

假设 INSERT 语句：

sql
BEGIN;
INSERT INTO users (id, name) VALUES (1, 'Alice');
COMMIT;

日志写入过程

步骤	操作	作用
1	Undo Log 写入	记录事务前状态（用于 MVCC）
2	Redo Log (prepare)	记录事务的物理变更（但未提交）
3	Binlog 写入	记录逻辑 SQL 语句（用于恢复和复制）
4	Redo Log (commit)	事务正式提交，数据落盘

5. 事务提交失败时的恢复

• 崩溃发生在 prepare 阶段：
  • 事务未提交，系统自动回滚。
• 崩溃发生在 Binlog 写入后，但 Redo Log 未提交：
  • 事务未提交，回滚事务，确保数据一致。
• 崩溃发生在 Redo Log 提交后：
  • 事务已提交，MySQL 通过 Redo Log 恢复数据，确保持久化。

6. 总结

MySQL INSERT 事务日志写入顺序

Undo Log → Redo Log (prepare) → Binlog → Redo Log (commit)

• Undo Log 先写入，保证事务可回滚，支持 MVCC。
• Redo Log 采用两阶段提交（Prepare → Commit）：
  • Prepare 阶段记录变更但不提交，避免 MySQL 崩溃导致数据不一致。
  • Commit 阶段确保事务完整提交，保证持久化。
• Binlog 记录 SQL 逻辑变更，确保主从复制一致性。

MySQL 事务日志机制确保了：

✅ 崩溃恢复：Redo Log 记录事务变更，保证宕机后数据一致。 ✅ 数据一致性：两阶段提交（2PC）确保 Binlog 和 Redo Log 同步，避免主从数据不一致。 ✅ 高效查询：Undo Log 支持 MVCC，提高事务并发性能。

🔥 优化建议 1️⃣ 使用 innodb_flush_log_at_trx_commit=1 确保事务提交后 Redo Log 持久化，防止丢失数据。 2️⃣ 使用 sync_binlog=1 让 Binlog 也同步刷盘，避免崩溃导致主从数据不一致。 3️⃣ 合理使用索引，减少 Undo Log 开销，提高查询性能。