批量执行&rewriteBatchedStatements=true深入理解、深度分析。通过查询mysql日志深入理解

菜鸟fox

已于 2022-08-05 14:04:21 修改

阅读量1.2w

点赞数 21

文章标签： mysql 数据库 mybatis jdbc mybatis-plus

于 2022-01-20 16:38:39 首次发布

本文链接：https://blog.csdn.net/lh155136/article/details/122437056

版权

网上的案例就一篇文章，也不知道谁抄谁的
https://www.codeleading.com/article/6048363902/
https://blog.csdn.net/qq_38361800/article/details/102697824
https://www.jianshu.com/p/0f4b7bc4d22c
要不就是解释不清楚
https://www.cnblogs.com/ad-zhou/p/9092941.html
http://events.jianshu.io/p/1eeb517c6ce7

最后还是自己写吧，先看这篇文章
首先了解一下我们的每一个动作mysql的sql是如何执行的

这篇文章没有分析源码，因为源码的底层也是数据库的日志为最终结果
由于我是用生产数据来做的测试，为了保密，以下字段和数据均做处理，但结果保证真实

URL的参数
url: jdbc:mysql://127.0.0.1:3306/photo?rewriteBatchedStatements=true

批量插入分析

一条insert语句后面拼接的参数个数1000个效率为最佳。
比如一万条数据，如果用一个insert语句后面加一万个参数的速度没有10条insert每条1000个参数快
但是如果一条insert后面的参数低于1000，效率也会慢，因为要提交至少大于一次的事务
就好比抛物线，他有一个峰值，两遍都是降低

url不加rewriteBatchedStatements=true参数的分析如下

1 jdbc的，只分析prepareStatement的

executeBatch的案例

String sql = "insert into table (name, age, sex) values (?, ?, ?)"
Connection conn = commonJdbcServiceImpl.getConn();
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 0; i < 2005; i++) {
     ps.setString(1, "3121022636620626");
     ps.setString(2, "2021-01-30 00:00:00.0");
     ps.setString(3, "31a0007");
     ps.addBatch();
}
ps.executeBatch();

有些杠精会说我可以再values后面自己拼接多个参数。我只想说，有的预编译不用，那就真的太愚蠢了

idea没有打印日志

下面是mysql日志，没有手动提交事务，我的测试数据2005条

2022-01-19T03:17:17.765251Z	249020 Query	insert into table (name, age, sex) values ('小李','18','男')
2022-01-19T03:17:17.800911Z	249020 Query	insert into table (name, age, sex) values ('小李','18','男')
2022-01-19T03:17:17.837039Z	249020 Query	insert into table (name, age, sex) values ('小李','18','男')
......一共有2005行insert

2 mybatis的foreach拼接sql语句，例如

list里面有2005条数数据

aaaMapper.saveBatch(list);

日志如下

insert into table (name, age, sex) values (?, ?, ?),(?, ?, ?),(?, ?, ?)......

一条insert有2005个(?, ?, ?)，然后一堆参数去填充
<==    Updates: 2005

看mysql日志，就一条insert，后面2005个小括号，没有手动提交事务

2022-01-18T07:36:48.366482Z	248874 Query	select @@session.tx_read_only
2022-01-18T07:36:48.428956Z	248874 Query	insert into table (name, age, sex) values ('小李','18','男'),('小李','18','男'),('小李','18','男')...... 

2022-01-18T07:38:18.364000Z	248875 Quit

3 mybatis-plus的saveBatch方法

aaaService.saveBatch(list);

先看mybatis-plus的日志

类似于下面这种

INSERT INTO table (name, age, sex) VALUES ( ?, ?, ?)
然后有1000行参数（==> Parameters: ）去填充，也就是1000行insert

看mysql的日志

2022-01-18T07:09:46.461726Z	248864 Query	SET autocommit=0
2022-01-18T07:09:46.960218Z	248864 Query	select @@session.tx_read_only
2022-01-18T07:09:46.960934Z	248864 Query	INSERT INTO table (name, age, sex) VALUES  ('小李','18','男')
2022-01-18T07:09:46.960934Z	248864 Query	INSERT INTO table (name, age, sex) VALUES  ('小李','18','男')
......
2022-01-18T07:09:47.921155Z	248864 Query	select @@session.tx_read_only
2022-01-18T07:09:47.922676Z	248864 Query	INSERT INTO table (name, age, sex) VALUES  ('小李','18','男')
2022-01-18T07:09:47.922676Z	248864 Query	INSERT INTO table (name, age, sex) VALUES  ('小李','18','男')
......

2022-01-18T07:09:47.923745Z	248864 Query	commit
2022-01-18T07:09:48.071948Z	248864 Query	SET autocommit=1

自动提交改为0
然后多行insert语句
最后提交事务

加上rewriteBatchedStatements=true后

1 jdbc的

代码和上面一样

mysql日志。会帮我们组装成一条insert，values 后面一共2005个小括号。不会分批，但也比多个insert强

2022-01-19T02:58:41.895906Z	249010 Query	select @@session.tx_read_only
2022-01-19T02:58:41.901423Z	249010 Query	insert into table (name, age, sex) values ('小李','18','男'),('小李','18','男'),('小李','18','男')...... 

2022-01-19T02:58:42.161162Z	249004 Quit

2 mybatis的foreach拼接sql语句

mybatis的日志和上面的一样

mysql日志也和上面的一样

3 mybatis-plus的saveBatch方法

mybatis-plus的日志和上面的一样

再看mysql日志

2022-01-18T06:41:47.986185Z	248859 Query	SET autocommit=0
2022-01-18T06:41:59.640821Z	248859 Query	select @@session.tx_read_only
2022-01-18T06:41:59.975773Z	248859 Query	INSERT INTO table (name, age, sex) VALUES  ('小李','18','男'), ('小李','18','男').......

2022-01-18T06:42:12.055502Z	248859 Query	select @@session.tx_read_only
2022-01-18T06:42:12.323399Z	248859 Query	INSERT INTO table (name, age, sex) VALUES  ('小李','18','男'), ('小李','18','男').......

2022-01-18T06:42:13.231933Z	248859 Query	select @@session.tx_read_only
2022-01-18T06:42:13.234253Z	248859 Query	INSERT INTO table (name, age, sex) VALUES  ('小李','18','男'), ('小李','18','男').......

2022-01-18T06:42:13.236109Z	248859 Query	commit
2022-01-18T06:42:13.373313Z	248859 Query	SET autocommit=1

它是先将自动提交改为0
然后将1000条数据组成一批（一个insert语句）来执行
最后提交事务，autocommit改为1

批量更新、删除分析

jdbc的批量更新和删除其实也是executeBatch

foreach的话需要开启多查询（&allowMultiQueries=true），才能拼接多条update语句

mybats-plus里面的批量更新其实是一条一条来更新的

优化最好可以把数据控制再1000条提交一次事务

总结

批量插入，从事务方面总结

url的参数	jdbc	foreach拼接	mybatis-plus
不加	一条insert一条参数	一条insert多条参数（建议1000条参数）	每条insert有1条参数，只提交一次事务
加	一条insert多条参数（建议1000条参数）	一条insert多条参数（建议1000条参数）	每条insert有1000条参数，只提交一次事务

效率总结
数据库和代码都是我本地，基础配置如下
处理器 i7-10510U CPU 1.80GHz
内存16.0 GB
机械硬盘HDD 转速5400转数据传输速率600M/秒

表有30个字段，每次测试都会清空数据，一条insert按1000条参数来测试