[Mysql]过大sql文件导入过慢问题解决

控制 MySQL 磁盘写入策略 以及 数据安全性 的两个关键参数： innodb_flush_log_at_trx_commit 和 sync_binlog

参数：innodb_flush_log_at_trx_commit

①如果设置为0，log buffer将每秒一次地写入log file中，并且同时进行log file的flush(刷新到磁盘中)操作。此模式下，在事务提交时，不主动触发写入磁盘的操作；

②如果设置为1，此模式下，在每次事务提交时，mysql都会把log buffer的数据写入log file中，并且同时进行log file的flush(刷新到磁盘中)操作；

③如果设置为2，此模式下，在每次事务提交时，mysql都会把log buffer的数据写入log file中，但是不会同时进行log file的flush(刷新到磁盘中)操作，会每秒执行一次log file的flush(刷新到磁盘中)操作。

注意：由于进程调度策略问题，不能保证"每秒执行一次flush(刷新到磁盘中)操作"100%的"每一秒执行一次"。

 

参数：sync_binlog

①如果设置N=0，像操作系统刷新其他文件的机制一样，mysql不会同步到磁盘中去，而是依赖操作系统来刷新binary log。

②如果设置N>0，mysql在每写N次二进制日志binary log时，会调用fdatasync()函数将二进制日志binary log同步到磁盘中去。

注意：如果启用了autocommit，那么每一个语句statement就会有一次写操作；否则每个事务对应一个写操作。

如图：

 

性能：

①测试场景1：

innodb_flush_log_at_trx_commit=2
sync_binlog=1000

②测试场景2：

innodb_flush_log_at_trx_commit=1
sync_binlog=1000

③测试场景3：

innodb_flush_log_at_trx_commit=1
sync_binlog=1

④测试场景4：

innodb_flush_log_at_trx_commit=1
sync_binlog=1000

⑤测试场景5：

innodb_flush_log_at_trx_commit=2
sync_binlog=1000

 

innodb_flush_log_at_trx_commit	sync_binlog	TPS
1000	2	41000
1000	1	33000
1	1	26000
1000	1	33000

 

 

 

 

由此可见：

Ⅰ、当innodb_flush_log_at_trx_commit=1和sync_binlog=1时，写入操作性能最差；

Ⅱ、当innodb_flush_log_at_trx_commit=2和sync_binlog=2时，写入操作达到最高性能；

 

安全：

①当innodb_flush_log_at_trx_commit设置为0时，mysqld进程崩溃会导致上一秒所有事务数据丢失。

②当innodb_flush_log_at_trx_commit和sync_binlog都为1时最为安全，mysqld进程崩溃或者服务器crash的情况下，binary log只有可能最多丢失一个事务。

③当innodb_flush_log_at_trx_commit设置为2时，只有在服务器崩溃或断电情况下，上一秒所有事务数据才可能丢失。