数据库批量插入这么讲究的么? (2)

运行上面的代码,我们可以得到下面的结果,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的限制:

mysql> show VARIABLES like '%max_allowed_packet%'; +---------------------------+------------+ | Variable_name | Value | +---------------------------+------------+ | max_allowed_packet | 67108864 | | mysqlx_max_allowed_packet | 67108864 | | slave_max_allowed_packet | 1073741824 | +---------------------------+------------+ 3 rows in set (0.12 sec)

这67108864足足600多M,太大了,怪不得不会报错,那我们去改改一下它吧,改完重新测试:

首先在启动mysql的情况下,进入容器内,也可以直接在Docker桌面版直接点Cli图标进入:

docker exec -it mysql bash

进入/etc/mysql目录,去修改my.cnf文件:

cd /etc/mysql

先按照vim,要不编辑不了文件:

apt-get update apt-get install vim

修改my.cnf

vim my.cnf

在最后一行添加max_allowed_packet=20M(按i编辑,编辑完按esc,输入:wq退出)

[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql secure-file-priv= NULL # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Custom config should go here !includedir /etc/mysql/conf.d/ max_allowed_packet=2M

退出容器

# exit

查看mysql容器id

docker ps -a

image-20211130005909539

重启mysql

docker restart c178e8998e68

重启成功后查看最大的max_allowed_pactet,发现已经修改成功:

mysql> show VARIABLES like '%max_allowed_packet%'; +---------------------------+------------+ | Variable_name | Value | +---------------------------+------------+ | max_allowed_packet | 2097152 | | mysqlx_max_allowed_packet | 67108864 | | slave_max_allowed_packet | 1073741824 | +---------------------------+------------+

我们再次执行拼接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循环没有什么区别?这是对的么?

这肯定是不对的,从官方文档中,我们可以看到它会批量更新,不会每次去创建预处理语句,理论是更快的。

image-20211130011820487

然后我发现我的一个最重要的问题:数据库连接 URL 地址少了rewriteBatchedStatements=true

如果我们不写,MySQL JDBC 驱动在默认情况下会忽视 executeBatch() 语句,我们期望批量执行的一组 sql 语句拆散,但是执行的时候是一条一条地发给 MySQL 数据库,实际上是单条插入,直接造成较低的性能。我说怎么性能和循环去插入数据差不多。

只有将 rewriteBatchedStatements 参数置为 true, 数据库驱动才会帮我们批量执行 SQL。

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/zwxzyj.html