一条sql更新语句是如何执行的？

之前经常听DBA的同事说，mysql可以恢复到半个月内任意1秒的状态，惊叹的同时，你是不是你不免有一些好奇，这是怎么做到的呢？
我们还是从一张表的一条更新语句说起，下面是这个表的创建语句，这个表有一个主键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

前面介绍了SQL语句基本的执行链路.这里更新语句同样适用

你执行语句之前要先连接数据库，这是连接器的工作。
前面我们说过，在一个表上有更新的时候，更这个表有关的查询缓存会失效，所以这条语句就会把T表上所有的缓存结果清空，这也是我们一般不使用查询缓存的原因。

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

与查询流程不一样的是更新流程还涉及到两个重要的日志模块，它们是今天要讨论的主角 redo log(重做日志）binlog(归档日志)。如果接触Mysql这两个词肯定是绕不过的。接下来分介绍这两种日志

重要的日志模块 ：redo log

不知道你还记不记得《孔乙己》这篇文章，酒店掌柜有一个粉板，专门用来记录客人的赊账记录，如果赊账的人不多，那么t她可以把顾客名和账目写在粉板上。但如果赊账的人多了，粉板总会有记不下的时候，这时候掌柜一定还有一个专门记录赊账的账本。
如果有人要赊账或者还账的时候，掌柜一般有两种做法：
一种做法是直接把账本翻出来，把这个赊账的记录加上去或者删除掉：
另一种做法是先在粉本上记下这次的账，等打烊后再把账本翻出来核算。
在生意红火柜台很忙时，掌柜一定会选择后者，因为前者操作实在是太麻烦了。首先你得找到这个人赊账的总额那条记录。你想想看密密麻麻几十页，掌柜要找到那个名字，还得带上老花镜慢慢找，找到之后再拿出来盘算计算，然后再将结果写回到账本上。
这个过程想想都麻烦。相比之下，还是先在粉本上记一下方便。你想想如果掌柜没有粉板的帮助，每次记账都得翻账本，效率是不是低的让人难以忍受？
同样在Mysql里也有这个问题，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到那条对应的记录，然后再更新，整个过程IO成本，查找成本都很高。为了解决这个问题， Mysql的设计者就用了类似酒店掌柜粉板的思路来提升更新效率。
而粉板和账本配合的整个过程，其实就是mysql里常说WAL技术，WAL的全称是Write-Ahead Loging 它的关键是先写日志 再写磁盘 也就是先写粉板 等不忙的时候再写账本
具体来说，当一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log(粉板)里面，并更新内存，这个时候更新就算是完成了。同时InnoDB 引擎会在适当的时候，将这个记录更新到磁盘里，而这个更新往往是在系统比较空闲的时候做，这就像打烊之后老板做的事。
如果今天赊账不多，掌柜可以等打烊之后再整理，但如果某天赊账特别多，粉板写满了又怎么办呢？和时候掌柜只好放下手中的活，把粉板的一部分记录更新到账本中，然后把这些记录从粉板上擦掉，为记新账腾出空间
与此类似,InnoDB的redo log是固定大小的，比如可以配置一组4个文件，每个文件打大小1GB，那么这块“粉板‘总共就可以记录4GB的操作。从头开始写，写到末尾又回到开头循环写 如图所示：

write pos是记录当前位置，一边写一边往后移，写到第三号文件的末尾就回到0号文件开头。
checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件
write pos和checkpoint之间的是“粉板”还空着的部分，可以用来记录新的操作。如果write pos追上 checkpoint 表示粉板已经满了，这个时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpint推进一下。
有了redo log ，innoDB就可以保证即使数据库发生异常重启，之前提交的记录不会丢失，这个能力被称为 crash-sale
要理解 crash-safe这个概念，可以想想我们前面赊账的例子。只要赊账记录在粉本上或者写在了账本上，之后即使掌柜忘记了，比如突然停业几天，恢复生意后依然可以通过账本和粉板上的数据明确赊账 账目

重要日志模块 binlog

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

我想你肯定会问会什么会有两种日志呢？
因为最开始Mysql里面并没有InnoDB引擎.Mysql自带的引擎是Mylsam,但是Mylsam没有crash-safe的能力，binlog日志只用于归档。而InnoDB是另一个公司以插件的形式引入Mysql的既然只依靠binlog是没有crash-safe能力的，所以innoDB使用另外一套日志系统–也就是rodo 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.引擎将这行新数据更新到内存中，同时将这个更新记录操作记录到redolog里面。此时redo log处于prepare 状态。然后告知执行器 执行完了，随时可以提交事务。
4.执行器 生成这个操作的binlog 并把binlog写入磁盘。
5。执行器 调用提交事务的接口，引擎把刚刚写入的redo log提交（commit）状态更新完成
绿色表示在执行器中执行 蓝色表示在InnoDB存储引擎中执行

你可能注意到，最后三步有点“绕”。将reloglog的写入拆成了两个步骤：prepare和commit 这就是“”两阶段提交

两阶段提交

为什么必须有“两阶段提交”呢？这就是为了让两份日志间的逻辑一致。要说明这个问题。我们得从？文章开头的那个问题说起：怎么样让数据恢复到半个月内任意一秒的状态？
前面我们说过，binlog会记录所有的逻辑操作，采用的是“追加写的”形式，如果你的DBA承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有binlog ,同时系统会定期做整库备份。这里“定期”取决于定期的重要性。可以一天一备，也可以一天一备。
当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：
首先，找到最近一份的全量备份，如果你运气好，可能就是昨晚的一个备份，从这个备份恢复到临时库
然后从备份时间点开始，将备份的binlog依次取出来，重放到中午误删表之前的那个时刻
这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢 复到线上库去
好了，说完了数据恢复过程，我们回来说说，为什么日志需要“两阶段提交”。这里不妨用反证法 来进行解释。
由于redo log和binlog是两个独立的逻辑，如果不用两阶段提交，要么就是先写完redo log再写 binlog，或者采用反过来的顺序。我们看看这两种方式会有什么问题。
仍然用前面的update语句来做例子。假设当前ID=2的行，字段c的值是0，再假设执行update语 句过程中在写完第一个日志后，第二个日志还没有写完期间发生了crash，会出现什么情况呢？

1. 先先写写rreeddoo lloogg后后写写bbiinnlloogg。假设在redo log写完，binlog还没有写完的时候，MySQL进程异 常重启。由于我们前面说过的，redo log写完之后，系统即使崩溃，仍然能够把数据恢复回 来，所以恢复后这一行c的值是1。 但是由于binlog没写完就crash了，这时候binlog里面就没有记录这个语句。因此，之后备份 日志的时候，存起来的binlog里面就没有这条语句。 然后你会发现，如果需要用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这 个临时库就会少了这一次更新，恢复出来的这一行c的值就是0，与原库的值不同。
2. 先先写写bbiinnlloogg后后写写rreeddoo lloogg。如果在binlog写完之后crash，由于redo log还没写，崩溃恢复以 后这个事务无效，所以这一行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个日 志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是 1，与原库的值不同。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的 状态不一致。 你可能会说，这个概率是不是很低，平时也没有什么动不动就需要恢复临时库的场景呀？ 其实不是的，不只是误操作后需要用这个过程来恢复数据。当你需要扩容的时候，也就是需要再 多搭建一些备库来增加系统的读能力的时候，现在常见的做法也是用全量备份加上应用binlog来 实现的，这个“不一致”就会导致你的线上出现主从数据库不一致的情况。 简单说，redo log和binlog都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保 持逻辑上的一致。