MYSQL中如何实现binlog优化-创新互联

这篇文章主要讲解了MYSQL中如何实现binlog优化，内容清晰明了，对此有兴趣的小伙伴可以学习一下，相信大家阅读完之后会有帮助。

问题

问题1：如何解决事务提交时flush redo log带来的性能损失

WAL是实现事务持久性(D)的一个常用技术，基本原理是将事务的修改记录redo log。redo log顺序追加写入。事务提交时，只需要保证事务的redo log落盘即可，通过redo log的顺序写代替页面的随机写提升数据库系统的性能。但是，该方案必须要求每个事务提交时都将其生成的redo log进行一次刷盘，效率不高。

问题2：binlog和引擎层事务提交的顺序问题

对于单个事务而言，日志写入顺序是先redo log再binlog，只要维持该顺序即可维持正确性。但对于一个高并发的数据库系统而言，每时每刻可能都会存在众多并发执行的事务。我们还需要通过一定的手段来维护Server层binlog和引擎层事务提交的顺序一致性。

维护这种顺序一致性其实是为了保证备份工具Xtrabackup的正确性。

当 binlog 作为协调者，如果其中记录的事务顺序和存储引擎层记录的顺序不一样的话，备份工具（Innodb Hot Backup）拿到备份集的位点可能会存在空洞。因为备份工具会拷贝 redo 日志，在 redo 的头部会记录最后一个提交的事务对应的 binlog 位点，备份集建立之后就会根据这个位点继续从主库 dump binlog。

假如有三个事务 T1，T2，T3 已经 fsync 到 binlog 文件中，三个事务的在文件中的位点分别是 100，200，300，但是在引擎层的只有 T1 和 T3 完成了 commit 并记录到 redo 中，最后一个 commit 的事务 T3 位点是 300。此时通过备份工具拿到的数据就是这样的状态，备份集启动的时候会走崩溃恢复的流程，prepare 事务被回滚（备份集不会备份 binlog 文件，对应上个小节 xid 集合为空），自位点 300 继续从主库同步binlog并apply，导致 T2 在备库就丢失了。

因此，我们必须设计一种机制来保证Server层的binlog写入顺序和存储引擎层的事务提交顺序保持一致。

问题3：同时写redo和binlog带来的性能下降

问题1中提到每次的事务提交会带来性能问题，而这个问题在引入binlog后会变得更加严重。每个事务提交都会增加一次文件IO，且需要刷盘。如果系统并发比较高，那么这些IO将会成为拖慢整体性能的瓶颈。

解决方案

问题1：Redo log组提交技术

redo组提交技术思想很简单：通过将多个事务redo log的刷盘动作合并，减少刷盘次数。Innodb的日志系统里面，每条redo log都有一个LSN(Log Sequence Number)。事务将日志拷贝到redo log buffer时，都会获取当前大的LSN，且LSN单调递增，因此可以保证不同事务的LSN不会重复。那么假设三个事务Trx1、Trx2、Trx3的日志的大LSN分别为LSN1、LSN2、LSN3（LSN1 < LSN2 < LSN3)，它们同时进行提交，那么如果trx3率先执行提交，它会要求刷盘至LSN3处，这样就顺便将Trx1、Trx2的redo log也刷了，Trx1和Trx2会判断自己的LSN小于当前已落盘的大LSN，就无需再次刷盘。

问题2：内部XA事务

开启binlog情况下，引入内部XA事务来协调上层和存储引擎层，具体来说，在事务提交时引入两个阶段：

prepare：将redo log刷盘操作以确保data页和undo页的更新已经刷新到磁盘，设置事务状态为PREPARE状态；

commit：1). 写binlog并刷盘，2).调用引擎层事务提交接口。将事务状态设置为COMMIT。

如此两阶段提交主要是要保证数据库崩溃时的正确性。因为一旦binlog落盘了，它就可能被下游节点消费。这种事务必须在重启后被commit而非rollback。而对于binlog未落盘的事务，崩溃恢复时直接回滚。

具体来说，故障恢复时，扫描最后一个binlog文件(在flush阶段，如果binlog大小超过阀值，进行rotate binlog文件，会保证该文件记录的最后一个事务一定被提交)，提取其中的xid。重做检查点以后的redo日志，读取事务的undo段信息，搜集处于prepare阶段的事务列表，将事务的xid与binlog中记录的xid对比，若存在，则提交，否则就回滚。

MySQL5.6以前，为了保证数据库binlog的写入顺序和InnoDB层的事务提交顺序一致，MySQL数据库内部使用了prepare_commit_mutex锁。

具体来说，在两阶段提交引擎层 prepare 的时候加锁，在引擎层 commit 之后释放锁：

innobase_xa_prepare()
write() and fsync() binary log
innobase_commit()