数据库批量插入这么讲究的么?
最近新的项目写了不少各种 insertBatch
的代码,一直有人说,批量插入比循环插入效率高很多,那本文就来实验一下,到底是不是真的?
测试环境:
- SpringBoot 2.5
- Mysql 8
- JDK 8
- Docker
首先,多条数据的插入,可选的方案:
foreach
循环插入- 拼接
sql
,一次执行 - 使用批处理功能插入
搭建测试环境`
sql
文件:
应用的配置文件:
启动文件,配置了Mapper
文件扫描的路径:
Mapper
文件一共准备了几个方法,插入单个对象,删除所有对象,拼接插入多个对象:
Mapper.xml
文件如下:
测试的时候,每次操作我们都删除掉所有的数据,保证测试的客观,不受之前的数据影响。
不同的测试
1. foreach 插入
先获取列表,然后每一条数据都执行一次数据库操作,插入数据:
2. 拼接sql插入
其实就是用以下的方式插入数据:
3. 使用Batch批量插入
将MyBatis session
的 executor type
设为 Batch
,使用sqlSessionFactory
将执行方式置为批量,自动提交置为false
,全部插入之后,再一次性提交:
4. 批量处理+分批提交
在批处理的基础上,每1000条数据,先提交一下,也就是分批提交。
初次结果,明显不对?
运行上面的代码,我们可以得到下面的结果,for
循环插入的效率确实很差,拼接的sql
效率相对高一点,看到有些资料说拼接sql
可能会被mysql
限制,但是我执行到1000w
的时候,才看到堆内存溢出。
下面是不正确的结果!!!
插入方式 | 10 | 100 | 1000 | 1w | 10w | 100w | 1000w |
---|---|---|---|---|---|---|---|
for循环插入 | 387 | 1150 | 7907 | 70026 | 635984 | 太久了... | 太久了... |
拼接sql插入 | 308 | 320 | 392 | 838 | 3156 | 24948 | OutOfMemoryError: 堆内存溢出 |
批处理 | 392 | 917 | 5442 | 51647 | 470666 | 太久了... | 太久了... |
批处理 + 分批提交 | 359 | 893 | 5275 | 50270 | 472462 | 太久了... | 太久了... |
拼接sql并没有超过内存
我们看一下mysql
的限制:
这67108864
足足600
多M,太大了,怪不得不会报错,那我们去改改一下它吧,改完重新测试:
- 首先在启动
mysql
的情况下,进入容器内,也可以直接在Docker
桌面版直接点Cli
图标进入:
- 进入
/etc/mysql
目录,去修改my.cnf
文件:
- 先按照
vim
,要不编辑不了文件:
- 修改
my.cnf
- 在最后一行添加
max_allowed_packet=20M
(按i
编辑,编辑完按esc
,输入:wq
退出)
- 退出容器
- 查看
mysql
容器id
- 重启
mysql
重启成功后查看最大的max_allowed_pactet
,发现已经修改成功:
我们再次执行拼接sql
,发现100w
的时候,sql
就达到了3.6M
左右,超过了我们设置的2M
,成功的演示抛出了错误:
org.springframework.dao.TransientDataAccessResourceException:
### Cause: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (36,788,583 > 2,097,152). You can change this value on the server by setting the 'max_allowed_packet' variable.
; Packet for query is too large (36,788,583 > 2,097,152). You can change this value on the server by setting the 'max_allowed_packet' variable.; nested exception is com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (36,788,583 > 2,097,152). You can change this value on the server by setting the 'max_allowed_packet' variable.
批量处理为什么这么慢?
但是,仔细一看就会发现,上面的方式,怎么批处理的时候,并没有展示出优势了,和for
循环没有什么区别?这是对的么?
这肯定是不对的,从官方文档中,我们可以看到它会批量更新,不会每次去创建预处理语句,理论是更快的。
然后我发现我的一个最重要的问题:数据库连接 URL
地址少了rewriteBatchedStatements=true
如果我们不写,MySQL JDBC
驱动在默认情况下会忽视 executeBatch()
语句,我们期望批量执行的一组 sql
语句拆散,但是执行的时候是一条一条地发给 MySQL
数据库,实际上是单条插入,直接造成较低的性能。我说怎么性能和循环去插入数据差不多。
只有将 rewriteBatchedStatements
参数置为 true
, 数据库驱动才会帮我们批量执行 SQL
。
正确的数据库连接:
找到问题之后,我们重新测试批量测试,最终的结果如下:
插入方式 | 10 | 100 | 1000 | 1w | 10w | 100w | 1000w |
---|---|---|---|---|---|---|---|
for循环插入 | 387 | 1150 | 7907 | 70026 | 635984 | 太久了... | 太久了... |
拼接sql插入 | 308 | 320 | 392 | 838 | 3156 | 24948(很可能超过sql长度限制) | OutOfMemoryError: 堆内存溢出 |
批处理(重点) | 333 | 323 | 362 | 636 | 1638 | 8978 | OutOfMemoryError: 堆内存溢出 |
批处理 + 分批提交 | 359 | 313 | 394 | 630 | 2907 | 18631 | OutOfMemoryError: 堆内存溢出 |
从上面的结果来看,确实批处理是要快很多的,当数量级太大的时候,其实都会超过内存溢出的,批处理加上分批提交并没有变快,和批处理差不多,反而变慢了,提交太多次了,拼接sql
的方案在数量比较少的时候其实和批处理相差不大,最差的方案就是for
循环插入数据,这真的特别的耗时。100
条的时候就已经需要1s
了,不能选择这种方案。
一开始发现批处理比较慢的时候,真的挺怀疑自己,后面发现是有一个参数,有一种拨开云雾的感觉,知道得越多,不知道的越多。
【作者简介】:
秦怀,公众号【秦怀杂货店】作者,技术之路不在一时,山高水长,纵使缓慢,驰而不息。
__EOF__
本文链接:https://www.cnblogs.com/Damaer/p/15761188.html
关于博主:评论和私信会在第一时间回复。或者直接私信我。
版权声明:本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!
声援博主:如果您觉得文章对您有帮助,可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力!
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 本地部署 DeepSeek:小白也能轻松搞定!
· 基于DeepSeek R1 满血版大模型的个人知识库,回答都源自对你专属文件的深度学习。
· 在缓慢中沉淀,在挑战中重生!2024个人总结!
· Tinyfox 简易教程-1:Hello World!
· 大人,时代变了! 赶快把自有业务的本地AI“模型”训练起来!