MySQL系列之一条更新SQL的生命历程

听说 MySQL 能恢复到半个月内任意一秒的状态

要从一条更新语句说起，如果将ID=2这一行的值+1，SQL语句可以这样写：

mysql> update T set c=c+1 where ID=2;
复制代码

执行一条更新语句同样会走一遍查询语句的流程:

• 连接 数据库
• 清空该表涉及到的 缓存
• 分析器 通过词法和语法解析出这是一条更新语句，并确定涉及到表与字段
• 优化器 决定使用 "ID" 这个索引
• 执行器 找到这一行数据，然后进行更新操作
  与查询过程不同的是，更新涉及到两个日志模块： redo log（重做日志） 和 bin log（归档日志）

临时记录：redo log

酒店掌柜有一个账本和一个小黑板，来做赊账的记录。有以下两种方案：

1. 每一笔账都打开账本做记录，当有人还账时，找到对应的赊账记录，修改记录的状态
2. 先在黑板上记录本次要做的操作，打烊后按照黑板上的记录向账本上进行核算

当生意红火，顾客络绎不绝时，第一种方案效率实在是低下，掌柜的一定按照第二种方案来记账。

同样的，MySQL如果每次更新操作都要写入磁盘，在磁盘中找到对应记录，然后更新，这个过程的IO成本、查找成本都太高了。

为了解决和这个问题，MySQL就使用了类似于 黑板-账本 模式来提高效率。这一模式即为 WAL技术 ，全程为 Write-Ahead Logging ，关键点: 先写日志，再写磁盘 ，也就是前文中先写黑板，再写账本。

具体步骤如下：

当有记录需要更新，innoDB先把记录写入redo log中，并更新内存，这是更新操作就算结束了。innoDB引擎会在适当的时候讲操作记录更新到磁盘里，这一动作一般是系统比较闲的时候做的。

redo log的大小是固定的，共有4个文件组成，每个大小为1G。逻辑上可以将4个文件理解为环形，从头开始写，写到末尾又重新开始新的一轮,如下图所示

write pos为当前记录位置，check point为当前擦除点的位置，当记录更新时，check point会随着文件的记录向后移动。擦除后未写入的位置可以记录新的操作。当write pos追上了check point，则需要停下来写入动作，将redo log内容写入磁盘,然后清除check point向后移动。

。

归档日志：bin log

redo log与bin log日志的区别：

1. redo log 是属于innoDB引擎所有，bin log是server提供的，所有引擎都可以使用
2. redo log 属于物理日志，记录 "在某个数据页做了什么修改" ，bin log记录的是该语句逻辑日志 "将ID=2的这一行c的值+1"
3. redo log日志文件是循环使用的，空间有使用完的时刻，bin log是追加记录的，不会覆盖之前的记录

也就是说，server搭配其他引擎是没有redo log的，因此也就没有了crash-safe能力

更新具体流程

基于对两个日志文件的了解，再次深入了解更新的流程

1. 执行器先通过引擎使用树搜索找到ID=2这一行，如果该记录所在的数据页本身就在内存中，则直接返回执行器，否则先从磁盘读入内存，然后返回
2. 执行器拿到数据后将c的值加一，然后通过引擎的写入接口将修改后的数据写入
3. 引擎j将新数据更新到内存中，然后在redo log中记录此次修改，这时redo log中该记录的状态置为prepare，并告知执行器已经更新完成，随时可以提交事务
4. 执行器生成此次操作的bin log，将bin log写入磁盘中
5. 执行器调用引擎的提交事务接口，引擎将刚刚写入的redo log置为commit状态，更新结束

下图是《MySQL实战》提供的流程图：

浅色代表在innoDB中执行，深色在server中执行

两阶段提交

从上图可以看出，redo log是分两个阶段来提交的,这是为了保持两个日志逻辑上一致 如果不用两阶段提交会发生什么呢 利用反证法来看下：

假设初始ID=2的数据行，c的值为0，现在要执行c+1的操作。

1. 先记录redo log 后记录bin log 如果刚记录完redo log，还没有记录bin log时，c的值已经记录变为1，这时MySQL服务崩溃重启，根据crash-safe机制，可以用redo log来恢复数据库，恢复后的数据中c的值为1。由于bin log中没有记录这一变化，以后备份bin log时，c的值还是0。如果有一天需要从bin log回复一台备用数据库，由于bin log少了一次更新，则最后恢复出来的c值仍然为0，与原库中值不符合
2. 先记录bin log 后记录redo log 写完bin log就发生crash，还没来得及写入redo log，崩溃恢复后这个事务是无效的，因此c的值还是0，但是bin log中已经记录了"将c的值+1"的日志，所以用bin log恢复出来的数据多出来一个事务，使得c的值为1，与原库中数据不符。
3. 两阶段提交 记录过bin log回过头提交commit （可参见评论区知识点） 更新redo log后，还没有记录bin log时崩溃，这时redo log的状态还是prepare，事务并没有提交，而且 bin log中没有记录 ，因此由于crash-safe机制，并不会恢复该记录，c的值仍然为0，由于bin log中没有记录，以后从bin log恢复数据时，c的值在此操作中并没有记录变化，因此还是0，与原库中数据一致；另一种情况：更新redo log，也更新了bin log，下一步执行器调用commit接口前崩溃，这时虽然redo log中状态为prepare，但是 从bin log中查到有记录 ，所以还是会从redo log中恢复c=1，后面直接从bin log恢复出新的数据库时，因为已经记录c的值+1，所以与原库中的值相同