深聊MySQL，从入门到入坟之：如何优化数据导入？
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
参数解析
| 参数 | 解析 |
|---|---|
| -ucarl_dj | 用户名 |
| -p’123456’ | 密码 |
| -h127.0.0.1 | 连接的MySQL服务端IP |
| set-gtid-purged=off | 不添加SET @@GLOBAL.GTID_PURGED |
| –single-transaction | 设置事务的隔离级别为可重复读，即REPEATABLE READ，这样能保证在一个事务中所有相同的查询读取到同样的数据 |
| –skip-add-locks | 取消每个表导出之前加lock tables操作。 |
| 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
参数解析
| 参数 | 解析 |
|---|---|
| –skip-extended-insert | 一条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
结果显示
real 0m0.214s
user 0m0.010s
sys 0m0.007s
可以看到，耗时时间是0.214秒
二、我们再来导入一个SQL只包含一条数据的数据文件：
命令执行
time mysql -ucarl_dj -p'123456' -h127.0.0.1 ClassDJ <d1_row.sql
结果显示
real 0m32.315s
user 0m0.066s
sys 0m0.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
user 0m0.059s
sys 0m0.208s
嗯，看到没，
在没有关闭自动提交， 时间是32.315秒；
关闭自动提交后，时间是2.360秒；
所以：
大批量导入时，关闭自动提交，让多条 insert 一次提交，也可以大大提升导入速度。
3、 总结
所以，提升数据导入速率，可以：
・一次插入的多行值；
・关闭自动提交，多次插入数据的 SQL 一次提交。
总之，找到自己最适合的方法，就是好的 方法。
MySql的学习历程很不简单，所以，要跟得住脚步啊 。
小鱼在重新列举一下，关于MySQL的一些博文。
基本篇：
1.《基本用法总括一》
2.《基本用法总括二》
3.《SQL去重查询数据》
进阶篇
湘公网安备 43102202000103号