前面我们了解了SQL查询语句是如何执行的,一条SQL查询语句的过程需要经过连接器、分析器、优化器、执行器等功能模块，最终到达存储引擎。
在MySQL中，可以恢复到半个月内的任何一个时间点，这时基于日志系统来实现的。
更新语句的流程
在这个例子中，假设创建了表T。
create table T(ID int primary key, c int)
上面显示了前面提到的查询语句的流程，首先要确定的是更新语句的过程与查询语句的过程相同。
[En]
The flow of the query statement mentioned earlier is shown above, and the first thing to be sure is that the process of updating the statement is the same as that of the query statement.
再假如我们在表T中执行更新语句如下：
update T set c = c + 1 where ID = 2;
执行任何语句前都需要连接上MySQL，这就是 连接器的工作。
接下来，因为我们执行的是更新操作，所以 查询缓存就需要刷新以确保缓存中数据的正确性， 因此需要将表T中的查询缓存都清空，这也是之前不推荐使用查询缓存的原因。
接下来， 分析器会通过词法和语法解析知道这是一条更新语句。
优化器决定来使用ID这个索引。
然后， 执行器负责具体执行，找到对应的数据行，然后执行更新。
与查询流程不一样的是，更新流程还涉及两个重要的日志模块(redo\ log)（重做日志)和(bin\ log)(归档日志)。
redo log
在MySQL中，如果每一次的更新都需要重新写入磁盘，然后从磁盘中找到那条记录，然后再执行更新，整个过程的I/O成本、查找成本都很高，为了解决这个问题，MySQL使用了WAL技术，全称为(Write-Ahead-Logging)，关键点在于 先写日志，再写磁盘。
具体的做法是，当有记录需要更新时，InnoDB引擎会先把记录写到(redo\ log)中，这时更新就算是完成了。同时，InnoDB引擎会在适当的时候，将这次操作更新到磁盘中，这个更新往往是在系统比较空闲的时候执行的。
然后继续关联，如果更新的记录不多，系统可以在空闲时等待真正的更新。如果有更多更新的记录，它必须暂时停止系统，将更新写入磁盘，然后从日志中删除这些记录，以便为新的更新提供空间。
[En]
Then continue to associate, if there are not many updated records, the system can wait for the real update when it is idle. If there are more updated records, it has to stop the system temporarily, write the updates to disk, and then remove these records from the log to provide space for new updates.
InnoDB的(redo\ log)大小是固定的，比如可以配置一组4个文件，每个文件的大小是1GB，那么整个日志的大小就是4GB。
如上所示，从头写到尾，从头写到尾。
[En]
As shown above, write from the beginning to the end and from the beginning to the end.
(write\ pos)是当前记录的位置，一边写一边后移，写到第3号文件末尾就又从第0号文件开头开始写。
(checkpoint)是当前要擦除的位置，也是往后推移并且循环的。
(write\ pos)和(checkpoint)之间的空间是空着的部分，用来记录新的操作。
当(write\ pos)追上(checkpoint)后，表示(redo\ log)满了，这时候不能执行新的更新，必须先把操作写入磁盘后，才能继续更新，需要把(checkpoint)往后推移一些距离。
有了(redo\ log)，InnoDB就可以保证即使数据库发生了异常重启，之前提交的记录不会丢失，这个能力成为(crash-safe)。
bin log
前面的(redo\ log)更多的是数据引擎层面的日志，在Server层也有(bin\ log)日志。
因为最初时MySQL并没有InnoDB引擎，自带的引擎是MyISAM，MyISAM并不具备(crash-safe)能力，(bin\ log)日志用于归档。
这两种原木有三点不同。
[En]
There are three differences between the two kinds of logs.
- (redo\ log) 是 InnoDB 引擎特有的；(bin\ log) 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
- (redo\ log) 是物理日志，记录的是”在某个数据页上做了什么修改”；(bin\ log) 是逻辑日志，记录的是这个语句的原始逻辑，比如”给 ID=2 这一行的 c 字段加 1 “。
- (redo\ log) 是循环写的，空间固定会用完；(bin\ log) 是可以追加写入的。”追加写”是指(bin\ log) 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
案例分析
在上面提到的例子中，update语句的流程如下：
- 执行器先找引擎取ID=2这一行。ID是主键，引擎直接到主键索引树上搜索找到这一行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器，否则，需要先从磁盘中读入内存，然后再返回。
- 执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在将其修改为N+1，得到新的一行数据，再调用引擎接口写入这行新数据；
- 引擎把这行新数据更新到内存中，同时将这个更新操作记录到(redo\ log)中，此时(redo\ log)处于prepare状态。然后告知执行器执行完成，可以提交事务；
- 执行器生产这个操作的(bin\ log)，并把(bin\ log)写入磁盘；
- 执行器调用引擎的提交事务接口，引擎把刚刚写入的(redo\ log)改成commit状态，表示事务已提交，更新完成。
上图的黑色部分表明它是在执行器中执行的，而亮部分表明它是在发动机中执行的。
[En]
The dark part of the image above indicates that it is executed in the actuator, and the light part indicates that it is executed in the engine.
将(redo\ log)的写入拆分为两个步骤：prepared和commit也成为两阶段提交。
两阶段提交
两阶段提交这是为了使两个日志之间的逻辑一致。
[En]
Two-phase commit this is to make the logic between the two logs consistent.
让我们首先来看看如何使用两个日志进行数据恢复。
[En]
Let’s first take a look at how to use two logs for data recovery.
(bin\ log)会记录下所有的逻辑操作，并且是追加写方式，因此当我们对某个时间的系统进行备份后，如果在某天下午2点发现12点有一次误删操作，需要找回数据，我们可以这么操作。
- 首先，找到最近的完整备份，比如昨晚的备份，我们会将其备份到临时库
[En]
first of all, find the most recent full backup, such as the one last night, and we will back it up to the temporary library*
- 然后从备份的时间点开始，将备份的(bin\ log)依次取出来，重放到中午误删表之前的那个时刻；
此时，我们的临时图书馆与误删除前的网上图书馆一样。此时，我们将表数据从临时库中取出，并根据需要将其恢复到在线库中。
[En]
At this time, our temporary library is the same as the online library before it was deleted by mistake. At this time, we take the table data out of the temporary library and restore it to the online library as needed.
由于 (redo\ log) 和 (bin\ log) 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 (redo\ log) 再写 (bin\ log)，或者采用反过来的顺序。我们看看这两种方式会有什么问题。
假定当前ID=2的行，字段c的值是0，再假定update过程中，我们写完第一个日志后，第二个日志还没有写入就发生了(crash)，会出现如下情况：
- 先写(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，与原库的值不同。 - 先写(bin\ log) 后写(redo\ log)。如果在(bin\ log) 写完之后 crash，由于(redo\ log) 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是(bin\ log) 里面已经记录了”把 c 从 0 改成 1″这个日志。所以，在之后用(bin\ log) 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。
因此，您可以看到，如果不使用两阶段提交，数据库的状态可能与其日志恢复的状态不同。
[En]
Therefore, you can see that if two-phase commit is not used, the state of the database is likely to be different from that recovered by its log.
事实上，我们不仅需要在恢复过程中迁移数据，当我们需要增加库或备份库的容量来增加系统的阅读能力时，也需要迁移数据。
[En]
In fact, we not only need to migrate data during recovery, but also need to migrate data when we need to increase the capacity of the library or the backup library to increase the reading capacity of the system.
现在的常用做法也是全量备份+(bin\ log)来实现的。
简单说，(redo\ log) 和 (bin\ log) 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。
Original: https://www.cnblogs.com/nullpointer-c/p/15864479.html
Author: NullPointer_C
Title: MySQL45讲-2-一条SQL更新语句是如何执行的?
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/507840/
转载文章受原作者版权保护。转载请注明原作者出处！