导入Excel大量数据如何优化
背景
线上导入excel 的时候发现速度太慢了 发现原来的代码 是 读入所有数据
然后按行去数据库比对是否存在，不存在再插入。 这样就相当于 n 条数据 要有 2n 次的数据库请求，以前数据量小的时候没问题，数据量一大就暴露出问题了，
下面的测试在 数据库中有20w条数据时导入6k条进行
原代码
excelDataList.stream().forEach(gt -> {
LambdaQueryWrapper wrapper = new LambdaQueryWrapper<>();
wrapper.eq(QydjQydjxx::getQymc, gt.getQymc()).or().eq(QydjQydjxx::getTyshxydm, gt.getTyshxydm());
List gtdjxxList = list(wrapper);
if (CollectionUtils.isNotEmpty(gtdjxxList)) {
fails.add(gt.getQymc() + " 已存在");
}
else {
QydjQydjxx gtdjxx = new QydjQydjxx();
BeanUtils.copyProperties(gt, gtdjxx);
save(gtdjxx);
}
优化思路
判断主要时间花费就在数据库IO上 然后就开始着手代码优化 思路方向就是减少与数据库的交互次数
刚开始的方案就是 把在数据库里进行的判断拿到内存中去判断，这样的话只需要查一次、存一次即可
准备冻手！！-- 减少与数据库的网络IO
/**
* 将企业名称和统一社会信用代码放在set中作为缓存
* 避免每行excel数据都去查询数据库判断是否存在
* @return 企业名称和统一社会信用代码的集合
*/
private Set<String> getExistingQymcAndTyshxydm() {
LambdaQueryWrapper<QydjQydjxx> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.select(QydjQydjxx::getQymc, QydjQydjxx::getTyshxydm);
List<QydjQydjxx> list = qydjQydjxxMapper.selectList(queryWrapper);
Set<String> set = new HashSet<>(list.size());
list.stream().forEach(qydjQydjxx -> {
set.add(qydjQydjxx.getQymc());
set.add(qydjQydjxx.getTyshxydm());
});
return set;
}
这里有一个潜在的问题不知道眼尖的同学能不能发现。
就这样写好后与前端进行测试，发现还是会很慢，这个时候已经想到是内存问题了，当时写就想着偷懒用mp直接查实体再取出来，接下来继续优化。
继续优化 – 降低数据占用内存
我们只需要把 企业名称和统一社会信用代码放在set里 没必要用实体接收。稍微计算一下40w长度的String数组的大小，每个String假定长度为10,
40w的话大概大小为20MB
接下来继续比对测试
用实体接收
用String接收
private Set<String> getExistingQymcAndTyshxydm() {
List<String> qymcList = qydjQydjxxMapper.selectAllQymc();
List<String> tyshxydmList = qydjQydjxxMapper.selectAllTyshxydm();
Set<String> set = new HashSet<>(qymcList.size()+tyshxydmList.size());
set.addAll(qymcList);
set.addAll(tyshxydmList);
return set;
}
结果显而易见，优化到现在差不多够线上使用了。
后续优化-- 数据库插入优化 + 多线程
后续我在测试导入大数据量的excel文件时，经常抛出这个警告，有时候还会导入失败
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@f4a8cbf]
Transaction not enabled
在搜索了相关资料后了解到是数据库插入相关问题，一次性插入过多数据，数据库服务器处理不过来，接下来继续优化
关于数据库插入优化有两方面
- 怎么插入，使用批处理（mp的saveBatch）还是 foreach标签
- 多少条数据插一次比较好
这里直接说我使用的了，想了解具体的可以去搜
- 使用的批处理，数据库配置时加rewriteBatchedStatements=true
- 网上看别人测的是1000条，同时 mp的 saveBatch默认也是1000条
然后使用线程池去进行插入
// 继承 ReaderListener类 读完 excel 后进行的操作
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
List<List<QydjQydjxx>> streamList = new ArrayList<>();
for (int i = 0; i < qydjqydjxxList.size(); i += BATCH_COUNT) {
int j = Math.min((i + BATCH_COUNT), qydjqydjxxList.size());
List<QydjQydjxx> subList = qydjqydjxxList.subList(i, j);
streamList.add(subList);
}
log.info("插入数据");
streamList.parallelStream().forEach(qydjxxService::saveBatch);
}
到此 我能想到的优化已经做完了！
总结
- 大部分的优化都是减少与数据库的 IO 时间，这方面我们可以通过 加 缓存 来将 判断转移在 jvm 中进行，多线程 + 批处理 加快插入速度
- 细节上要注意 内存 的开销，减少没有必要的对象的创建。
如果还有更好的优化思路请指教！