周杰伦的所有歌曲中，我最喜欢的歌就是《听妈妈的话》，其中有这么一句歌词：小朋友，你是否有很多问号，为什么别人在那看漫画，我却在学画画。
应用在现在的场景就是：小伙伴，你是否有很多问号，为什么别人只需要简单用一下MySQL，你却要对MySQL深入浅出。
实际上每天的进步都是为了自己能接受顶尖大佬的技术熏陶，虽然我们不能亲自聆听他们的声音，但是他们已经将自己的思路写在了他们的开源项目里，这就是我们学习开源项目的意义所在。
比如今天，我们的话题是：MySQL可以存储上亿级别的数据，但是却几乎不会丢失数据，这里面到底是因为什么？
先给出结论：MySQL的数据不丢失就需要保证binlog和redo log都持久化到磁盘，因此，为了保证数据不丢失，就需要了解两个日志的写入机制。
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，也能恢复数据。
图片
如图所示，每个线程有自己binlog cache，但是共用同一份binlog文件。执行流程为：
Page Cache是OS关于磁盘IO的缓存，位于内核中，不适用于大文件传输，因为大文件传输page cache的命中率比较低，这个时候page cache不仅没有起到作用还增加了一次数据从磁盘buffer到内核page cache的开销；
高版本的linux系统中已经把Buffer跟虚拟文件系统的page cache合并在一起了，因此也就没有从磁盘buffer拷贝到内核page cache的开销；
write和fsync的时机，由参数sync_binlog控制（与redis的Appendfsync相似）：
因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。
但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。
图片
如图所示的三种颜色就是redo log的三种状态：
fsync函数同步内存中所有已修改的文件数据到储存设备。一般情况下，对硬盘（或者其他持久存储设备）文件的write操作，更新的只是内存中的页缓存（page cache），而脏页面不会立即更新到硬盘中，而是由操作系统统一调度，如由专门的flusher内核线程在满足一定条件时（如一定时间间隔、内存中的脏页达到一定比例）内将脏页面同步到硬盘上（放入设备的IO请求队列）。 因为write调用不会等到硬盘IO完成之后才返回，因此如果OS在write调用之后、硬盘同步之前崩溃，则数据可能丢失。
如果事务执行过程中MySQL发生异常重启，这部分日志丢了，也不会有损失，因为事务还没有提交， 因此，redo log buffer不需要每次生成都直接持久化磁盘。
由于都是内存操作，因此日志写入redo log buffer，以及write到page cache都很快，但是持久化磁盘的速度比较慢。
为控制写入策略，InnoDB提供了innodb_flush_log_at_trx_commit参数：
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 里的日志一起持久化到磁盘。
两阶段提交，时序上是redo log先prepare，再写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。
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状态完整。
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。
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都要执行刷盘，此时可能有其他的线程也在执行事务，也可以将他们组成一个组实现组提交。
图片
两阶段提交可以简化为如下两步：
既然组提交能够优化磁盘的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 来实现。
这两个条件是或的关系，也就是说只要有一个满足条件就会调用 fsync，当 binlog_group_commit_sync_delay 设置为 0 的时候，binlog_group_commit_sync_no_delay_count 也无效了。
之前我们多次提及的WAL能够减少磁盘写，主要是得益于：
分析到这里，我们再来回答这个问题：如果你的 MySQL 现在出现了性能瓶颈，而且瓶颈在 IO 上，可以通过哪些方法来提升性能呢？针对这个问题，可以考虑以下三种方法：
但是并不建议把 innodb_flush_log_at_trx_commit 设置成 0。因为把这个参数设置成 0，表示 redo log 只保存在内存中，这样的话 MySQL 本身异常重启也会丢数据，风险太大。而 redo log 写到文件系统的 page cache 的速度也是很快的，所以将这个参数设置成 2 跟设置成 0 其实性能差不多，但这样做 MySQL 异常重启时就不会丢数据了，相比之下风险会更小。
京公网安备 11010802035086号