需要阐明

我的项目中有一个 Excel 导入的需要：缴费记录导入

由施行 / 用户 将别的零碎的数据填入咱们零碎中的 Excel 模板，利用将文件内容读取、校对、转换之后产生欠费数据、票据、票据详情并存储到数据库中。

在我接手之前可能因为之前导入的数据量并不多没有对效率有过高的谋求。然而到了 4.0 版本，我预估导入时Excel 行数会是 10w+ 级别，而往数据库插入的数据量是大于 3n 的，也就是说 10w 行的 Excel，则至多向数据库插入 30w 行数据。因而优化原来的导入代码是势在必行的。我逐渐剖析和优化了导入的代码，使之在百秒内实现(最终性能瓶颈在数据库的处理速度上，测试服务器 4g 内存不仅放了数据库，还放了很多微服务利用。解决能力不太行)。具体的过程如下，每一步都有列出影响性能的问题和解决的方法。

导入 Excel 的需要在零碎中还是很常见的，我的优化方法可能不是最优的，欢送读者在评论区留言交换提供更优的思路

一些细节

  • 数据导入：导入应用的模板由零碎提供，格局是 xlsx (反对 65535+行数据) ，用户依照表头在对应列写入相应的数据

  • 数据校验：数据校验有两种：

    • 字段长度、字段正则表达式校验等，内存内校验不存在内部数据交互。对性能影响较小
    • 数据重复性校验，如票据号是否和零碎已存在的票据号反复(须要查询数据库，非常影响性能)
  • 数据插入：测试环境数据库应用 MySQL 5.7，未分库分表，连接池应用 Druid

迭代记录

第一版：POI + 逐行查问校对 + 逐行插入

这个版本是最古老的版本，采纳原生 POI，手动将 Excel 中的行映射成 ArrayList 对象，而后存储到 List ，代码执行的步骤如下：

1.手动读取 Excel 成 List

2.循环遍历，在循环中进行以下步骤

  • 测验字段长度
  • 一些查询数据库的校验，比方校验以后行欠费对应的屋宇是否在零碎中存在，须要查问屋宇表
    写入以后行数据
  • 返回执行后果，如果出错 / 校验不合格。则返回提示信息并回滚数据

3.不言而喻的，这样实现肯定是赶工赶出来的，后续可能用的少也没有察觉到性能问题，然而它最多实用于个位数/十位数级别的数据。存在以下显著的问题：

  • 查询数据库的校验对每一行数据都要查问一次数据库，利用拜访数据库来回的网络IO次数被放大了 n 倍，工夫也就放大了 n 倍
  • 写入数据也是逐行写入的，问题和下面的一样
  • 数据读取应用原生 POI，代码非常冗余，可维护性差。

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

针对第一版剖析的三个问题，别离采纳以下三个办法优化

缓存数据，以空间换工夫
逐行查询数据库校验的工夫老本次要在来回的网络IO中，优化办法也很简略。将加入校验的数据全副缓存到 HashMap 中。间接到 HashMap 去命中。

例如：校验行中的屋宇是否存在，本来是要用 区域 + 楼宇 + 单元 + 房号 去查问屋宇表匹配屋宇ID，查到则校验通过，生成的欠单中存储屋宇ID，校验不通过则返回错误信息给用户。而房屋信息在导入欠费的时候是不会更新的。并且一个小区的房屋信息也不会很多(5000以内)因而我采纳一条SQL，将该小区下所有的屋宇以 区域/楼宇/单元/房号 作为 key，以 屋宇ID 作为 value，存储到 HashMap 中，后续校验只须要在 HashMap 中命中

自定义 SessionMapper
Mybatis 原生是不反对将查问到的后果间接写人一个 HashMap 中的，须要自定义 SessionMapper

SessionMapper 中指定应用 MapResultHandler 解决 SQL 查问的后果集

@Repositorypublic class SessionMapper extends SqlSessionDaoSupport {    @Resource    public 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;    }}

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

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

示例 Mapper

@Mapper@Repository public interface BaseUnitMapper {    // 免费规范绑定 区域楼宇单元房号 - 屋宇ID    Map<String, Long> getHouseMapByAreaId(@Param("areaId") Long areaId);}    示例 Mapper.xml<select id="getHouseMapByAreaId" resultMap="mapResultLong">    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_id</select>            <resultMap id="mapResultLong" type="java.util.HashMap">    <result property="key" column="k" javaType="string" jdbcType="VARCHAR"/>    <result property="value" column="v" javaType="long" jdbcType="INTEGER"/></resultMap>

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

应用 values 批量插入
MySQL insert 语句反对应用 values (),(),() 的形式一次插入多行数据，通过 mybatis foreach 联合 java 汇合能够实现批量插入，代码写法如下：

<insert id="insertList">    insert into table(colom1, colom2)    values    <foreach collection="list" item="item" index="index" separator=",">     ( #{item.colom1}, #{item.colom2})    </foreach></insert>

应用 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("java.util.concurrent.ForkJoinPool.common.parallelism", "4");    }    /**     * 插入方法     *     * @param list     插入数据汇合     * @param consumer 消费型办法，间接应用 mapper::method 办法援用的形式     * @param <T>      插入的数据类型     */    public static <T> void insertData(List<T> list, Consumer<List<T>> consumer) {        if (list == null || list.size() < 1) {            return;        }        List<List<T>> streamList = new ArrayList<>();        for (int i = 0; i < list.size(); i += SIZE) {            int j = Math.min((i + SIZE), list.size());            List<T> subList = list.subList(i, j);            streamList.add(subList);        }        // 并行流应用的并发数是 CPU 外围数，不能部分更改。全局更改影响较大，斟酌        streamList.parallelStream().forEach(consumer);    }}

这里少数应用到很多 Java8 的API，不理解的敌人能够翻看我之前对于 Java 的博客。办法应用起来很简略

InsertConsumer.insertData(feeList, arrearageMapper::insertList);

其余影响性能的内容

日志

防止在 for 循环中打印过多的 info 日志

在优化的过程中，我还发现了一个特地影响性能的货色：info 日志，还是应用 41w行、25列、45.5m 数据，在 开始-数据读取结束 之间每 1000 行打印一条 info 日志，缓存校验数据-校验结束 之间每行打印 3+ 条 info 日志，日志框架应用 Slf4j 。打印并长久化到磁盘。上面是打印日志和不打印日志效率的差异

打印日志

不打印日志

我认为是我选错 Excel 文件了，又从新选了一次，后果仍旧

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

总结

晋升Excel导入速度的办法：

  • 应用更快的 Excel 读取框架(举荐应用阿里 EasyExcel)
  • 对于须要与数据库交互的校验、依照业务逻辑适当的应用缓存。用空间换工夫
  • 应用 values(),(),() 拼接长 SQL 一次插入多行数据
  • 应用多线程插入数据，利用掉网络IO等待时间(举荐应用并行流，简略易用)
  • 防止在循环中打印无用的日志

作者 | 后青春期的Keats
起源 | https://www.cnblogs.com/keats...