MySQL之日志写入顺序

当MySQL执行一条UPDATE语句时，InnoDB存储引擎和Server层会协同工作，按照严格的顺序写入undo log、redo log和binlog，并通过两阶段提交保证数据一致性和崩溃恢复能力。以下是详细的执行流程和日志写入顺序：

1. 更新语句执行前的准备

顺序：第1步写入
在修改数据之前，InnoDB会先将修改前的旧值写入**undo log**，记录到undo log buffer中。
- 例如：将“修改前”的整行数据或主键信息保存下来，用于事务回滚或MVCC。
- undo log本身也是物理修改（因为undo页也需要持久化），因此对undo页的修改也会在稍后被redo log保护。

顺序：第2步（内存操作）
在内存中更新缓冲池里的数据页，将新值写入，此时该页变为“脏页”（内存与磁盘不一致）。
- 注意：这一步不会立即写回磁盘，而是等待后续刷盘。

顺序：第3步写入
在数据页修改的同时，InnoDB会生成对应的物理日志（记录“在哪个页的哪个偏移量改为什么值”），并写入redo log buffer。
- 这一步遵循WAL（Write-Ahead Logging）原则：日志先于数据持久化。
- undo页的修改也会同时被记录到redo log中。

此时，UPDATE语句的执行阶段完成，但事务尚未提交。

当客户端发起COMMIT时，MySQL会进入两阶段提交流程，确保redo log和binlog的一致性。

顺序：第4步
InnoDB将当前事务的redo log buffer刷入磁盘的redo log file，并记录事务状态为PREPARE。
- 如果innodb_flush_log_at_trx_commit=1，此时会强制fsync落盘，保证redo log已持久化。

顺序：第5步
MySQL Server将事务的binlog（逻辑日志，记录SQL语句或行变更）写入binlog file，并执行fsync刷盘。
- 至此，binlog已持久化。

顺序：第6步
InnoDB在redo log中写入一个COMMIT标记，表示事务正式提交。此时事务才算真正完成。
- 这一步通常不需要再次刷盘，只需在内存中标记即可（但redo log中已有完整的修改记录）。

步骤	操作内容	日志类型	说明
1	记录旧值到`undo log buffer`	`undo log`	内存中记录，为回滚做准备
2	修改缓冲池中的数据页	无	内存中的脏页生成
3	生成`redo log`并写入`redo log buffer`	`redo log`	记录物理修改
4	两阶段提交第1阶段：`redo log`刷盘并标记`PREPARE`	`redo log`	持久化到`redo log file`
5	两阶段提交第2阶段：`binlog`刷盘	`binlog`	持久化到`binlog file`
6	两阶段提交完成：`redo log`标记`COMMIT`	`redo log`	在`redo log`中写入提交标记
7	返回客户端成功	-	-

保证数据一致性：如果崩溃发生在步骤4之后、步骤5之前，恢复时会发现redo log处于PREPARE状态且无对应binlog，则事务回滚；如果崩溃在步骤5之后，则redo log和binlog都已持久化，事务可以提交。这样就避免了主从数据不一致或数据丢失。
undo log先于修改：确保任何修改都可以被撤销，即使系统在修改后立即崩溃，重启后也可以通过undo log回滚未提交的事务。

undo log的持久化：虽然undo log是逻辑日志，但其所在的数据页修改也会被redo log记录，因此undo log的持久化依赖于redo log的WAL机制。
刷盘策略的影响：如果innodb_flush_log_at_trx_commit或sync_binlog设置为非1的值，刷盘时机可能延迟，但日志写入的顺序逻辑不变。
自动提交：如果autocommit=1，则每条UPDATE语句执行完后会自动提交，即上述提交阶段会立即执行。

通过这种严格的顺序和两阶段提交，MySQL在保证高性能的同时，实现了事务的ACID特性和主从复制的数据一致性。