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

已于 2022-08-05 14:04:21 修改
阅读量1.3w 收藏 56
点赞数 21
文章标签： mysql 数据库 mybatis jdbc mybatis-plus
于 2022-01-20 16:38:39 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：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的参数jdbcforeach拼接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条参数来测试

数量/大小jdbc不加参数jdbc加foreach加不加一样mybatis-plus不加mybatis-plus加
1万/3.53MB420秒2秒4秒5秒5秒
10万/32.58MB没测17秒30秒35秒20秒
100万/276.84MB没测266秒455秒330秒180秒

实际写入速度请以自己电脑或服务器配置为准。我这个测试没有网络消耗，正常生产环境是有网络消耗的

一万以下
jdbc不加 < mybatis-plus不加 < mybatis-plus加 < foreach < jdbc加

一万到十万
jdbc不加 < mybatis-plus不加 < foreach < mybatis-plus加 < jdbc加

十万到-一百万
jdbc不加 < mybatis-plus不加 < jdbc加 < foreach < mybatis-plus加

mybatis-plus加参数比其他情况还是有所提升的

过了好几个月补的

为了贴近实际，测试了一个有网络消耗的

数据库在内网服务器
java代码再我本地，我连上WiFi来连接数据库

50个字段

数量/大小foreach加不加一样mybatis-plus不加mybatis-plus加
1千/0.33MB1秒15秒0.8秒
1万/3.53MB4秒150秒5秒

mybatis-plus不加 比 foreach |mybatis-plus加 慢15到30倍

菜鸟fox
关注
MybatisPlus rewriteBatchedStatements=true 批量插入失效，依然是单条插入问题解决
xzh_blog
09-26 3967
默认情况下，MybatisPlus的批量插入saveBatch方法实际上是for循环单条插入。如果想要实现真实的批量插入，需要在spring.datasource.url配置中加。
[性能优化之批处理系列：杂]批处理 rewriteBatchedStatements=true和useCursorFetch=true
qq_35789269的博客
11-20 1811
12
MySQL插入性能之参数 rewriteBatchedStatements=true
小单的博客专栏
03-05 8445
提高MySQL插入性能的方法大概分为3类： 1、调整MySQL相关参数 2、插入的时候对事物进行控制，不要每条提交一次 3、减少程序提交SQL给MySQL的频率，不要每次提交一条 本文针对第三点增加对应的参数，效果立竿见影，连接数据库参数 rewriteBatchedStatements=true。 例如：jdbc:mysql://127.0.0.1:3306/shanhy?rewriteBat...
达梦数据库+JPA+Springboot 报错 ：无效的列名
最新发布
Champion-Dai
09-04 783
国产达梦数据库适配，问题解决
批处理 rewriteBatchedStatements=true
GSON的博客
09-15 1662
最近在优化大批量数据插入的性能问题。 项目原来使用的大批量数据插入方法是Mybatis的foreach拼接SQL的方法。 我发现不管改成Mybatis Batch提交或者原生JDBC Batch的方法都不起作用，实际上在插入的时候仍然是一条条记录的插，速度远不如原来Mybatis的foreach拼接SQL的方法。这对于常理来说是非常不科学的。 下面先罗列一下三种插入方式： public class NotifyRecordDaoTest extends BaseTest { @Resourc
jdbc批量操作之rewriteBatchedStatements=true
真是适合睡觉的天气
10-23 3970
作者：晚歌y 链接：https://www.jianshu.com/p/0f4b7bc4d22c 最近在优化大批量数据插入的性能问题。 项目原来使用的大批量数据插入方法是Mybatis的foreach拼接SQL的方法。 我发现不管改成Mybatis Batch提交或者原生JDBC Batch的方法都不起作用，实际上在插入的时候仍然是一条条记录的插，速度远不如原来Mybatis的foreach拼接...
MySQL 慢查询日志导入 Elasticsearch 可视化查询分析
qhh0205
10-24 3225
当应用程序后台 SQL 查询慢的时候我们一般第一时间会查看数据库慢查询记录，但是慢查询记录是原始文本，直接查询搜索分析比较费时费力，虽然业界有针对 MySQL 慢查询分析的命令行工具（比如：pt-query-digest），但是使用起来还是不够方便，而且分析结果也是针对整个实例的大概统计，不能及时定位到某个应用（库.表）的慢查询。出于这个目的我们可以将 MySQL 原始慢查询日志结构化导入 Ela...
Mysql默认配置导致Mybatis-plus批处理性能受限
a1290320893的博客
12-02 1553
结论： 1、默认情况下，Mysql驱动在在默认情况下会无视executeBatch()语句；把我们期望批量执行的一组sql语句拆散，一条一条地发给MySQL数据库，批量插入实际上是单条插入，直接造成较低的性能。 2、MySQL的JDBC连接的url中要加&rewriteBatchedStatements=true参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入，另外这个选项对INSERT/UPDATE/DELETE都有效。 url: jdbc:mysql://ip地址:3306/em
allowMultiQueries 设置为true不生效问题
攻防人生3722的博客
09-08 9737
因为之前项目在啊做分页查询时用到allowMultiQueries，在一个select标签下一次执行两次查询，一次查数据，一次查总数，避免了多次查询操作 这次在设置了allowMultiQueries=true 的情况下，始终得不到两个查询的结果，每次只有一个查询的结果。 研究了很久，最后发现是因为版本问题。而且测试方法不正确。 使用mysql版本5.7，mysql-connector-java版本原来是5.1.6 后来改为5.1.45才可以。 <dependency> <g
MyBatis Plus中的批量插入：通过开启rewriteBatchedStatements=true
jakelihua
07-31 2952
在MyBatis Plus框架中，批量插入是一种高效的数据库操作方式。通过开启，可以获得许多优点，从而提高数据库插入性能、减轻负载以及简化代码。
使用rewriteBatchedStatements属性优化Mybatis-Plus批量插入数据
mcband的博客
07-04 4543
由于项目是使用MyBatis-Plus开发的，用起来也确实比较方便，尤其是service层封装好的一些通用的增删改查方法，省去了不少sql语句的书写，但是在开发过程中，我也发现MyBatis-Plus的saveBatch批量插入方法针对MySQL数据库可能会出现效率贼低的情况，通过不断查询资料发现可以通过加rewriteBatchedStatements参数来解决(保证5.1.13以上版本)。
介绍MySQL Jdbc驱动的rewriteBatchedStatements参数
tonyruiyu123的博客
12-28 347
(转) http://www.cnblogs.com/chenjianjx/archive/2012/08/14/2637914.html 感谢作者分享。 介绍MySQL Jdbc驱动的rewriteBatchedStatements参数 MySQL Jdbc驱动在默认情况下会无视executeBatch()语句，把我们期望批量执行的一组sql语句拆散，一条一条地发给M...
Java mysql batch 提速 "rewriteBatchedStatements=true"
LionBule
06-20 628
Mysql jdbc 批处理数据，需要给jdbc连接加上rewriteBatchedStatements=true。默认jdbc是不会开启批处理。 例如：jdbc:mysql://10.250.4.98:3306/dbname?rewriteBatchedStatements=true   Java UpdateBatchSample： http://www.java2s.com/Code...
jdbc 使用rewriteBatchedStatements=true后，报错
m0_37528968的博客
08-18 2813
rewriteBatchedStatements=true是一个配置选项，它影响MySQL JDBC驱动程序的行为。JDBC是Java数据库连接的标准。当你使用Java程序连接MySQL数据库时，你需要使用这个驱动程序。rewriteBatchedStatements=true的意思是，当你在Java程序中使用批量插入（batching）时，MySQL JDBC驱动程序将尝试重新编写（rewrite）你的SQL语句，以便更有效地执行这些批量插入操作。
数据库批处理rewriteBatchedStatements=true
s_ohygvhj的博客
03-09 362
https://www.jianshu.com/p/0f4b7bc4d22c 感谢原文作者
批量插入rewriteBatchedStatements=true
huangpf_vcx
12-01 1986
今天写导入数据涉及到批量插入的地方使用mybatisPlus saveBatch，结果执行的时候大于sql语句发现是一条一条插入的。 查阅资料发现 MySQL JDBC驱动在默认情况下会无视executeBatch()语句，把我们期望批量执行的一组sql语句拆散，一条一条地发给MySQL数据库，批量插入实际上是单条插入，直接造成较低的性能。 只有把rewriteBatchedStatements参数置为true, 驱动才会帮你批量执行SQL 另外这个选项对INSERT/UPDATE/DELETE都有效
mybatis-plus优化-rewriteBatchedStatements飞速提升
常有理爱摸鱼
10-08 5463
深入了解rewriteBatchedStatements优化过程
rewriteBatchedStatements
05-02
rewriteBatchedStatements 是一个 JDBC 配置参数，用于开启或关闭批量更新语句的重写优化。当开启该参数时，JDBC 驱动会将多个更新语句重写为一个批量更新语句，从而减少与数据库的通信次数，提高数据更新效率。这个优化在大量数据批量更新时特别有效，可以显著提升性能。但是在部分情况下，由于语句重写可能会改变 SQL 语句的语义，因此开启该选项需要谨慎。
评论 8
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值