点击关注公众号,回复“2T”获取2TB学习资源!
互联网架构师后台回复 2T 有特别礼包
来源:blog.csdn.net/huanghanqian/article/details/83177178
上一篇:短信验证码最佳实践
近日,项目中有一个耗时较长的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");
转化为:搜索公众号互联网架构师回复“2T”,送你一份惊喜礼包。
INSERT INTO `table1` (`field1`, `field2`)
VALUES ("data1", "data2"),
("data1", "data2"),
("data1", "data2"),
("data1", "data2"),
("data1", "data2");
在MySql Docs中也提到过这个trick,如果要优化插入速度时,可以将许多小型操作组合到一个大型操作中。理想情况下,这样可以在单个连接中一次性发送许多新行的数据,并将所有索引更新和一致性检查延迟到最后才进行。
乍看上去这个foreach没有问题,但是经过项目实践发现,当表的列数较多(20+),以及一次性插入的行数较多(5000+)时,整个插入的耗时十分漫长,达到了14分钟,这是不能忍的。在资料中也提到了一句话:
它强调,当插入数量很多时,不能一次性全放在一条语句里。可是为什么不能放在同一条语句里呢?这条语句为什么会耗时这么久呢?我查阅了资料发现:
SqlSession session = sessionFactory.openSession(ExecutorType.BATCH);
for (Model model : list) {
session.insert("insertStatement", model);
}
session.flushStatements();
Unlike default ExecutorType.SIMPLE, the statement will be prepared once and executed for each record to insert.
从资料中可知,默认执行器类型为Simple,会为每个语句创建一个新的预处理语句,也就是创建一个PreparedStatement对象。在我们的项目中,会不停地使用批量插入这个方法,而因为MyBatis对于含有<foreach>
的语句,无法采用缓存,那么在每次调用方法时,都会重新解析sql语句。搜索公众号互联网架构师回复“2T”,送你一份惊喜礼包。
[1] simply put, it is a mapping between placeholders and the parameters.
从上述资料可知,耗时就耗在,由于我foreach后有5000+个values,所以这个PreparedStatement特别长,包含了很多占位符,对于占位符和参数的映射尤其耗时。并且,查阅相关资料可知,values的增长与所需的解析时间,是呈指数型增长的。
![125efb8fd82602f239c113b8b6853071.png](https://i-blog.csdnimg.cn/blog_migrate/89f009ede41d9a2de75d2694abd153b0.png)
所以,如果非要使用 foreach 的方式来进行批量插入的话,可以考虑减少一条 insert 语句中 values 的个数,最好能达到上面曲线的最底部的值,使速度最快。一般按经验来说,一次性插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 便能全部插入完成。
总结一下,如果MyBatis需要进行批量插入,推荐使用 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
http://blog.harawata.net/2016/04/bulk-insert-multi-row-vs-batch-using.html
https://stackoverflow.com/questions/7004390/java-batch-insert-into-mysql-very-slow
http://www.mybatis.org/mybatis-dynamic-sql/docs/insert.html
感谢您的阅读,也欢迎您发表关于这篇文章的任何建议,关注我,技术不迷茫!小编到你上高速。
· END ·
最后,关注公众号互联网架构师,在后台回复:2T,可以获取我整理的 Java 系列面试题和答案,非常齐全。
正文结束
推荐阅读 ↓↓↓
1.不认命,从10年流水线工人,到谷歌上班的程序媛,一位湖南妹子的励志故事
2.深圳一普通中学老师工资单曝光,秒杀程序员,网友:敢问是哪个学校毕业的?