10w+ Excel 資料匯入，怎么優化？-有解無憂

作者：后青春期的Keats
https://www.cnblogs.com/keatsCoder/

需求說明

專案中有一個 Excel 匯入的需求：繳費記錄匯入，

由實施 / 用戶將別的系統的資料填入我們系統中的 Excel 模板，應用將檔案內容讀取、校對、轉換之后產生欠費資料、票據、票據詳情并存盤到資料庫中，

在我接手之前可能由于之前匯入的資料量并不多沒有對效率有過高的追求，但是到了 4.0 版本，我預估匯入時Excel 行數會是 10w+ 級別，而往資料庫插入的資料量是大于 3n 的，也就是說 10w 行的 Excel，則至少向資料庫插入 30w 行資料，

因此優化原來的匯入代碼是勢在必行的，我逐步分析和優化了匯入的代碼，使之在百秒內完成(最終性能瓶頸在資料庫的處理速度上，測驗服務器 4g 記憶體不僅放了資料庫，還放了很多微服務應用，處理能力不太行)，

具體的程序如下，每一步都有列出影響性能的問題和解決的辦法，

匯入 Excel 的需求在系統中還是很常見的，我的優化辦法可能不是最優的，歡迎讀者在評論區留言交流提供更優的思路

一些細節

資料匯入：匯入使用的模板由系統提供，格式是 xlsx (支持 65535+行資料) ，用戶按照表頭在對應列寫入相應的資料
資料校驗：資料校驗有兩種：
欄位長度、欄位正則運算式校驗等，記憶體內校驗不存在外部資料互動，對性能影響較小
資料重復性校驗，如票據號是否和系統已存在的票據號重復(需要查詢資料庫，十分影響性能)
資料插入：測驗環境資料庫使用 MySQL 5.7，未分庫分表，連接池使用 Druid

迭代記錄

第一版：POI + 逐行查詢校對 + 逐行插入

這個版本是最古老的版本，采用原生 POI，手動將 Excel 中的行映射成 ArrayList 物件，然后存盤到 List ，代碼執行的步驟如下：

手動讀取 Excel 成 List
回圈遍歷，在回圈中進行以下步驟
檢驗欄位長度
一些查詢資料庫的校驗，比如校驗當前行欠費對應的房屋是否在系統中存在，需要查詢房屋表
寫入當前行資料
回傳執行結果，如果出錯 / 校驗不合格，則回傳提示資訊并回滾資料

顯而易見的，這樣實作一定是趕工趕出來的，后續可能用的少也沒有察覺到性能問題，但是它最多適用于個位數/十位數級別的資料，存在以下明顯的問題：

查詢資料庫的校驗對每一行資料都要查詢一次資料庫，應用訪問資料庫來回的網路IO次數被放大了 n 倍，時間也就放大了 n 倍
寫入資料也是逐行寫入的，問題和上面的一樣
資料讀取使用原生 POI，代碼十分冗余，可維護性差，

第二版：EasyPOI + 快取資料庫查詢操作 + 批量插入

針對第一版分析的三個問題，分別采用以下三個方法優化

快取資料，以空間換時間

逐行查詢資料庫校驗的時間成本主要在來回的網路IO中，優化方法也很簡單，將參加校驗的資料全部快取到 HashMap 中，直接到 HashMap 去命中，另外關注公眾號Java技術堆疊回復福利獲取一份Java面試題資料，

例如：校驗行中的房屋是否存在，原本是要用區域 + 樓宇 + 單元 + 房號去查詢房屋表匹配房屋ID，查到則校驗通過，生成的欠單中存盤房屋ID，校驗不通過則回傳錯誤資訊給用戶，而房屋資訊在匯入欠費的時候是不會更新的，

并且一個小區的房屋資訊也不會很多(5000以內)因此我采用一條SQL，將該小區下所有的房屋以區域/樓宇/單元/房號作為 key，以房屋ID 作為 value，存盤到 HashMap 中，后續校驗只需要在 HashMap 中命中，

自定義 SessionMapper

Mybatis 原生是不支持將查詢到的結果直接寫人一個 HashMap 中的，需要自定義 SessionMapper，

SessionMapper 中指定使用 MapResultHandler 處理 SQL 查詢的結果集

@Repository  
public 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 分鐘仍然未能成功讀取全部資料，另外關注公眾號Java技術堆疊回復JVM46獲取一份JVM調優教程，

故在讀取大 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 ，列印并持久化到磁盤，下面是列印日志和不列印日志效率的差別

列印日志