MYSQL日志系统：一条SQL查询语句是如何执行的？

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

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

如果要将ID=2这一行的值+1：

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

分析之前再回顾以下mysql的逻辑架构图：

执行语句前先通过连接器连接数据库。

缓存略过。

接下来分析器通过词汇和语法解析这是一条更新语句。

优化器决定使用ID这个索引。

执行器负责具体执行，找到对应位置，更新。

与查询流程不同的是，更新还涉及了两个重要的日志模块，也就是本文的重点：redo log（重做日志）和bin log（归档日志）

重要的日志模块：redo log

假设酒店掌柜有一个粉板，专门用来记录客人的赊账记录。如果赊账的人不多，那么可以把顾客名和账目写在粉板上，但是如果赊账的人多了，粉板记不下的时候，掌柜一定还有一个专门记录赊账的账本。

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

• 直接拿出账本，记录赊账或还账。
• 在粉板上记下这次的账目，等打烊后再把账本翻出来算。

而在生意红火柜台很忙时，掌柜一定会选择后者，因为前者操作太麻烦了。首先，得找到这个人得赊账总额那条记录，密密麻麻几十页，掌柜要找到那个名字，可能还得带上老花镜慢慢找，找到之后再拿出算盘计算，最后再将结果写入账本。

相比之下，还是粉板记一下方便，你想想，如果掌柜没有粉板的帮助，每次记账都得翻账本，效率是不是就低得让人难以忍受？

在MYSQL里同样存在这个问题，如果每一次更新操作都要写入磁盘，然后磁盘也要找到对应的那条记录更新，整个过程IO成本，查询成本都很高。为了解决这个问题，MYSQL就采用了类似掌柜粉板的思路提升更新效率。

粉板和账本配合的整个过程，也就是MYSQL里常说到的WAL技术（write-ahead-logging），其关键点在于先写日志，再写磁盘，对应粉板账本也就是先写粉板，之后有空闲再写账本。

具体来说，每当有一条记录需要更新，InnoDB就会先把记录写到redo log里，并更新到内存，这个时候更新就完成了。同时，InnoDB会在适当的时候，将这个操作记录更新到磁盘，而这个操作往往是在系统比较空闲的时候去做。

如果今天赊账不多，掌柜可以等打烊后再整理，但如果某天赊账的特别多，粉板写满了怎么办呢？这时掌柜只能把粉板中的一部分赊账记录更新到账本，然后把这些记录在粉板上擦除，腾出新的记账空间。

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

write pos是当前记录位置，一边写一边后移，写到ib-logfile-3末尾回到ib-logfile-0开头。
check point是当前要擦除的位置，一边擦除一边后移，擦除记录前要把当前记录更新到数据文件。

write pos和check point之间的部分也就是"粉板"还空余的部分，可以用来记录新的操作，如果write pos追上check point，则表示"粉板"写满，这时候不能再执行新的操作，需要停下来先擦除记录。

有了redo log，InnoDB就可以保证即使数据库异常重启，之前提交的记录也不会丢失，这个能力被称为crash-safe。可理解为：只要赊账记录在粉板上或写在账本上，即便停业几天后，依然可以通过账本和粉板上的数据明确赊账账目。

重要的日志模块：bin log

redo log是InnoDB引擎特有的日志，而Server层也有自己的日志——bin log（归档日志）

最初MYSQL并没有InnoDB，它自带的引擎是MyISAM，但是MyISAM没有crash-safe的能力，bin log只能用于归档，而InnoDB是以插件的形式引入MYSQL的，既然只依靠bin log不能实现crash-safe，所以InnoDB使用了redo log实现了crash-safe。

bin log和redo log区别：

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

有了这两个日志的概念后，再回顾update语句的内部流程：

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

最后三步，将redo log写入拆分为两个步骤：prepare和commit，这就是两阶段提交。

两阶段提交

两阶段提交是为了让两份日志之间的逻辑一致。

之前有说过，binlog会记录所有的逻辑操作，并采用追加写的方式。如果你的DBA承诺说半个月内可以让数据库恢复到这个期间任何一秒的状态，那么备份系统中一定保存了半个月内所有的bin log，同时系统会定期做整库备份。

当需要恢复到指定的某一秒，例如某天下午2点发现中午12点有一次误删表，需要找回数据：

• 首先，找到最近一次的全量备份，从这个备份恢复到临时库。
• 然后，从备份的时间开始，将备份的bin log依次取出，重放到12点误删表之前的那个时刻。

这样临时库就和误删之前的线上库一样了，然后可以把表数据从临时库中取出，恢复到线上库。

那么为什么日志需要两阶段提交呢？
由于redo log和bin log是两个独立的逻辑，如果不采用两阶段提交，那么就是先写完redo log再写bin log，或者采用相反顺序。那么这两种方式会出现什么问题呢？

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

1. 先写redo log再写bin log：假设redo log写完，bin log还没写完的期间，MYSQL异常重启，redo log写完后，系统即使崩溃，仍然能恢复数据，所以恢复后的这一行c的值为1。
   但是由于bin log没有写完就crash了，这时候bin log里就没有记录这个语句，因此，之后备份日志的时候，存起来的bin log里就没有这条语句。
   如果需要用这个bin log恢复临时库的话，这个语句的bin log丢失，这个临时库就会少一次更新，恢复出来的这一行c的值就是0，与原库不同。

2. 先写bin log再写redo log：如果bin log写完后crash，由于redo log没写，崩溃后恢复这个事务无效，所以这一行c的值为0。但是bin log里已经记录了"把c从0改成1"这个日志，所以，在之后用bin log恢复临时库的时候就多出了一个事务，恢复出来的c的值就是1，与原库不同。

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

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

参考文章：https://time.geekbang.org/column/article/115538