SpringBoot项目中使用Mybatis批量插入百万条数据
背景:因为一些业务问题,需要做多数据源,多库批量查询、插入操作,所以就研究了一下。今天先整理记录一下批量插入的过程。
一般项目中常用三种方式向数据库插入数据,单条数据插入、mybatis中foreach插入、批处理插入,这三种各有不同。在数据量小的情况下区别不大。需要注意的是foreach插入的方式,参数最多2100条。
话不多说,直接上代码,测试原生批处理的效率
首先定义一个工具类,方便我们在其他地方使用批处理,实现复用
package com.databases.utils;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.support.TransactionSynchronizationManager;
import java.util.List;
import java.util.function.BiFunction;
/**
* @CreatTime: 2022/7/27 10:53
*/
@Component
public class MybatisBatchUtils {
/**
* 每次处理1000条
*/
private static final int BATCH_SIZE = 1000;
@Autowired
private SqlSessionFactory sqlSessionFactory;
/**
* 批量处理修改或者插入
*
* @param data 需要被处理的数据
* @param mapperClass Mybatis的Mapper类
* @param function 自定义处理逻辑
* @return int 影响的总行数
*/
public <T,U,R> int batchUpdateOrInsert(List<T> data, Class<U> mapperClass, BiFunction<T,U,R> function) {
int i = 1;
SqlSession batchSqlSession = sqlSessionFactory.openSession();
batchSqlSession.getConfiguration().setDefaultExecutorType(ExecutorType.BATCH);
try {
U mapper = batchSqlSession.getMapper(mapperClass);
int size = data.size();
for (T element : data) {
function.apply(element,mapper);
if ((i % BATCH_SIZE == 0) || i == size) {
System.out.println(batchSqlSession.flushStatements());
}
i++;
}
// 非事务环境下强制commit,事务情况下该commit相当于无效
batchSqlSession.commit(!TransactionSynchronizationManager.isSynchronizationActive());
} catch (Exception e) {
batchSqlSession.rollback();
throw new RuntimeException(e);
} finally {
batchSqlSession.close();
}
return i - 1;
}
}
mapper文件中的sql用的mybatis-generator直接生成的,自己写也是OK的
/**
* This method was generated by MyBatis Generator.
* This method corresponds to the database table user_mst
*
* @mbg.generated
*/
@Insert({
"insert into user_mst (user_id,user_name, update_dt, ",
"create_dt)",
"values (#{userId,jdbcType=INTEGER},#{userName,jdbcType=NVARCHAR}, #{updateDt,jdbcType=TIMESTAMP}, ",
"#{createDt,jdbcType=TIMESTAMP})"
})
@Options(useGeneratedKeys=false,keyProperty="userId")
int insert(UserMst record);
使用,调用MybatisBatchUtils中的批处理方法batchUpdateOrInsert
public String test2(int count){
List<UserMst> list = new ArrayList<>(10001);
SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
for (int i=1;i<=count;i++){
UserMst userMst = new UserMst();
userMst.setUserId(i);
userMst.setUserName("test-"+i);
userMst.setCreateDt(LocalDateTime.now());
userMst.setUpdateDt(LocalDateTime.now());
list.add(userMst);
}
long time = System.currentTimeMillis();
mybatisBatchUtils.batchUpdateOrInsert(list, UserMstMapperExt.class,
(userMst, userMstMapperExt) -> userMstMapperExt.insert(userMst));
long time1 = System.currentTimeMillis();
System.out.println("批量插入"+ (double)list.size()/10000+"W条数据耗时:"+(time1-time));
return "批量插入"+ (double)list.size()/10000+"W条数据耗时:"+(time1-time);
}
开始测试
- 批处理方式测试
项目启动后第一次运行:插入1万条数据竟然用了2959ms
清空数据库,重新再执行一次: 1252ms,快了一倍
继续加码:测试10W、100W条数据插入需要多久。经过测试插入10W条需要13.2秒,100W数据需要131.5秒
- 循环语句单条插入
代码实现方式:
public void test3(int count){
List<UserMst> list = new ArrayList<>(10001);
SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
for (int i=1;i<=count;i++){
UserMst userMst = new UserMst();
userMst.setUserId(i);
userMst.setUserName("test-"+i);
userMst.setCreateDt(LocalDateTime.now());
userMst.setUpdateDt(LocalDateTime.now());
list.add(userMst);
}
long time = System.currentTimeMillis();
list.stream().forEach(userMst -> mapperExt.insert(userMst));
long time1 = System.currentTimeMillis();
System.out.println("遍历插入"+ list.size()+"条数据耗时:"+(time1-time));
}
测试1W条数据和10W条数据的插入耗时:
百万条数据插入时资源使用情况
- foreach方式
通过mybatis的foreach方式去批量插入受限于其字符拼接SQL原理,不适用太多的数据插入。
通过批处理的测试,一百万的数据单机插入竟然需要130秒左右,感觉还是有点慢,觉得还有优化的空间。期望8秒能够完成10万级别的数据插入。