2日志系统：一条sql更新语句是如何执行的 - MySQL实战45讲学习笔记

上一讲介绍了一条sql查询语句是如何执行的，其过程一般是经过连接器、分析器、优化器、执行器，最后使用存储引擎提供的接口获得想要的结果。

那么，

一条更新语句是怎样的呢？

大家都知道，更新语句涉及到写操作，也就是需要去写数据库，而数据库的文件保存在磁盘里，每次去写磁盘是非常耗时间的。

所以，InnoDB引擎使用了叫做redo log的一种日志文件，先把更新写到redo log里面，并更新内存。InnoDB在合适的时候将多个更新操作写进磁盘中。

当然真正具体并且详细的更新操作并不只是这样，还涉及到bin log日志，两阶段协议，介绍完这些概念，再来看看一条sql更新语句具体是怎么执行的把。

看之前带着这样一个问题：如果在某个时刻数据库发生故障，我们想将数据库恢复到一个月前得某个时刻，或者昨天得某个时刻，我们该怎么恢复呢？

redo log日志

这个日志是干什么的呢？其实刚才已经提到了，就是每次写操作都要写磁盘，磁盘先去查找相应记录，然后再更新，整个过程的I/O成本、查找成本太高，

所以InnoDB引擎就会将更新先写到redo log日志里（不需要去查找，直接将更新操作加入队列），并更新内存中的记录，此时这条sql更新语句就算是完成了。InnoDB引擎会在系统比较空闲的时候将这些更新写到磁盘中。

这个过程是MySQL中经常提到的WAL技术，WAL—Write-Ahead Logging，关键点在于：先写日志、再写磁盘。

注意

InnoDB的redo log是固定大小的，比如：可以配置4个文件，每个文件1GB，可以认为redo log一共4GB大小。

所以，问题是：如果我们把redo log写满了，那该怎么办呢？

我们可以先看看下面这个图，表示了redo log的数据结构
此图来自于极客时间丁奇老师MySQL实战45讲

在我看来，redo log的数据结构就像是一个循环队列，write pos表示入队指针，checkpoint表示出队指针，指针指向的是记录。

所以，回到刚才的问题，redo log满了怎么办？

当redo log满了的时候，也就是write pos指针追上checkpoint指针的时候，此时，要解决日志满了的问题，只需要出队一些记录，腾出一些空间就可以了。

也就是，当日志满的时候，推进checkpoint指针，将记录写入磁盘。

有了redo log，就可以保证 即使数据库发生异常重启，之前提交的记录也不会丢失。这个能力叫做crash-safe

binlog日志

上一章我们学习了MySQL分为server层（负责功能层面的事情）和存储引擎层（负责存储相关的事情）

上面的redo log日志是InnoDB所特有的，而Server层也有自己的日志，binlog日志。

所以，为什么会有两个日志呢？一个日志不就可以完成我们需要的工作吗？（带着问题接着看下去）

丁奇老师提到的历史问题：最开始MySQL里并没有InnoDB引擎。MySQL自带的引擎是MyIASM，而binlog日志只能用于归档，日志和引擎不具备crash-safe能力。而InnoDB是另一个公司以插件的形式引入MySQL的，只依靠binlog不能实现crash-safe能力，所以InnoDB使用redo log实现了crash-safe能力。（这解释了为什么有两个日志）

现在看看binlog到底是什么吧

我们通过比较binlog和redo log来了解

1. redo log是InnoDB特有的，用来实现crash-safe能力；binlog是Server层的日志，所有引擎都可以使用。
2. redo log是物理日志，记录的是“在哪个数据页上做了什么修改”；binlog是逻辑日志，记录的是“语句的原始逻辑”（应该可以理解成记录的是SQL语句，有错误请大神指出）
3. redo log是循环写的，空间固定，可能写满；binlog是追加写入的，可以理解成binlog不会被写满。

看到这里，不知道你对刚开始提出的问题（怎么恢复到一个月内任何一个时刻的数据库状态）有没有了自己的答案，没有继续往下看。

学习了两个日志之后，再看看sql更新语句是怎么执行的？

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

前面省略。。。到了执行器：

1. 执行器调用引擎提供的接口，取ID=2这一行。如果ID有索引，那么去B+树上去搜索记录（如果没有索引一行一行找），如果记录在内存直接返回；如果不在，则去磁盘中读到内存，然后返回。
2. 执行器拿到行记录，执行set操作加一，再调用接口写入这行新数据到内存。
3. 引擎将数据写入内存的同时，将更新操作记录到redo log中，redo log此时处于prepared状态，然后，引擎通知执行器我执行完成了，随时可以commit。
4. 执行器收到消息后，生成这个更新操作的binlog，并把binlog写入磁盘。
5. 执行器调用提交事务接口，引擎就可以把redo log改成commit状态，事务提交成功，更新完成。

上面将redo log的写入拆成了两个步骤：prepare和commit，这就是”两阶段提交“。

两阶段提交

为什么要有两阶段提交呢？

答：为了让两个日志文件之间的逻辑保持一致。

那为什么我们需要保证这两个日志文件的逻辑一致呢？不一致又会怎样？

这个问题就和刚才一直说的问题（恢复到一个月内任一时刻）相关联起来了。

先说怎么让数据库恢复到一个月内任一时刻的状态？

我们已经知道binlog以”追加写“的形式记录所有的逻辑操作，如果我们想要数据库可以恢复到一个月内任意时刻的状态，那我们就需要保存一个月内所有的binlog文件，与此同时，数据库也会定期做整库备份（可以理解成这一时刻数据库整体的快照）（定期取决于系统重要性，周期可以是一天、也可以是一周）。

比如此时此刻，数据库出问题了，数据丢失了，我们想恢复数据，我们可以这样：

1. 先找到距离此时此刻最近的一个整库备份，将数据库恢复到这个时刻的临时库；
2. 从这个整库备份的时间点，在binlog中找到这个时间点的那条语句，开始一直执行binlog中的逻辑操作，一直执行到此时此刻（数据库出问题的时刻）

这样，数据库就和出问题之前一样了。

上面提到的问题：为什么要用两个日志？

我认为，一方面是redo log实现crash-safe能力，但它毕竟保存的记录不如binlog多，所以恢复的时候用binlog，而不用redo log。

现在就剩最后一个问题了

为什么需要用“两阶段提交”去保证两个日志文件逻辑一致？

这里用到了反证法，也就是当两个日志文件不一致，会出现什么问题？

以上面的update语句（比如c=0，set后c=1）为例，我们在执行这条更新的时候数据库出现问题：

1. 情况一：先写redo log，后写binlog。会出现redo log写完并且已经提交了而binlog还没有写的情况。

   如果此时，MySQL异常重启，数据丢失，我们想去恢复数据库到此时此刻。根据上面介绍的，我们先找到整库备份，然后会从临时库的时刻去执行binlog中的逻辑语句，一直恢复到此时此刻。

   但是，binlog还没有写入最后一条语句，那么使用binlog恢复的数据库（c=0）就少了这一次更新，和原库（c=1）不一致了。

2. 情况二：先写binlog，后写redo log。会出现binlog写完而redo log还没有写的情况。

   此时，数据库崩溃了，那么在崩溃恢复之后的数据库（c=1），而原来redo log没有提交事务，原库（c=0），不一致了。

丁奇老师还说：你可能会说，这个概率是不是很低，平时也没有什么动不动就需要恢复临时库的场景呀？

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

丁奇老师留的思考题：前面我说到定期全量备份的周期“取决于系统重要性，有的是一天一备，有的是一周一备”。那么在什么场景下，一天一备会比一周一备更有优势呢？或者说，它影响了这个数据库系统的哪个指标？

上一期答案：分析器，因为mysql在分析器的时候就知道了表名、列名，所以，如果如果列不存在的话会报错。