场景
当遇到定时任务需要大数据量的写入数据的时候,会非常的慢,可能需要好几个小时,如何优化?
优化方案
- 批量插入代替单条插入;
- session 一次提交代替自动提交;
代码
import org.mybatis.spring.SqlSessionTemplate;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
@Autowired
private SqlSessionTemplate sqlSessionTemplate;
private void bathInsert(List<SciInventoryInfoEntity> dataList) {
SqlSession session = null;
try {
int batchNumber = 100;
int times = dataList.size() / batchNumber;
int lastCount = dataList.size() % batchNumber;
times = lastCount > 0 ? times + 1 : times;
// 关闭 session 自动提交
session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
// 从 session 获取 mapper
SciInventoryInfoMapper mapper = session.getMapper(SciInventoryInfoMapper.class);
for (int i = 1; i <= times; i++) {
int skip = (i - 1) * batchNumber;
int limit = batchNumber;
if (i == times) {
limit = batchNumber > lastCount ? lastCount : batchNumber;
}
List<SciInventoryInfoEntity> collect = dataList.stream().skip(skip).limit(limit).collect(Collectors.toList());
if (CollectionUtils.isNotEmpty(collect)) {
mapper.batchInsert(collect);
}
}
// 手动提交 session
session.commit();
} finally {
// 关闭 session
if (null != session) {
session.close();
}
}
}