【MySQL数据库原理 五】MySQL日志机制小结

基于前两篇blog对【MySQL数据库原理 二】MySQL日志系统与事务和【MySQL数据库原理 三】MySQL如何保证数据安全可靠对日志系统做一个串联性的描述，从为什么需要使用开始讲起一直到日志是如何满足MySQL的安全可靠的。为了方便把前面的知识串联起来，我画了一个流程图：

循着这个路线正序的提取下日志部分的知识精华。

为什么要用日志

日志有三方面的作用，分别是WAL机制、备份归档以及crash-safe能力。一个个来说说它的好处：

WAL机制

WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志【log file on disk】，再写磁盘【Page on disk】。看定义貌似跑不掉写磁盘这一步，因为日志文件也存储在磁盘中。为啥WAL的写磁盘就快呢？

• redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快，到达一定时机会将日志中涉及的数据写到磁盘中的数据库。如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高
• 组提交机制，积累一些buffer后一次性刷盘。可以大幅度降低磁盘的 IOPS 消耗（IOPS (Input/Output Per Second)即每秒的输入输出量(或读写次数),是衡量磁盘性能的主要指标之一。IOPS是指单位时间内系统能处理的I/O请求数量），这样其实写日志【log file on disk】的次数也会少，可能一次写好几个事务。

其中IOPS (Input/Output Per Second)即每秒的输入输出量(或读写次数)，是衡量磁盘性能的主要指标之一，数值越小，说明IO占用越低。

crash-safe能力

crash-safe能力表示，即使数据库发生异常重启【可能是数据库进程掉电，也可能是主机掉电】，之前提交的记录，都不会丢失，也即已经提交（commit）成功的事务不会被丢失，可以通过持久化到磁盘的日志【log file on disk】+持久化到磁盘的数据【Page on disk】来恢复，

begin;
insert into t1 ...
insert into t2 ...
commit;

举个例子，如果仅仅只有数据库，掉电的时候以上的第一个insert执行完中断，数据库中的数据就不是我们预想的了，满足不了事务的ACID特性。

备份归档

数据库的备份和恢复依赖于日志和数据库的全量备份的，因为我们不能频繁的进行数据库的实时的全量备份，成本太大而且不易实现，所以定期的整库备份+日志才是实现数据库安全稳定的王道

日志如何满足

Redolog和Binlog可以满足以上提到的三个日志需要的功能。

Redolog和Binlog实现WAL

其实无论是Redolog还是Binlog，都有WAL机制，也就是更新记录都会先落到磁盘上的日志文件上，并且更新内存中的数据页，等到合适的时机再刷数据落盘。具体而言有如下三个步骤：

• 创建阶段：事务创建一条日志，并添加到日志缓存
• 日志刷盘【log file on disk】：日志缓存经过文件系统缓存后写入到磁盘上的日志文件，并更新记录到内存中的数据页，使其变为脏页
• 数据刷盘【Page on disk】：日志对应的脏页数据写入到磁盘上的数据文件
• 更新checkpoint【Redo log特有】，RedoLog在数据刷盘之后还有一个步骤就是，也就是说redolog中的checkpoint前进方向区域一定是数据页还没落盘的提交，这样我们就能确定哪些数据即使事务commit也就是日志刷盘已经成功的状态下其实数据还没有刷盘

commit主要就是在日志刷盘阶段，我们主要讨论下事务到产生到日志落盘为止的过程。

Redolog写入机制

以下这几个步骤对应于整个事务数据落盘的前两步，也就是日志刷盘为止。

redo log 可能存在三种状态，这三种状态是依次写入的：

1. 事务先写到 redo log buffer 中，物理上是在 MySQL 进程内存中，就是图中的红色部分；日志写到 redo log buffer 是很快的
2. 写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的 page cache 里面，也就是图中的黄色部分【redo log file on FS cache】，wirte 到 page cache 也很快
3. 持久化到磁盘，对应的是 hard disk，也就是图中的绿色部分【binlog file on disk】，fsync到磁盘的速度就慢多了

为了控制 redo log 的写入策略，InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，它有三种可能取值：

• 设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ，不管是主机掉电还是MySQL异常重启，都有丢数据的风险，风险高
• 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache，写入文件系统的page cache，主机掉电后会丢数据，但是MySQL异常重启不会丢数据，风险较低，写入比较快
• 设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘，非常安全，但慢。

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。可以理解为innodb_flush_log_at_trx_commit三个设置对应每次事务写入到不同级别，而每隔一秒就会从最不稳定状态直接刷到最稳定状态

Binlog写入机制

事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件【文件系统缓存page cache】中。一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。

系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache

1. 事务先写到每个线程自己的 binlog cache，但是共用同一份 binlog 文件。
2. 然后write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快【binlog file on FS cache】。
3. 最后fsync，才是将数据持久化到磁盘的操作【binlog file on disk】

一般情况下，我们认为 fsync 才占磁盘的 IOPS。write 和 fsync 的时机，是由参数 sync_binlog 控制的：

• sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0
• sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
• sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。比较常见的是将其设置为 100~1000 中的某个数值。但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志，主机都crash了，文件系统缓存里的日志当然也丢了，没法落盘

可以理解为sync_binlog等于几表明每几个事务发生一次fsync，因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。

Redolog实现crash-safe

为什么Redolog可以实现crash-safe而binlog不可以，因为RedoLog的特殊数据结构，RedoLog是循环写的，有特有的checkpoint。

binglog是顺序写的，binlog 没有能力恢复“数据页”。如果MySQL 发生了 crash需要恢复（恢复的过程就是将未刷盘的内存中的数据脏页恢复到内存中）

• binlog由于没有checkpoint所以可能虽然有N个事务已经commit，但数据页还没有落盘，依据binlog没有办法判断哪些事务的commit涉及的数据没有落盘，也就不知道该从哪里恢复
• redo log是循环写的，有checkpoint，通过checkpoint的移动位置可以确定哪些事务指向的数据脏页确实被刷盘了，只要刷入磁盘的数据，都会从 redo log 中抹掉，数据库重启后，直接把 redo log 中的数据都恢复至内存就可以了

如果真想使用binlog来恢复的话，那么就要在每个commit之前，将更改的内存记录刷盘。刷盘之后再将这个事务改为commit状态。 这样崩溃恢复就可以在事务级去做了，而不用在数据页级去做了，但是这样显然违背WAL技术【累计多个事务修改的数据页存储内存，一次刷盘来减少IOPS】的初衷

binlog实现备份归档

redo log 是循环写的，空间固定会用完，用完后会之前的记录会被覆盖；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志，所以归档使用追加写的binlog。

Redo log和Binglog存在意义

除了上述二者各自不可替代的功能外，他们还有一些必须存在的历史原因

• Binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。binlog 作为 MySQL 一开始就有的功能，被用在了很多地方。其中，MySQL 系统高可用的基础，就是 binlog 复制。还有很多公司有异构系统（比如一些数据分析系统），这些系统就靠消费 MySQL 的 binlog 来更新自己的数据。关掉 binlog 的话，这些下游系统就没法输入了。总之，由于现在包括 MySQL 高可用在内的很多系统机制都依赖于 binlog
• Redo log是 InnoDB 引擎特有的，可以实现crash-safe，因为这个能力，后续被当做了MySQL的默认引擎

所以因为有了Redo log的crash-safe，所以没必要再去做一个出来，而又因为Binlog比较早期，除了归档还在很多生态中被广泛使用，所以二者都不能被取代。只能并存，那么并存就有一个问题，我们需要保证这两份并存的日志是一致的！

两阶段提交机制

两阶段提交的顺序如下图所示，那么为什么要两阶段提交呢？前面我们讨论了两个日志各自存在的必要性，表明我们不能只用其中一种日志，而要结合使用，结合使用就一定要保证它两是一致的。

如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。

• 先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 binlog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。binlog丢，无法备份
• 先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于 redo log 还没写，崩溃恢复以后这个事务无效。本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。binlog多一条事务执行记录，备份归档或主从同步时产生脏事务

所以如果采用 redo log 两阶段提交的方式就不一样了，写完 binglog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。采用这种方式再验证一下：

• 写redo log 预提交过程中，机器挂了，则回滚事务【时刻A】
• 写完redo log 预提交，并且写binglog过程中，机器挂了，则判断【时刻B】

• 如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交【说明写完binlog了】
• 如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务

这样就解决了数据一致性的问题

组提交策略

三个并发事务 (trx1, trx2, trx3) 在 prepare 阶段，都写完 redo log buffer，持久化到磁盘的过程，对应的 LSN 分别是 50、120 和 160

从图中可以看到：

1. trx1 是第一个到达的，会被选为这组的 leader；
2. 等 trx1 要开始写盘的时候，这个组里面已经有了三个事务，这时候 LSN 也变成了 160；
3. trx1 去写盘的时候，带的就是 LSN=160，因此等 trx1 返回时，所有 LSN 小于等于 160 的 redo log，都已经被持久化到磁盘；这时候 trx2 和 trx3 就可以直接返回了。

所以，一次组提交里面，组员越多，节约磁盘 IOPS 的效果越好。但如果只有单线程压测，那就只能老老实实地一个事务对应一次持久化操作了。在并发更新场景下，第一个事务写完 redo log buffer 以后，接下来这个 fsync 越晚调用，组员可能越多，节约 IOPS 的效果就越好

提升MySQL的IO性能

结合以上的日志写入策略和延迟刷盘尽量组里事务多一些来谈谈MySQL的IO性能

日志落盘参数配置

如果你的 MySQL 现在出现了性能瓶颈，而且瓶颈在 IO 上，可以通过哪些方法来提升性能呢？针对这个问题，可以考虑以下三种方法：

• 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
• 将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）【事务都写到文件系统缓存】。这样做的风险是，主机crash掉电时会丢 binlog 日志。
• 将 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 异常重启时就不会丢数据了，相比之下风险会更小

MySQL的延迟刷盘策略

为了让一次 fsync 带的组员更多，MySQL 有一个优化策略：拖时间。了解了两种日志的写入过程后，我们可以把这个过程更加的细化

这样做有两个好处，对redolog和binglog都加了组提交优化：

• redolog可以更好的组提交。把 redo log 做 fsync 的时间拖到了步骤 2之后，这样redo log做fsync 和 write的中间隔了一个binlog的write，所以会累计更多的事务在文件缓存中一起刷盘
• binlog 也可以组提交了。在执行第 4 步把 binlog fsync 到磁盘时，如果有多个事务的 binlog 已经写完了，也是一起持久化的，这样也可以减少 IOPS 的消耗。不过通常情况下第 3 步执行得会很快，所以 binlog 的 write 和 fsync 间的间隔时间短，导致能集合到一起持久化的 binlog 比较少，因此 binlog 的组提交的效果通常不如 redo log 的效果那么好

这就是MySQL的拖时间组提交策略，需注意其实组提交处理的是并发的场景，单线程的话在双1设置下都是每次事务完成单个刷盘的。