【MySQL】2｜一条SQL更新语句是如何执行的

我们以一个例子来说明。

下面是表的创建语句，这个表有一个主键ID和一个整形字段c：

mysql> create table T(ID int primary key, c int);

如果要将 ID=2 这一行的值加1，SQL语句这样写：

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

与查询语句执行的那一套流程类似，更新语句也会从 连接器→查询缓存→ 分析器→优化器→执行器 走一遍。

• 连接器：执行语句前要先连接数据库
• 查询缓存：清空表T上所有的查询缓存
• 分析器：通过语法和词法解析出这是一条更新语句
• 优化器：决定使用ID索引
• 执行器：找到这一行，进行更新

但是，与查询流程不一样的是，更新流程还涉及到两个重要的日志模块，redo log(重做日志) 和 binlog(归档日志)

1、redo log - 重做日志

借用原文中的《孔乙己》例子。酒店掌柜有一个粉板，专门用来记录客人的赊账记录。如果赊账的人不多，那么他可以把顾客名和账目写在板上。但如果赊账的人多了，粉板总会有记不下的时候，这个时候掌柜一定还有一个专门记录赊账的账本。

如果有人要赊账或者还款的话，掌柜一般有两种做法：

• 一种是直接翻出账本，把这次赊账加上或者扣除掉
• 另一种是先在粉板上记下这次的账，等打烊后再把账本翻出来核算

在生意红火柜台很忙时，掌柜肯定会选择后者，因为前者操作实在是太麻烦了。相比之下，还是先在粉板上记一下方便。

类比MySQL，粉板对应redo log，账本对应数据文件。因为直接更新数据文件，涉及到随机写磁盘，效率低下。为了解决这个问题，MySQL的设计者就用了类似酒店掌柜粉板的思路来提升更新效率。

• redo log 为顺序写磁盘，提升IO效率
• redo log 可以攒一批再刷磁盘，进一步提升效率

粉板和账本配合的整个过程，其实就是MySQL里经常说到的WAL（Write-Ahead Logging）技术，关键点就是先写日志，再写磁盘，也就是先写粉板，等闲的时候再写账本。

但是粉板的空间有限，如果今天赊账不多，掌柜可以等打烊后再整理。但如果某天赊账的特别多，粉板写满了，此时，掌柜不得不停下来，把粉板中的一部分赊账记录更新到账本中，然后把这些记录从粉板上擦掉，为新记账腾出空间。

与此类似，InnoDB 的 redo log 是固定大小的，比如可以配置为一组4个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又从头开始循环写，如下图所示：

redo log

• write pos：记录当前的位置，一边写一边后移，写到3号文件的末尾就回到0号文件的开头
• check point：记录当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件
• 图中绿色部分，write pos 和 check point 中间段，是“粉板”上还空着的部分，可以用来记录新的操作。如果 write pos 追上 check point ，也就是粉板写满了，这时不能再执行新的更新，得停下来先擦掉一些记录，把check point 推进一下

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

2、binlog - 归档日志

redo log 是InnoDB 引擎特有的日志，而server 层也有自己的日志，称为 binlog。

至于为什么有两份日志？

这是个历史问题，MySQL最开始的时候没有InnoDB引擎，自带的引擎是MyISAM，而InnoDB是另一个公司以插件形式引入MySQL的。考虑到MyISAM没有crash-safe的能力，binlog 日志只能用于归档，InnoDB就实现了另外一套日志系统（redo log）来实现 crash-safe 能力。

这两种日志有以下三点不同：

1. redo log 是InnoDB特有的；binlog 是MySQL的Server层实现的，所有引擎都可以使用
2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1”
3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

3、更新流程

理解了两个日志的概念，我们再来看看执行器和InnoDB引擎在执行这个简单的update 语句时的内部流程。

1. 执行器先找引擎取 ID=2 这一行。ID是主键，引擎直接用树搜索找到这一行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要从磁盘先读入内存，然后再返回
2. 执行器拿到引擎给的行数据，把这个值加上1，得到新一行数据，再调用引擎接口写入这行新数据
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务
4. 执行器生成这个操作的binlog，并把binlog写入磁盘
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log 改成提交（commit）状态，更新完成

下面是update语句执行流程图：

更新流程图

最后三步，将redo log 的写入拆成了两个步骤：prepare 和 commit，这就是“两阶段提交”

4、两阶段提交

为什么要“两阶段提交”？

这是为了让两份日志之间的逻辑一致，由于redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 在写 binlog，或者采用反过来的顺序。

以前面update语句来做例子。假设当前ID=2的行，字段c的值是0，再假设执行update语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

1、先写redo log 后写 binlog

假设在 redo log 写完，binlog 还没有写完的时候，MySQL进程异常重启。redo log 有crash-safe的能力，在系统恢复时，能够恢复这一行c的值是1。

但是binlog没写完就crash了，这时binlog里面没有记录这个语句。因此，之后备份日志的时候，存起来的binlog里面就没有这个语句。

后续恢复临时库或者从库同步的时候，由于少了这一次更新，恢复出来的这一行c的值就是0，与原库的值不同。

2、先写 binlog 后写 redolog

如果在binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。但是 binlog 里面已经记录了“把c从0改成1”这个日志。所以在恢复临时库或者从库同步的时候，多了一个事务出来，恢复出来的这一行c的值是1，与原库的值不同。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

可能会有人问，既然redo log这么强大，为什么还要binlog，增加系统复杂度

主要基于以下几点：

1. redo log 只有InnoDB 能用，其他引擎用不了
2. binlog 具有归档的功能，可以用来恢复和备份；而redo log 采用循环记录，不具备归档功能
3. 主从是使用的 binlog 日志来同步数据的

5、小结

redo log 用于保证crash-safe能力。innodb_flsh_log_at_trx_commit这个参数设置成1的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数建议设置成1，可以保证MySQL异常重启之后的数据不丢失。

binlog 可以通过设置 sync_binlog 参数成 1，表示每次事务的binlog 都持久化到磁盘。建议这样设置，可以保证MySQL异常重启之后的binlog不丢失。