MySQL 各种日志深度解析

一、Double Write（二次写）

1.1 脏页刷盘的风险是什么

首先介绍一下 IO 的最小单位：

  1. 数据库 IO 的最小单位是 16K（MySQL 默认，Oracle 是 8K）
  2. 文件系统 IO 的最小单位是 4K（也有 1K 的）
  3. 磁盘 IO 的最小单位是 512 字节

因此，存在 IO 写入导致 page 损坏的风险。

1.2 ⭐二次写解决了什么问题

提高了 InnoDB 的可靠性，用来解决部分写失败（partial page write 页断裂）。

一个数据页的大小是 16K，假设在把内存中的脏页写到数据库的时候，写了 2K 突然掉电，也就是说前 2K 数据是新的，后 14K 是旧的，那么磁盘数据库这个数据页就是不完整的，是一个坏掉的数据页。

redo 只能加上旧、校检完整的数据页恢复一个脏块，不能修复坏掉的数据页，所以这个数据就丢失了，可能会造成数据不一致，所以需要 Double Write。

当数据库正在从内存向磁盘写一个数据页时，数据库宕机，从而导致这个页只写了部分数据，这就是部分写失效，它会导致数据丢失。这时是无法通过重做日志恢复的，因为重做日志记录的是对页的物理修改，如果页本身已经损坏，重做日志也无能为力。

1.3 ⭐二次写的工作流程

double write 由两部分组成，一部分为内存中的 doublewrite buffer，其大小为 2MB，另一部分是磁盘上共享表空间（ibdata x）中连续的 128 个页，即 2 个区（extent），大小也是 2MB。

工作流程

  1. 当一系列机制触发数据缓冲池中的脏页刷新时，并不直接写入磁盘数据文件中，而是先拷贝至内存中的 doublewrite buffer 中
  2. 接着从两次写缓冲区分两次写入磁盘共享表空间中（连续存储，顺序写，性能很高），每次写 1MB
  3. 待第二步完成后，再将 doublewrite buffer 中的脏页数据写入实际的各个表空间文件（离散写）；（脏页数据固化后，即进行标记对应 doublewrite 数据可覆盖）
  4. doublewrite 的崩溃恢复：如果操作系统在将页写入磁盘的过程中发生崩溃，在恢复过程中，InnoDB 存储引擎可以从共享表空间的 doublewrite 中找到该页的一个最近的副本，将其复制到表空间文件，再应用 redo log，就完成了恢复过程。因为有副本所以也不担心表空间中数据页是否损坏。

核心要点

  • doublewrite 解决的是”页写入原子性问题”，redo log 解决的是”事务逻辑一致性问题”

数据流向

1
2
3
4
5
6
7
Buffer Pool
↓
doublewrite buffer（内存）
↓
doublewrite 区（磁盘，连续写）
↓
.ibd / ibdata 文件（离散写）

二、Binlog（二进制日志）

2.1 写入机制

其实，binlog 的写入逻辑比较简单：

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

一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。因为单线程的环境下，只能同时执行一个事务，如果拆开了，那么下一个事务执行的时候默认会把上一个事务提交，这样就会被当作多个事务分段执行，会破坏原子性。

这就涉及到了 binlog cache 的保存问题。

2.2 ⭐写入磁盘

系统给 binlog cache 分配了一片内存用于缓冲 binlog，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘，默认大小是 32KB。

事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。

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

write 和 fsync

  • 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 日志。

2.3 ⭐组提交

（组提交机制的详细内容）

三、Redo Log（重做日志）

3.1 ⭐写入机制

产生的 redo log 是直接写入磁盘的吗？

不是的，如果直接写入磁盘会产生大量的 I/O 操作，所以其实 redo log 也有自己的缓存 —— redo log buffer。

每产生一条 redo log 时，会先写入到 redo log buffer，后续才会持久化到磁盘。redo log buffer 默认大小为 16M，可以通过 innodb_log_buffer_size 参数来动态地调整大小，增大它的大小可以让 MySQL 处理大事务的时候不必写入磁盘，进而提升写的 I/O 性能。

redo log buffer 的持久化时机

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

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

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

答案是，确实会有。

刷盘时机

主要有下面几个时机：

  1. MySQL 正常关机的时候
  2. redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半的时候
  3. InnoDB 后台线程每隔一秒，就会将 redo log buffer 中的内容持久化到磁盘
  4. 每次事务提交的时候都会将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘，该行为由 innodb_flush_log_at_trx_commit 参数控制

3.2 ⭐redo log 的三种状态

这个问题，要从 redo log 可能存在的三种状态说起，这三种状态分别是：

  1. 存在 redo log buffer 中 - 物理上是在 MySQL 进程内存中
  2. 写到磁盘（write），但是没有持久化（fsync） - 物理上是在文件系统的 page cache 里面
  3. 持久化到磁盘 - 对应的是 hard disk

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

innodb_flush_log_at_trx_commit 参数

为了控制 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

3.3 ⭐后台进程提前写

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

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

3.4 ⭐其它两种 redo log 提前写的场景

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

  1. redo log buffer 占用空间达到一半 - 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 里的日志一起持久化到磁盘

3.5 ⭐两阶段提交和组提交中的应用

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

如果把 innodb_flush_log_at_trx_commit 设置成 1，那么 redo log 在 prepare 阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于 prepare 的 redo log，再加上 binlog 来恢复的。

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

双 1 配置

通常我们说 MySQL 的”双 1”配置，指的就是 sync_binlog（表示每次提交事务都会执行 fsync）和 innodb_flush_log_at_trx_commit（表示每次事务提交时都将 redo log 直接持久化到磁盘，两阶段提交）都设置成 1。

也就是说，一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。

组提交机制

这时候，你可能有一个疑问，这意味着我从 MySQL 看到的 TPS 是每秒两万的话，每秒就会写四万次磁盘。但是，我用工具测试出来，磁盘能力也就两万左右，怎么能实现两万的 TPS？

解释这个问题，就要用到组提交（group commit）机制了。

这里，我需要先和你介绍日志逻辑序列号（log sequence number，LSN）的概念。LSN 是单调递增的，用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log， LSN 的值就会加上 length。

LSN 也会写到 InnoDB 的数据页中，来确保数据页不会被多次执行重复的 redo log。

3.6 MySQL 怎么提升 IOPS

分析到这里，我们再来回答这个问题：如果你的 MySQL 现在出现了性能瓶颈，而且瓶颈在 IO 上，可以通过哪些方法来提升性能呢？

针对这个问题，可以考虑以下三种方法：

  1. 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数 - 减少 binlog 的写盘次数。这个方法是基于”额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险

  2. 将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）- 这样做的风险是，主机掉电时会丢 binlog 日志

  3. 将 innodb_flush_log_at_trx_commit 设置为 2 - 这样做的风险是，主机掉电的时候会丢数据

我不建议你把 innodb_flush_log_at_trx_commit 设置成 0。因为把这个参数设置成 0，表示 redo log 只保存在内存中，这样的话 MySQL 本身异常重启也会丢数据，风险太大。而 redo log 写到文件系统的 page cache 的速度也是很快的，所以将这个参数设置成 2 跟设置成 0 其实性能差不多，但这样做 MySQL 异常重启时就不会丢数据了，相比之下风险会更小。

3.7 为什么我的 MySQL 会”抖”一下

（MySQL 抖动问题的详细分析）

3.8 ⭐WAL 机制

WAL，全称是 Write-Ahead Logging，预写日志系统。指的是 MySQL 的写操作并不是立刻更新到磁盘上，而是先记录在日志上，然后在合适的时间再更新到磁盘上。这样的好处是错开高峰期。（简单的说，WAL 就是延迟更新的，拖到没有办法了再更新）。

日志主要分为 undo log（MVCC）、redo log（防止写操作因为宕机而丢失）、binlog（写操作的备份，保证主从一致）。

这三种在之前的博客已经详细说过了，作用分别是：

  • “完成 MVCC 从而实现 MySQL 的隔离级别”
  • “降低随机写的性能消耗（转成顺序写），同时防止写操作因为宕机而丢失”
  • “写操作的备份，保证主从一致”

关于这三种日志的内容讲的比较分散且具体的执行过程没有提到，所以这里来总结一下这三种日志。

四、Undo Log（回滚日志）

4.1 是什么

MySQL 把这些为了回滚而记录的内容称之为”撤销日志”或者”回滚日志”（即 undo log）。

注意，由于查询操作（SELECT）并不会修改任何用户记录，所以在查询操作行时，并不需要记录相应的 undo 日志。

此外，undo log 会产生 redo log，也就是 undo log 的产生会伴随着 redo log 的产生，这是因为 undo log 也需要持久性的保护。

这里解释一下为什么它们是相辅相成的，这里简单地说一下，本来我们执行到一半挂了，undo 是回到开始，redo 是继续执行完。

4.2 ⭐底层存储结构

undo log 的存储由 InnoDB 存储引擎实现，数据保存在 InnoDB 的数据文件中。

在 InnoDB 存储引擎中，undo log 是采用分段（segment）的方式进行存储的。rollback segment 称为回滚段，每个回滚段中有 1024 个 undo log segment，而在每个 undo log segment 段中可以进行 undo 页的申请。

MySQL 5.5 之前

在 MySQL 5.5 之前，只支持 1 个 rollback segment，也就是只能记录 1024 个 undo 操作（这个记录的方式和 Redis 的 AOF 很类似），因此支持同时在线的事务限制为 1024，对绝大多数的应用来说都已经够用。

MySQL 5.5 之后

在 MySQL 5.5 之后，可以支持 128 个 rollback segment，分别从 resg slot0 - resg slot127，每一个 resg slot，也就是每一个回滚段，内部由 1024 个 undo segment 组成，即总共可以记录 128 * 1024 个 undo 操作（这个又有一点像 Redis 分片集群的 slot 槽），故其支持同时在线的事务限制提高到了 128 * 1024。

undo log 的内容

undo log 日志里面不仅存放着数据更新前的记录，还记录着 RowID、事务 ID、回滚指针（这个玩意和 Git 底层很类似）。

其中事务 ID 每次递增，回滚指针第一次如果是 insert 语句的话，回滚指针为 NULL，第二次 update 之后的 undo log 的回滚指针就会指向刚刚那一条 undo log 日志，依次类推，就会形成一条 undo log 的回滚链，方便找到该条记录的历史版本。

4.3 底层实现原理

接下来谈谈 undo log 实际的落地方案——read-view。

假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似上面的记录。当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。

在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。

对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。

同时你会发现，即使现在有另外一个事务正在将 4 改成 5，这个事务跟 read-view A、B、C 对应的事务是不会冲突的，因为只要是修改没有 commit，看的都是 undo log 中的备份数据。

回滚日志的删除时机

你一定会问，回滚日志总不能一直保留吧，什么时候删除呢？

答案是，在不需要的时候才删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。

什么时候才不需要了呢？就是当系统里没有比这个回滚日志更早的 read-view 的时候（这里的回收机制有点类似 GCRoot）。

4.4 ⭐种类

insert undo log

  • 是指在 insert 操作中产生的 undo log
  • 因为 insert 操作的记录只对事务本身可见，对其他事务不可见（这是事务隔离性的要求）
  • 故该 undo log 可以在事务提交后直接删除，不需要进行 purge 操作

update undo log

  • 记录的是对 delete 和 update 操作产生的 undo log
  • 该 undo log 可能需要提供 MVCC 机制，因此不能在事务提交时就进行删除
  • 提交时放入 undo log 链表，等待 purge 线程进行最后的删除

4.5 ⭐重用

当事务提交时，InnoDB 存储引擎会做以下两件事情：

  1. 将 undo log 放入列表中，以供之后的 purge（清洗、清除）操作
  2. 判断 undo log 所在的页是否可以重用（低于 3/4 可以重用），若可以分配给下个事务使用

重用机制详解

当我们开启一个事务需要写 undo log 的时候，就得先去 undo log segment 中去找到一个空闲的位置，当有空位的时候，就去申请 undo 页，在这个申请到的 undo 页中进行 undo log 的写入。

我们知道 MySQL 默认一页的大小是 16k，为每一个事务分配一个页，是非常浪费的（除非你的事务非常长），假设你的应用的 TPS（每秒处理的事务数目）为 1000，那么 1s 就需要 1000 个页，大概需要 16M 的存储，1 分钟大概需要 1G 的存储。如果照这样下去除非 MySQL 清理得非常勤快，否则随着时间的推移，磁盘空间会增长得非常快，而且很多空间都是浪费的。

于是 undo 页就被设计得可以重用了，当事务提交时，并不会立刻删除 undo 页。因为重用，所以这个 undo 页可能混杂着其他事务的 undo log。

undo log 在 commit 后，会被放到一个链表中，然后判断 undo 页的使用空间是否小于 3/4，如果小于 3/4 的话，则表示当前的 undo 页可以被重用，那么它就不会被回收，其他事务的 undo log 可以记录在当前 undo 页的后面。

由于 undo log 是离散的，所以清理对应的磁盘空间时，效率不高。

4.6 ⭐清理

在更新数据之前，MySQL 会提前生成 undo log 日志，当事务提交的时候，并不会立即删除 undo log，要执行回滚（rollback）操作时，从缓存中读取数据。

undo log 日志的删除是通过通过后台 purge 线程进行回收处理的

五、⭐两阶段提交

（两阶段提交的详细内容和流程图）