Msql 学习课程02-日志系统：一条SQL查询语句是如何执行的？

文章出自极客时间《MySQL 实战 45 讲》专栏，作者是前阿里资深技术专家丁奇

 

前面我们系统的了解了一个查询语句的执行流程，并介绍了执行过程中涉及的处理模块。一条查询语句一般是经过连接器、分析器、优化器、执行器等功能模块，最后到达存储引擎。

那么，一条更新语句的执行流程又是怎么之心的呢？

之前你可能经常听DBA的同事说，MySQL可以恢复到半个月内任意一秒的状态，惊叹的同时，你是不是也在好奇是如何做到的呢？

我们还是从一条更新一句说起，下面这个表的创建语句，这个表有一个主键和一个整形字段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

前面我有跟你介绍过SQL语句基本执行链路，这里我在把那张图拿过来

你的执行语句要先连接数据库，这是连接器的工作。

前面我们说过，在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以找条语句会把表T的所有缓存都清空。这也是我们不建议使用缓存的原因。

接下来，分析器会通过词法分析和语法分析知道这是一条更新语句。优化器决定要是用ID=2这个索引，然后，执行器负责具体执行，找到这一行，然后更新。

与查询流程不一样的是，更新流程还涉及到两个日志模块。他们正式我们今天要讨论的主角：redo log（重做日志）和binlog（归档日志）。如果接触MySQL 这两个词肯定绕不过的。redo log和binglog的设计上有很多有意思的地方，这些设计思路也可以用再你自己的程序里。

重要的日志模块：redo log（记录这个页做了什么改动）

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

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

1：一种做法是直接把账本翻出来，把这次的赊账加上去或者扣除掉。                                                                                        2：另一种做法是现在粉板上几下这次赊账，等打样后在把账本翻出核算。

在生意红火柜台很忙时，掌柜一定会选择后者，因为前者操作太麻烦了。首先你的找到这个赊账的那天记录，你想想，密密麻麻几十页，掌柜要找到那个人的名字，找到之后在拿出算盘计算，最后在将结果写回版本上。

这整个过程想想都麻烦，相比之下，还是现在粉板上记下方便，你想想，如果掌柜没有粉板的帮助，每次记账都翻账本，效率是不是低的让人难受?

同样，在MySQL 里也有整个问题，如果每一次更新操作都要写进磁盘，然后磁盘也要找到对应的那条记录，然后在跟新，整个过程IO成本，查找成本都很高，为了解决这个问题，MySQL的设计者就用了类似酒店掌柜的思路来提升效率。

而粉板和账本配合的整个过程，其实就是MySQL里常说的WAL技术，全称：Write-Ahead Logging,它的关键点就是先写日志，在写磁盘。

具体来说，当有一条记录需要更新的时候，InnDB引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往在系统比较闲的时候做，这就想打洋后掌柜做的事。

如果今天账不多，掌柜可以等大洋后在整理，但如果某天赊账的特别多，粉板写满了，又怎么办呢？这个时候掌柜只好放下手中的活，把粉板中的一部分赊账记录更新到账本中，然后把这些记录从粉板行删除，腾出新的空间。

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

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

write pos 和checkpoint 之间是粉板上还空着的部分，可以用来记录新的操作。如果writepos 追上了checkpoint，标识粉板满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint 推进一下。

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

要理解crash-safe这个概念，可以想想我们前面赊账记录的例子，只要赊账记录在记录板上 或者账本上。即使掌柜忘记了，或者停业几天，恢复生意后依然可以通过账本和粉板上的数据明确赊账账目。

重要的日志模块：binlog

Binlog有两种模式，statement格式的话记录SQL语句，row格式的话记录行的内容，记两条，更新之前和更新之后都有。

前面我们讲过，MySQL整体来看，其实就有两块，一个块石Server层，它主要做的事MySQL功能层面的事，还有一块存储引擎层，负责存储相关的具体事宜。上面我们聊到粉板redo log是InnoDB引擎特有的日志，而Server层也有自己的日志，成为binlog

为什么会有两份日志呢？

因为最开始MySQL里并没有InnoDB引擎，MySQL自带引擎是MyISAM ,但是MyISAM没有crash-safe的能力，binlog日志只能用于归档，而InnoDB是另一个公司以插件形式引入MySQL的既然只依靠binlog是没有crash-safe能力的，所以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文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

有了这两个日志的概念性理解，我们在来看下执行器和InnoDB引擎执行这一update语句的内部流程

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

这里我给出了这个update语句的执行流程图，图种浅色框标识在InnoDB内部执行的，深色框表示是在执行器中执行的。

你可能注意到，最后三步看上去有点绕，将redo log的写入拆成了两个步骤：prepare和commit,这是两阶段提交。

两阶段提交

为什么必须有“两端提交”呢？这是为了然两份日志保持逻辑一致、要说明这个问题，我们得从文章开头的那个问题说起：怎样让数据库回复到半个月内任何一秒的状态。

前面我们说过，binlog会记录所有逻辑操作。并且采用“追加写”的形式。如果你的DBA承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有binglog。同事系统会定期做证库备份。这里的定期取决于系统的重要性，可以是一天一备，也可以是一周一备。

当需要恢复到指定的某一秒时，比如某天下午两点发现中午12点又一次误删除表，需要找回数据，那你可以这么做：

1、首先，找到最近一次的全量备份，如果你运气好，可能昨晚上的一个备份，从这备份恢复到临时库；                                        2、然后，从备份的时间点开始，降备份的binlog一次取出来，重放到中午误删的表之前的那个时刻。

这样你的临时库就跟误删除之前的线上库一样了。然后你可以把表数据从临时库取出来，看需要的恢复到线上库去。

由于redo log和binlog是两个独立的逻辑，如果不用两段式提交，要么就是先写完redo log在写 binlog ，或者反过来的顺序，我们看看这两种方式会有什么问题。

1、先写redo log 后写binlog，假设在redo log写完，binlog还没写完的时候，MySQL进程异常重启，由于我们前面说过，redo log写完之后，系统即时崩溃，仍然能恢复回来，所以恢复后这一行，C的值是1.但是由于binlog没写完就crash了，这个时候binlog里面没有记录这个语句，因此，之后备份日志，存起来的binlog里面就没有这条语句。然后你会发现，如果需要用到这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就会少一次更新，恢复出来的这一行C的值就是0，与原库值不同。

2、先写binlog 后写redo log，如果在binlog写完之后crash,由于redo log还没写完。崩溃恢复以后这个失误无效，所以这一行值是0，但是binlog里面已经记录了，把C从0改成1，这个日志。所以在之后用这个binlog来恢复的时候就多了一个事物出来，恢复出来这一行的C的值就是1，与原库不同。

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

不只误操作后需要用这个过程来恢复数据。当你需要扩容的时候，也就是需要在多搭建一些备份库来增加系统的读能力的时候，现在常见的做法也是用全量备份加上应用binlog来实现的。这个不一致就会导致你的线上出现主从数据库不一致的情况。

简单说，redo log 和binlog都可以用于表示事物提交状态，而两段提交就是让这两个状态保持逻辑上的一致。

小结

今天，我们介绍了MySQL里面重要的两个日志，即物理日志redo log和逻辑日志binlog.

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

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