江苏龙网

  1. 主页 > 生活百科 > >

100000行级别数据的 Excel 导入优化之路

excel

今天分享一篇大数据量Excel导入如何优化的文章，非常不错 。
需求说明项目中有一个 Excel 导入的需求：缴费记录导入
由实施 / 用户 将别的系统的数据填入我们系统中的 Excel 模板，应用将文件内容读取、校对、转换之后产生欠费数据、票据、票据详情并存储到数据库中 。
在接手之前可能由于之前导入的数据量并不多没有对效率有过高的追求 。但是到了 4.0 版本，预估导入时Excel 行数会是 10w+ 级别，而往数据库插入的数据量是大于 3n 的，也就是说 10w 行的 Excel，则至少向数据库插入 30w 行数据 。因此优化原来的导入代码是势在必行的 。逐步分析和优化了导入的代码，使之在百秒内完成(最终性能瓶颈在数据库的处理速度上，测试服务器 4g 内存不仅放了数据库，还放了很多微服务应用 。处理能力不太行) 。具体的过程如下，每一步都有列出影响性能的问题和解决的办法 。
导入 Excel 的需求在系统中还是很常见的，优化办法可能不是最优的，欢迎读者在评论区留言交流提供更优的思路
一些细节

  • 数据导入：导入使用的模板由系统提供，格式是 xlsx (支持 65535+行数据) ，用户按照表头在对应列写入相应的数据
  • 数据校验：数据校验有两种：
字段长度、字段正则表达式校验等，内存内校验不存在外部数据交互 。对性能影响较小
数据重复性校验，如票据号是否和系统已存在的票据号重复(需要查询数据库，十分影响性能)
  • 数据插入：测试环境数据库使用 MySQL 5.7，未分库分表，连接池使用 Druid
迭代记录1. 第一版：POI + 逐行查询校对 + 逐行插入这个版本是最古老的版本，采用原生 POI，手动将 Excel 中的行映射成 ArrayList 对象，然后存储到 List，代码执行的步骤如下：
  1. 手动读取 Excel 成 List
  2. 循环遍历，在循环中进行以下步骤
  3.  
  1. 检验字段长度
  2. 一些查询数据库的校验，比如校验当前行欠费对应的房屋是否在系统中存在，需要查询房屋表
  3. 写入当前行数据
  1. 返回执行结果，如果出错 / 校验不合格 。则返回提示信息并回滚数据
显而易见的，这样实现一定是赶工赶出来的，后续可能用的少也没有察觉到性能问题，但是它最多适用于个位数/十位数级别的数据 。存在以下明显的问题：
  • 查询数据库的校验对每一行数据都要查询一次数据库，应用访问数据库来回的网络IO次数被放大了 n 倍，时间也就放大了 n 倍
  • 写入数据也是逐行写入的，问题和上面的一样
  • 数据读取使用原生 POI，代码十分冗余，可维护性差 。
2. 第二版：EasyPOI + 缓存数据库查询操作 + 批量插入针对第一版分析的三个问题，分别采用以下三个方法优化
缓存数据，以空间换时间逐行查询数据库校验的时间成本主要在来回的网络IO中，优化方法也很简单 。将参加校验的数据全部缓存到 HashMap 中 。直接到 HashMap 去命中 。关注公号：码猿技术专栏，回复关键词：1111 获取阿里内部JAVA调优手册
例如：校验行中的房屋是否存在，原本是要用 区域 + 楼宇 + 单元 + 房号 去查询房屋表匹配房屋ID，查到则校验通过，生成的欠单中存储房屋ID，校验不通过则返回错误信息给用户 。而房屋信息在导入欠费的时候是不会更新的 。并且一个小区的房屋信息也不会很多(5000以内)因此我采用一条SQL，将该小区下所有的房屋以 区域/楼宇/单元/房号 作为 key，以 房屋ID 作为 value，存储到 HashMap 中，后续校验只需要在 HashMap 中命中
自定义 SessionMApperMyBatis 原生是不支持将查询到的结果直接写人一个 HashMap 中的，需要自定义 SessionMapper
SessionMapper 中指定使用 MapResultHandler 处理 SQL 查询的结果集
@Repositorypublic class SessionMapper extends SqlSessionDaoSupport {@Resourcepublic void setSqlSessionFactory(SqlSessionFactory sqlSessionFactory) {super.setSqlSessionFactory(sqlSessionFactory);}// 区域楼宇单元房号 - 房屋ID@SuppressWarnings("unchecked")public Map<String, Long> getHouseMapByAreAId(Long areaId) {MapResultHandler handler = new MapResultHandler(); this.getSqlSession().select(BaseUnitMapper.class.getName()+".getHouseMapByAreaId", areaId, handler);Map<String, Long> map = handler.getMappedResults();return map;}}


推荐阅读


上一篇：Cilium：基于eBPF的高效云原生网络和ServiceMesh方案

下一篇：从理论到实现，手把手实现Attention网络