1、一次插入多行数据

插入行所需的时间是由以下因素决定的：

・连接：30%

・向服务器发送查询：20%

・解析查询：20%

・插入行：10% x 行的大小

・插入索引：10% x 索引数

・结束：10%

这是引用 参考MySQL 5.7参考手册

可以发现，大部分时间是消耗在客户端与服务器之间的通信，

所以，我们就可以使用insert包含多个值来减少客户端和服务器之间的通信。

为了能更直观的看到效果，

我们还是老规矩，

上例子，在例子中看效果。

1.1 准备测试表及数据

创建测试表及写入数据。

 /* 使用ClassDJ这个database */
use ClassDJ;   
 /* 如果表d1存在则删除表t1 */
drop table if exists d1;
 /* 创建表d1 */
 CREAT TABLE ‘d1’ (
'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 ;
/* 如果存在存储过程insert_d1，则删除 */
drop procedure if exists insert_d1;
delimiter;;
/* 创建存储过程insert_d1 */
create procedure insert_d1()
begin
   /* 声明变量i */
  declare i int;
  /* 设置i的初始值为1 */
  set i = 1;
   /* 对满足i<=1000的值进行while循环 */
  while(i<=1000) do
    /* 写入表d1中a、b两个字段，值都为i当前的值 */
    insert into d1(a.b) vlaue(i,i);   
    set i=i+1;
  end while;
end;;
delimiter;
 /* 运行存储过程insert_d1 */
call insert_d1() ;

创建数据的例子，小鱼在《从入门到入坟，深度理解MySQL之：如何让order by、group by查询速度飞起来》有写过。

没看到的，或者好奇的大佬们，可以去瞅一瞅，瞧一瞧。

1.2 导出一条 SQL 包含多行数据的数据文件

为了获取批量导入数据的 SQL，首先对测试表的数据进行备份，备份的 SQL 为一条 SQL 包含多行数据的形式。

命令执行

mysqldump -ucarl_dj -p'123456' -h127.0.0.1 --set-gtid-purged=off --single-transaction --skip-add-locks  ClassDJ d1 >d1.sql

参数解析

查看d1.sql文件内容

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

1.3 导出一条SQL只包含一行数据的数据文件

命令执行

mysqldump -ucarl_dj -p'123456' -h127.0.0.1 --set-gtid-purged=off --single-transaction --skip-add-locks --skip-extended-insert ClassDJ d1 >d1_row.sql

参数解析

查看d1_row.sql文件内容

......
INSERT INTO `d1` VALUES (1,'1',1,'2020-11-04 03:44:10');
INSERT INTO `d1` VALUES (2,'2',2,'2020-11-04 03:44:10');
INSERT INTO `d1` VALUES (3,'3',3,'2020-11-04 03:44:10');
......

1.4 导入时间的对比

一、先导入一条SQL包含多条数据的的数据文件：

命令执行

time mysql -ucarl_dj -p'123456' -h127.0.0.1 ClassDJ <d1.sql

结果显示

real0m0.214s
user0m0.010s
sys0m0.007s

可以看到，耗时时间是 0.214秒

二、我们再来导入一个SQL只包含一条数据的数据文件：

命令执行

time mysql -ucarl_dj -p'123456' -h127.0.0.1 ClassDJ <d1_row.sql

结果显示

real0m32.315s
user0m0.066s
sys0m0.192s

可以看到， 执行时间是 32.315秒

1.5 结论

一次插入多行花费时间0.214秒左右，

一次插入一行花费了32.315秒，

这样一对比，你的选择，是不是就非常的明确了呢~~

Nice

所以，

如果大批量导入时，记得使用一条insert语句插入多行数据的方式。

2 、关闭自动提交

2.1 对比开启和关闭自动提交的效率

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

命令执行

SET autocommit=0;
INSERT INTO `d1` VALUES (1,'1',1,'2020-11-04 03:44:10');
INSERT INTO `d1` VALUES (2,'2',2,'2020-11-04 03:44:10');
INSERT INTO `d1` VALUES (3,'3',3,'2020-11-04 03:44:10');
......
COMMIT;

这里可以看到，

我们使用的是 d1_row.sql这个文件的内容；

在前后分别追加了两行参数：

前面追加内容：

SET autocommit=0;

最后追加的内容：

COMMIT;

然后呢，

我们再重新导入这个数据文件，看看效率有没有提升。

命令执行

time mysql -ucarl_dj -p'123456' -h127.0.0.1 ClassDJ <d1_row.sql

显示结果

real 0m2.360s
user0m0.059s
sys   0m0.208s

嗯，看到没，

在没有关闭自动提交， 时间是 32.315秒；

关闭自动提交后，时间是 2.360秒；

所以：

大批量导入时，关闭自动提交，让多条 insert 一次提交，也可以大大提升导入速度。

3、 总结

所以，提升数据导入速率，可以：

・一次插入的多行值；

・关闭自动提交，多次插入数据的 SQL 一次提交。

总之，找到自己最适合的方法，就是好的 方法。

关于SQL的进阶篇，可以看：

《深聊MySQL，从入门到入坟之：MySQL竟然也有后悔药！！！》

我是奕然，

     CSDN博客专家；

     51Testing认证讲师

关注我，带你学习更多更专业的测试开发知识。