今天分享一篇大数据量Excel导入优化的文章，很不错。需求描述项目中有一个Excel导入需求：付款记录导入。实施/用户将其他系统的数据填写到我系统的Excel模板中，应用程序读取、校对、转换文件内容，生成欠费数据、账单、账单明细存入数据库。接手之前，可能是之前导入的数据量小，没有过分追求效率。但是在4.0版本中，估计导入时Excel的行数是10w+，插入数据库的数据量大于3n，也就是说10w行的Excel，至少需要30w行的数据将被插入到数据库中。因此，对原有导入代码进行优化势在必行。导入的代码逐步分析优化，百秒内完成（最终的性能瓶颈在于数据库的处理速度，测试服务器4g内存不仅存储了数据库，还放置了很多微服务应用。处理能力不够好）。具体过程如下，每一步都列出了影响性能的问题和解决方法。需要导入Excel在系统中还是很常见的，优化的方法可能不是最优的。欢迎读者在评论区留言，提供更好的思路。数据导入的一些细节：用于导入的模板由系统提供，格式为xlsx（支持65535+行数据），用户根据表头在相应的列写入相应的数据。数据校验：数据校验有字段长度、字段正则表达式校验等两种，内存校验没有外部数据交互。对性能影响不大。数据重复性校验，比如票号是否与系统中已有的票号相同（需要查询数据库，对性能影响很大）。数据插入：测试环境数据库使用MySQL5.7，不分库分表，连接池使用Druid。迭代记录1.第一个版本：POI+逐行查询校对+逐行插入这个版本是最老的版本，使用原生POI，手动将Excel中的行映射到ArrayList对象，然后存入List，代码执行步骤如下：手动将Excel读入一个List并循环遍历，循环中执行以下步骤检查字段长度。一些查询数据库的查询，比如查询当前欠款行对应的房子在系统中是否存在，需要查询房子表。写入当前行数据并返回执行结果，如果有错误/验证失败。返回提示信息和回滚数据是显而易见的。这个实现一定要赶出来，后续用的可能会少一些，也不会注意到性能问题，但是最适合个位数/十位数的数据。存在以下明显的问题：查询数据库的校验需要每行数据查询一次数据库，应用访问数据库的网络IO次数被放大了n倍，时间也被放大了n次。写入数据也是逐行写入，问题同上。数据读取使用原生POI，代码非常冗余，可维护性差。2、第二版：EasyPOI+缓存数据库查询操作+批量插入针对第一版分析的三个问题，分别采用以下三种方法进行优化。缓存数据，以空间换时间，逐行查询数据库。数据库校验的时间成本主要在往返网络IO上，优化方式也很简单。将所有参与校验的数据缓存到HashMap中。直接到HashMap中去命中。例如：验证行中的房子是否存在，本来需要用地区+楼+单元+房号查询房子表匹配房子ID。如果找到，则验证通过。如果通过，则向用户返回一条错误消息。欠款导入时不会更新房源信息。而且一个小区的房子不多（5000以内）所以我用一个SQL把小区所有的房子都存储在HashMap中，以面积/楼/单元/房号为key，以房子ID为值。后续检查只需要命中HashMap即可。自定义SessionMapperMybatis原生不支持将查询结果直接写入HashMap，需要自定义SessionMapper。SessionMapper指定使用MapResultHandler来处理SQL查询的结果集。@RepositorypublicclassSessionMapperextendsSqlSessionDaoSupport{@ResourcepublicvoidsetSqlSessionFactory(SqlSessionFactorysqlSessionFactory){super.setSqlSessionFactory(sqlSessionFactory);}//区域建筑单元房号-房屋ID@SuppressWarningMap("uncheckedMap>String,publicLrea(LongareaId){MapResultHandlerhandler=newMapResultHandler();this.getSqlSession().select(BaseUnitMapper.class.getName()+".getHouseMapByAreaId",areaId,handler);Mapmap=handler.getMappedResults();returnmap;}}MapResultHandler将结果集放入HashMap的Handler。公共类MapResultHandler实现ResultHandler{privatefinalMapmappedResults=newHashMap();@OverridepublicvoidhandleResult(ResultContextcontext){@SuppressWarnings("rawtypes")Mapmap=(Map)context.getResultObject();mappedResults.put(map.get("key"),map.get("value"));}publicMapgetMappedResults(){返回映射结果；}}说明映射器。@Mapper@RepositorypublicinterfaceBaseUnitMapper{//收费标准绑定区域建筑单元房号-房屋IDMapgetHouseMapByAreaId(@Param("areaId")LongareaId);}ExampleMapper.xmlSELECTCONCAT(h.bulid_area_name,h.build_name,h.unit_name,h.house_num)k,h.house_idvFROMbase_househWHEREh.area_id=##{areaId}GROUPBYh。house_id之后，在代码中调用SessionMapper类的对应方法。使用values批量插入MySQLinsert语句支持使用values(),(),()一次插入多行数据，通过mybatisforeach结合java集合可以实现批量插入。代码是这样写的：insertintotable(colom1,colom2)values(##{item.colom1},##{item.colom2})使用EasyPOI读写Excelhttp://doc.wupaas.com/docs/easypoi/easypoi-1c0u4mo8p4ro8使用annotation-based导入导出，可以通过修改注解来修改Excel，非常方便，代码也容易维护。3、第三版：EasyExcel+缓存数据库查询操作+批量插入第二版采用EasyPOI后，导入几千条或几万条Excel数据很容易，但耗时长（5W数据写入10左右minutes)tothedatabase)但是，由于后面的导入操作基本都是边导入边看日志开发的，所以没有做进一步的优化。但好景不长。需要搬入一个新小区，账单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读取。4.第四版：优化数据插入速度在第二版插入的时候，我使用的是values批量插入，而不是逐行插入。拼接一条长SQL，每30000行依次插入。整个导入方式耗时最多，非常捉襟见肘。后来我把每次拼接的行数减少到10000、5000、3000、1000、500，发现1000表现最快。结合网上一些关于innodb_buffer_pool_size的描述，我猜测是因为过长的SQL在写操作时超过了内存阈值，发生了磁盘交换。关注公众号：码猿技术专栏，回复关键词：1111获取阿里内部Java调优手册；速度有限，而且测试服务器的数据库性能不是很好，他不能处理太多的插入。所以最终每次都使用了1000次插入。每次插入1000条后，为了耗尽数据库的CPU，需要利用网络IO的等待时间。这就需要通过多线程来解决，最简单的多线程可以使用并行流来实现。那我就用代码并行流来测试一下：10w行excel，42w欠单，42w条记录明细，2w条记录，16线程并行插入数据库，每次1000行。插入时间为72s，总导入时间为95s。并行插入工具类并行插入代码我封装了一个函数式编程工具类，提供给大家/***功能：使用并行流快速插入数据**@authorKeats*@date2020/7/19:25*/publicclassInsertConsumer{/***每个长SQL插入的行数可以根据数据库的性能进行调整*/privatefinalstaticintSIZE=1000;/***如果需要调整并发数，修改下面方法的第一个方法就两个参数*/static{System.setProperty("java.util.concurrent.ForkJoinPool.common.parallelism","4");}/***插入方法**@paramlist插入数据集*@paramconsumer消费方法，直接使用mapper::method方法引用*@param插入的数据类型*/publicstaticvoidinsertData(Listlist,Consumer>consumer){if(list==null||list.size()<1){返回;}List>streamList=newArrayList<>();for(inti=0;isubList=list.subList(i,j);流列表.add（子列表）；}//并行流使用的并发数是CPU核数，本地无法更改。全球变化影响更大。考虑streamList.parallelStream().forEach(consumer);}}这里用到了Java8的大部分API，不了解的朋友可以看我之前关于Java的博客。方法很简单，使用InsertConsumer.insertData(feeList,arrearageMapper::insertList);其他影响性能的日志内容避免在for循环中打印过多的info日志在优化的过程中，我还发现了一个特别影响性能的东西：info日志，依然使用41w行，25列，45.5m的数据，打印一个info从数据读取开始到完成每1000行记录一次，缓存验证数据到验证完成之间每行打印3+条信息日志记录，日志框架使用Slf4j。打印并保存到磁盘。下面是打印日志和不打印日志效率上的区别：我以为我选错了打印日志的Excel文件却没有打印日志。打印日志只需要1/10的时间！总结提高Excel导入速度的方法：使用更快的Excel阅读框架（推荐使用阿里EasyExcel）。对于需要与数据库交互的验证，根据业务逻辑适当使用缓存。以空间换时间。使用values(),(),()连接长SQL一次插入多行数据。使用多线程插入数据，利用网络IO等待时间（推荐并行流，简单易用）。避免循环打印无用的日志。
