往期热门文章：
0.2秒居然复制了100G文件？ 4 款 MySQL 调优工具，大神都在用！ 近日，项目中有一个耗时较长的 Job 存在 CPU 占用过高的问题，经排查发现，主要时间消耗在往 MyBatis 中批量插入数据。mapper configuration是用 foreach 循环做的，差不多是这样。（由于项目保密，以下代码均为自己手写的demo代码） <insert id="batchInsert" parameterType="java.util.List">
insert into USER (id, name) values
<foreach collection="list" item="model" index="index" separator=",">
(#{model.id}, #{model.name})
</foreach>
</insert>这个方法提升批量插入速度的原理是，将传统的： INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");转化为： INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2"),
("data1", "data2"),
("data1", "data2"),
("data1", "data2"),
("data1", "data2");在 MySql Docs： https://dev.mysql.com/doc/refman/5.6/en/insert-optimization.html中也提到过这个 trick，如果要优化插入速度时，可以将许多小型操作组合到一个大型操作中。理想情况下，这样可以在单个连接中一次性发送许多新行的数据，并将所有索引更新和一致性检查延迟到最后才进行。乍看上去这个 foreach 没有问题，但是经过项目实践发现，当表的列数较多（20+），以及一次性插入的行数较多（5000+）时，整个插入的耗时十分漫长，达到了 14 分钟，这是不能忍的。在[资料] https://stackoverflow.com/questions/19682414/how-can-mysql-insert-millions-records-fast中也提到了一句话：❝ Of course don’t combine ALL of them, if the amount is HUGE. Say you have 1000 rows you need to insert, then don’t do it one at a time. You shouldn’t equally try to have all 1000 rows in a single query. Instead break it into smaller sizes. ❞ 它强调，当插入数量很多时，不能一次性全放在一条语句里。可是为什么不能放在同一条语句里呢？这条语句为什么会耗时这么久呢？我查阅了[资料] https://stackoverflow.com/questions/32649759/using-foreach-to-do-batch-insert-with-mybatis/40608353发现：❝ 「Insert inside Mybatis foreach is not batch」, this is a single (could become giant) SQL statement and that brings drawbacks:
-
some database such as Oracle here does not support. -
in relevant cases: there will be a large number of records to insert and the database configured limit (by default around 2000 parameters per statement) will be hit, and eventually possibly DB stack error if the statement itself become too large.
SqlSession session = sessionFactory.openSession(ExecutorType.BATCH);
for (Model model : list) {
session.insert("insertStatement", model);
}
session.flushStatements();
微信搜索公众号：架构师指南，回复：架构师 领取资料 。
https://blog.csdn.net/wlwlwlwl015/article/details/50246717中可知，默认执行器类型为Simple，会为每个语句创建一个新的预处理语句，也就是创建一个「PreparedStatement」对象。在我们的项目中，会不停地使用批量插入这个方法，而因为MyBatis对于含有<foreach>的语句，无法采用缓存，那么在每次调用方法时，都会重新解析sql语句。<foreach /> element and the statement varies depending on the parameters. As a result, MyBatis has to 1) evaluate the foreach part and 2) parse the statement string to build parameter mapping [1] on every execution of this statement.
And these steps are relatively costly process when the statement string is big and contains many placeholders.
http://blog.harawata.net/2016/04/bulk-insert-multi-row-vs-batch-using.html 可知，耗时就耗在，由于我 foreach 后有 5000+ 个 values，所以这个PreparedStatement 特别长，包含了很多占位符，对于占位符和参数的映射尤其耗时。并且，查阅相关[资料]https://www.red-gate.com/simple-talk/sql/performance/comparing-multiple-rows-insert-vs-single-row-insert-with-three-data-load-methods 可知，values 的增长与所需的解析时间，是呈指数型增长的。
https://stackoverflow.com/questions/7004390/java-batch-insert-into-mysql-very-slow来说，一次性插 20~50 行数量是比较合适的，时间消耗也能接受。<foreach>的方式来插入，可以提升性能的方式。而实际上，MyBatis文档中写批量插入的时候，是推荐使用另外一种方法。（可以看http://www.mybatis.org/mybatis-dynamic-sql/docs/insert.html中 「Batch Insert Support」 标题里的内容）SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
try {
SimpleTableMapper mapper = session.getMapper(SimpleTableMapper.class);
List<SimpleTableRecord> records = getRecordsToInsert(); // not shown
BatchInsert<SimpleTableRecord> batchInsert = insert(records)
.into(simpleTable)
.map(id).toProperty("id")
.map(firstName).toProperty("firstName")
.map(lastName).toProperty("lastName")
.map(birthDate).toProperty("birthDate")
.map(employed).toProperty("employed")
.map(occupation).toProperty("occupation")
.build()
.render(RenderingStrategy.MYBATIS3);
batchInsert.insertStatements().stream().forEach(mapper::insert);
session.commit();
} finally {
session.close();
}
MyBatis session的executor type设为 「Batch」，然后多次执行插入语句。就类似于 JDBC 的下面语句一样。Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?useUnicode=true&characterEncoding=UTF-8&useServerPrepStmts=false&rewriteBatchedStatements=true","root","root");
connection.setAutoCommit(false);
PreparedStatement ps = connection.prepareStatement(
"insert into tb_user (name) values(?)");
for (int i = 0; i < stuNum; i++) {
ps.setString(1,name);
ps.addBatch();
}
ps.executeBatch();
connection.commit();
connection.close();
ExecutorType.BATCH的插入方式，性能显著提升，不到 2s 便能全部插入完成。ExecutorType.BATCH的插入方式，如果非要使用<foreach>的插入的话，需要将每次插入的记录控制在 20~50 左右。参考资料
-
https://dev.mysql.com/doc/refman/5.6/en/insert-optimization.html -
https://stackoverflow.com/questions/19682414/how-can-mysql-insert-millions-records-fast -
https://stackoverflow.com/questions/32649759/using-foreach-to-do-batch-insert-with-mybatis/40608353 -
https://blog.csdn.net/wlwlwlwl015/article/details/50246717 -
http://blog.harawata.net/2016/04/bulk-insert-multi-row-vs-batch-using.html -
https://www.red-gate.com/simple-talk/sql/performance/comparing-multiple-rows-insert-vs-single-row-insert-with-three-data-load-methods -
https://stackoverflow.com/questions/7004390/java-batch-insert-into-mysql-very-slow -
http://www.mybatis.org/mybatis-dynamic-sql/docs/insert.html
关注公众号：Java后端编程，回复下面关键字
要Java学习完整路线，回复 路线
缺Java入门视频，回复： 视频
要Java面试经验，回复 面试
缺Java项目，回复： 项目
进Java粉丝群： 加群
PS：如果觉得我的分享不错，欢迎大家随手点赞、在看。
（完） 加我"微信" 获取一份 最新Java面试题资料
请备注：666，不然不通过～
最近好文
最近面试BAT，整理一份面试资料《Java面试BAT通关手册》，覆盖了Java核心技术、JVM、Java并发、SSM、微服务、数据库、数据结构等等。 获取方式：关注公众号并回复 java 领取，更多内容陆续奉上。 明天见(｡･ω･｡)ﾉ♡ 本篇文章来源于微信公众号:程序IT圈
原创文章，作者：software，如若转载，请注明出处：https://www.sldh123.com/7458.html
