当前位置：网站首页 > 技术教程 > 正文

加速mysql批量数据插入（mysql批量加载）

mhr18 2024-09-13 20:40 38 浏览 0 评论

问题描述：

由于测试需要，需要将生产库的一些数据导入到测试库进行开发测试。一些表的数据量有些多，达到6W+条，从生产库导出的文件达到10.6M，内容是每行一条插入语句（INSERT INTO TableName VALUES(…);）。

将这个导出的.sql文件通过工具载入到测试库后，点击了“运行”，只见信息栏一条条SQL语句“疯狂”的执行着，心想，应该很快应该执行完了吧，但是等了好一会儿，见到信息栏还在一条条努力的输出着插入的消息。

仔细看了下控制台，发现平均每条语句执行0.03s以上，多的甚至达到了0.09s，算了一下，如果按每条0.03s的速度执行，插入完这批数据一共需要花费0.03s/条*66176条=1985.28s，也就是大概33.088分钟！

这个时候，SQL已经执行了993.598s，也就是16多分钟，还要再执行16多分钟，才能将66176条数据插入完毕，这显然是不能忍的！

查询时间

分析解决尝试中。。。

为什么会这么慢呢？后来分析了一下，每个insert语句，大体都要经过发送数据到服务器端、解析SQL、预处理、优化SQL执行计划等步骤，有没有哪个步骤是可以优化或减少的呢？

顺着这个思路，后来把SQL语句改造了一下：

SQL改造

只保留一个INSERT INTO tableName，剩下的改为逗号相隔。这样做的好处，一是减少文件大小（改造前.sql文件10.6M，改造后，文件大小为8.09M），减少发送到服务器的时间；二是减少SQL语句多余的解析操作，改造后，只需要解析一次就可以进行数据插入操作。

后来通过工具的“状态”栏也可以证明这一点：

改造前后对比

状态

在寻求解决方案过程中，曾想到多线程，想找到一种像oracle中的并行操作技术parallel

insert /*+parallel(t 16)*/ into tableName(...)

但是没有找到，但无意中发现了一种更快的方法：加上事务！

START TRANSACTION;
INSERT INTO TABLE_NAME VALUES(...), (...), (...), ...;
COMMIT;

原因是：在进行insert操作时，mysql内部会建立一个事务，在事务内进行真正的处理。主动加上事务语句，可以减少创建事务的操作，减少执行时间。

通过这种方法，我做了下尝试，结果如下：

执行结果

相比于一条条插入语句，效率提高了100多倍！

总结：

通过改造SQL语句可以减少网络传输IO，减少解析时间，提高批量插入速度。通过主动创建事务，进一步减少执行时间。
这里需要注意的是，发送到服务器端的数据大小是有默认限制的，有的是1M，有的是4M，可以通过改动max_allowed_packet配置跳过该限制：
方法一（具有linux权限）：改动服务器的mysql配置文件my.cnf，增加： max_allowed_packet=20M, 然后重启mysql服务
方法二（具有mysql修改配置权限）：执行SQL语句，set global max_allowed_packet=20*1024*1024

oracle批量插入数据

上一篇：【实战】批量插入数据，如何优化?
下一篇：MyBatis中批量插入、删除写法(MySql、Oracle)

加速mysql批量数据插入（mysql批量加载）

问题描述：

分析解决尝试中。。。

总结：

相关推荐

取消回复欢迎你发表评论:

生产系统数据库操作规范(2):（数据库生产库是什么意思）

Redis客户端 Jedis 与 Lettuce

高并发架构系列:Redis并发竞争key的解决方案详解

redis如何防止并发（redis如何防止高并发）

开源推荐:如何实现的一个高性能 Redis 服务器

redis安装与调优部署文档(WinServer)

Redis 入门 - 安装最全讲解(Windows、Linux、Docker)

一文带你了解 Redis 的发布与订阅的底层原理

Redis如何应对并发访问（redis控制并发量）

Java SE Development Kit 8u441下载地址【windows版本】

加速mysql批量数据插入（mysql批量加载）

问题描述：

分析解决尝试中。。。

总结：

相关推荐

取消回复欢迎 你 发表评论:

生产系统数据库操作规范(2):（数据库生产库是什么意思）

Redis客户端 Jedis 与 Lettuce

高并发架构系列:Redis并发竞争key的解决方案详解

redis如何防止并发（redis如何防止高并发）

开源推荐:如何实现的一个高性能 Redis 服务器

redis安装与调优部署文档(WinServer)

Redis 入门 - 安装最全讲解(Windows、Linux、Docker)

一文带你了解 Redis 的发布与订阅的底层原理

Redis如何应对并发访问（redis控制并发量）

Java SE Development Kit 8u441下载地址【windows版本】

取消回复欢迎你发表评论: