运行上面的代码,我们可以得到下面的结果,for循环插入的效率确实很差,拼接的sql效率相对高一点,看到有些资料说拼接sql可能会被mysql限制,但是我执行到1000w的时候,才看到堆内存溢出。
下面是不正确的结果!!!
插入方式 10 100 1000 1w 10w 100w 1000wfor循环插入 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重启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循环没有什么区别?这是对的么?
这肯定是不对的,从官方文档中,我们可以看到它会批量更新,不会每次去创建预处理语句,理论是更快的。
然后我发现我的一个最重要的问题:数据库连接 URL 地址少了rewriteBatchedStatements=true
如果我们不写,MySQL JDBC 驱动在默认情况下会忽视 executeBatch() 语句,我们期望批量执行的一组 sql 语句拆散,但是执行的时候是一条一条地发给 MySQL 数据库,实际上是单条插入,直接造成较低的性能。我说怎么性能和循环去插入数据差不多。
只有将 rewriteBatchedStatements 参数置为 true, 数据库驱动才会帮我们批量执行 SQL。