MySQL 02 日志系统：一条SQL更新语句是如何执行的？

比如执行一条更新语句：

update T set c=c+1 where ID=2; 

首先，更新语句也会走一遍查询语句的流程。除此以外，更新还涉及两个日志模块，分别是redo log和binlog。

redo log

MySQL的更新用到了WAL（Write-Ahead Logging）技术，关键点就是先写日志，再写磁盘。具体来说，当有一条记录需要更新时，InnoDB引擎先将记录写到redo log并更新内存，这时更新就可以算完成了。之后，InnoDB会在适当的时候将这个操作记录更新到磁盘里。

InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件大小为1GB。它的写法是从头开始写，写到末尾后又继续从开头写，如下所示：

这里，write pos是当前记录的位置，check point是当前要擦除的位置。当记录更新到磁盘，check point会向前移动。当有新的更新操作要记录，write pos会向前移动。

因此，有可能write pos会追上check point。这时候就不能执行新的更新，需要先将一部分记录更新到磁盘。

有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

当设置innodb_flush_log_at_trx_commit=1，表示每次事务的redo log都直接持久化到磁盘。推荐设置，这样可以保证MySQL异常重启后数据不丢失。

binlog

redo log是InnoDB引擎特有的日志，而binlog是Server层的日志。最开始，由于MySQL自带引擎为MyISAM，并没有crash-safe的能力，因此后来引入InnoDB后，同时使用这两种日志。binlog的主要作用是做备份。

当设置sync_binlog=1，表示每次事务的binlog都持久化到磁盘。推荐设置，这样可以保证MySQL异常重启后binlog不丢失。

两者的具体区别如下：

• redo log是InnoDB引擎特有的，而binlog是Server层实现的。
• redo log是物理日志，记录“在某个数据页上做了什么修改”；binlog是逻辑日志，记录这个语句的原始逻辑，比如“给ID=2的行的c字段加1”。
• redo log是循环写，空间固定；binlog是追加写，写完一个文件会写下一个文件。

介绍完两个日志的概念，来看执行器+InnoDB引擎完成前面的更新语句的流程：

• 执行器找到ID=2这一行，若这一行所在的数据页在内存中，则直接返回给执行器，否则需要先从磁盘读入数据页再返回。
• 执行器拿到引擎返回的数据，做c+1的操作，得到新数据。
• 引擎将新数据更新到内存，同时将这个更新操作记录到redo log里，此时redo log处于prepare状态。然后告知执行器执行完成，随时可以提交事务。
• 执行器生成该操作的binlog，并将binlog写入磁盘。
• 执行器调用引擎的提交事务接口，引擎把刚写入的redo log改成commit状态，更新完成。

上面redo log写入拆为了prepare和commit，就是两阶段提交。

为什么需要两阶段提交

以前面的更新语句为例。假设ID=2的行中，字段c初始为0。并假设执行update过程中，写完第一个日志但还没写完第二个日志时发生了crash。

如果不使用两阶段提交，那么无非两种情况：

• 先写redo log再写binlog。当redo log写完，系统即使崩溃，仍能恢复数据c=1。但binlog里并没有记录更新语句，之后要用binlog去做备份时，恢复出来的c=0，与原库不同。
• 先写binlog再写redo log。当binlog写完之后crash，由于redo log还没写，崩溃后恢复数据c=0。但由于binlog已写完，之后用binlog去做备份时，恢复出来的c=1，与原库不同。

即如果不使用两阶段提交，那么恢复临时库或主从备份就可能出现不一致。

参考资料：极客时间专栏《MySQL实战45讲》https://time.geekbang.org/column/intro/100020801?tab=catalog