bin log/ undo log / redo log MYSQL三种日志作用?

bin log/ undo log / redo log MYSQL三种日志干啥的?

三种日志介绍

MySQL日志主要包括查询日志、慢查询日志、事务日志、错误日志、二进制日志等。其中比较重要的是 bin log（二进制日志）和 redo log（重做日志）和 undo log（回滚日志）。

bin log

bin log 是MySQL数据库级别的文件，记录对MySQL数据库执行修改的所有操作，不会记录select和show语句，主要用于恢复数据库和同步数据库。

undo log

除了记录redo log外，当进行数据修改时还会记录undo log，用于数据的撤回操作，它保留了记录修改前的内容。通过undo log可以实现事务回滚，并且可以根据回溯到某个特定的版本的数据，实现MVCC。

redo log

redo log是innodb引擎级别，用来记录innodb存储引擎的事务日志，不管事务是否提交都会记录下来，用于数据恢复。当数据库发生故障，innoDB存储引擎会使用redo log恢复到发生故障前的时刻，以此来保证数据的完整性。

MVVC与undo log

MVCC(Multiversion concurrency control) 就是同一份数据保留多版本的一种方式，进而实现并发控制。在查询的时候，通过read view和版本链找到对应版本的数据。

作用：提升并发性能。对于高并发场景，MVCC比行级锁开销更小。

四种事务隔离级别

• READ UNCOMMITTED 读取未提交：一个事务还没提交时，它做的变更就能被别的事务看到

• READ COMMITTED 读取已提交：一个事务提交之后，它做的变更才会被其他事务看到。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。

• REPEATABLE READ 可重复读（InnoDB 存储引擎默认的隔离级别）：保证在同一个事务中多次读取同一数据的结果是一样的。当然了，在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

  可重复读就是：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。或者简单来说，事务在执行期间看到的数据前后是一致的。

• SERIALIZABL 可串行化：顾名思义，强制事务串行执行，对于同一行记录，“写” 会加 “写锁”，“读” 会加 “读锁”，当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。这样多个事务互不干扰，不会出现并发一致性问题

​

读取已提交和可重复读是如何实现的。这就是我们要说的 MVCC 了

对于这两个隔离级别，数据库会为每个事务创建一个视图 (ReadView)，访问的时候以视图的逻辑结果为准：

• 在 “读取已提交” 隔离级别下，这个视图是在每个 SQL 语句开始select执行的时候创建的
• 在 “可重复读” 隔离级别下，这个视图是在事务启动时就创建的(第一次select时更新这个read view)，整个事务存在期间都用这个视图（这就是为什么说在可重复读隔离级别下，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的）

那么问题了就来了，已经执行了这么多的操作，事务该如何重新回到之前视图记录的状态？数据库会通过某种手段记录这之间执行的种种操作吗？

这就是 undo log 版本链做的事 👇

undo log 版本链

在 MySQL 中，每条记录在更新的时候都会同时记录一条回滚操作（也就是 undo log），当前记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

简单理解，undo log 就是每次操作的反向操作，比如比如当前事务执行了一个插入 id = 100 的记录的操作，那么 undo log 中存储的就是删除 id = 100 的记录的操作。

也就是说，B+ 索引树上对应的记录只会有一个最新版本，但是 InnoDB 可以根据 undo log 得到数据的历史版本。同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）

那么，还有个问题，undo log 是如何和某条行记录产生联系的呢？换句话说，我怎么能通过这条行记录找到它拥有的 undo log 呢？

具体来说，InnoDB 存储引擎中每条行记录其实都拥有两个隐藏的字段：trx_id 和 roll_pointer：

• trx_id 就是最近更新这条行记录的事务 ID
• roll_pointer 就是指向之前生成的 undo log 的指针

掏出我们的 user 表，来举个例子，假设 id = 100 的事务 A 插入一条行记录（id = 1, username = “Jack”, age = 18），那么，这行记录的两个隐藏字段 trx_id = 100 和 roll_pointer 指向一个空的 undo log，因为在这之前并没有事务操作 id = 1 的这行记录。如图所示：

然后，id = 200 的事务 B 修改了这条行记录，把 age 从 18 修改成了 20，于是，这条行记录的 trx_id 就变成了 200，rooll_pointer 就指向事务 A 生成的 undo log ：

接着，id = 300 的事务 C 再次修改了这条行记录，把 age 从 20 修改成了 30，如下图：

可以看到，每次修改行记录都会更新 trx_id 和 roll_pointer 这两个隐藏字段，之前的多个数据快照对应的 undo log 会通过 roll_pointer 指针串联起来，从而形成一个版本链。

那么问题又来了，一个记录会被一堆事务进行修改，一个记录上会存在许许多多的 undo log，那么对于其中某一个事务来说，它能看见哪些 undo log？或者说，对于其中某一个事务来说，它能够根据哪些 undo log 执行回滚操作？

让我们来详细解释一下这个视图（ReadView）机制 👇

ReadView 机制

ReadView 机制就是用来判断当前事务能够看见哪些版本的，一个 ReadView 主要包含如下几个部分：

• m_ids：生成 ReadView 时有哪些事务在执行但是还没提交的（称为 “活跃事务”），这些活跃事务的 id 就存在这个字段里
• min_trx_id：m_ids 里最小的值
• max_trx_id：生成 ReadView 时 InnoDB 将分配给下一个事务的 ID 的值（事务 ID 是递增分配的，越后面申请的事务 ID 越大）
• creator_trx_id：当前创建 ReadView 事务的 ID

接下来，再掏出 user 表，通过一个例子来理解下 ReaView 机制是如何做到判断当前事务能够看见哪些版本的：

假设表中已经被之前的事务 A（id = 100）插入了一条行记录（id = 1, username = “Jack”, age = 18），如图所示：

接下来，有两个事务 B（id = 200） 和 C（id = 300）过来并发执行，事务 B 想要更新（update）这行 id = 1 的记录，而事务 C（select）想要查询这行数据，这两个事务都执行了相应的操作但是还没有进行提交：

如果现在事务 B 开启了一个 ReadView，在这个 ReadView 里面：

• m_ids 就包含了当前的活跃事务的 id，即事务 B 和事务 C 这两个 id，200 和 300
• min_trx_id 就是 200
• max_trx_id 是下一个能够分配的事务的 id，那就是 301
• creator_trx_id 是当前创建 ReadView 事务 B 的 id 200
  

现在事务 B 进行第一次查询（select 操作不会生成 undo log 的哈），会把这行记录的隐藏字段 trx_id 和 ReadView 的 min_trx_id 进行下判断，此时，发现 trx_id 是 100，小于 ReadView 里的 min_trx_id（200），这说明在事务 B 开始之前，修改这行记录的事务 A 已经提交了，所以开始于事务 A 提交之后的事务 B、是可以查到事务 A 对这行记录的更新的。

row.trx_id < ReadView.min_trx_id

接着事务 C 过来修改这行记录，把 age = 18 改成了 age = 20，所以这行记录的 trx_id 就变成了 300，同时 roll_pointer 指向了事务 C 修改之前生成的 undo log：

那这个时候事务 B 再次进行查询操作，会发现这行记录的 trx_id（300）大于 ReadView 的 min_trx_id（200），并且小于 max_trx_id（301）。

row.trx_id > ReadView.min_trx_id && row.trx_id < max_trx_id

这说明一个问题，就是更新这行记录的事务很有可能也存在于 ReadView 的 m_ids（活跃事务）中。所以事务 B 会去判断下 ReadView 的 m_ids 里面是否存在 trx_id = 300 的事务，显然是存在的，这就表示这个 id = 300 的事务是跟自己（事务 B）在同一时间段并发执行的事务，也就说明这行 age = 20 的记录事务 B 是不能查询到的。

既然无法查询，那该咋整？事务 B 这次的查询操作能够查到啥呢？

没错，undo log 版本链

这时事务 B 就会顺着这行记录的 roll_pointer 指针往下找，就会找到最近的一条 trx_id = 100 的 undo log，而自己的 id 是 200，即说明这个 trx_id = 100 的 undo log 版本必然是在事务 B 开启之前就已经提交的了。所以事务 B 的这次查询操作读到的就是这个版本的数据即 age = 18。

通过上述的例子，我们得出的结论是，通过 undo log 版本链和 ReadView 机制，可以保证一个事务不会读到并发执行的另一个事务的更新。

那自己修改的值，自己能不能读到呢？

这当然是废话，肯定可以读到呀。上面的例子我们只涉及到了 ReadView 中的前三个字段，而 creator_trx_id 就与自己读自己的修改有关，所以这里还是图解出来让大家更进一步理解下 ReadView 机制：

假设事务 C 的修改已经提交了，然后事务 B 更新了这行记录，把 age = 20 改成了 age = 66，如下图所示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7kndnhyp-1659514653761)(img/read_view_7.png)]

然后，事务 B 再来查询这条记录，发现 trx_id = 200 与 ReadView 里的 creator_trx_id = 200 一样，这就说明这是我自己刚刚修改的啊，当然可以被查询到。

row.trx_id = ReadView.creator_trx_id

那如果在事务 B 的执行期间，突然开了一个 id = 500 的事务 D，然后更新了这行记录的 age = 88 并且还提交了，然后事务 B 再去读这行记录，能读到吗？

答案是不能的。

因为这个时候事务 B 再去查询这行记录，就会发现 trx_id = 500 大于 ReadView 中的 max_trx_id = 301，这说明事务 B 执行期间，有另外一个事务更新了数据，所以不能查询到另外一个事务的更新。

row.trx_id > ReadView.max_trx_id

那通过上述的例子，我们得出的结论是，通过 undo log 版本链和 ReadView 机制，可以保证一个事务只可以读到该事务自己修改的数据或该事务开始之前的数据。

• 🥸 总结：讲一下数据库的四种隔离级别，以及具体的实现

  😎 答：数据库的四种隔离级别主要是用来解决四种并发一致性问题的，隔离级别越高，能够处理的并发一致性问题越多，相应的数据库付出的性能代价也就越高。

  最低的隔离级别是读取未提交，一个事务还没提交时，它做的变更就能被别的事务看到：可以解决丢失更新问题（所谓丢失更新问题，就是指一个事务的更新操作会被另一个事务的更新操作所覆盖）；

  然后是读取已提交，一个事务提交之后，它做的变更才会被其他事务看到：可以解决丢失更新和脏读问题（所谓脏读，就是一个事务读到了另外一个事务未提交的数据）；

  然后是 InnoDB 默认的隔离级别可重复读，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的：可以解决丢失更新、脏读和不可重复读问题（所谓不可重复读，就是指第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据是不一样的）。另外，InnoDB 的这个默认隔离级别，会通过 Next-Lock key 来解决幻读问题，所以其实是可以达到 SQL 标准的可串行化隔离级别的；

  最后是可串行化，强制事务串行执行，对于同一行记录，“写” 会加 “写锁”，“读” 会加 “读锁”，当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。这样可以避免并发一致性问题，解决丢失更新、脏读、不可重复读和幻读问题（所谓幻读，和不可重复读差不多，不过幻读侧重于记录数量的增减，不可重复读侧重于记录的修改）

  对于读取已提交和可重复读这两个隔离级别来说，其底层实现就是多版本并发控制 MVCC。

  具体来说，对于这两个隔离级别，数据库会为每个事务创建一个视图 (ReadView)，访问的时候以视图的逻辑结果为准。通过 undo log 版本链使得事务可以回滚到视图记录的状态。

  而这两个隔离级别的区别就在于，它们生成 ReadView 的时机是不同的：

  • 在 “读取已提交” 隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的
  • 在 “可重复读” 隔离级别下，这个视图是在事务启动时就创建的，整个事务存在期间都用这个视图

bin log 与 redo log

前面我们说过，MySQL Server 层拥有的 bin log 只能用于归档，不足以实现崩溃恢复（crash-safe），需要借助 InnoDB 引擎的 redo log 才能拥有崩溃恢复的能力。所谓崩溃恢复就是：即使在数据库宕机的情况下，也不会出现操作一半的情况（更直白点说：日志已经写好，但是数据并未落盘，通过日志来把数据恢复到磁盘上）

至于为什么说 redo log 具有崩溃恢复的能力，而 bin log 没有，我们先来简单看一下这两种日志有哪些不同点：

1）适用对象不同：

• bin log 是 MySQL 的 Server 层实现的，所有引擎都可以使用
• 而 redo log 是 InnoDB 引擎特有的

2）写入内容不同：

• bin log 是逻辑日志，记录的是这个语句的原始逻辑，比如 “给 id = 1 这一行的 age 字段加 1”
• redo log 是物理日志，记录的是 “在某个数据页上做了什么修改”

3）写入方式不同：

• bin log 是可以追加写入的。“追加写” 是指 bin log 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志
• redo log 是循环写的，空间固定会被用完

可以看到，redo log 和 bin log 的一个很大的区别就是，一个是循环写，一个是追加写。也就是说 redo log 只会记录未刷入磁盘的日志，已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。

而 bin log 是追加日志，保存的是全量的日志。这就会导致一个问题，那就是没有标志能让 InnoDB 从 bin log 中判断哪些数据已经刷入磁盘了，哪些数据还没有。

举个例子，bin log 记录了两条日志：

记录 1：给 id = 1 这一行的 age 字段加 1
记录 2：给 id = 1 这一行的 age 字段加 1

假设在记录 1 刷盘后，记录 2 未刷盘时，数据库崩溃。重启后，只通过 bin log 数据库是无法判断这两条记录哪条已经写入磁盘，哪条没有写入磁盘，不管是两条都恢复至内存，还是都不恢复，对 id = 1 这行数据来说，都是不对的。

但 redo log 不一样，只要刷入磁盘的数据，都会从 redo log 中被抹掉，数据库重启后，直接把 redo log 中的数据都恢复至内存就可以了。

这就是为什么说 redo log 具有崩溃恢复的能力，而 bin log 不具备。

直接用 binlog 做全量恢复也能做崩溃后的数据恢复，类比主从同步，但是速度肯定很慢，一般都是用 redolog 做崩溃恢复，binlog 做数据同步

redo log 两阶段提交

介绍过一条 SQL 查询语句的执行过程：

1. MySQL 客户端与服务器间建立连接，客户端发送一条查询给服务器；

2. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果；否则进入下一阶段；

3. 服务器端进行 SQL 解析、预处理，生成合法的解析树；

4. 再由优化器生成对应的执行计划；

5. 执行器根据优化器生成的执行计划，调用相应的存储引擎的 API 来执行，并将执行结果返回给客户端
   

对于更新语句来说，这套流程同样也是要走一遍的，不同的是，更新流程还涉及两个重要的日志模块 bin log 和 redo log。

以下面这条简单的 SQL 语句为例，我们来解释下执行器和 InnoDB 存储引擎在更新时做了哪些事情：

update table set age = age + 1 where id = 1;

1. 执行器：找存储引擎取到 id = 1 这一行记录

2. 存储引擎：根据主键索引树找到这一行，如果 id = 1 这一行所在的数据页本来就在内存池（Buffer Pool）中，就直接返回给执行器；否则，需要先从磁盘读入内存池，然后再返回

3. 执行器：拿到存储引擎返回的行记录，把 age 字段加上 1，得到一行新的记录，然后再调用存储引擎的接口写入这行新记录

4. 存储引擎：将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，为 redo log 中的事务打上 prepare 标识。然后告知执行器执行完成了，随时可以提交事务

   注意不要把这里的提交事务和我们 sql 语句中的提交事务 commit 命令搞混了哈，我们这里说的提交事务，指的是事务提交过程中的一个小步骤，也是最后一步。当这个步骤执行完成后，commit 命令就执行成功了。

5. 执行器：生成这个操作的 bin log，并把 bin log 写入磁盘

6. 执行器：调用存储引擎的提交事务接口

7. 存储引擎：把刚刚写入的 redo log 状态改成提交（commit）状态，更新完成

如下图所示：

图片中的写入 redo log 和写入 bin log，并不等同于写入磁盘文件了哦，redo log 和 bin log 的写入其实分为两个阶段，可能仅仅写入内存了，也可能继续持久化到磁盘了，这个下篇文章有详细解释~

可以看到，所谓两阶段提交，其实就是把 redo log 的写入拆分成了两个步骤：prepare 和 commit。

所以，为什么要这样设计呢？这样设计怎么就能够实现崩溃恢复呢？

根据两阶段提交，崩溃恢复时的判断规则是这样的：

1. 如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交
2. 如果 redo log 里面的事务处于 prepare 状态，则判断对应的事务 binlog 是否存在并完整

• a. 如果 binlog 存在并完整，则提交事务；
• b. 否则，回滚事务。

当然，这样说小伙伴们肯定没法理解，下面来看几个实际的例子：

如下图所示，假设数据库在写入 redo log(prepare) 阶段之后、写入 binlog 之前，发生了崩溃，此时 redo log 里面的事务处于 prepare 状态，binlog 还没写（对应 2b），所以崩溃的时候，这个事务会回滚。

Why？

因为 binlog 还没有写入，之后从库进行同步的时候，无法执行这个操作，那如果我们主库上继续恢复这个操作的话显然就会导致主备不一致，所以在主库上需要回滚这个事务

并且，由于 binlog 还没写，所以也就不会传到备库，从而避免主备不一致的情况

而如果数据库在写入 binlog 之后，redo log 状态修改为 commit 前发生崩溃，此时 redo log 里面的事务仍然是 prepare 状态，binlog 存在并完整（对应 2a），所以如果在这个时刻数据库崩溃了，该事务在崩溃恢复的时候会被正常提交。

Why？

因为 binlog 已经写入成功了，这样之后就会被从库同步过去，但是如果 redolog 没有这条记录的话，所以为了主备一致，在主库上崩溃恢复的时候需要提交这个事务。

所以，其实可以看出来，处于 prepare 阶段的 redo log 加上完整的 bin log，就能保证数据库的崩溃恢复了。

可能有同学就会问了，MySQL 咋知道 bin log 是不是完整的？

简单来说，一个事务的 binlog 是有完整格式的：

• statement 格式的 bin log，最后会有 COMMIT
• row 格式的 bin log，最后会有 XID event

而对于 bin log 可能会在中间出错的情况，MySQL 5.6.2 版本以后引入了 binlog-checksum 参数，用来验证 bin log 内容的正确性。

思考一个问题，两阶段提交是必要的吗？可不可以先 redo log 写完，再写 bin log 或者反过来？

1）对于先写完 redo log 后写 bin log 的情况：

假设在 redo log 写完，bin log 还没有写完的时候，MySQL 崩溃。崩溃恢复的时候主库会根据 redolog 执行更新，但是这时候 binlog 里面并没有记录这个语句。因此，从库同步的时候，就会丢失这个更新，和主库不一致。

2）对于先写完 binlog 后写 redo log 的情况：

如果在 bin log 写完，redo log 还没写的时候，MySQL 崩溃。因为 binlog 已经写入成功了，这样之后就会被从库同步过去，但是实际上 redo log 还没写，崩溃恢复的时候主库并不会执行这个事务，所以从库相比主库就会多执行一个事务，导致主备不一致

⭐ 所以，总结来说，不论 MySQL 什么时刻发生崩溃，最终是 commit 还是 rollback 完全取决于 MySQL 能不能判断出 binlog 和 redolog 在逻辑上是否达成了一致。只要逻辑上达成了一致就可以 commit，否则只能rollback。

🥸 总结：

• 问法 1：如何解决 bin log 与 redo log 的一致性问题？
• 问法 2：一条 SQL 更新语句是如何执行的？
• 问法 3：讲一下 redo log / redo log 两阶段提交原理

😎 答：

所谓两阶段提交，其实就是把 redo log 的写入拆分成了两个步骤：prepare 和 commit。

首先，存储引擎将执行更新好的新数据存到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务

然后执行器生成这个操作的 bin log，并把 bin log 写入磁盘

最后执行器调用存储引擎的提交事务接口，存储引擎把刚刚写入的 redo log 状态改成提交（commit）状态，更新完成

如果数据库在写入 redo log(prepare) 阶段之后、写入 binlog 之前，发生了崩溃：

此时 redo log 里面的事务处于 prepare 状态，binlog 还没写，之后从库进行同步的时候，无法执行这个事务，那如果我们主库上继续恢复执行这个事务的话显然就会导致主备不一致，所以崩溃恢复的时候在主库上需要回滚这个事务

而如果数据库在写入 binlog 之后，redolog 状态修改为 commit 前发生崩溃，此时 redolog 里面的事务仍然是 prepare 状态，binlog 存在并完整，这样之后就会被从库同步过去，所以为了主备一致，在主库上崩溃恢复的时候需要提交这个事务。