导入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

在搜索了相关资料后了解到是数据库插入相关问题，接下来继续优化

关于数据库插入优化有两方面

1. 怎么插入，使用批处理（mp的saveBatch）还是 foreach标签
2. 多少条数据插一次比较好

这里直接说我使用的了，想了解具体的可以去搜

1. 使用的批处理，数据库配置时加rewriteBatchedStatements=true
2. 网上看别人测的是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 中进行，多线程 + 批处理 加快插入速度
• 细节上要注意 内存 的开销，减少没有必要的对象的创建。

如果还有更好的优化思路请指教！

参考文章

10w+ Excel 数据导入，怎么优化？