MySQL基础优化(3)：高效导入数据

一次插入多行的值

插入行所需时间有以下因素决定，

1. 连接：30%
2. 向服务器发送查询：20%
3. 解析查询：20%
4. 插入行：10% * 行的大小
5. 插入索引：10% * 索引数
6. 结束：10%

大部分的时间消耗在客户端与服务端的通信过程中，因此可以使用insert包含多个值来减少客户端与非完全之间的通信。

1）文件准备

一条语句含多行数据

use muke;                 /* 使用muke这个database */

drop table if exists t1;  

CREATE TABLE t1 (	      /* 创建表t1 */
  id int(11) NOT NULL AUTO_INCREMENT,
  a varchar(20) DEFAULT NULL,
  b int(20) DEFAULT NULL,
  c datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARSET=utf8mb4 ;

drop procedure if exists insert_t1;
delimiter ;;
create procedure insert_t1()        
begin
  declare i int;                  
  set i=1;                        
  while(i<=10000)do			      
    insert into t1(a,b) values(i,i); 
    set i=i+1;                       
  end while;
end;;
delimiter ;
call insert_t1();               /* 运行存储过程insert_t1 */

数据表创建完成后，导出一个SQL文件，包含多行数据，在shell中调用MySQL的命令行即可，

mysqldump -uroot -p'xxxx' -h127.0.0.1 --set-gtid-purged=off --single-transaction --skip-add-locks  test t1 >t1.sql

命令参数说明，

参数	说明
set-gtid-purged	不添加SET @@GLOBAL.GTID_PURGED，参数含义参考
single-transaction	设置事务隔离级别为RR(会在事务相关笔记中详解)，保证在一个事务中所有相同的查询读到相同数据
skip-add-locks	取消每个表导出之前加lock table的操作

查看导出的t1.sql文件中的内容，

DROP TABLE IF EXISTS `t1`;		
/* 按照上面的备份语句备份的数据文件包含drop命令时，需要特别小心，在后续使用备份文件做导入操作时，应该确定所有表名，防止drop掉业务正在使用的表 */
......
CREATE TABLE `t1`......
......
INSERT INTO `t1` VALUES (1,'1',1,'2019-05-24 15:44:10'),(2,'2',2,'2019-05-24 15:44:10'),(3,'3',3,'2019-05-24 15:44:10')......
......

一条语句含一行数据

mysqldump -uroot -p'xxxx' -h127.0.0.1 --set-gtid-purged=off --single-transaction --skip-add-locks --skip-extended-insert test t1 >t1_row.sql

命令参数说明，

参数	说明
skip-extended-insert	一条SQL包含一行数据的形式导出数据

上面创建的t1_row.sql文件的内容如下，

.....
INSERT INTO `t1` VALUES (1,'1',1,'2019-05-24 15:44:10');
INSERT INTO `t1` VALUES (2,'2',2,'2019-05-24 15:44:10');
INSERT INTO `t1` VALUES (3,'3',3,'2019-05-24 15:44:10');
.....

2）导入时间对比

首先一行一行导入数据，

time mysql -uroot -p'xxxx' -h127.0.0.1 test < t1_row.sql

real	0m31.138s
user	0m0.088s
sys		0m0.126s

耗时31秒。之后测试一次导入多行数据的消耗，

time mysql -uroot -p'xxxx' -h127.0.0.1 test  < t1.sql

real	0m0.230s
user	0m0.007s
sys		0m0.003s

大批量数据导入时，推荐一条insert语句插入多行数据。

关闭自动提交

1）开启与关闭自动提交导入效率对比

autocommit开启时会为每个插入执行提交，可以在InnoDB导入数据时手动关闭。

创建一个sql文件，插入数据的内容与文件t1_row.sql相同，只是在insert命令之前显式地将自动提交关闭，

......
set autocommit = 0;
INSERT INTO `t1` VALUES (1,'1',1,'2019-05-24 15:44:10');
INSERT INTO `t1` VALUES (2,'2',2,'2019-05-24 15:44:10');
INSERT INTO `t1` VALUES (3,'3',3,'2019-05-24 15:44:10');
......
COMMIT;

当关闭了自动提交后，需要通过commit手动提交插入的数据。

测试该文件的数据插入效率，

time mysql -uroot -p'xxxx' -h127.0.0.1 test < no_autocommit.sql

real		0m1.036s
user		0m0.062s
sys		    0m0.108s

与t1_row.sql文件相比，时常大大缩短(自动提交时，耗时31秒，见上一小节)。

导入多条数据时，关闭自动提交，让多条insert一次性提交可以大大提升导入速度。

2）原因分析

与一次插入多行数据提升性能的原因相同，较少客户端与服务端通行时间，减少数据落盘次数。

参数调整

有两个参数影响MySQL的写入速度，

• innodb_flush_log_at_trx_commit
• sync_binlog

1）参数说明

1. innodb_flush_log_at_trx_commit

参数值	含义
0	每一秒钟，主线程将redo log buffer写到操作系统缓存，再刷入磁盘
1	每次事务提交都将redo log buffer写入到系统缓存，再刷到磁盘
2	每次事务提交都将redo log buffer写入到系统缓存，有系统决定何时写入磁盘

redo log相关内容涉及到事务，会在相关笔记中详解。

2. sync_binlog
   控制binlog写入磁盘的时机

参数值	含义
0	binlog落盘由操作系统决定
1	每次事务提交后都会进行一次落盘
n	每n次提交执行一次落盘

binlog相关内容同样涉及到事务，会在之后笔记中详解。

2）写入速度测试

使用压测工具sysbench测试写入速度，测试结果如下，

如果数据库安全性要求不高，可以尝试将两个参数都设置为0。但是考虑到事务安全时，双0的设置是不可取的。具体讨论会在事务笔记中提及。