MySQL 内部两阶段提交机制深度解析

一、为什么需要两阶段提交

1.1 核心问题

MySQL 需要保证两个日志的一致性:

• redo log (InnoDB 引擎层)
• binlog (MySQL Server 层)

如果不使用两阶段提交,会导致主从数据不一致。

1.2 两个日志的作用

为什么不能只用一个日志?

1. redo log 是 InnoDB 特有,其他引擎(MyISAM)没有
2. binlog 是 Server 层,所有引擎共享
3. 历史原因: binlog 先存在,redo log 后加入

1.3 不用两阶段提交的后果

场景1: 先写 redo log,后写 binlog

1

UPDATE users SET balance = 900 WHERE id = 1;

执行流程:

1. 写 redo log ✅
2. 【此时崩溃】
3. binlog 未写入 ❌

后果:

• 重启后,redo log 恢复数据: balance = 900 ✅
• 从库通过 binlog 复制: 没有这条更新 ❌
• 主库 900,从库 1000 → 数据不一致

场景2: 先写 binlog,后写 redo log

1

UPDATE users SET balance = 900 WHERE id = 1;

执行流程:

1. 写 binlog ✅
2. 【此时崩溃】
3. redo log 未写入 ❌

后果:

• 重启后,redo log 没有记录: balance = 1000 (未改变) ❌
• 从库通过 binlog 复制: balance = 900 ✅
• 主库 1000,从库 900 → 数据不一致

二、MySQL 两阶段提交完整流程

2.1 流程图

客户端执行: UPDATE users SET balance = 900 WHERE id = 1;
    ↓
┌─────────────────────────────────────────┐
│ 1. InnoDB 执行器修改内存                  │
│    - 在 Buffer Pool 中修改数据            │
│    - balance: 1000 → 900                │
└─────────────────────────────────────────┘
    ↓
┌─────────────────────────────────────────┐
│ 2. 阶段1: Prepare                        │
│    - 写 redo log (标记为 prepare 状态)    │
│    - 生成 XID (事务ID)                   │
│    - redo log 内容: XID=100, prepare     │
└─────────────────────────────────────────┘
    ↓
┌─────────────────────────────────────────┐
│ 3. 写 binlog                             │
│    - 写入完整的 SQL 或 ROW 格式           │
│    - binlog 内容: XID=100, UPDATE...    │
│    - fsync 刷盘                          │
└─────────────────────────────────────────┘
    ↓
┌─────────────────────────────────────────┐
│ 4. 阶段2: Commit                         │
│    - 写 redo log (标记为 commit 状态)     │
│    - redo log 内容: XID=100, commit      │
│    - 事务提交完成                         │
└─────────────────────────────────────────┘
    ↓
返回客户端: Query OK

2.2 关键点详解

Prepare 阶段

redo log 写入:
┌──────────────────────────┐
│ XID: 100                 │
│ 状态: prepare            │
│ 数据: page 5, offset 100 │
│ 修改: balance 1000→900   │
└──────────────────────────┘

特点:

• 事务还未提交
• 数据已写入 redo log
• 标记为 prepare 状态

写 binlog

binlog 写入:
┌──────────────────────────────────┐
│ XID: 100                         │
│ SQL: UPDATE users                │
│      SET balance = 900           │
│      WHERE id = 1                │
└──────────────────────────────────┘

特点:

• 完整的 SQL 语句(STATEMENT 格式)
• 或完整的行数据(ROW 格式)
• 包含相同的 XID

Commit 阶段

redo log 更新:
┌──────────────────────────┐
│ XID: 100                 │
│ 状态: prepare → commit   │
└──────────────────────────┘

特点:

• 只需要标记状态为 commit
• 不需要重复写数据
• 事务正式提交

三、崩溃恢复逻辑

3.1 恢复流程

MySQL 重启
    ↓
扫描 redo log,找到所有 prepare 状态的事务
    ↓
对每个 prepare 事务:
    ↓
在 binlog 中查找对应的 XID
    ↓
找到了? ──Yes──> 提交事务 (认为已完成)
    ↓ No
回滚事务 (认为未完成)

3.2 崩溃场景分析

3.3 详细示例

示例1: 正常提交

1
2
3

START TRANSACTION;
UPDATE users SET balance = 900 WHERE id = 1;
COMMIT;

时间线:

T1: 修改 Buffer Pool (balance = 900)
T2: 写 redo log (XID=100, prepare)
T3: 写 binlog (XID=100, UPDATE...)
T4: 写 redo log (XID=100, commit)
T5: 返回客户端

日志内容:

redo log:
  [XID=100, prepare, balance 1000→900]
  [XID=100, commit]

binlog:
  [XID=100, UPDATE users SET balance=900 WHERE id=1]

示例2: Prepare 后崩溃

T1: 修改 Buffer Pool
T2: 写 redo log (XID=100, prepare) ✅
T3: 【崩溃】
T4: binlog 未写入 ❌

恢复过程:

1. 扫描 redo log: 发现 XID=100, prepare
2. 查找 binlog: 没有 XID=100
3. 判断: 事务未完成
4. 操作: 回滚事务
5. 结果: balance 恢复为 1000

示例3: binlog 后崩溃

T1: 修改 Buffer Pool
T2: 写 redo log (XID=100, prepare) ✅
T3: 写 binlog (XID=100) ✅
T4: 【崩溃】
T5: redo log commit 未写入 ❌

恢复过程:

1. 扫描 redo log: 发现 XID=100, prepare
2. 查找 binlog: 找到 XID=100
3. 判断: 事务已完成(binlog 已写入)
4. 操作: 提交事务
5. 结果: balance = 900

为什么这样判断?

• binlog 已写入 → 从库会执行这个事务
• 如果主库回滚 → 主从不一致
• 所以主库必须提交

四、关键参数配置

4.1 innodb_flush_log_at_trx_commit

推荐: 生产环境设置为 1

4.2 sync_binlog

推荐: 生产环境设置为 1

4.3 双1配置

1
2
3

[mysqld]
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

效果: 保证数据不丢失,但性能最低

五、性能影响与优化

5.1 每次提交的磁盘操作

1. redo log 写入 (prepare)  → 1次磁盘IO
2. binlog 写入              → 1次磁盘IO
3. redo log 写入 (commit)   → 1次磁盘IO
─────────────────────────────────────
总计: 3次磁盘IO

5.2 组提交优化 (Group Commit)

多个事务一起提交:
事务1: prepare → ┐
事务2: prepare → ├─ 一起写 binlog → 一起 commit
事务3: prepare → ┘

减少磁盘IO次数

原理: 将多个事务的 binlog 写入和 redo log commit 合并为一次磁盘操作。

六、实战验证

6.1 查看两阶段提交状态

查看 redo log 配置

1
2
3
4
5

-- 查看 redo log 相关配置
SHOW VARIABLES LIKE 'innodb_log%';

-- 查看 redo log 使用情况
SHOW ENGINE INNODB STATUS\G

查看 binlog

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

# 查看 binlog 文件列表
ls -lh /var/lib/mysql/mysql-bin.*

# 查看 binlog 内容
mysqlbinlog /var/lib/mysql/mysql-bin.000001

# 输出示例:
# BEGIN
# /*!*/;
# # at 123
# #260331 10:00:00 server id 1  end_log_pos 456  Xid = 100
# UPDATE users SET balance=900 WHERE id=1
# /*!*/;
# COMMIT/*!*/;

6.2 模拟崩溃恢复

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

# 1. 执行事务
mysql> START TRANSACTION;
mysql> UPDATE users SET balance = 900 WHERE id = 1;
mysql> COMMIT;

# 2. 强制杀死 MySQL (模拟崩溃)
kill -9 $(pidof mysqld)

# 3. 重启 MySQL
systemctl start mysqld

# 4. 查看恢复日志
tail -f /var/log/mysqld.log
# 会看到: InnoDB: Starting crash recovery
# 会看到: InnoDB: Apply batch completed

6.3 验证数据一致性

1
2
3
4
5
6
7

-- 主库查询
SELECT balance FROM users WHERE id = 1;

-- 从库查询
SELECT balance FROM users WHERE id = 1;

-- 对比结果应该一致

七、总结

7.1 核心要点

1. 两个日志的作用

   • redo log: 崩溃恢复,保证持久性
   • binlog: 主从复制,数据备份

2. 两阶段提交流程

   • Prepare: 写 redo log (prepare 状态)
   • 写 binlog
   • Commit: 写 redo log (commit 状态)

3. 崩溃恢复原则

   • binlog 已写入 → 提交事务
   • binlog 未写入 → 回滚事务

4. 性能代价

   • 每次提交需要 3 次磁盘 IO
   • 组提交可以优化性能

7.2 生产环境建议

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

[mysqld]
# 双1配置,保证数据不丢失
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

# redo log 大小(根据业务调整)
innodb_log_file_size = 1G
innodb_log_files_in_group = 2

# binlog 格式(推荐 ROW)
binlog_format = ROW

7.3 监控指标

1
2
3
4
5
6
7
8

-- 查看事务提交次数
SHOW GLOBAL STATUS LIKE 'Com_commit';

-- 查看 redo log 写入次数
SHOW GLOBAL STATUS LIKE 'Innodb_log_writes';

-- 查看 binlog 写入次数
SHOW GLOBAL STATUS LIKE 'Binlog_cache_use';

参考资料

• MySQL 官方文档: InnoDB Redo Log
• MySQL 官方文档: The Binary Log
• 《MySQL 技术内幕: InnoDB 存储引擎》