MySQL学习[5] ——MySQL日志

五、MySQL日志

5.1 MySQL中有哪些日志？

MySQL中主要有三种日志：undo log（回滚日志）、redo log（重做日志）、binlog（归档日志），简单介绍：

• undo log（回滚日志）：InnoDB存储引擎层生成的日志，保证事务中的原子性，用于事务回滚和MVCC；
• redo log（重做日志）：InnoDB存储引擎层生成的日志，保证事务中的持久性，用于掉电等故障恢复；
• binlog（归档日志）：Server层生成的日志，用于数据备份和主从复制。

5.2 为什么需要undo log？

5.2.1 undo log是什么？

当事务对数据库进行更新（插入、修改、删除）时，MySQL会记录**更新前的数据到undo log文件中**，当事务回滚时，可以利用undo log来实现。如图：

每当 InnoDB 引擎对一条记录进行操作（修改、删除、新增）时，要把回滚时需要的信息都记录到 undo log 里，比如：

• 在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了；
• 在删除一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了；
• 在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了。

不同的操作，需要记录的内容也是不同的，所以不同类型的操作（修改、删除、新增）产生的 undo log 的格式也是不同的。

5.2.2 undo log的两大作用？

除了事务回滚作用。前面提到，MySQL中一个记录的真实数据中有几个隐藏字段，包括 roll_pointer 指针和一个 trx_id 事务id。

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

通过trx_id 和版本链，利用**ReadView + undo log 可以实现MVCC（多版本并发控制）**。

「事务的 Read View 里的字段」和「记录中的两个隐藏列（trx_id 和 roll_pointer）」的比对，如果不满足可见性，就会**顺着 undo log 版本链里找到满足其可见性的记录**，从而控制并发事务访问同一个记录时的行为，这就叫 MVCC（多版本并发控制）。

因此，undo log 两大作用：

• 实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执 行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。
• 实现 MVCC（多版本并发控制）关键因素之一。MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

5.3 为什么需要redo log？

5.3.1 认识Buffer Pool

MySQL的数据是保存在磁盘的，但是频繁的进行磁盘I/O会影响性能。因此，InnoDB存储引擎设计了一个**缓冲池（Buffer Pool）**，提高数据库的读写性能。

有了 Buffer Poo 后：

• 当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
• 当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为**脏页（该页的内存数据和磁盘上的数据已经不一致），为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘**。

这样可以显著提高数据库的性能。

5.3.2 Buffer Pool缓存什么？

由于InnoDB存储引擎中，使用**「页」作为磁盘与内存交互的基本单位**。因此Buffer Pool中也是由页组成的，在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。初始时，这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中。

Buffer Pool 除了缓存**「索引页」和「数据页」**，还包括了 **Undo 页，插入缓存页、自适应哈希索引、锁信息**等等。

数据库更新时，undo log 就是写入 Buffer Pool 中的 Undo 页面中的。

Buffer Pool 和磁盘交换的单位是页，当我们查询一条记录时，InnoDB 是会把整个页的数据加载到 Buffer Pool 中，将页加载到 Buffer Pool 后，再通过页里的「页目录」去定位到某条具体的记录。

5.3.3 redo log是什么？

Buffer Pool 是基于内存的，而内存总是不可靠，万一断电重启，还没来得及落盘的脏页数据就会丢失。因此，当有一条记录需要更新时，InnoDB首先更新内存，并将这个Buffer Pool页面标记为脏页，然后**把本次对这个页的修改以redo log的形式记录下来，此时已经算是完成更新了**（但实际上还没有存储到磁盘）。

后续，InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是 WAL （Write-Ahead Logging）技术： MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

redo log 是物理日志，记录了某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志。在事务提交时，只要**先将 redo log 持久化到磁盘即可**，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。

当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态。

5.3.4 redo log和undo log的区别

这两种日志是属于 InnoDB 存储引擎的日志，它们的区别在于：

• redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；
• undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；

事实上，当InnoDB层更新记录时，会生成一条undo log，这个undo log会写入到Buffer Pool中的Undo页，此时这个写入过程也会被记录对应的redo log。所以，可以说：undo log 和数据页的刷盘（持久化到磁盘）策略是一样的，都需要通过 redo log 保证持久化。

5.3.5 redo log 要写到磁盘，数据也要写磁盘，为什么要多此一举？

写入 redo log 的方式使用了追加操作， 所以磁盘操作是**顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写**。

磁盘的「顺序写 」比「随机写」 高效的多，因此 redo log 写入磁盘的开销更小。

至此， 针对为什么需要 redo log 这个问题我两个答案：

• 实现事务的持久性，让 MySQL 有 crash-safe 的能力，能够保证 MySQL 在任何时间段突然崩溃，重启后之前已提交的记录都不会丢失；
• 将写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能。

5.3.6 redo log是怎么写入磁盘的？ 是直接写入吗？

redo log也不是直接写入磁盘的，而是有自己的缓存：redo log buffer。每当产生一条 redo log 时，会先写入到 redo log buffer，后续在持久化到磁盘。redo log buffer 默认大小 16 MB，可以通过 innodb_log_Buffer_size 参数动态的调整大小，增大它的大小可以让 MySQL 处理「大事务」是不必写入磁盘，进而提升写 IO 性能。

缓存在 redo log buffer 里的 redo log 还是在内存中，它什么时候刷新到磁盘？

主要有下面几个时机：

• MySQL 正常关闭时；
• 当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘；
• InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。
• 每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘（由参数 innodb_flush_log_at_trx_commit 参数控制）。

5.3.7 redo log文件写满了怎么办？

默认情况下， InnoDB 存储引擎有 1 个重做日志文件组( redo log Group），「重做日志文件组」由有 2 个 redo log 文件组成。在重做日志组中，每个 redo log File 的大小是固定且一致的，假设每个 redo log File 设置的上限是 1 GB，那么总共就可以记录 2GB 的操作。

重做日志文件组是以**循环写**的方式工作的，从头开始写，写到末尾就又回到开头，相当于一个环形。所以 InnoDB 存储引擎会先写 ib_logfile0 文件，当 ib_logfile0 文件被写满的时候，会切换至 ib_logfile1 文件，当 ib_logfile1 文件也被写满时，会切换回 ib_logfile0 文件。

redo log 是循环写的方式，相当于一个环形，InnoDB 用 write pos 表示 redo log 当前记录写到的位置，用 checkpoint 表示当前要擦除的位置，如下图：

write pos 追上了 checkpoint，就意味着 redo log 文件满了，这时 MySQL 不能再执行新的更新操作，也就是说 MySQL 会被阻塞。

此时会停下来将 Buffer Pool 中的脏页刷新到磁盘中，然后标记 redo log 哪些记录可以被擦除，接着对旧的 redo log 记录进行擦除，等擦除完旧记录腾出了空间，checkpoint 就会往后移动（图中顺时针），然后 MySQL 恢复正常运行，继续执行新的更新操作。

5.4 为什么需要binlog？

5.4.1 什么是binlog？

除了InnoDB存储引擎层生成的undo log和redo log，MySQL在完成一条更新操作后，Server层还会生成一条binlog，事务提交时会将该事务执行过程中产生的binlog统一写入binlog 文件。

最开始 MySQL 里并没有 InnoDB 引擎，MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用 redo log 来实现 crash-safe 能力。

5.4.2 redo log和binlog的区别？

• 适用对象不同：binlog是Server层实现的，所以引擎都可以使用；redo log是InnoDB引擎实现的。

• 文件格式不同：binlog有三种格式statement、row、mixed，区别如下：

  • statement：记录修改数据的SQL语句，相当于记录了逻辑操作，因此binlog可以成为逻辑日志；
  • row：记录数据最终被修改成什么样了，此时就不是逻辑日志了；
  • mixed：根据不同情况自动选择使用statement模式或row模式。

• 写入方式不同：binlog 是追加写，写满一个文件就新创建一个文件继续写；redo log是循环追加写，日志空间大小固定，写满了就从头开始。

• 用途不同：binlog主要用于备份恢复（因为是全量记录的）、主从复制；redo log用于掉电等故障恢复（只能存一部分）

如果不小心整个数据库的数据被删除了，能使用 redo log 文件恢复数据吗？

不可以使用 redo log 文件恢复，只能使用 binlog 文件恢复。

因为 redo log 文件是循环写，是会边写边擦除日志的，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件里擦除。

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

5.4.3 binlog什么时候刷盘？

事务执行过程中，先把日志写到 binlog cache（Server 层的 cache），事务提交的时候，再把 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**。

5.5 为什么需要两阶段提交？

5.5.1 为什么需要两阶段提交？

事务提交后，redo log和binlog都需要持久化到磁盘，但是它们是独立的逻辑，可能出现**半成功（只有一个成功持久化了）**的状态，于是两份日志之间的逻辑不一致，存在两种情况：

• 如果在将 redo log 刷入到磁盘之后， MySQL 突然宕机了，而 binlog 还没有来得及写入。
• 如果在将 binlog 刷入到磁盘之后， MySQL 突然宕机了，而 redo log 还没有来得及写入。

redo log 影响主库的数据，binlog 影响从库的数据，所以 redo log 和 binlog 不一致会导致**主从环境中的数据不一致性**。

MySQL 为了避免出现两份日志之间的逻辑不一致的问题，使用了「两阶段提交」来解决，两阶段提交其实是分布式事务一致性协议，它可以保证多个逻辑操作要不全部成功，要不全部失败，不会出现半成功的状态。

5.5.2 两阶段提交的过程是怎样的？

两阶段提交把单个事务的提交拆分成了 2 个阶段，分别是「准备（Prepare）阶段」和「提交（Commit）阶段」，每个阶段都由协调者（Coordinator）和参与者（Participant）共同完成。在 MySQL 的 InnoDB 存储引擎中，开启 binlog 的情况下，MySQL 会同时维护 binlog 日志与 InnoDB 的 redo log，为了保证这两个日志的一致性，MySQL 使用了内部 XA 事务，内部 XA 事务由 binlog 作为协调者，存储引擎是参与者。

当客户端执行 commit 语句或者在自动提交的情况下，MySQL 内部开启一个 XA 事务，分两阶段来完成 XA 事务的提交，如下图：

事务的提交包含两个阶段，本质上就是**将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog**：

• 准备阶段（prepare）：将内部XA事务的ID写入到redo log，同时将redo log对应的事务状态设置为prepare，然后将redo log持久化到磁盘；
• 提交阶段（commit）：将内部XA事务的ID写入到binlog，然后将binlog持久化到磁盘。将redo log的状态设置为commit，此时该状态不需要立马持久化到磁盘中，写入到缓存区就够了。

5.5.3 两阶段提交如何保证数据一致的？

下图中有时刻 A 和时刻 B 都有可能发生崩溃，不管是时刻 A（redo log 已经写入磁盘， binlog 还没写入磁盘），还是时刻 B （redo log 和 binlog 都已经写入磁盘，还没写入 commit 标识）崩溃，此时的 redo log 都处于 prepare 状态。

MySQL 重启后会按顺序扫描 redo log 文件，碰到处于 prepare 状态的 redo log，就拿着 redo log 中的 XID 去 binlog 查看是否存在此 XID：

• 如果 binlog 中没有当前内部 XA 事务的 XID，说明 redolog 完成刷盘，但是 binlog 还没有刷盘，则回滚事务。对应时刻 A 崩溃恢复的情况。
• 如果 binlog 中有当前内部 XA 事务的 XID，说明 redolog 和 binlog 都已经完成了刷盘，则提交事务。对应时刻 B 崩溃恢复的情况。

这样就可以保证 redo log 和 binlog 这两份日志的一致性了。

5.5.4 两阶段提交存在什么问题？

两阶段提交虽然保证了两个日志文件的数据一致性，但是性能很差，主要有两个方面的影响：

• 磁盘I/O次数高：每个事务提交都会进行两次刷盘，redo log刷盘和binlog刷盘；
• 锁竞争激烈：在「多事务」的情况下，两阶段提交不能保证两者的提交顺序一致，因此，在两阶段提交的流程基础上，还需要加一个锁来保证提交的原子性，从而保证多事务的情况下，两个日志的提交顺序一致。

5.5.5 MySQL 磁盘 I/O 很高，有什么优化的方法？

事务在提交的时候，需要将 binlog 和 redo log 持久化到磁盘，那么如果出现 MySQL 磁盘 I/O 很高的现象，我们可以通过**控制一些参数（主要是指定redo log和binlog何时进行刷盘的参数）**，来 “延迟” binlog 和 redo log 刷盘的时机，从而降低磁盘 I/O 的频率。

资料参考

内容大多参考自：图解MySQL介绍 | 小林coding (xiaolincoding.com)