MYSQL事务日志的优化

事务日志定义

事务日志，transaction log，它的日志分为两种：

• redo log：实现写前日志， WAL（Write Ahead Log) ，数据更新前先记录redo log
• undo log：保存与执行的操作相反的操作，用于实现回滚，rollback

相关配置

查看InnoDB事务日志的相关变量

show variables like 'innodb_log%';

innodb_log_file_size #每个日志文件的大小，适当调大可以节约I/O
innodb_log_files_in_group #日志文件数量，适当调大可以节约I/O
innodb_log_group_home_dir #存放日志文件的路径
innodb_log_buffer_size #缓存大小

与事务日志性能相关的重要配置

事务日志写磁盘的方式，有三种可设置的值0、1、2，默认为1。

SET GLOBAL innodb_flush_log_at_trx_commit=0|1|2

当我们在表中插入一条记录，这条数据只有可能出现在两个地方：内存（上图浅黄色区域）或者磁盘（蓝色区域）。内存分为三块空间，buffer pool（黄色）命令的缓冲区、log buffer 日志的缓冲区、OS cache/buffer 操作系统内核的缓冲区。前两块空间属于MYSQL，后者属于操作系统。

三种模式说明：

0

每次提交事务，先写入MYSQL的缓冲区，然后每隔1秒写入操作系统的缓冲区和磁盘。节省I/O。

1

每次提交事务，都会写入操作系统的缓冲区并且立即写磁盘。提交100次事务就等于写了100次磁盘，I/O次数多，但是安全性相相对较高。如果追求高安全性，建议选1。

2

每次提交事务，都会写入操作系统缓冲区，然后每隔1秒写入磁盘。比0更安全，比1节省I/O。高并发业务，追求性能的同时想兼顾安全，建议选2。

本站使用的是2

修改方法

先登录数据库

然后执行以下命令

SET GLOBAL innodb_flush_log_at_trx_commit=2;