Mysql 事务日志

Mysql 事务日志

前言

很早之前就想写一篇关于事务日志的博客。因为对于关于事务，大家肯定不会陌生，事务的四大特性也是信手拈来，原子性、隔离性、一致性、持久性。但是对于事务日志可能刚接触的同志，就不会了解那么清楚。

之所以写这篇博客是因为两年前，我一个前辈在做系统的压力测试并优化时，提到了这个不常见的细节。

最开始这个前辈为了提高系统的并发能力，在本地进行压测时发现最高也不过每秒二十并发。然后浑身解数，进行各种提升手段，包括jvm调优、负载均衡、读写分离。似乎并没有见到什么成效。

他断定瓶颈肯定是出现在数据库，而具体哪一步又不得而知，然后一步一步排查，发现系统的代码中用到了大量的事务。并且还发现测试环境的参数也并没有优化，其中就包括“innodb_flush_log_at_trx_commit”

innodb_flush_log_at_trx_commit

主要是用于控制事务日志何时将日志缓存写入到磁盘文件。

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';  

innodb_flush_log_at_trx_commit 一共有三个参数：0，1，2，默认为 1

0：当设置为0的时候，事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。

1：事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。

2：每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk。当设置为2的时候，也是性能最好的，阿里云上面的调优的参数也是设置为2，但是按照Mysql的读写速度，一秒也可能丢失大量的数据。

事务日志又分为：重做日志（redo log）和回滚日志（undo log)

重做日志（redo log)

重做日志（redo log）：通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置。

redo log 他又包含两部分：一个是内存中的缓存日志（redo log buffer），该部分是易丢失的；另一个是磁盘上的日志文件（redo log file）该部分是持久的。

回滚日志（undo log）

回滚日志（undo log）：undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。

回滚日志（undo）他又两个作用，一个是用于回滚，还有一个是用于控制版本（MVCC)。他是一个逻辑文件，记录的是与事务相反的操作。比如：执行一个delete操作，而他记录的就是insert操作。

重做日志（redo log）与二进制日志（bin log)的区别：

1.二进制日志在任何一个引擎中都会产生，而重做日志（redo log)只会在innodb引擎中产生

2.二进制日志记录操作的方法是逻辑性的语句，即便他是基于行格式的记录方式，其本质也是逻辑的SQL设置，如该列每行的记录是多少。而redo log 是在物理格式上的日志，他记录数据库中每个页的修改

3.二进制文件和重做日志的写入顺序不同，二进制文件（binlog）只在事务提交时，便将事务写入到日志文件中，而重做日志（redo log)在数据准备修改前写入缓存中的redo log中，然后在对缓存中的数据执行修改操作。而且保证每个在发出事务提交指令时，先向缓存中的redo log 写入日志，写完之后在执行提交操作

4.事务日志记录的是物理页的情况，它具有幂等性，因此记录日志的方式极其简练。幂等性的意思是多次操作前后状态是一样的，例如新插入一行后又删除该行，前后状态没有变化。而二进制日志记录的是所有影响数据的操作，记录的内容较多。例如插入一行记录一次，删除该行又记录一次。

sync_binlog

MySQL提供一个sync_binlog参数来控制数据库的binlog刷到磁盘上去。

默认，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。

如果sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，同样很大的影响MySQL和IO性能。虽然可以通过group commit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

所以很多MySQL DBA设置的sync_binlog并不是最安全的1，而是100或者是0。这样牺牲一定的一致性，可以获得更高的并发和性能。在阿里云上，sync_binlog 优化值为 500 。

尾声

那位前辈优化后innodb_flush_log_at_trx_commit的值后，性能提升了20倍，从原来的每秒20并发；提升到了每秒200并发。虽然说优化后的值，性能方面有了飞速提升，但其实任何事都有利和有弊。刚刚说到了，容易造成1秒的数据丢失，再次结合Mysql的读写速度，呀。细思极恐。…

说了那么多，最后祝大家1024节日快乐，愿生活多点美好，愿工作少点bug。

也以此篇博客，纪念、感恩那些在修复bug道路上帮助我的前辈们。

借鉴于：
https://www.cnblogs.com/f-ck-need-u/p/9010872.html#auto_id_1 https://www.cnblogs.com/xuxubaobao/p/10839979.html