「MySQL高级篇」MySQL日志、事务原理 -- undolog、redolog、binlog、两阶段提交

大家好，我是melo，一名大三后台练习生，最近断断续续冷面翻炒redis、MySQL、sso，知识大杂烩属于是hhh

• MySQL高级篇专栏

• SSO单点登录专栏

• Redis入门与实战

🍳引言

日志日志，在我们平时开发中主要的用途在于监控、备份，但在MySQL中，日志的功能远远不止这些，分别有用于记录的慢查询日志，回滚版本的undolog，宕机恢复的redolog、全量备份的binlog等等，而这些日志，也刚好是我们事务的原理

🎏本篇速览脑图

🎯undolog -- 原子性

回滚日志，记录数据被修改前的信息，属于逻辑日志

• 什么是逻辑日志？

比如我们执行一条delete语句，undolog里边记录的是相反的操作insert记录【相当于存放的是操作逻辑语句，而不是数据】

🤷‍♂️逻辑日志好处

1. 比如全表更新，如果是物理日志，我们需要把全表的数据都存下来
   1. 若是逻辑日志，只需要存放一条语句就可以恢复了

undolog用处

回滚

一个事务在执行过程中，在还没有提交事务之前，如果MySQL 发生了崩溃，要怎么回滚到事务之前的数据呢？

• 在事务没提交之前，MySQL 会先记录更新前的数据到 undo log 日志文件里面，当事务回滚时，可以利用 undo log 来进行回滚。

版本链

类似一个链表，通过回滚指针，串联起来

一条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务id：

• 通过 trx_id 可以知道该记录是被哪个事务修改的；
• 通过 roll_pointer 指针可以将这些 undo log 串成一个链表，这个链表就被称为版本链；

MVCC

通过 ReadView + undo log 实现 MVCC

具体看我的另一篇博客MVCC

🎯redolog -- 持久性

重做日志，物理记录

redo log 是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎。

前置知识-- Buffer Pool

Buffer Pool是很经典的缓存池，其中又以Page为单位

1. 空闲页
2. 干净页
3. 脏页【类似操作系统中，修改位为1】

正常同步

首先我们要知道，每次事务提交后，首先是跟 Buffer pool 打交道

1. 若缓存中没有我们要操作的数据【类似缺页中断，MySQL 中数据是以页为单位】，则会启动后台线程，去磁盘中拉取过来
2. 之后就都是直接操作缓存了

• 现在缓存的内容更新好了，但磁盘的内容还是旧的，何时更新到磁盘呢？

我们可能会有如下方案：

1. 每隔一段时间，就同步到磁盘

弊端

每次更新都要写入磁盘，磁盘IO很高

如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。 不如先把更新的操作，记录在redolog日志里边，之后在适当的时候，再一起刷盘！【而不是每次更新就一条一条的刷】

这就是MySQL的 WAL 技术，（Write-Ahead Logging) 先写进日志，再刷入磁盘

buffer挂掉

一般情况下都没什么问题，但可能在执行某次更新操作的时候，buffer挂掉了呢？此时同步过程就GG了，导致缓存不一致

• 因此，为了双保险，我们还会把数据相关的变化【物理修改】存到redolog里边，用来实现事务的持久性【宕机后还能查询redolog，来进行恢复】

redolog保险出场

当事务提交时，会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。

缓存redolog -- Log Buffer

刷盘时机

MySQL 正常关闭时

当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘

每次事务提交时【具体不同策略不一样】

一般情况下，事务一提交就会进行刷盘操作。

InnoDB 存储引擎为 redo log 的刷盘策略提供了 innodb_flush_log_at_trx_commit 参数，它支持三种策略：

• 0 ：设置为 0 的时候，表示每次事务提交时不进行刷盘操作
• 1 ：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作（默认值）
• 2 ：设置为 2 的时候，表示每次事务提交时都只把 redo log buffer 内容写入 page cache

后台线程

InnoDB 存储引擎有一个后台线程，每隔1 秒，就会把 redo log buffer 中的内容写到文件系统缓存（page cache），然后调用 fsync 刷盘。

不同参数对应情况

innodb_flush_log_at_trx_commit 设置为不同值时，分别是什么时候将 redo log 写入磁盘？

1. 参数0：事务提交时不刷盘，而是靠后台线程，把缓存在 redo log buffer 中的 redo log ，通过调用 write() 写到操作系统的 Page Cache，然后调用 fsync() 持久化到磁盘。所以参数为 0 的策略，MySQL 进程的崩溃会导致上一秒钟所有事务数据的丢失;
2. 参数1：只要事务提交成功，redo log记录就一定在硬盘里，不会有任何数据丢失。

如果事务执行期间MySQL挂了或宕机，这部分日志丢了，但是事务并没有提交，所以日志丢了也不会有损失！！

1. 参数2：只要事务提交成功，redo log buffer中的内容都会写入文件系统缓存（page cache）。调用 fsync，将缓存在**操作系统中 Page Cache **里的 redo log 持久化到磁盘。

如果仅仅只是MySQL挂了不会有任何数据丢失，只有操作系统崩溃的情况下，上一秒钟所有事务数据才可能丢失。

如何选择参数

• 数据安全性：参数 1 > 参数 2 > 参数 0

• 写入性能：参数 0 > 参数 2> 参数 1

• 在一些对数据安全性要求比较高的场景中，显然 innodb_flush_log_at_trx_commit 参数需要设置为 1。

• 在一些可以容忍数据库崩溃时丢失 1s 数据的场景中，我们可以将该值设置为 0，这样可以明显地减少日志同步到磁盘的 I/O 操作。

• 安全性和性能折中的方案就是参数 2，虽然参数 2 没有参数 0 的性能高，但是数据安全性方面比参数 0 强，因为参数 2 只要操作系统不宕机，即使数据库崩溃了，也不会丢失数据，同时性能方便比参数 1 高。

好处

1. redolog是文件追加的形式，而缓存同步到磁盘是随机IO

可以说这是 WAL 技术的另外一个优点：MySQL 的写操作从磁盘的「随机写」变成了「顺序写」

总结

redolog的用处：

1. 实现事务的持久性
2. 将写操作从磁盘的「随机写」变成了「顺序写」

🎈🎈redolog日志文件组 -- 应对写满的情况

硬盘上存储的 redo log 日志文件不止一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。、、、中止这种做做做做做做做做做做做做做做做做做做做做做做做做做做做做错错错

比如可以配置为一组4个文件，每个文件的大小是 1GB，整个 redo log 日志文件组可以记录4G的内容。 它采用的是环形数组形式，从头开始写，写到末尾又回到头循环写，如下图所示。

除了写入，我们还需要擦除【redolog刷盘到磁盘后，就可以进行擦除了】，因此我们用两个指针来表示：

1. write pos：当前记录写到的位置
2. checkpoint：当前要擦除的位置

跟循环队列的设计有些神似，维护两个指针，head跟tail一般

这两个指针把整个环形划成了几部分

1. write pos - checkpoint：待写入的部分
2. checkpoint - write pos：还未刷入磁盘的记录

若write pos追上了checkpoint，说明没有空间写入了【跟我们队列满了是一样的情况】，这时候不能再写入新的 redo log 记录，MySQL 得停下来，清空一些记录，把 checkpoint 推进一下。

🎯binlog

redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎。 而 binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。 不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志。

与redolog区别

这两个日志有四个区别

1、适用对象不同：

• binlog 是 MySQL 的 **Server 层 **实现的日志，所有存储引擎都可以使用；
• redo log 是 Innodb 存储引擎实现的日志；

2、文件格式不同：

• binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED，区别如下：

  • STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中（相当于记录了逻辑操作，所以针对这种格式， binlog 可以称为逻辑日志），主从复制中 slave 端再根据 SQL 语句重现。

但 STATEMENT 有动态函数的问题

比如:

update_time=now()//这里会获取当前系统时间
复制代码

直接执行会导致与原库的数据不一致，因此我们引入了row

• ROW：记录行数据最终被修改成什么样了（这种格式的日志，就不能称为逻辑日志了），不会出现 STATEMENT 下动态函数的问题。

但也有缺点

每行数据的变化结果都会被记录，比如：

update user set name ='melo';
复制代码

更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句而已；

• MIXED：这是目前MySQL默认的日志格式，即混合了STATEMENT 和 ROW两种格式。默认情况下采用STATEMENT，但是在一些特殊情况下采用ROW来进行记录。MIXED 格式能尽量利用两种模式的优点，而避开他们的缺点。

MySQL会判断这条SQL语句是否可能引起数据不一致，如果是，就用row格式，否则就用statement格式。

• redo log 是物理日志，记录的是在某个数据页做了什么修改。

3、写入方式不同：

• binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。
• redo log 是循环写，日志空间大小是固定的，全部写满就从头开始，保存未被刷入磁盘的脏页日志。

4、用途不同：

• binlog 用于备份恢复、主从复制；
• redo log 用于掉电等故障恢复。

如果不小心整个数据库的数据被删除了，能使用 redo log 文件恢复数据吗？ 不可以使用 redo log 文件恢复，只能使用 binlog 文件恢复。 因为 redo log 文件是循环写，是会边写边擦除日志的，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件里擦除。

binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 binlog 上的数据，都可以恢复，所以如果不小心整个数据库的数据被删除了，得用 binlog 文件恢复数据。

写入时机

事务执行过程中，先把日志写到 binlog cache（Server 层的 cache），事务提交的时候，再把 binlog cache 写到 binlog 文件中。

因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。

参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘（Swap）。

什么时候 binlog cache 会写到 binlog 文件？

• 在事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 文件中，并清空 binlog cache。如下图：

  

虽然每个线程有自己 binlog cache，但是最终都写到同一个 binlog 文件：

• 图中的 write，指的就是指把日志写入到 binlog 文件，但是并没有把数据持久化到磁盘，因为数据还缓存在文件系统的 page cache 里，write 的写入速度还是比较快的，因为不涉及磁盘 I/O。
• 图中的 fsync，才是将数据持久化到磁盘的操作，这里就会涉及磁盘 I/O，所以频繁的 fsync 会导致磁盘的 I/O 升高。

MySQL提供一个 sync_binlog 参数来控制数据库的 binlog 刷到磁盘上的频率：

• sync_binlog = 0 的时候，表示每次提交事务都只 write，不 fsync，后续交由操作系统决定何时将数据持久化到磁盘；
• sync_binlog = 1 的时候，表示每次提交事务都会 write，然后马上执行 fsync；
• sync_binlog =N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

在MySQL中系统默认的设置是 sync_binlog = 0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦主机【指的是操作系统】发生异常重启，还没持久化到磁盘的数据就会丢失。

而当 sync_binlog 设置为 1 的时候，是最安全但是性能损耗最大的设置。因为当设置为 1 的时候，即使主机发生异常重启，最多丢失一个事务的 binlog，而已经持久化到磁盘的数据就不会有影响，不过就是对写入性能影响太大。

如果能容许少量事务的 binlog 日志丢失的风险，为了提高写入的性能，一般会 sync_binlog 设置为 100~1000 中的某个数值。

🎈两阶段提交

🎐为何需要两阶段提交

主要是因为：redolog影响的是主库，而binlog涉及主从复制，影响的是从库？？

也有说用binlog来作为备份恢复，恢复后的结果可能会跟主库不一样

执行一条更新语句时，此时宕机了，有两种情况：

1. redolog刷盘成功，但binlog没有
   1. 主库由于有redolog的存在，能够恢复，而binlog中并没有相关的更新语句，导致从库中丢失了本次更新
2. binlog刷盘成功，但redolog没有
   1. 从库由于有binlog的存在，记录了更新，binlog 会被复制到从库，从库执行了这条更新语句，而主库的redolog还没刷盘成功，导致崩溃后没法恢复，主库丢失了本次更新

XA事务、两阶段提交的具体流程

第一阶段

这里是事务管理器，相当于一个总的管理者，其负责管理参与事务的多个部分【比如这里的redolog，binlog】，放到分布式系统里边，可能是两个不同的系统

应用程序调用了事务管理器的提交方法，此后第一阶段分为两个步骤：

事务管理器通知参与该事务的各个资源管理器，通知他们开始准备事务。

资源管理器接收到消息后开始准备阶段，写好事务日志并执行事务，但不提交，然后将是否就绪的消息返回给事务管理器（此时已经将事务的大部分事情做完，以后的内容耗时极小）。

第二阶段

第二阶段也分为两个步骤：

事务管理器在接受各个消息后，开始分析，如果有任意其一失败，则发送回滚命令，否则发送提交命令。 各个资源管理器接收到命令后，执行（耗时很少），并将提交消息返回给事务管理器。

🍙内部XA事务

内部XA事务主要指单节点实例内部，一个事务跨多个存储引擎进行读写，那么就会产生内部XA事务

存储引擎与插件之间，存储引擎与存储引擎之间

提交过程

将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog，具体如下：

• prepare 阶段：将 XID（内部 XA 事务的 ID） 写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘；
  • commit 阶段：把 XID 写入到 binlog，然后将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 调用引擎的提交事务接口，将 redo log 状态设置为 commit（将事务设置为 commit 状态后，刷入到磁盘 redo log 文件，所以 commit 状态也是会刷盘的）；

🎐出现异常会怎么样？

总结

只要binlog也好了，就不需要回滚，binlog没好，就需要回滚

其实就是两个变成一个事务，redolog和binlog

写入binlog出现异常

使用两阶段提交后，写入binlog时发生异常也不会有影响，因为MySQL根据redo log日志恢复数据时，发现redo log还处于prepare阶段，并且没有对应binlog日志【binlog没有当前内部XA事务的XID】，就会回滚该事务。

设置commit异常

并不会回滚事务，它会执行上图框住的逻辑，虽然redo log是处于prepare阶段，但是能通过事务id找到对应的binlog日志【binlog有当前内部XA事务的XID】，所以MySQL认为是完整的，就会提交事务恢复数据。

🎐疑问--好问题

在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样。

• 事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些缓存在 redo log buffer 里的 redo log 也会被「后台线程」每隔一秒一起持久化到磁盘。

也就是说，事务没提交的时候，redo log 也是可能被持久化到磁盘的。

那事务执行过程中不断写入，如果执行事务期间mysql宕机呢？ 此时重启后，mysql要利用redolog恢复，有一部分操作redolog已经写入磁盘了，后续恢复会不会出现数据不一致的情况？【因为事务实际上还没有提交呢，是需要回滚的相当于出现了异常】

此时由于有两阶段提交，binlog必须在事务提交后才写入，所以此时即使redolog写入了，但binlog还未写入，重启时读取到该redolog会发现没有对应的binlog，会放弃掉

🍿🍿🍿手撕面答环节 -- 这是一条分割线

🍔MySQL 日志文件有哪些？分别介绍下作用？

分别有 undolog、redolog、 binlog。

1. undolog用于实现原子性，记录版本链用于回滚，并且属于逻辑日志，记录操作便于恢复
2. redolog用于宕机后的数据恢复，记录实际的数据，属于物理日志
3. binlog主要用于主从复制，全量备份，相比redolog可存储的内容更多，既可以物理也可以逻辑存储，一般使用Mix

其中server层还有其他的日志，比如：

• 错误日志（error log）：错误日志文件对 MySQL 的启动、运行、关闭过程进行了记录，能帮助定位 MySQL 问题。
• 慢查询日志（slow query log）：慢查询日志是用来记录执行时间超过 long_query_time 这个变量定义的时长的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。
• 一般查询日志（general log）：一般查询日志记录了所有对 MySQL 数据库请求的信息，无论请求是否正确执行。

🍔redolog如何刷入磁盘的

有三种策略，参数不同的时候，对应的刷入时机不同 首先都会先写到redolog的 buffer 区

参数为0：不写入，等待后台线程定时【每秒】将 buffer 区中的内容写入磁盘

若崩溃，会丢失上一秒中，buffer区里边所有的内容

参数为1：每次提交就写入，先写到 buffer 区，然后立马调用 fsync 写入磁盘

不会丢失，可以看成事务提交和写入磁盘是一个原子性的操作？

参数为2：每次提交，先写到 buffer 区，然后写入到 pageCache 里边，由操作系统来决定何时写入磁盘

只有操作系统崩溃了，才会丢失上一秒中

要选择哪种策略呢？

考虑性能：0>2>1 考虑安全性：1>2>0 综合：2

🍔为何要引入redolog来记录呢？

将磁盘的随机IO转换为追加写入

原本如果我们每次都去更改磁盘里边的内容，则需要先随机IO找到，然后更改 有了写入的话，我们每次都先追加写入到redolog，然后再一次性去磁盘里边找就好了，而不是每次都去磁盘找

🍔redolog 和 binlog 有什么区别

存储的容量不同

redolog以日志文件组作为存储的数据结构，容量是有限的，写满的时候会删除 undolog以追加文件的形式存储，理论上存储容量是无限的

🎈刷盘时机不同

redolog有三种不同的刷盘策略

标答：binlog只在事务提交的时候才写入，而redolog由于有后台进程，事务期间也会写入

所属的范畴不同

redolog是 innoDB 特有的，而binlog是 server 层的

用途不同

redolog用于宕机后的数据恢复 binlog主要用于主从复制，全量备份

🍔🎈为什么需要两阶段提交

1. redolog提交成功，则binlog提交失败了

此时主库没有影响，但从库由于binlog丢失了，从库得不到复制

1. binlog提交成功，但redolog提交失败了

从库由于有binlog的存在，记录了更新，binlog 会被复制到从库，从库执行了这条更新语句，而主库的redolog还没刷盘成功，导致崩溃后没法恢复，主库丢失了本次更新

🍔两阶段提交过程中出现异常了怎么办？

写入binlog的时候出异常

此时redolog虽然准备好了，但是binlog还没写入，也就还没关联好binlog，宕机恢复后扫描到这个redolog的时候发现其没有关联的binlog，认为是不完整的，会丢弃掉

commit阶段出异常

由于此时redolog其实已经准备好了，并且binlog也已经写入了，关联好了XA事务的ID，也可以认定为这个事务是完整的了，宕机恢复后，能找到该redolog对应的binlog

🍔redolog事务执行期间也写入，会不会有数据不一致的问题？

事务执行期间也会写入redolog，而binlog是事务提交后才写入，那这样宕机恢复后会不会出现不一致的情况呢？

• 其实是不会的，因为事务执行过程中宕机的话，binlog还没写呢，也就对应了我们上文的：写binlog出现异常的情况，宕机后恢复，扫描的时候会丢弃