點選「 IT碼徒 」， 關註，置頂 公眾號

每日技術幹貨，第一時間送達！

今天分享一篇大數據量Excel匯入如何最佳化的文章，非常不錯。

需求說明

計畫中有一個 Excel 匯入的需求：繳費記錄匯入

由實施 / 使用者 將別的系統的數據填入我們系統中的 Excel 樣版，套用將檔內容讀取、校對、轉換之後產生欠費數據、票據、票據詳情並儲存到資料庫中。

在接手之前可能由於之前匯入的數據量並不多沒有對效率有過高的追求。但是到了 4.0 版本，預估匯入時Excel 行數會是 10w+ 級別，而往資料庫插入的數據量是大於 3n 的，也就是說 10w 行的 Excel，則至少向資料庫插入 30w 行數據。因此最佳化原來的匯入程式碼是勢在必行的。逐步分析和最佳化了匯入的程式碼，使之在百秒內完成(最終效能瓶頸在資料庫的處理速度上，測試伺服器 4g 記憶體不僅放了資料庫，還放了很多微服務套用。處理能力不太行)。具體的過程如下，每一步都有列出影響效能的問題和解決的辦法。

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

一些細節

叠代記錄

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

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

1. 手動讀取 Excel 成 List

2. 迴圈遍歷，在迴圈中進行以下步驟

1. 檢驗欄位長度

2. 一些查詢資料庫的校驗，比如校驗當前行欠費對應的房屋是否在系統中存在，需要查詢房屋表

3. 寫入當前行數據

3. 返回執行結果，如果出錯 / 校驗不合格。則返回提示資訊並回滾數據

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

2. 第二版：EasyPOI + 緩存資料庫查詢操作 + 批次插入

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

緩存數據，以空間換時間

逐行查詢資料庫校驗的時間成本主要在來回的網路IO中，最佳化方法也很簡單。將參加校驗的數據全部緩存到 HashMap 中。直接到 HashMap 去命中。

例如：校驗行中的房屋是否存在，原本是要用 區域 + 樓宇 + 單元 + 房號 去查詢房屋表匹配房屋ID，查到則校驗透過，生成的欠單中儲存房屋ID，校驗不透過則返回錯誤資訊給使用者。而房屋資訊在匯入欠費的時候是不會更新的。並且一個小區的房屋資訊也不會很多(5000以內)因此我采用一條SQL，將該小區下所有的房屋以 區域/樓宇/單元/房號 作為 key，以 房屋ID 作為 value，儲存到 HashMap 中，後續校驗只需要在 HashMap 中命中

自訂 SessionMapper

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

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

@Repository
public classSessionMapperextendsSqlSessionDaoSupport{
@Resource
publicvoidsetSqlSessionFactory(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 classMapResultHandlerimplementsResultHandler{
privatefinal Map mappedResults = new HashMap();
@Override
publicvoidhandleResult(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
publicinterfaceBaseUnitMapper{
// 收費標準繫結 區域樓宇單元房號 - 房屋ID
Map<String, Long> getHouseMapByAreaId(@Param("areaId") Long areaId);
}


範例 Mapper.xml

<selectid="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>
<resultMapid="mapResultLong"type="java.util.HashMap">
<resultproperty="key"column="k"javaType="string"jdbcType="VARCHAR"/>
<resultproperty="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

http://doc.wupaas.com/docs/easypoi/easypoi-1c0u4mo8p4ro8 采用基於註解的匯入匯出,修改註解就可以修改Excel，非常方便，程式碼維護起來也容易。

3. 第三版：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 讀取。

4. 第四版：最佳化數據插入速度

在第二版插入的時候，我使用了 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 classInsertConsumer{
/**
* 每個長 SQL 插入的行數，可以根據資料庫效能調整
*/
privatefinalstaticint SIZE = 1000;
/**
* 如果需要調整並行數目，修改下面方法的第二個參數即可
*/
static {
System.setProperty("java.util.concurrent.ForkJoinPool.common.parallelism", "4");
}
/**
* 插入方法
*
* @param list 插入數據集合
* @param consumer 消費型方法，直接使用 mapper::method 方法參照的方式
* @param <T> 插入的數據型別
*/
publicstatic <T> voidinsertData(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匯入速度的方法：

— END —

PS：防止找不到本篇文章，可以收藏點贊，方便翻閱尋找哦。

往期推薦