MySQL语句执行过程以及三大日志

素颜马尾好姑娘i 2022-09-11 13:14 144阅读 0赞

# 前言 #

当初看MySQL45讲时做的笔记，发布出来记录下自己的学习心得吧。

# 查询缓存 #

连接建立完成后，你就可以执行select语句了。执行逻辑就会来到第二步：查询缓存。

MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果。如果你的查询能够直接在这个缓存中找到key，那么这个value就会被直接返回给客户端。

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。你可以看到，如果查询命中缓存，MySQL不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

**但是大多数情况下我会建议你不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利。**

查询缓存的失效非常频繁，**只要有对一个表的更新，这个表上所有的查询缓存都会被清空**。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

# 执行器 #

MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示(在工程实现上，如果命中查询缓存，会在查询缓存放回结果的时候，做权限验证。查询也会在优化器之前调用precheck验证权限)。

mysql> select * from T where ID=10;
    
    ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

比如我们这个例子中的表T中，ID字段没有索引，那么执行器的执行流程是这样的：

1.  调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存在结果集中；
2.  调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3.  执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

至此，这个语句就执行完成了。

对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。

你会在数据库的慢查询日志中看到一个rows\_examined的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。

在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此\*\*引擎扫描行数跟rows\_examined并不是完全相同的。\*\*我们后面会专门有一篇文章来讲存储引擎的内部机制，里面会有详细的说明。

# binlog和redolog #

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

我想你肯定会问，为什么会有两份日志呢？

因为最开始MySQL里并没有InnoDB引擎。MySQL自带的引擎是MyISAM，但是MyISAM没有crash-safe的能力，binlog日志只能用于归档。而InnoDB是另一个公司以插件形式引入MySQL的，既然只依靠binlog是没有crash-safe能力的，所以InnoDB使用另外一套日志系统——也就是redo 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.  引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
4.  执行器生成这个操作的binlog，并把binlog写入磁盘。
5.  执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

这里我给出这个update语句的执行流程图，图中浅色框表示是在InnoDB内部执行的，深色框表示是在执行器中执行的。

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VegFJK2x-1631800170190)(https://i.loli.net/2021/04/16/hGjufz9IeWyM2Px.png)\]

update语句执行流程

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

# Binlog三种格式详细介绍 #

https://blog.csdn.net/sj349781478/article/details/77816524

1.Statement：每一条会修改数据的sql都会记录在binlog中。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)

缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last\_insert\_id()，以及user-defined functions(udf)会出现问题).

2.Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题

缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

3.Mixedlevel: 是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

# 两阶段提交 #

为什么必须有“两阶段提交”呢？这是为了让两份日志之间的逻辑一致。要说明这个问题，我们得从文章开头的那个问题说起：**怎样让数据库恢复到半个月内任意一秒的状态？**

前面我们说过了，binlog会记录所有的逻辑操作，并且是采用“追加写”的形式。如果你的DBA承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有binlog，同时系统会定期做整库备份。这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。

当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：

*  首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；
 *  然后，从备份的时间点开始，将备份的binlog依次取出来，重放到中午误删表之前的那个时刻。

这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去。

好了，说完了数据恢复过程，我们回来说说，为什么日志需要“两阶段提交”。这里不妨用反证法来进行解释。

由于redo log和binlog是两个独立的逻辑，如果不用两阶段提交，要么就是先写完redo log再写binlog，或者采用反过来的顺序。我们看看这两种方式会有什么问题。

仍然用前面的update语句来做例子。假设当前ID=2的行，字段c的值是0，再假设执行update语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了crash，会出现什么情况呢？

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

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

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

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

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

## 为什么有了binlog还要有redo log？ ##

binlog会记录所有与MySQL数据库有关的日志记录，包括InnoDB, MyISAM，Heap等其他存储引起的日志。而redo log只记录innodb引擎本身的日志。  
binlog记录的是关于一个事务的具体操作内容，即该日志是逻辑日志。而redolog记录的是关于每个页的更改的物理情况。  
写入时间不同。binlog仅在事务提交前提交，只写磁盘一次，不论这个事务有多大。而redolog在事务进行过程中会不停的写入。  
它们分工是不同的。binlog用来做数据归档，但不具备崩溃恢复的能力，也就是说如果系统突然崩溃，重启后可能会有部分数据丢失。  
innodb将所有对页面的修改操作写入一个专门的文件，并在数据库启动时从此文件进行恢复操作。  
![image-20210416105603986][]

# binlog的写入机制 #

https://www.jianshu.com/p/f242bc1e95ff

其实，binlog的写入逻辑比较简单：事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。

一个事务的binlog是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了binlog cache的保存问题。

系统给binlog cache分配了一片内存，每个线程一个，参数 binlog\_cache\_size用于控制单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

事务提交的时候，执行器把binlog cache里的完整事务写入到binlog中，并清空binlog cache。状态如图1所示。

![img][]

图1 binlog写盘状态

可以看到，每个线程有自己binlog cache，但是共用同一份binlog文件。

*  图中的write，指的就是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快。
 *  图中的fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为fsync才占磁盘的IOPS。

write 和fsync的时机，是由参数sync\_binlog控制的：

> 1.  sync\_binlog=0的时候，表示每次提交事务都只write，不fsync；
> 2.  sync\_binlog=1的时候，表示每次提交事务都会执行fsync；
> 3.  sync\_binlog=N(N>1)的时候，表示每次提交事务都write，但累积N个事务后才fsync。

因此，在出现IO瓶颈的场景里，将sync\_binlog设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成0，比较常见的是将其设置为100~1000中的某个数值。

但是，将sync\_binlog设置为N，对应的风险是：如果主机发生异常重启，会丢失最近N个事务的binlog日志。

# redo log的写入机制 #

redo log buffer里面的内容，是不是每次生成后都要直接持久化到磁盘呢？

答案是，不需要。

**如果事务执行期间MySQL发生异常重启，那这部分日志就丢了。由于事务并没有提交，所以这时日志丢了也不会有损失。**

那么，另外一个问题是，事务还没提交的时候，redo log buffer中的部分日志有没有可能被持久化到磁盘呢？

答案是，确实会有。

这个问题，要从redo log可能存在的三种状态说起。这三种状态，对应的就是图2 中的三个颜色块。

![img][img 1]

图2 MySQL redo log存储状态

这三种状态分别是：

1.  存在redo log buffer中，物理上是在MySQL进程内存中，就是图中的红色部分；
2.  写到磁盘(write)，但是没有持久化（fsync)，物理上是在文件系统的page cache里面，也就是图中的黄色部分；
3.  持久化到磁盘，对应的是hard disk，也就是图中的绿色部分。

日志写到redo log buffer是很快的，wirte到page cache也差不多，但是持久化到磁盘的速度就慢多了。

为了控制redo log的写入策略，InnoDB提供了innodb\_flush\_log\_at\_trx\_commit参数，它有三种可能取值：

> 1.  设置为0的时候，表示每次事务提交时都只是把redo log留在redo log buffer中;
> 2.  设置为1的时候，表示每次事务提交时都将redo log直接持久化到磁盘；
> 3.  设置为2的时候，表示每次事务提交时都只是把redo log写到page cache。

InnoDB有一个后台线程，每隔1秒，就会把redo log buffer中的日志，调用write写到文件系统的page cache，然后调用fsync持久化到磁盘。

注意，事务执行中间过程的redo log也是直接写在redo log buffer中的，这些redo log也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的redo log，也是可能已经持久化到磁盘的。

实际上，除了后台线程每秒一次的轮询操作外，还有两种场景会让一个没有提交的事务的redo log写入到磁盘中。

1.  \*\*一种是，redo log buffer占用的空间即将达到 innodb\_log\_buffer\_size一半的时候，后台线程会主动写盘。\*\*注意，由于这个事务并没有提交，所以这个写盘动作只是write，而没有调用fsync，也就是只留在了文件系统的page cache。
2.  \*\*另一种是，并行的事务提交的时候，顺带将这个事务的redo log buffer持久化到磁盘。\*\*假设一个事务A执行到一半，已经写了一些redo log到buffer中，这时候有另外一个线程的事务B提交，如果innodb\_flush\_log\_at\_trx\_commit设置的是1，那么按照这个参数的逻辑，事务B要把redo log buffer里的日志全部持久化到磁盘。这时候，就会带上事务A在redo log buffer里的日志一起持久化到磁盘。

这里需要说明的是，我们介绍两阶段提交的时候说过，时序上redo log先prepare， 再写binlog，最后再把redo log commit。

如果把innodb\_flush\_log\_at\_trx\_commit设置成1，那么redo log在prepare阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于prepare 的redo log，再加上binlog来恢复的。（如果你印象有点儿模糊了，可以再回顾下[第15篇文章][15]中的相关内容）。

每秒一次后台轮询刷盘，再加上崩溃恢复这个逻辑，InnoDB就认为redo log在commit的时候就不需要fsync了，只会write到文件系统的page cache中就够了。

通常我们说MySQL的“双1”配置，指的就是sync\_binlog和innodb\_flush\_log\_at\_trx\_commit都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是redo log（prepare 阶段），一次是binlog。

# MySQL的WAL（Write-Ahead Logging）机制 #

https://www.jianshu.com/p/f242bc1e95ff

MySQL 里经常说到的 WAL技术，也就是先写日志，再写磁盘。

**当内存数据页跟磁盘数据页内容不一致的时候，我们成这个内存页为“脏页”。内存数据写入磁盘后，内存和磁盘上的数据页内容就一致了，称为“干净页”。**

MySQL 从 内存更新到磁盘的过程，称为刷脏页的过程（flush）。

InnoDB 刷脏页的时机：

1.内存中的redo log 写满了，这时系统就会停止所有更新操作，把checkoutpoint 往前推，redo log留出空间可以继续写。

往前推进之后，就要把两个点之间的日志对应的所有脏页都 flush 到磁盘上。

这种情况是 InnoDB 要尽量避免的。因为出现这种情况，整个系统都不能接受更新。更新数会跌为0。

2.系统中内存不足时，当这个时候需要新的数据页到内存中，就要淘汰掉一些数据页，如果淘汰的是“脏页”，就要先将“脏页”写到磁盘。

那么为什么不能直接淘汰所有的内存，下次请求的时候，再从磁盘读入数据页，然后 拿 redo log 出来应用？这其实也是从性能的角度来考虑的，刷脏页一定写盘，就保证了每个数据页只有两种情况：

*  数据页直接在内存里，内存里的肯定是正确的，直接返回
 *  内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。 这样的效率最高。

这种情况在日常应用中其实是常态。**在InnoDB 中，使用缓冲池 （buffer pool）管理内存，缓冲池中的内存页有三种状态：**

*  还没有使用的；
 *  使用了并且是干净页
 *  使用了并且是脏页

3.数据库空闲的时候刷脏页。

4.数据库正常关闭的时候，也要把内存中所有的脏页全都flush 到磁盘上。

# 基于Redo Log和Undo Log的MySQL崩溃恢复流程 #

![基于Redo Log和Undo Log的MySQL崩溃恢复流程][Redo Log_Undo Log_MySQL]

### **黑盒下的更新数据流程** ###

当我们查询数据的时候，会先去Buffer Pool中查询。如果Buffer Pool中不存在，存储引擎会先将数据从磁盘加载到Buffer Pool中，然后将数据返回给客户端；同理，当我们更新某个数据的时候，如果这个数据不存在于Buffer Pool，同样会先数据加载进来，然后修改修改内存的数据。被修改过的数据会在之后统一刷入磁盘。

![img][img 2]

这个过程看似没啥问题，实则不讲武德。假设我们修改Buffer Pool中的数据成功，但是还没来得及将数据刷入磁盘MySQL就挂了怎么办？按照上图的逻辑，此时更新之后的数据只存在于Buffer Pool中，如果此时MySQL宕机了，这部分数据将会永久的丢失；

再者，我更新到一半突然发生错误了，想要回滚到更新之前的版本，该怎么办？那不完犊子吗，连数据持久化的保证、事务回滚都做不到还谈什么崩溃恢复？

### **Redo Log & Undo Log** ###

而通过MySQL能够实现崩溃恢复的**事实**来看，MySQL必定实现了某些骚操作。没错，这就是接下来我们要介绍的另外的两个关键功能，**Redo Log**和**Undo Log**。

这两种日志是属于InnoDB存储引擎的日志，和MySQL Server的Binlog不是一个维度的日志。

1.  **Redo Log** 记录了此次事务\*\*「完成后」\*\* 的数据状态，记录的是更新之 \*\*「后」\*\*的值
2.  **Undo Log** 记录了此次事务\*\*「开始前」\*\* 的数据状态，记录的是更新之 \*\*「前」\*\*的值

所以这两种日志有明显的区别，我用一种更加通俗的例子来解释一下这两种日志。

Redo Log就像你在命令行敲了很长的命令，敲回车执行，结果报错了。此时我们只需要再敲个↑就会拿到上一条命令，再执行一遍即可。

Undo Log就像你刚刚在Git中Commit了一下，然后再做一个较为复杂的改动，但是改着改着你的心态崩了，不想要刚刚的改动了，于是直接`git reset --hard $lastCommitId`回到了上一个版本。

### **实现日志后的更新流程** ###

有了Redo Log和Undo Log，我们再将上面的那张图给完善一下。

![img][img 3]

首先，更新数据还是会判断数据是否存在于Buffer Pool中，不存在则加载。上面我们提到了回滚的问题，在更新Buffer Pool中的数据之前，我们需要先将该数据事务开始之前的状态写入Undo Log中。假设更新到一半出错了，我们就可以通过Undo Log来回滚到事务开始前。

然后执行器会更新Buffer Pool中的数据，成功更新后会将数据最新状态写入Redo Log Buffer中。因为一个事务中可能涉及到多次读写操作，写入Buffer中分组写入，比起一条条的写入磁盘文件，效率会高很多。

![img][img 4]

那为什么Undo Log不也搞一个Undo Log Buffer，也给Undo Log提提速，雨露均沾？那我们假设有这个一个Buffer存在于InnoDB，将事务开始前的数据状态写入了Undo Log Buffer中，然后开始更新数据。

突然啪一下，很快啊，MySQL由于意外进程退出了，此时会发生一件很尴尬的事情，如果更新的数据一部分已经刷回磁盘了，但是此时事务没有成功需要回滚，你发现Undo Log随着进程退出一起没了，此时就没有办法通过Undo Log去做回滚。

那如果刚刚更新完内存，MySQL就挂了呢？此时Redo Log Buffer甚至都可能没有写入，即使写入了也没有刷到磁盘，Redo Log也丢了。

其实无所谓，因为意外宕机，该事务没有成功，既然事务事务没有成功那就需要回滚，而MySQL重启后会读取磁盘上的Redo Log文件，将其状态给加载到Buffer Pool中。而通过磁盘Redo Log文件恢复的状态和宕机前事务开始前的状态是一样的，所以是没有影响的。然后等待事务commit了之后就会将Redo Log和Binlog刷到磁盘。

### **流程中仍然存在的问题** ###

你可能认为到这一步就完美了，事实上则不然。假设我们在将Redo Log刷入到磁盘之后MySQL突然宕机了，binlog还没有来得及写入。此时重启，Redo Log所代表的状态就和Binlog所代表的状态**不一致**了。Redo Log恢复到Buffer Pool中的某行的A字段是3，但是任何监听其Binlog的数据库读取出来的数据确是2。

即使Redo Log和Binlog都写入文件了，但是这个时候MySQL所在的物理机或者VM宕机了，日志仍然会丢失。现在的OS在你写入文件的时候，会先将改动的内容写入的**OS Cache**中，以此来提高效率。然后根据策略（受你配置的参数的影响）来将OS Cache中的数据刷入磁盘。

### **基于2PC的一致性保障** ###

从这你可以发现一个关键的问题，那就是必须保证Redo Log和Binlog在事务提交时的数据一致性，要么都存在，要么都不存在。MySQL是通过 \*\*2PC（two-phase commit protocol）\*\*来实现的。

![img][img 5]

简单介绍一下2PC，它是一种保证分布式事务数据一致性的协议，它中文名叫两阶段提交，它将分布式事务的提交拆分成了2个阶段，分别是Prepare和Commit/Rollback。

就向两个拳击手开始比赛之前，裁判会在中间确认两个选手的状态，类似于问你准备好了吗？得到确认之后，裁判才会说**Fight**。

裁判询问选手的状态，对应的是**第一阶段Prepare**；得到了肯定的回答之后，裁判宣布比赛正式开始，对应的是**第二阶段Commit**，但是如果有一方选手没有准备好，裁判会宣布比赛暂停，此时对应的是第一阶段失败的情况，第二阶段的状态会变为**Rollback**。裁判就对应2PC中的**协调者Coordinator**，选手就对应**参与者Participant**。

下面我们通过一张图来看一下整个流程。

![img][img 6]

Prepare阶段，将Redo Log写入文件，并刷入磁盘，记录上内部XA事务的ID，同时将Redo Log状态设置为Prepare。Redo Log写入成功后，再将Binlog同样刷入磁盘，记录XA事务ID。

Commit阶段，向磁盘中的Redo Log写入Commit标识，表示事务提交。然后执行器调用存储引擎的接口提交事务。这就是整个过程。

### **验证2PC机制的可用性** ###

这就是2PC提交Redo Log和Binlog的过程，那在这个期间发生了异常，2PC这套机制真的能保证数据一致性吗？

假设Redo Log刷入成功了，但是还没来得及刷入Binlog MySQL就挂了。此时重启之后会发现Redo Log并没有Commit标识，此时根据记录的XA事务找到这个事务，进行回滚。

如果Redo Log刷入成功，而且Binlog也刷入成功了，但是还没有来得及将Redo Log从Prepare改成Commit MySQL就挂了，此时重启会发现虽然Redo Log没有Commit标识，但是通过XID查询到的Binlog却已经成功刷入磁盘了。

此时，虽然Redo Log没有Commit标识，MySQL也要提交这个事务。因为Binlog一旦写入，就可能会被从库或者任何消费Binlog的消费者给消费。如果此时MySQL不提交事务，则可能造成数据不一致。而且目前Redo Log和Binlog从数据层面上，其实已经Ready了，只是差个标志位。

# 推荐参数 #

*  始终设置 innodb\_flush\_log\_at\_trx\_commit=1
 *  如果启用了二进制日志记录，请设置 `sync_binlog=1`。

这也是大家常说的**双1设置**。前者保证redolog的不丢失、后者保证了binlog的不丢失。

# 必须了解的MySQL三大日志：binlog、redo log和undo log #

https://zhuanlan.zhihu.com/p/190886874

日志是mysql数据库的重要组成部分，记录着数据库运行期间各种状态信息。mysql日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。作为开发，我们重点需要关注的是二进制日志(binlog)和事务日志(包括redo log和undo log)，本文接下来会详细介绍这三种日志。

## **binlog** ##

binlog用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog是mysql的逻辑日志，并且由Server层进行记录，使用任何存储引擎的mysql数据库都会记录binlog日志。

*  逻辑日志：可以简单理解为记录的就是sql语句。
 *  物理日志：因为mysql数据最终是保存在数据页中的，物理日志记录的就是数据页变更。

binlog是通过追加的方式进行写入的，可以通过max\_binlog\_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。

### **binlog使用场景** ###

在实际应用中，binlog的主要使用场景有两个，分别是主从复制和数据恢复。

*  主从复制：在Master端开启binlog，然后将binlog发送到各个Slave端，Slave端重放binlog从而达到主从数据一致。
 *  数据恢复：通过使用mysqlbinlog工具来恢复数据。

### **binlog刷盘时机** ###

对于InnoDB存储引擎而言，只有在事务提交时才会记录biglog，此时记录还在内存中，那么biglog是什么时候刷到磁盘中的呢？mysql通过sync\_binlog参数控制biglog的刷盘时机，取值范围是0-N：

*  0：不去强制要求，由系统自行判断何时写入磁盘；
 *  1：每次commit的时候都要将binlog写入磁盘；
 *  N：每N个事务，才会将binlog写入磁盘。

从上面可以看出，sync\_binlog最安全的是设置是1，这也是MySQL 5.7.7之后版本的默认值。但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能。

推荐阅读：[Java面试题拆解][Java]

### **binlog日志格式** ###

binlog日志有三种格式，分别为STATMENT、ROW和MIXED。

> 在 MySQL 5.7.7之前，默认的格式是STATEMENT，MySQL 5.7.7之后，默认值是ROW。日志格式通过binlog-format指定。

**STATMENT**

基于SQL语句的复制(statement-based replication, SBR)，每一条会修改数据的sql语句会记录到binlog中。

*  优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO, 从而提高了性能；
 *  缺点：在某些情况下会导致主从数据不一致，比如执行sysdate()、slepp()等。

**ROW**

基于行的复制(row-based replication, RBR)，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了。

*  优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题；
 *  缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨

**MIXED**

基于STATMENT和ROW两种模式的混合复制(mixed-based replication, MBR)，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog

### 为什么会有mixed格式的binlog？ ###

基于上面的信息，我们来讨论一个问题：\*\*为什么会有mixed这种binlog格式的存在场景？\*\*推论过程是这样的：

*  因为有些statement格式的binlog可能会导致主备不一致，所以要使用row格式。
 *  但row格式的缺点是，很占空间。比如你用一个delete语句删掉10万行数据，用statement的话就是一个SQL语句被记录到binlog中，占用几十个字节的空间。但如果用row格式的binlog，就要把这10万条记录都写到binlog中。这样做，不仅会占用更大的空间，同时写binlog也要耗费IO资源，影响执行速度。
 *  所以，MySQL就取了个折中方案，也就是有了mixed格式的binlog。mixed格式的意思是，**MySQL自己会判断这条SQL语句是否可能引起主备不一致，如果有可能，就用row格式**，否则就用statement格式。

## **redo log** ##

### **为什么需要redo log** ###

我们都知道，事务的四大特性里面有一个是持久性，具体来说就是只要事务提交成功，那么对数据库做的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态。那么mysql是如何保证一致性的呢？最简单的做法是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题，主要体现在两个方面：

*  因为Innodb是以页为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！
 *  一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！

因此mysql设计了redo log，具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。

搜索Java知音公众号，回复“后端面试”，送你一份Java面试题宝典

### **redo log基本概念** ###

redo log包括两部分：一个是内存中的日志缓冲(redo log buffer)，另一个是磁盘上的日志文件(redo log file)。mysql每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file。这种先写日志，再写磁盘的技术就是MySQL里经常说到的WAL(Write-Ahead Logging) 技术。

在计算机操作系统中，用户空间(user space)下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统内核空间(kernel space)缓冲区(OS Buffer)。因此，redo log buffer写入redo log file实际上是先写入OS Buffer，然后再通过系统调用fsync()将其刷到redo log file中，过程如下：

![img][img 7]

mysql支持三种将redo log buffer写入redo log file的时机，可以通过innodb\_flush\_log\_at\_trx\_commit参数配置，各参数值含义如下：

![img][img 8]

![img][img 9]

### **redo log记录形式** ###

前面说过，redo log实际上记录数据页的变更，而这种变更记录是没必要全部保存，因此redo log实现上采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志。如下图：

![img][img 10]

同时我们很容易得知，在innodb中，既有redo log需要刷盘，还有数据页也需要刷盘，redo log存在的意义主要就是降低对数据页刷盘的要求。在上图中，write pos表示redo log当前记录的**LSN**(逻辑序列号)位置，check point表示数据页更改记录刷盘后对应redo log所处的LSN(逻辑序列号)位置。

write pos到check point之间的部分是redo log空着的部分，用于记录新的记录；check point到write pos之间是redo log待落盘的数据页更改记录。当write pos追上check point时，会先推动check point向前移动，空出位置再记录新的日志。

启动innodb的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。因为redo log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如binlog)要快很多。

重启innodb时，首先会检查磁盘中数据页的LSN，如果数据页的LSN小于日志中的LSN，则会从checkpoint开始恢复。

还有一种情况，在宕机前正处于checkpoint的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度，此时会出现数据页中记录的LSN大于日志中的LSN，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。

![img][img 11]

## **redo log与binlog区别** ##

由binlog和redo log的区别可知：binlog日志只用于归档，只依靠binlog是没有crash-safe能力的。但只有redo log也不行，因为redo log是InnoDB特有的，且日志上的记录落盘后会被覆盖掉。因此需要binlog和redo log二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。

## **undo log** ##

数据库事务四大特性中有一个是原子性，具体来说就是 原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。

实际上，原子性底层就是通过undo log实现的。undo log主要记录了数据的逻辑变化，**比如一条INSERT语句，对应一条DELETE的undo log，对于每个UPDATE语句，对应一条相反的UPDATE的undo log，这样在发生错误时，就能回滚到事务之前的数据状态**。推荐阅读：[Java面试题拆解][Java]

同时，undo log也是MVCC(多版本并发控制)实现的关键，这部分内容在面试中的老大难-mysql事务和锁，一次性讲清楚！中有介绍，不再赘述。

[image-20210416105603986]: /images/20220828/85d68d607ff0458090cf3fe4eab2b93f.png
[img]: /images/20220828/d02ec48df3c34d90a77d3a9905b2c8fd.png
[img 1]: /images/20220828/7c403c5c5041494e8821ec951170d75e.png
[15]: https://time.geekbang.org/column/article/73161
[Redo Log_Undo Log_MySQL]: /images/20220828/ea7ebd1c62c74aa6ac014850a58ce814.png
[img 2]: /images/20220828/5f4e77dcbd1b4269ac23e8f60524e824.png
[img 3]: /images/20220828/3c2c051bba85444c8040e82a6e131987.png
[img 4]: /images/20220828/8b72a7bca78c4cadaa07d56d6bda9006.png
[img 5]: /images/20220828/4670981b73ec417db77b348add026a69.png
[img 6]: /images/20220828/cb1e14a28b074fd0beab3f3160a2335c.png
[Java]: https://link.zhihu.com/?target=https%3A//mp.weixin.qq.com/s%3F__biz%3DMzIyNDU2ODA4OQ%3D%3D%26mid%3D2247485351%26idx%3D2%26sn%3D214225ab4345f4d9c562900cb42a52ba%26scene%3D21%23wechat_redirect
[img 7]: /images/20220828/becec3d5c17c490ab218badc8b17f2b4.png
[img 8]: /images/20220828/5ab7e0db91234f5bafdf59c9d3e9c21b.png
[img 9]: /images/20220828/2cc2858b4f38436e870565a9fd7e050c.png
[img 10]: /images/20220828/ae68a934917244c6b255fad25dcb2427.png
[img 11]: /images/20220828/bc9ebe63a5fb46f0a223dc6bb8d9983e.png