做质量工作的数据汇总excel技巧（10w 行级别数据的 Excel 导入优

如何删除excel中数万多余行中10w行级数据的Excel导入优化记录？

10w 行级别数据的 Excel 导入优化记录

需求说明

项目有Excel导入需求付款记录的导入。

实施者/用户将其他系统的数据填入我们系统中的Excel模板，应用程序读取、校对和转换文件内容，生成欠款数据、账单和账单明细并存储在数据库中。

在我接手之前，我可能没有。不要因为之前导入的数据量小而过分追求效率。但在4.0版本中，我预测导入时Excel的行数为10w，插入数据库的数据量将超过3n，也就是说10w行的Excel将至少有30w行的数据插入数据库。，优化原导入代码势在必行。我逐步对导入的代码进行分析优化，使其在100秒内完成(最终的性能瓶颈在于数据库的处理速度，测试服务器的4g内存不仅放了数据库，还放了很多微服务应用。处理能力不是很好)。具体流程如下，每一步都列出了影响性能的问题和解决 。

系统中导入Excel的需求还是很普遍的，我的优化 可能不是更优的。欢迎读者在评论区留言，提供更好的思路。

一些细节

数据导入导入使用的模板由系统提供，格式为xlsx(支持65535行数据)。用户根据表头在相应的栏目中写入相应的数据。数据校验有两种字段长度、字段正则表达式校验等。在内存验证中没有外部数据交互。对性能影响不大的数据重复性检查，比如票据编号是否与系统中已有的票据编号重复(需要查询数据库，对性能影响很大)。数据插入测试环境数据库使用MySQL 5.7，连接池使用Druid

迭代记录

之一版POI + 逐行查询校对 + 逐行插入

。这个版本是最老的版本，用原来的POI手工把Excel中的行映射成ArrayList对象，然后存储在列表中，有代码。

1.手动将Excel读入List2。循环，并在循环中执行以下步骤。

检查字段的长度。检查数据库。比如检查系统中是否存在当前欠费行对应的房子。您需要检查house表并写入当前行的数据。3.返回执行结果。如果有错误/检查不合格。返回提示信息和回滚数据是显而易见的，所以实现一定要赶制出来，后续可能注意不到性能问题，但最多适合个位数/十位数的数据。存在以下明显的问题

查询数据库的验证要求对每行数据查询一次数据库。 IO次数和应用访问数据库的时间放大了n倍，写入的数据也是逐行写入。问题同上。使用原生POI读取数据，代码非常冗余，可维护性差。

第二版EasyPOI + 缓存数据库查询操作 + 批量插入

针对之一版分析的三个问题，分别采用了以下三种 进行优化。

缓存数据，以空间换时间

逐行查询数据库验证的时间开销主要在往返 IO，优化 也很简单。将所有参与验证的数据缓存到HashMap中。直接去HashMap打一下。

比如验证行中有房子，原来的目的是用区域建筑单元的房号查询房子表匹配房子ID。如果找到就通过验证，生成的账单会存储房屋ID。如果失败，它将向用户返回一条错误消息。导入欠费时不会更新住房信息。而且一个小区的房子信息不会很多(5000以内)，所以我用了一个SQL，这个小区所有的房子都以面积/楼号/单元号/房号为键，以房子ID为值存储在HashMap中，后续验证只需要在HashMap中命中即可。

自定义 SessionMapper

Mybatis本身不支持将查询结果直接写入HashMap，所以需要定制SessionMapper。

SessionMapper指定使用MapResultHandler处理SQL查询的结果集。

@Repositorypublic类会话映射器扩展SqlSessionDaoSupport { @ Resource public void setSqlSessionFactory(SqlSessionFactory SqlSessionFactory){ super。setSqlSessionFactory(SqlSessionFactory)；} //区域楼宇单元房号-房屋ID @ suppress警告( 未检查 )public MapString，Long getHouseMapByAreaId(Long areaId){ MapResultHandler handler=new MapResultHandler()；this.getSqlSession().select(BaseUnitMapper.class.g

etName()+#34.getHouseMapByAreaId#34, areaId, handler) MapltString, Longgt map = handler.getMappedResults() return map }}

MapResultHandler 处理程序，将结果集放入 HashMap

public class MapResultHandler implements ResultHandler { private final Map mappedResults = new HashMap() @Override public void handleResult(ResultContext context) { @SuppressWarnings(#34rawtypes#34) Map map = (Map)context.getResultObject() mappedResults.put(map.get(#34key#34), map.get(#34value#34)) } public Map getMappedResults() { return mappedResults }}

示例 Mapper

@Mapper@Repository public interface BaseUnitMapper { // 收费标准绑定 区域楼宇单元房号 - 房屋ID MapltString, Longgt getHouseMapByAreaId(@Param(#34areaId#34) Long areaId)} 示例 Mapper.xmlltselect id=#34getHouseMapByAreaId#34 resultMap=#34mapResultLong#34gt SELECT CONCAT( h.bulid_area_name, h.build_name, h.unit_name, h.house_num ) k, h.house_id v FROM base_house h WHERE h.area_id = #{areaId} GROUP BY h.house_idlt/selectgt ltresultMap id=#34mapResultLong#34 type=#34java.util.HashMap#34gt ltresult property=#34key#34 column=#34k#34 javaType=#34string#34 jdbcType=#34VARCHAR#34/gt ltresult property=#34value#34 column=#34v#34 javaType=#34long#34 jdbcType=#34INTEGER#34/gtlt/resultMapgt

之后在代码中调用 SessionMapper 类对应的 即可。

使用 values 批量插入

MySQL insert 语句支持使用 values (),(),() 的方式一次插入多行数据，通过 mybatis foreach 结合 java 可以实现批量插入，代码写法如下

ltinsert id=#34insertList#34gt insert into table(colom1, colom2) values ltforeach collection=#34list#34 item=#34item#34 index=#34index#34 separator=#34,#34gt ( #{item.colom1}, #{item.colom2}) lt/foreachgtlt/insertgt

使用 EasyPOI 读写 Excel

EasyPOI 采用基于注解的导入导出,修改注解就可以修改Excel，非常方便，代码维护起来也容易。

第三版EasyExcel + 缓存数据库查询操作 + 批量插入

第二版采用 EasyPOI 之后，对于几千、几万的 Excel 数据已经可以轻松导入了，不过耗时有点久(5W 数据 10分钟左右写入到数据库)不过由于后来导入的操作基本都是开发在一边看日志一边导入，也就没有进一步优化。好景不长，有新小区需要迁入，票据 Excel 有 41w 行，这个时候使用 EasyPOI 在开发环境跑直接就 OOM 了，增大 JVM 内存参数之后，虽然不 OOM 了， CPU 占用 100% 20 分钟仍然未能成功读取全部数据。故在读取大 Excel 时需要再优化速度。莫非要我这个渣渣去深入 POI 优化了吗？别慌，先上 GITHUB 找找别的开源项目。这时阿里 EasyExcel 映入眼帘

emmm，这不是为我量身定制的吗！赶紧拿来试试。EasyExcel 采用和 EasyPOI 类似的注解方式读写 Excel，从 EasyPOI 切换过来很方便，分分钟就搞定了。也确实如阿里大神描述的41w行、25列、45.5m 数据读取平均耗时 50s，对于大 Excel 建议使用 EasyExcel 读取。

第四版优化数据插入速度

在第二版插入的时候，我使用了 values 批量插入代替逐行插入。每 30000 行拼接一个长 SQL、顺序插入。整个导入 这块耗时最多，非常拉跨。后来我将每次拼接的行数减少到 10000、5000、3000、1000、500 发现执行最快的是 1000。结合网上一些对 innodb_buffer_pool_size 描述我猜是因为过长的 SQL 在写操作的时候由于超过内存阈值，发生了磁盘交换。限制了速度，测试服务器的数据库性能也不怎么样，过多的插入他也处理不过来。所以最终采用每次 1000 条插入。

每次 1000 条插入后，为了榨干数据库的 CPU，那么 IO的等待时间就需要利用起来，这个需要多线程来解决，而最简单的多线程可以使用 并行流 来实现，接着我将代码用并行流来测试了一下

10w行的 excel、42w 欠单、42w记录详情、2w记录、16 线程并行插入数据库、每次 1000 行。插入时间 72s，导入总时间 95 s。

并行插入工具类

并行插入的代码我封装了一个函数式编程的工具类，也提供给大家

/ 功能利用并行流快速插入数据 @author Keats @date 2020/7/1 9:25 /public class InsertConsumer { / 每个长 SQL 插入的行数，可以根据数据库性能调整 / private final static int SIZE = 1000 / 如果需要调整并发数目，修改下面 的第二个参数即可 / static { System.setProperty(#34java.util.concurrent.ForkJoinPool.common.paralleli #34, #344#34) } / 插入 @param list 插入数据 @param consumer 消费型 ，直接使用 mapper::method 引用的方式 @param ltTgt 插入的数据类型 / public static ltTgt void insertData(ListltTgt list, ConsumerltListltTgtgt consumer) { if (list == null || list.size() lt 1) { return } ListltListltTgtgt streamList = new ArrayListltgt() for (int i = 0 i lt list.size() i += SIZE) { int j = Math.min((i + SIZE), list.size()) ListltTgt subList = list.subList(i, j) streamList.add(subList) } // 并行流使用的并发数是 CPU 核心数，不能局部更改。全局更改影响较大，斟酌 streamList.parallelStream().forEach(consumer) }}

这里多数使用到很多 Java8 的API，不了解的朋友可以翻看我之前关于 Java 的博客。 使用起来很简单InsertConsumer.insertData(feeList, arrearageMapper::insertList)

其他影响性能的内容

日志

避免在 for 循环中打印过多的 日志

在优化的过程中，我还发现了一个特别影响性能的东西 日志，还是使用 41w行、25列、45.5m 数据，在 开始-数据读取完毕 之间每 1000 行打印一条 日志，缓存校验数据-校验完毕 之间每行打印 3+ 条 日志，日志框架使用 Slf4j 。打印并持久化到磁盘。下面是打印日志和不打印日志效率的差别

打印日志

不打印日志

我以为是我选错 Excel 文件了，又重新选了一次，结果依旧

缓存校验数据-校验完毕 不打印日志耗时仅仅是打印日志耗时的 1/10 ！

提升Excel导入速度的 使用更快的 Excel 读取框架(推荐使用阿里 EasyExcel)对于需要与数据库交互的校验、按照业务逻辑适当的使用缓存。用空间换时间使用 values(),(),() 拼接长 SQL 一次插入多行数据使用多线程插入数据，利用掉 IO等待时间(推荐使用并行流，简单易用)避免在循环中打印无用的日志

利用excel 对资料进行整理和描述 excel做月度数据及全年汇总