MySQL插入一条数据竟然耗时100ms


数据引擎为innodb,数据量并没有多大,理论上插入一条数据也就几ms。磁盘IO、内存占用以及cpu使用都没有问题。所以想到是某个参数设置不当导致。查资料,发现可以设置一个参数

innodb_flush_log_at_trx_commit = 2

插入速度立马变快。  这个参数有什么意义?

该参数取值可以是0,1,2。

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 或者是主机断电后可能丢失的数据量就完全取决于文件系统了。