mysql innodb_flush _log_at_trx_commit

前言

      今天去跟师兄去某一国企帮助他们优化他们的SQL。由于现在国家大力倡导国产数据库的使用，所以很多的国企都不在使用Oracle，转为国内自主研发的数据库，基本都是以mysql为基础改的。。学到了不少东西。其中innodb_flush_log_at_trx_commit这个参数让我印象深刻

正文

今天碰到了一个insert innodb表相当慢的问题，随意本来就应该很慢但是不应该那么慢。。。。涉及到今天题目中的参数

首先讲解一下这个参数的作用，这个参数可以有三个值。默认为1

•  1 表示当一个事物提交，立即将log buffer的内容写入log file并flush到磁盘上即立即发生日志写盘，这是保证innodb ACID的一种手段，数据不丢失
•  0 表示log buffer的内容每innodb_flush_log_at_timeout 秒写入log file并flush到磁盘上，也就是说如果这个时候mysql crash了，那么会丢失一秒的数据
•  2 表示log buffer的内容在一个事物提交后写入log file,没innodb_flush_log_at_timeout 秒 flush到磁盘上，也就是说如果这个时候mysql crash了，那么 应该会丢失一个事务

上面的描述可能有点迷惑，既然都写入log file了为何还要flush到磁盘？？这是因为innodb实现采用的是aio即异步IO，所以这里的写入并不是真正的写入磁盘而是仅仅写入了VFS的缓存（可以暂时这样理解），只有调用flush的时候（对应fsync() fdatasync()系统调用）才是真正的写入到的磁盘上。

innodb_flush_log_at_timeout默认为1

innodb_flush_log_at_trx_commit默认的为1是为了保证innodb的ACID，如果数据的丢失是允许的设置为0或者2 会令insert 操作更快，下面看个例子体验。

准备一个表t1 只有两列就行c1,c2都是int类型，表结构如下：

准备一个插入数据的存储过程，这里插入的数据为1000行

mysql> create procedure p_data()
    -> begin
    -> declare i int;
    -> set i=0;
    -> while i<1000
    -> do
    -> insert into t1 values(i,i);
    -> set i=i+1;
    -> end while;
    -> end /

下面看一下这个参数的影响

当innodb_flush_log_at_trx_commit为1时 1000条数据的插入需要38.71 sec 如果对其进行show profile for query可以看到耗时都发生query end阶段，这个阶段包含写盘操作

当innodb_flush_log_at_trx_commit为0时 1000条数据的插入仅需要0.18 sec 这时间差距。。。。   

总结一下

如果业务对于数据的1s丢失是不可接受的那么也不要管什么效率不效率的了 直接默认值吧，或者直接上Oracle吧。

如果可以忍受，那么如果业务的插入操作比较频繁的话，或者已经成为了性能瓶颈的话可以考虑一下这个参数的调整。