MySQL：亿级别数据不丢失是如何实现的

周杰伦的所有歌曲中，我最喜欢的歌就是《听妈妈的话》，其中有这么一句歌词：小朋友，你是否有很多问号，为什么别人在那看漫画，我却在学画画。

应用在现在的场景就是：小伙伴，你是否有很多问号，为什么别人只需要简单用一下MySQL，你却要对MySQL深入浅出。

实际上每天的进步都是为了自己能接受顶尖大佬的技术熏陶，虽然我们不能亲自聆听他们的声音，但是他们已经将自己的思路写在了他们的开源项目里，这就是我们学习开源项目的意义所在。

比如今天，我们的话题是：MySQL可以存储上亿级别的数据，但是却几乎不会丢失数据，这里面到底是因为什么？

先给出结论：MySQL的数据不丢失就需要保证binlog和redo log都持久化到磁盘，因此，为了保证数据不丢失，就需要了解两个日志的写入机制。

1 binlog写入机制

1.1 写入原则

binlog的写入逻辑为：事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。

同时，一个事务的Binlog是不能拆开的，因此，无论事务多大，也要确保一次性写入，这就涉及到binlog cache的保存问题。原因在于：binlog写入的前提条件是事务被提交，事务至少进入prepare状态，若此时一个事务的binlog拆分写，意味着备库执行时，可能将还没有提交的事务执行，导致主备数据不一致。

系统给binlog cache分配了一块内存，每个线程一个，参数binlog_cache_size用于控制单个线程内binlog cache所占内存大小。如果超过了这个参数规定大小，就要暂存到磁盘。可以通过语句show status like 'Binlog_cache_disk_use';判断默认大小32KB是否满足大小，如果语句的值远大于0，需要增加binlog_cache_size的值；

图片

事务提交时，执行器把binlog cache里的完整事务写入到binlog，并清空binlog cache。实际上，在第一段提交状态变为prepare状态时，就可以把binlog cache写入binlog，因此，即使之后crash，也能恢复数据。

1.2 写入流程

图片

如图所示，每个线程有自己binlog cache，但是共用同一份binlog文件。执行流程为：

• 事务执行过程中先把日志写到binlog cache ，事务提交的时候再把binlog cache 写入到binlog文件中，并清空binlog cache；
• 系统为每个线程分配了一片binlog cache内存，参数binlog_cache_size控制单个线程内binlog cache大小。如果超过这个大小就要暂存到磁盘；
• 事务提交的时候，执行器把binlog cache里完整的事务写入binlog中。并清空binlog cache。 
• 每个线程都有自己的binlog cache，共用一份binlog文件 
• write，是把日志写入到文件系统的page cache内存中，没有持久化到磁盘，所以速度比较快。fsync是将数据持久化到磁盘，因此说，fsync才会占用磁盘的IOPS；

Page Cache是OS关于磁盘IO的缓存，位于内核中，不适用于大文件传输，因为大文件传输page cache的命中率比较低，这个时候page cache不仅没有起到作用还增加了一次数据从磁盘buffer到内核page cache的开销；

高版本的Linux系统中已经把Buffer跟虚拟文件系统的page cache合并在一起了，因此也就没有从磁盘buffer拷贝到内核page cache的开销；

write和fsync的时机，由参数sync_binlog控制（与redis的appendfsync相似）：

• sync_binlog=0，每次提交事务都只write，不做fysnc；
• sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
• sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。

但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。

2 redo log机制

2.1 redo log三种状态

图片

如图所示的三种颜色就是redo log的三种状态：

• 红色部分：存在redo log buffer中，物理上是在MySQL进程内存中；
• 黄色部分：写到磁盘（write），但是没有持久化（fsync），物理上是在文件系统的page cache中；
• 绿色部分：持久化到磁盘，对应的是hard disk；

fsync函数同步内存中所有已修改的文件数据到储存设备。一般情况下，对硬盘（或者其他持久存储设备）文件的write操作，更新的只是内存中的页缓存（page cache），而脏页面不会立即更新到硬盘中，而是由操作系统统一调度，如由专门的flusher内核线程在满足一定条件时（如一定时间间隔、内存中的脏页达到一定比例）内将脏页面同步到硬盘上（放入设备的IO请求队列）。 因为write调用不会等到硬盘IO完成之后才返回，因此如果OS在write调用之后、硬盘同步之前崩溃，则数据可能丢失。

如果事务执行过程中MySQL发生异常重启，这部分日志丢了，也不会有损失，因为事务还没有提交， 因此，redo log buffer不需要每次生成都直接持久化磁盘。

2.2 redo log写入策略

由于都是内存操作，因此日志写入redo log buffer，以及write到page cache都很快，但是持久化磁盘的速度比较慢。

为控制写入策略，InnoDB提供了innodb_flush_log_at_trx_commit参数：

• 0：每次事务提交都只是把redo log留在redo log buffer；
• 1：每次事务提交都将redo log直接持久化到磁盘；【innodb的默认值】
• 2：每次事务提交时都只是把redo log写到page cache；

2.3 刷盘时机

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

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

2）空间不足：redo log buffer占用的空间即将到达innodb_log_buffer_size一半时，后台线程会主动写盘。注意，此时由于这个事务还没有提交，所以这个写盘动作只是write，没有调用fsync，即：只是写入到page cache中。

图片

3）其他事务提交：并行事务提交时，顺带将这个事务的redo log buffer持久化到磁盘。假设一个事务 A 执行到一半，已经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘。

2.4 配置说明

两阶段提交，时序上是redo log先prepare，再写binlog，最后再把redo log commit。

• 在redo log执行prepare阶段MySQL异常重启，redo log没有fsync，内存丢失，直接回滚，不影响数据一致性；
• 当redo log执行fsync成功，但是binlog持久化异常，此时MySQL异常重启，此时检查redo log在prepare状态，但是Binlog写入失败，则直接回滚即可；
• 当binlog持久化后，但是redo log commit失败，此时的redo log一定是prepare状态，并且binlog完成，则添加commit标记，进而提交执行持久化，满足数据一致性；
• binlog完成且提交，redo log也commit成功，此时数据满足一致性。

如果把innodb_flush_log_at_trx_commit设置为1，那么redo log在prepare阶段就要持久化一次，因为crash-safe依赖于prepare状态的redo log + binlog恢复。

每秒一次后台轮询刷盘，再加上crash-safe，InnoDB认为redo log在commit时只需要write到文件系统的page cache就可以了，因为只要binlog写盘成功，就算redo log状态还是prepare状态也会被认为事务已经执行成功，所以只需要write到page cache就OK了，没必要浪费IO主动执行一次fsync。

• redo log prepare && binlog commit：事务提交；
• redo log prepare && binlog uncommitted：事务回滚；

MySQL的“双1”配置，指的就是sync_binlog和innodb_flush_log_at_trx_commit设置为1.即：一个事务完整提交前，需要等待两次刷盘，一次是redo log的prepare阶段，一个是Binlog。

这里需要注意的是，在事务中有两次commit，第一次commit是事务语句的commit，这里说的commit主要是第二次commit，即：redo log的commit。

两个commit的不是一个东西，在事务提交时，commit语句可以称为commit1, 这时就会将redolog和 binlog fsync到磁盘, 这里写入的redolog是prepare状态（此时是语句的commit事务），如果这个prepare状态的redolog和binlog都fsync成功的话，这个数据就不会丢失了。 然后后续把redolog的状态从prepare的状态变成commit状态，这里称为commit2，这里的改变状态是后台线程刷的，和数据不丢就没啥关系，只是为了让redolog状态完整。

2.5 组提交（group commit）

2.5.1 LSN

LSN(Log Sequence Number，日志逻辑序列号）是单调递增的，用来对应redo log的一个个写入点，每次写入长度为length的redo log，LSN的值就会加上length。

LSN可以看成是事务提交的序号，这个序号是在事务提交写盘的时候生成的，因此可以说LSN反映了事务提交的顺序。

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

图片

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

• trx1是第一个到达的，会被选择这组leader；
• 等trx1开始写盘时，组内有三个事务，LSN变成了160；
• trx1写盘时，携带的LSN为160，因此等trx1返回时，所有LSN小于等于160的redo log都已经被持久化到磁盘；
• 这时候trx2和trx3可以直接返回；

MySQL当多个线程在提交完prepare，redo log写入到redo log buffer中，此时，redo log buffer存在多个线程的日志，并同步更新了LSN。第一个写完的线程带着LSN去刷盘，写完后，别的线程发现自己的redo log已经写完了（LSN大于线程的LSN），直接就返回。

如上所示，一个组提交的事务越多，节约磁盘的IOPS效果越好。在并发场景，即使innodb_flush_log_at_trx_commit设置为1，事务每次prepare都要执行刷盘，此时可能有其他的线程也在执行事务，也可以将他们组成一个组实现组提交。

2.5.2 两阶段优化

图片

两阶段提交可以简化为如下两步：

• 先把binlog从binlog cache写到磁盘的binlog文件；
• 调用fsync持久化；

既然组提交能够优化磁盘的IOPS，那就有了如下的优化：

图片

如图所示，把redo log做fysnc的时间拖到了步骤1之后，采用交叉fsync的方式，就是为了收集更多的“提交”，这样的组提交效果更好一些。

这么一来，binlog也可以组提交了。在执行第4步把binlog fsync到磁盘时，如果有多个事务的 binlog 已经写完了，也是一起持久化的，这样也可以减少 IOPS 的消耗。

不过通常情况下第 3 步执行得会很快（redo log顺序写，相对较快），所以 binlog 的 write 和 fsync 间的间隔时间短，导致能集合到一起持久化的 binlog 比较少，因此 binlog 的组提交的效果通常不如 redo log 的效果那么好。

如果想提升 binlog 组提交的效果，可以通过设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 来实现。

• binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;
• binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。

这两个条件是或的关系，也就是说只要有一个满足条件就会调用 fsync，当 binlog_group_commit_sync_delay 设置为 0 的时候，binlog_group_commit_sync_no_delay_count 也无效了。

之前我们多次提及的WAL能够减少磁盘写，主要是得益于：

• redo log和binlog都是顺序写，磁盘的顺序写比随机写要快；
• 组提交机制，大大降低磁盘的IOPS消耗；

3 MySQL的IO瓶颈优化

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

• 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。之所以说没有丢失数据风险，指的是无论fsync延迟多久，只要binlog没有持久化，只是做回滚，不会出现丢数据。但是可能导致业务侧超时。
• 将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 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 异常重启时就不会丢数据了，相比之下风险会更小。