批量插入mysql的使用场景
在sqlServer里有BulkInsert，可以高效的插入数据。在Java中，我们常用的数据库是mysql，mysql并没有提供类似的功能。我们现在常用的操作数据库的技术是mybatis，但是，如果使用mybatis来大批量插入数据，比如一次插入50万行数据，这样会特别慢，速度是无法接受的。
查了一些资料，发现直接使用JDBC批量插入效率不错，写一些笔记备忘。
样例代码
public void batchInsertGeneratePlanOrder(List<MRPRequirement> requirementList, Long planParamId, String creator) throws Exception {
Connection conn = DriverManager.getConnection(dbUrl, user, password);
//将自动提交关闭
conn.setAutoCommit(false);
PreparedStatement preparedStatement = null;
if (preparedStatement != null) {
preparedStatement.clearParameters();
preparedStatement.clearBatch();
}
String planOrderSql = ".........";
preparedStatement = conn.prepareStatement(planOrderSql);
for (int requirementItemIndex = 0; requirementItemIndex < requirementList.size(); requirementItemIndex++) {
//将sql语句中的参数替换为实际的值
handleParameters(preparedStatement, requirementList.get(requirementItemIndex), planParamId, creator);
preparedStatement.addBatch();
// 分段提交
if (requirementItemIndex % 20000 == 0 && requirementItemIndex != 0) {
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
}
// 提交事务
preparedStatement.executeBatch();
conn.commit();
preparedStatement.close();
conn.close();
}
涉及到mysql的一些参数
- bulk_insert_buffer_size=120M 或者更大
- 将insert语句的长度设为最大。Max_allowed_packet=1M
- Net_buffer_length=512k
- mysql的链接中加入参数：&rewriteBatchedStatements=true
参考链接
https://www.jianshu.com/p/04d3d235cb9f
https://yq.aliyun.com/articles/378296
https://blog.csdn.net/u011277123/article/details/61914773/
http://www.mamicode.com/info-detail-1778588.html
https://blog.csdn.net/zhangjingao/article/details/80331415
https://www.jianshu.com/p/610dac44fb36
https://blog.csdn.net/tolcf/article/details/52102849
https://www.jianshu.com/p/04d3d235cb9f
备注
分段提交的取模值不一定是20000，我这边是尝试了插入50万数据，不同的设置值的对比如下：
批次数量1000 53s
批次数量10000 44s
批次数量500 45s
批次数量20000 40s
暂时选择了每批20000条sql来提交。
