事务日志刷写参数 innodb_flush_log_at_trx_commit=0｜1｜2三个值
其原理如下图
1 此为默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性
0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供更好的性能，但服务器崩溃可能丢失最后一秒的事务
2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些，但操作系统或停电可能导致最后一秒的交易丢失
高并发业务行业最佳实践，是使用第三种折衷配置（=2）：
1.配置为2和配置为0，性能差异并不大，因为将数据从Log Buffer拷贝到OS cache，虽然跨越用户态与内核态，但毕竟只是内存的数据拷贝，速度很快
2.配置为2和配置为0，安全性差异巨大，操作系统崩溃的概率相比MySQL应用程序崩溃的概率，小很多，设置为2，只要操作系统不奔溃，也绝对不会丢数据
那么我们了解了参数innodb_flush_log_at_trx_commit=0｜1｜2
无非是1是最安全的，每条事务commit后都需要立即从事务日志缓冲区log buffer 执行落盘
0是每条事务commit后会继续放到事务日志缓冲区log buffer中，但是每隔一秒就会落盘log buffer中已提交的事务。假如log buffer中存了上千上万个已提交事务，这一秒也要落盘完。
2和0一样，每条事务commit后不会立即落盘，而是放到os buffer缓冲区中，它和0的区别就是：
0将已提交的事务放在log buffer即mysql的在内存的日志缓冲区，而2是将已提交的事务放到os buffer 系统的内存缓冲区，从用户态转向内核态仅此而已。
准备阶段
库下创建数据表testlog 查看初始大小
use helldb;
CREATE TABLE `testlog` (
`id` int NOT NULL AUTO_INCREMENT,
`name` char(10) DEFAULT NULL,
`age` int DEFAULT '20',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8
root@17 hellodb]# ll -h
总用量 992K
-rw-r----- 1 mysql mysql 112K 1月 12 04:59 classes.ibd
-rw-r----- 1 mysql mysql 112K 1月 12 04:59 coc.ibd
-rw-r----- 1 mysql mysql 112K 1月 12 04:59 courses.ibd
-rw-r----- 1 mysql mysql 112K 1月 12 04:59 scores.ibd
-rw-r----- 1 mysql mysql 112K 1月 12 04:59 students.ibd
-rw-r----- 1 mysql mysql 112K 1月 14 14:33 t1.ibd
-rw-r----- 1 mysql mysql 112K 1月 14 16:10 t2.ibd
-rw-r----- 1 mysql mysql 112K 1月 13 18:22 t3.ibd
-rw-r----- 1 mysql mysql 112K 1月 12 04:59 teachers.ibd
-rw-r----- 1 mysql mysql 112K 1月 14 16:57 testlog.ibd #看到只有112k
-rw-r----- 1 mysql mysql 112K 1月 12 04:59 toc.ibd
-rw-r----- 1 mysql mysql 128K 1月 14 10:41 t_user.ibd
定义存储过程 向testlog表中插入十万行数据
delimiter //
create procedure sp_testlog()
begin
declare i int;
set i=1;
while i <= 100000
do insert into testlog(name,age) values(concat("wang",i),i);
set i=i+1;
end while;
end //
delimiter;
(root@localhost) [hellodb]> show create procedure sp_testlog\G;
*************************** 1. row ***************************
Procedure: sp_testlog
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_testlog`()
begin
declare i int;
set i=1;
while i <= 100000
do insert into testlog(name,age) values(concat("wang",i),i);
set i=i+1;
end while;
end
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
验证
innodb_flush_log_at_trx_commit=1级别下十万次事务刷写时长
当我们直接call调用定义好的存储过程（procredure）sp_testlog时，由于自动提交autocommit=1开启，所以mysql认为存储过程中的每条DML语句都是一条事务。这里有十万条事务提交。
在innodb_flush_log_at_trx_commit=1时，每条事务commit都必须立即刷盘，所以10万条事务，会落盘10万次 IO操作。相当慢。
#innodb_flush_log_at_trx_commit=1 每次提交事务都立即将日志从log buffer中刷写到磁盘
(root@localhost) [hellodb]> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
| 1 |
+----------------------------------+
1 row in set (0.00 sec)
#直接call sp_testlog
(root@localhost) [hellodb]> call sp_testlog;
Query OK, 1 row affected (45.36 sec) #用时45秒
#因为我们定义的存储过程 如下
delimiter //
create procedure sp_testlog()
begin
declare i int;
set i=1;
while i <= 100000
do insert into testlog(name,age) values(concat("wang",i),i);
set i=i+1;
end while;
end //
delimiter;
#每条insert在autocommit=1自动提交下都是单独的一个事务。
#所以该存储过程在执行了10万次事务。在innodb_flush_log_at_trx_commit=1级别刷写时长为45秒
#查看ibd文件变大为12M
root@17 hellodb]# ll -h
总用量 13M
-rw-r----- 1 mysql mysql 112K 1月 12 04:59 classes.ibd
-rw-r----- 1 mysql mysql 112K 1月 12 04:59 coc.ibd
-rw-r----- 1 mysql mysql 112K 1月 12 04:59 courses.ibd
-rw-r----- 1 mysql mysql 112K 1月 12 04:59 scores.ibd
-rw-r----- 1 mysql mysql 112K 1月 12 04:59 students.ibd
-rw-r----- 1 mysql mysql 112K 1月 14 14:33 t1.ibd
-rw-r----- 1 mysql mysql 112K 1月 14 16:10 t2.ibd
-rw-r----- 1 mysql mysql 112K 1月 13 18:22 t3.ibd
-rw-r----- 1 mysql mysql 112K 1月 12 04:59 teachers.ibd
-rw-r----- 1 mysql mysql 12M 1月 14 19:03 testlog.ibd
-rw-r----- 1 mysql mysql 112K 1月 12 04:59 toc.ibd
-rw-r----- 1 mysql mysql 128K 1月 14 10:41 t_user.ibd
innodb_flush_log_at_trx_commit=1级别 一次事务执行时长
autocommit=1,但是begin显式开启事务 调用存储过程（procredure）sp_testlog时，mysql认为整个存储过程是一条事务，即使里面执行了10万次insert也是一条事务。所以只有一次事务提交。
在innodb_flush_log_at_trx_commit=1时，每条事务commit都必须立即刷盘，所以1条事务，flush一次 打包把这十万个一次性落盘不用每次一条一条落盘了 很快。
#innodb_flush_log_at_trx_commit=1 每次提交事务都立即将日志从log buffer中刷写到磁盘
(root@localhost) [hellodb]> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
| 1 |
+----------------------------------+
1 row in set (0.00 sec)
(root@localhost) [hellodb]> begin;call sp_testlog;commit;
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (4.01 sec)
Query OK, 0 rows affected (0.49 sec)
#我们看到将存储过程call sp_testlog作为一个事务来说，其内部的十万条insert合起来一个事务的所有操作。
#总共执行时间为4秒 大大小于10万次事务操作
innodb_flush_log_at_trx_commit=0级别 一次事务执行时长
#innodb_flush_log_at_trx_commit=0 事务提交时不会立即刷新到磁盘 每秒从log buffer中刷新已提交的事务到磁盘。如果故障 丢失一秒钟的数据 但是性能因为从log buffer直接刷磁盘 很快
(root@localhost) [hellodb]> begin;call sp_testlog;commit;
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (3.01 sec)
Query OK, 0 rows affected (0.03 sec)
#我们看到将存储过程call sp_testlog作为一个事务来说，其内部的十万条insert合起来一个事务的所有操作。
#总共执行时间为3秒 大大小于10万次事务操作
innodb_flush_log_at_trx_commit=2级别下一次事务执行时长
#innodb_flush_log_at_trx_commit=2 事务提交时会写入os buffer缓冲区，每秒执行一次从os buffer刷新日志记录到到磁盘文件中。 性能比0略差一些，但操作系统或停电可能导致最后一秒的交易丢失
(root@localhost) [hellodb]> begin;call sp_testlog;commit
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (2.97 sec)
Query OK, 0 rows affected (0.05 sec)
通过上面例子，我们明显看到了有事务日志与没有事务日志的区别。
有事务 十万条insert打包统一从内存写入到磁盘。
有事务 级别不一样 速度差不多 安全不一样。
