深聊MySQL：如何优化数据导入？

叁歲伎倆 2023-09-27 11:17 86阅读 0赞

### 一次插入多行数据 ###

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

连接：30%

向服务器发送查询：20%

解析查询：20%

插入行：10% x 行的大小

插入索引：10% x 索引数

结束：10%

可以发现，大部分时间是消耗在客户端与服务器之间的通信，所以，我们就可以使用insert包含多个值来减少客户端和服务器之间的通信。

为了能更直观地看到效果，我们还是老规矩，上例子，在例子中看效果。

### 准备测试表及数据 ###

创建测试表及写入数据：

/* 使用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() ;

### 导出一条 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

参数解析：

![在这里插入图片描述][51085b0929fc4f95957a75cf5c0d3abb.png]

查看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')......
    ......

### 导出一条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

参数解析：

![图片][a2ea03f3fb3a41f087aa25045296d470.png]

查看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');
    ......

### 导入时间的对比 ###

先导入一条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秒。

### 结论 ###

一次插入多行花费时间0.214秒左右，一次插入一行花费了32.315秒，这样一对比，你的选择，是不是就非常的明确了呢~

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

### 关闭自动提交 ###

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

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 一次提交，也可以大大提升导入速度。

### 总结 ###

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

一次插入的多行值；

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

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

**喜欢软件测试的小伙伴们，如果我的博客对你有帮助、如果你喜欢我的博客内容，请 “点赞” “评论” “收藏” 一 键三连哦！**  
![在这里插入图片描述][e5839dcedca14117aff9fd1945058bd6.gif_pic_center]

[51085b0929fc4f95957a75cf5c0d3abb.png]: https://img-blog.csdnimg.cn/51085b0929fc4f95957a75cf5c0d3abb.png
[a2ea03f3fb3a41f087aa25045296d470.png]: https://img-blog.csdnimg.cn/a2ea03f3fb3a41f087aa25045296d470.png
[e5839dcedca14117aff9fd1945058bd6.gif_pic_center]: https://img-blog.csdnimg.cn/e5839dcedca14117aff9fd1945058bd6.gif#pic_center