Innodb参数innodb_flush_log_at_trx_commit详解

首先我们继续上回的分析。当时那么InnoDB那么慢，原来是’innodb_flush_log_at_trx_commit’设置成1.

mysql> SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

---

先对它的值做个分析
innodb_flush_log_at_trx_commit = 0

Innodb 中的Log Thread 没隔1 秒钟会将log buffer中的数据写入到文件，同时还会通知文件系统进行文件同步的flush
操作，保证数据确实已经写入到磁盘上面的物理文件。但是，每次事务的结束（commit 或者是rollback）并不会触发Log Thread
将log buffer 中的数据写入文件。所以，当设置为0 的时候，当MySQL Crash 和OS Crash
或者主机断电之后，最极端的情况是丢失1 秒时间的数据变更。

innodb_flush_log_at_trx_commit = 1

这也是Innodb 的默认设置。我们每次事务的结束都会触发Log Thread 将log buffer
中的数据写入文件并通知文件系统同步文件。**这个设置是最安全的设置，能够保证不论是MySQL Crash 还是OS Crash
或者是主机断电都不会丢失任何已经提交的数据。**

innodb_flush_log_at_trx_commit = 2

当我们设置为2 的时候，Log Thread
会在我们每次事务结束的时候将数据写入事务日志，但是这里的写入仅仅是调用了文件系统的文件写入操作。而我们的文件系统都是有缓存机制的，所以Log
Thread
的这个写入并不能保证内容真的已经写入到物理磁盘上面完成持久化的动作。文件系统什么时候会将缓存中的这个数据同步到物理磁盘文件Log
Thread 就完全不知道了。所以，当设置为2 的时候，MySQL Crash 并不会造成数据的丢失，但是OS Crash
或者是主机断电后可能丢失的数据量就完全控制在文件系统上了。各种文件系统对于自己缓存的刷新机制各不一样，大家可以自行参阅相关的手册。

根据上面三种参数值的说明，0的时候，如果mysql crash可能会丢失数据，可靠性不高。我们着重测试1和2两种情况。1的时候会影响数据库写入性能，相对2而言写入速度会慢。这只能根据实际情况来决定吧。

图解：
首先需要大致了解一下mysql日志操作步骤：
log_buff —mysql写 (write)—> log_file —OS刷新 (flush)—> disk

---

---

继续按上回的php测试下时间：

innodb_flush_log_at_trx_commit	执行写入10000条数据的时间(毫秒)
0	1.4132061004639
1	104.06923484802
2	1.5720040798187

总结：
这执行的时间真是相差太大了。当innodb_flush_log_at_trx_commit 设置为0或2时，写入的速度和MyISAM的时间1.298492193222 基本接近。但设置为1时，时间差距那么大，主要是因为I/O操作是实时进行的，这部分消耗时间较多。