Mysql实战之日志系统：一条SQL更新语句是如何执行的

1.前言

上一篇咱们了解了MySQL 的执行过程，其中设计连接器、分析器、优化器、执行器和存储引擎，接下来我将给大家讲解一下在MySQL中一条更新语句是如何执行。我相信大家可能听公司的DBA提起过，可以将数据恢复到半个月内任意时间的状态，是不是感觉很高大上，很厉害呢，下面我就将这个谜底一步一步解开

2. 首次分析更新语句执行

例如我们存在如下一下update 语句

(相关资料图)

update order set status = 2 where id = 10;

复制

根据上一章，我们可以知道它的执行流程是如下图

客服端先通过连接器连接数据库，然后通过分析器发现是更新的SQL语句，优化器针对SQL语句进行优化，使用id索引，最后执行器执行SQL语句

到这里大家会说：这不是和查询语句一样吗，都是这些流程，其实不然，更新语句还设计两个比较重要的模块，那就是我们今天主要介绍的内容：redo log(重做日志)和binlog(归档日志)

3.先看redo log

再说将redo log之前，先给大家讲一个关于孔乙己的故事，酒店掌柜有一个粉板，专门用来记录客人的赊账记录。如果赊账的人不多，那么他可以把顾客名和账目写到板上。但是如果赊账的人多了，粉板总会有记不下的时候，这个时候掌柜一定还有一个专门记录赊账的账本。

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

直接把账本拿出来，把此次赊的账加上或者扣除掉将此次赊的账记录到粉板上，等打烊了在把账本拿出来，把今天的粉板的账记录到账本

如果是在生意比较火的时候，掌柜肯定会选择第二种方式，因为第一种操作太麻烦了，每次都要从账本里面找到对应人的账，进行计算，然后更新账本。相比之下，先把账记录到粉板上，等打烊在一块统计到账本上，更省时。

MySQL同样也存在类似的问题，就是如果每次更新操作都需要写入磁盘，然后磁盘也要找到对应的记录，然后更新，整个过程IO成本和查找成本都很高。为了解决这个问题，MySQL设计者就采取了类似掌柜粉板的思路来提升更新的效率

当有更新操作执行的时候，InnoDB引擎就会先把记录写到redo log里面，并更新内存，这样更新操作就算结束了。InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘，这个写磁盘的操作一般都是在MySQL比较空闲的时候执行。

这里存在一个问题，就是redo log不可能是无限大的，总有因为频繁操作，将redo log打满的情况，InnoDB的设计人员也考虑到了这一点，将redo log设定成了固定大小，默认是一组四个文件，一个文件1G，也就是说redo log总大小是4GB，下图就是redo log的设计图

wirte pos是记录当前的位置，一边写一遍后移，写到第三号文件的末尾就回到0号文件开头。checkpoint 是当前要察除的位置，也是往后推移并且循环的，察除记录前要把记录更新到数据文件。

wirte pos和checkpoint 之间还空着的部分，可以用来记录新的操作。如果wirte pos追上checkpoint，标识redo log已经满了，此时不能在执行更新操作，需要将内存的数据同步到磁盘中了。

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

4.再看binlog

上一篇文章我们讲过数据库架构分为两层，一个是server层，一个是存储引擎层，而binlog就属于server层的日志，而redo log是InnoDB独有的日志

说道这里，大家肯定会有一个疑惑，为啥会有两个日志系统呢？

因为一开始MySQL并没有InnoDB引擎，MyISAM是MySQL自带的引擎，但是MyISAM没有日志系统的功能，只存在server层的binlog 归档日志，InnoDB是后来集成到mysql里面去的

下面看一下两种日志的不同点

redo log是InnoDB引擎独有的；binlog是mysql共用的redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如给id=2这一行的c字段加1.redo log日志是循环写的，空间固定会用完；binlog日志是追加写的，不会覆盖以前的日志

5.再次分析更新语句执行

上面我们对两种日志做了概念性讲解，下面看一下执行器和InnoDB引擎在执行update语句是的内部流程

执行器先找引擎取到id=2的这一行，因为id是主键，直接可以通过主键索引查到这一行，如果id=2这一行所在的数据页本来就在内存中，执行器直接放回结果，如果不在，在需要将磁盘的数据，写到内存在返回结果执行器会将id=2的这一行的c字段进行加1操作，然后会更新当前行InnoDB引擎将当前行更新到内存后，redo log日志会记录当前更新操作，此时redo log日志处于prepare的状态。然后告知执行器执行完成，可以提交事务了binlog日志会记录当前update语句，并且把binlog写入磁盘执行器调用引擎提交事务，引擎把刚刚写入的redo log改成提交状态（commit），更新完成

大家可以看到我们对redo log日志做了两次提交，也就是我们平常说的两阶段提交

6.两阶段提交

问：为啥需要进行两阶段提交

答：两阶段提交是为了让binlog和redo log可以保持数据一致

问：如果不使用两阶段提交，会产生什么样的问题？

答：如果不存在两阶段提交，那边就会有两种情况。

先写redo log日志，后写binlog日志。如果我们写完了redo log日志，但是binlog日志还没有写，发生了宕机，我们在重启MySQL的时候用binlog恢复数据的话，id=2这一行的数据没有完成加1操作，和之前库的数据不一致了先写binlog日志，后写redo log日志。如果我们写完了binlog日志，但是redo log日志还没有写，发生了宕机，此时事务没有生效，我们通过binlog日志恢复数据，发现id=2这一行已经完成加1操作了，和之前库的数据不一致了

7.小结

今天主要讲了MySQL最重要的两个日志，一个是server层的binlog归档日志，一个是InnoDB引擎的redo log重做日志

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

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

我还跟你介绍了与 MySQL 日志系统密切相关的“两阶段提交”。两阶段提交是跨系统维持数据逻辑一致性时常用的一个方案，即使你不做数据库内核开发，日常开发中也有可能会用到。