10w行級別數(shù)據(jù)的Excel導(dǎo)入優(yōu)化記錄
作者：后青春期的Keats
www.cnblogs.com/keatsCoder/p/13217561.html
需求說明
項(xiàng)目中有一個(gè) Excel 導(dǎo)入的需求：繳費(fèi)記錄導(dǎo)入
由實(shí)施 / 用戶 將別的系統(tǒng)的數(shù)據(jù)填入我們系統(tǒng)中的 Excel 模板，應(yīng)用將文件內(nèi)容讀取、校對、轉(zhuǎn)換之后產(chǎn)生欠費(fèi)數(shù)據(jù)、票據(jù)、票據(jù)詳情并存儲到數(shù)據(jù)庫中。
在我接手之前可能由于之前導(dǎo)入的數(shù)據(jù)量并不多沒有對效率有過高的追求。但是到了 4.0 版本，我預(yù)估導(dǎo)入時(shí)Excel 行數(shù)會(huì)是 10w+ 級別，而往數(shù)據(jù)庫插入的數(shù)據(jù)量是大于 3n 的，也就是說 10w 行的 Excel，則至少向數(shù)據(jù)庫插入 30w 行數(shù)據(jù)。
因此優(yōu)化原來的導(dǎo)入代碼是勢在必行的。我逐步分析和優(yōu)化了導(dǎo)入的代碼，使之在百秒內(nèi)完成(最終性能瓶頸在數(shù)據(jù)庫的處理速度上，測試服務(wù)器 4g 內(nèi)存不僅放了數(shù)據(jù)庫，還放了很多微服務(wù)應(yīng)用。處理能力不太行)。具體的過程如下，每一步都有列出影響性能的問題和解決的辦法。
導(dǎo)入 Excel 的需求在系統(tǒng)中還是很常見的，我的優(yōu)化辦法可能不是最優(yōu)的，歡迎讀者在評論區(qū)留言交流提供更優(yōu)的思路
一些細(xì)節(jié)
數(shù)據(jù)導(dǎo)入：導(dǎo)入使用的模板由系統(tǒng)提供，格式是 xlsx (支持 65535+行數(shù)據(jù)) ，用戶按照表頭在對應(yīng)列寫入相應(yīng)的數(shù)據(jù)
數(shù)據(jù)校驗(yàn)：數(shù)據(jù)校驗(yàn)有兩種：
字段長度、字段正則表達(dá)式校驗(yàn)等，內(nèi)存內(nèi)校驗(yàn)不存在外部數(shù)據(jù)交互。對性能影響較小
數(shù)據(jù)重復(fù)性校驗(yàn)，如票據(jù)號是否和系統(tǒng)已存在的票據(jù)號重復(fù)(需要查詢數(shù)據(jù)庫，十分影響性能)
數(shù)據(jù)插入：測試環(huán)境數(shù)據(jù)庫使用 MySQL 5.7，未分庫分表，連接池使用 Druid
迭代記錄
第一版：POI + 逐行查詢校對 + 逐行插入
這個(gè)版本是最古老的版本，采用原生 POI，手動(dòng)將 Excel 中的行映射成 ArrayList 對象，然后存儲到?List?，代碼執(zhí)行的步驟如下：
1.手動(dòng)讀取 Excel 成?List
2.循環(huán)遍歷，在循環(huán)中進(jìn)行以下步驟
檢驗(yàn)字段長度
一些查詢數(shù)據(jù)庫的校驗(yàn)，比如校驗(yàn)當(dāng)前行欠費(fèi)對應(yīng)的房屋是否在系統(tǒng)中存在，需要查詢房屋表
寫入當(dāng)前行數(shù)據(jù)
3.返回執(zhí)行結(jié)果，如果出錯(cuò) / 校驗(yàn)不合格。則返回提示信息并回滾數(shù)據(jù)
顯而易見的，這樣實(shí)現(xiàn)一定是趕工趕出來的，后續(xù)可能用的少也沒有察覺到性能問題，但是它最多適用于個(gè)位數(shù)/十位數(shù)級別的數(shù)據(jù)。存在以下明顯的問題：
查詢數(shù)據(jù)庫的校驗(yàn)對每一行數(shù)據(jù)都要查詢一次數(shù)據(jù)庫，應(yīng)用訪問數(shù)據(jù)庫來回的網(wǎng)絡(luò)IO次數(shù)被放大了 n 倍，時(shí)間也就放大了 n 倍
寫入數(shù)據(jù)也是逐行寫入的，問題和上面的一樣
數(shù)據(jù)讀取使用原生 POI，代碼十分冗余，可維護(hù)性差。
第二版：EasyPOI + 緩存數(shù)據(jù)庫查詢操作 + 批量插入
針對第一版分析的三個(gè)問題，分別采用以下三個(gè)方法優(yōu)化
緩存數(shù)據(jù)，以空間換時(shí)間
逐行查詢數(shù)據(jù)庫校驗(yàn)的時(shí)間成本主要在來回的網(wǎng)絡(luò)IO中，優(yōu)化方法也很簡單。將參加校驗(yàn)的數(shù)據(jù)全部緩存到 HashMap 中。直接到 HashMap 去命中。
例如：校驗(yàn)行中的房屋是否存在，原本是要用 區(qū)域 + 樓宇 + 單元 + 房號 去查詢房屋表匹配房屋ID，查到則校驗(yàn)通過，生成的欠單中存儲房屋ID，校驗(yàn)不通過則返回錯(cuò)誤信息給用戶。而房屋信息在導(dǎo)入欠費(fèi)的時(shí)候是不會(huì)更新的。
并且一個(gè)小區(qū)的房屋信息也不會(huì)很多(5000以內(nèi))因此我采用一條SQL，將該小區(qū)下所有的房屋以 區(qū)域/樓宇/單元/房號 作為 key，以 房屋ID 作為 value，存儲到 HashMap 中，后續(xù)校驗(yàn)只需要在 HashMap 中命中
自定義 SessionMapper
Mybatis 原生是不支持將查詢到的結(jié)果直接寫人一個(gè) HashMap 中的，需要自定義 SessionMapper
SessionMapper 中指定使用 MapResultHandler 處理 SQL 查詢的結(jié)果集
@Repository
public?class?SessionMapper?extends?SqlSessionDaoSupport?{
????@Resource
????public?void?setSqlSessionFactory(SqlSessionFactory?sqlSessionFactory)?{
????????super.setSqlSessionFactory(sqlSessionFactory);
????}
????//?區(qū)域樓宇單元房號?-?房屋ID
????@SuppressWarnings("unchecked")
????public?MapLong>?getHouseMapByAreaId(Long?areaId)?{
????????MapResultHandler?handler?=?new?MapResultHandler();
?this.getSqlSession().select(BaseUnitMapper.class.getName()+".getHouseMapByAreaId",?areaId,?handler);
????????MapLong>?map?=?handler.getMappedResults();
????????return?map;
????}
}????
MapResultHandler 處理程序，將結(jié)果集放入 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?{
????//?收費(fèi)標(biāo)準(zhǔn)綁定?區(qū)域樓宇單元房號?-?房屋ID
????MapLong>?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>
"mapResultLong"?type="java.util.HashMap">
????property="key"?column="k"?javaType="string"?jdbcType="VARCHAR"/>
????property="value"?column="v"?javaType="long"?jdbcType="INTEGER"/>
????????
之后在代碼中調(diào)用 SessionMapper 類對應(yīng)的方法即可。
使用 values 批量插入
MySQL insert 語句支持使用 values (),(),() 的方式一次插入多行數(shù)據(jù)，通過 mybatis foreach 結(jié)合 java 集合可以實(shí)現(xiàn)批量插入，代碼寫法如下：
<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 采用基于注解的導(dǎo)入導(dǎo)出,修改注解就可以修改Excel，非常方便，代碼維護(hù)起來也容易。
第三版：EasyExcel + 緩存數(shù)據(jù)庫查詢操作 + 批量插入
第二版采用 EasyPOI 之后，對于幾千、幾萬的 Excel 數(shù)據(jù)已經(jīng)可以輕松導(dǎo)入了，不過耗時(shí)有點(diǎn)久(5W 數(shù)據(jù) 10分鐘左右寫入到數(shù)據(jù)庫)不過由于后來導(dǎo)入的操作基本都是開發(fā)在一邊看日志一邊導(dǎo)入，也就沒有進(jìn)一步優(yōu)化。
但是好景不長，有新小區(qū)需要遷入，票據(jù) Excel 有 41w 行，這個(gè)時(shí)候使用 EasyPOI 在開發(fā)環(huán)境跑直接就 OOM 了，增大 JVM 內(nèi)存參數(shù)之后，雖然不 OOM 了，但是 CPU 占用 100% 20 分鐘仍然未能成功讀取全部數(shù)據(jù)。故在讀取大 Excel 時(shí)需要再優(yōu)化速度。莫非要我這個(gè)渣渣去深入 POI 優(yōu)化了嗎？別慌，
先上 GITHUB 找找別的開源項(xiàng)目。這時(shí)阿里 EasyExcel 映入眼簾：
emmm，這不是為我量身定制的嗎！趕緊拿來試試。EasyExcel 采用和 EasyPOI 類似的注解方式讀寫 Excel，因此從 EasyPOI 切換過來很方便，分分鐘就搞定了。也確實(shí)如阿里大神描述的：41w行、25列、45.5m 數(shù)據(jù)讀取平均耗時(shí) 50s，因此對于大 Excel 建議使用 EasyExcel 讀取。
第四版：優(yōu)化數(shù)據(jù)插入速度
在第二版插入的時(shí)候，我使用了 values 批量插入代替逐行插入。每 30000 行拼接一個(gè)長 SQL、順序插入。整個(gè)導(dǎo)入方法這塊耗時(shí)最多，非常拉跨。后來我將每次拼接的行數(shù)減少到 10000、5000、3000、1000、500 發(fā)現(xiàn)執(zhí)行最快的是 1000。
結(jié)合網(wǎng)上一些對 innodb_buffer_pool_size 描述我猜是因?yàn)檫^長的 SQL 在寫操作的時(shí)候由于超過內(nèi)存閾值，發(fā)生了磁盤交換。限制了速度，另外測試服務(wù)器的數(shù)據(jù)庫性能也不怎么樣，過多的插入他也處理不過來。所以最終采用每次 1000 條插入。
每次 1000 條插入后，為了榨干數(shù)據(jù)庫的 CPU，那么網(wǎng)絡(luò)IO的等待時(shí)間就需要利用起來，這個(gè)需要多線程來解決，而最簡單的多線程可以使用 并行流 來實(shí)現(xiàn)，接著我將代碼用并行流來測試了一下：
10w行的 excel、42w 欠單、42w記錄詳情、2w記錄、16 線程并行插入數(shù)據(jù)庫、每次 1000 行。插入時(shí)間 72s，導(dǎo)入總時(shí)間 95 s。
并行插入工具類
并行插入的代碼我封裝了一個(gè)函數(shù)式編程的工具類，也提供給大家
/**
?*?功能：利用并行流快速插入數(shù)據(jù)
?*
?*?@author?Keats
?*?@date?2020/7/1?9:25
?*/
public?class?InsertConsumer?{
????/**
?????*?每個(gè)長?SQL?插入的行數(shù)，可以根據(jù)數(shù)據(jù)庫性能調(diào)整
?????*/
????private?final?static?int?SIZE?=?1000;
????/**
?????*?如果需要調(diào)整并發(fā)數(shù)目，修改下面方法的第二個(gè)參數(shù)即可
?????*/
????static?{
????????System.setProperty("java.util.concurrent.ForkJoinPool.common.parallelism",?"4");
????}
????/**
?????*?插入方法
?????*
?????*?@param?list?????插入數(shù)據(jù)集合
?????*?@param?consumer?消費(fèi)型方法，直接使用?mapper::method?方法引用的方式
?????*?@param???????插入的數(shù)據(jù)類型
?????*/
????public?static??void?insertData(List?list,?Consumer<List>?consumer)?{
????????if?(list?==?null?||?list.size()?1)?{
????????????return;
????????}
????????List<List>?streamList?=?new?ArrayList<>();
????????for?(int?i?=?0;?i?list.size();?i?+=?SIZE)?{
????????????int?j?=?Math.min((i?+?SIZE),?list.size());
????????????List?subList?=?list.subList(i,?j);
????????????streamList.add(subList);
????????}
????????//?并行流使用的并發(fā)數(shù)是 CPU 核心數(shù)，不能局部更改。全局更改影響較大，斟酌
????????streamList.parallelStream().forEach(consumer);
????}
}
這里多數(shù)使用到很多 Java8 的API，不了解的朋友可以翻看我之前關(guān)于 Java 的博客。方法使用起來很簡單
InsertConsumer.insertData(feeList,?arrearageMapper::insertList);
其他影響性能的內(nèi)容
日志
避免在 for 循環(huán)中打印過多的 info 日志
在優(yōu)化的過程中，我還發(fā)現(xiàn)了一個(gè)特別影響性能的東西：info 日志，還是使用 41w行、25列、45.5m 數(shù)據(jù)，在 開始-數(shù)據(jù)讀取完畢 之間每 1000 行打印一條 info 日志，緩存校驗(yàn)數(shù)據(jù)-校驗(yàn)完畢 之間每行打印 3+ 條 info 日志，日志框架使用 Slf4j 。打印并持久化到磁盤。下面是打印日志和不打印日志效率的差別
打印日志
不打印日志
我以為是我選錯(cuò) Excel 文件了，又重新選了一次，結(jié)果依舊
緩存校驗(yàn)數(shù)據(jù)-校驗(yàn)完畢 不打印日志耗時(shí)僅僅是打印日志耗時(shí)的 1/10 ！
總結(jié)
提升Excel導(dǎo)入速度的方法：
使用更快的 Excel 讀取框架(推薦使用阿里 EasyExcel)
對于需要與數(shù)據(jù)庫交互的校驗(yàn)、按照業(yè)務(wù)邏輯適當(dāng)?shù)氖褂镁彺?。用空間換時(shí)間
使用 values(),(),() 拼接長 SQL 一次插入多行數(shù)據(jù)
使用多線程插入數(shù)據(jù)，利用掉網(wǎng)絡(luò)IO等待時(shí)間(推薦使用并行流，簡單易用)
避免在循環(huán)中打印無用的日志