100000行級(jí)別數(shù)據(jù)的 Excel 導(dǎo)入優(yōu)化之路
今天分享一篇大數(shù)據(jù)量Excel導(dǎo)入如何優(yōu)化的文章，非常不錯(cuò)。
需求說明
項(xiàng)目中有一個(gè) Excel 導(dǎo)入的需求：繳費(fèi)記錄導(dǎo)入
由實(shí)施 / 用戶 將別的系統(tǒng)的數(shù)據(jù)填入我們系統(tǒng)中的 Excel 模板，應(yīng)用將文件內(nèi)容讀取、校對(duì)、轉(zhuǎn)換之后產(chǎn)生欠費(fèi)數(shù)據(jù)、票據(jù)、票據(jù)詳情并存儲(chǔ)到數(shù)據(jù)庫中。
在接手之前可能由于之前導(dǎo)入的數(shù)據(jù)量并不多沒有對(duì)效率有過高的追求。但是到了 4.0 版本，預(yù)估導(dǎo)入時(shí)Excel 行數(shù)會(huì)是 10w+ 級(jí)別，而往數(shù)據(jù)庫插入的數(shù)據(jù)量是大于 3n 的，也就是說 10w 行的 Excel，則至少向數(shù)據(jù)庫插入 30w 行數(shù)據(jù)。因此優(yōu)化原來的導(dǎo)入代碼是勢(shì)在必行的。逐步分析和優(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)的，歡迎讀者在評(píng)論區(qū)留言交流提供更優(yōu)的思路
一些細(xì)節(jié)
- 數(shù)據(jù)導(dǎo)入：導(dǎo)入使用的模板由系統(tǒng)提供，格式是 xlsx (支持 65535+行數(shù)據(jù)) ，用戶按照表頭在對(duì)應(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ù)交互。對(duì)性能影響較小
數(shù)據(jù)重復(fù)性校驗(yàn)，如票據(jù)號(hào)是否和系統(tǒng)已存在的票據(jù)號(hào)重復(fù)(需要查詢數(shù)據(jù)庫，十分影響性能)
- 數(shù)據(jù)插入：測試環(huán)境數(shù)據(jù)庫使用 MySQL 5.7，未分庫分表，連接池使用 Druid
迭代記錄
1. 第一版：POI + 逐行查詢校對(duì) + 逐行插入
這個(gè)版本是最古老的版本，采用原生 POI，手動(dòng)將 Excel 中的行映射成 ArrayList 對(duì)象，然后存儲(chǔ)到 List，代碼執(zhí)行的步驟如下：
- 手動(dòng)讀取 Excel 成 List
- 循環(huán)遍歷，在循環(huán)中進(jìn)行以下步驟
- 檢驗(yàn)字段長度
- 一些查詢數(shù)據(jù)庫的校驗(yàn)，比如校驗(yàn)當(dāng)前行欠費(fèi)對(duì)應(yīng)的房屋是否在系統(tǒng)中存在，需要查詢房屋表
- 寫入當(dāng)前行數(shù)據(jù)
- 返回執(zhí)行結(jié)果，如果出錯(cuò) / 校驗(yàn)不合格。則返回提示信息并回滾數(shù)據(jù)
顯而易見的，這樣實(shí)現(xiàn)一定是趕工趕出來的，后續(xù)可能用的少也沒有察覺到性能問題，但是它最多適用于個(gè)位數(shù)/十位數(shù)級(jí)別的數(shù)據(jù)。存在以下明顯的問題：
- 查詢數(shù)據(jù)庫的校驗(yàn)對(duì)每一行數(shù)據(jù)都要查詢一次數(shù)據(jù)庫，應(yīng)用訪問數(shù)據(jù)庫來回的網(wǎng)絡(luò)IO次數(shù)被放大了 n 倍，時(shí)間也就放大了 n 倍
- 寫入數(shù)據(jù)也是逐行寫入的，問題和上面的一樣
- 數(shù)據(jù)讀取使用原生 POI，代碼十分冗余，可維護(hù)性差。
2. 第二版：EasyPOI + 緩存數(shù)據(jù)庫查詢操作 + 批量插入
針對(duì)第一版分析的三個(gè)問題，分別采用以下三個(gè)方法優(yōu)化
緩存數(shù)據(jù)，以空間換時(shí)間
逐行查詢數(shù)據(jù)庫校驗(yàn)的時(shí)間成本主要在來回的網(wǎng)絡(luò)IO中，優(yōu)化方法也很簡單。將參加校驗(yàn)的數(shù)據(jù)全部緩存到 HashMap 中。直接到 HashMap 去命中。關(guān)注公號(hào)：碼猿技術(shù)專欄，回復(fù)關(guān)鍵詞：1111 獲取阿里內(nèi)部Java調(diào)優(yōu)手冊(cè)
例如：校驗(yàn)行中的房屋是否存在，原本是要用 區(qū)域 + 樓宇 + 單元 + 房號(hào) 去查詢房屋表匹配房屋ID，查到則校驗(yàn)通過，生成的欠單中存儲(chǔ)房屋ID，校驗(yàn)不通過則返回錯(cuò)誤信息給用戶。而房屋信息在導(dǎo)入欠費(fèi)的時(shí)候是不會(huì)更新的。并且一個(gè)小區(qū)的房屋信息也不會(huì)很多(5000以內(nèi))因此我采用一條SQL，將該小區(qū)下所有的房屋以 區(qū)域/樓宇/單元/房號(hào) 作為 key，以 房屋ID 作為 value，存儲(chǔ)到 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ū)域樓宇單元房號(hào) - 房屋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 處理程序，將結(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ū)域樓宇單元房號(hào) - 房屋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>之后在代碼中調(diào)用 SessionMapper 類對(duì)應(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
http://doc.wupaas.com/docs/easypoi/easypoi-1c0u4mo8p4ro8 采用基于注解的導(dǎo)入導(dǎo)出,修改注解就可以修改Excel，非常方便，代碼維護(hù)起來也容易。
3. 第三版：EasyExcel + 緩存數(shù)據(jù)庫查詢操作 + 批量插入
第二版采用 EasyPOI 之后，對(duì)于幾千、幾萬的 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，因此對(duì)于大 Excel 建議使用 EasyExcel 讀取。
4. 第四版：優(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)上一些對(duì) innodb_buffer_pool_size 描述我猜是因?yàn)檫^長的 SQL 在寫操作的時(shí)候由于超過內(nèi)存閾值，發(fā)生了磁盤交換。關(guān)注公號(hào)：碼猿技術(shù)專欄，回復(fù)關(guān)鍵詞：1111 獲取阿里內(nèi)部Java調(diào)優(yōu)手冊(cè)；限制了速度，另外測試服務(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 <T> 插入的數(shù)據(jù)類型
*/
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);
}
// 并行流使用的并發(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)
- 對(duì)于需要與數(shù)據(jù)庫交互的校驗(yàn)、按照業(yè)務(wù)邏輯適當(dāng)?shù)氖褂镁彺妗Ｓ每臻g換時(shí)間
- 使用 values(),(),() 拼接長 SQL 一次插入多行數(shù)據(jù)
- 使用多線程插入數(shù)據(jù)，利用掉網(wǎng)絡(luò)IO等待時(shí)間(推薦使用并行流，簡單易用)
- 避免在循環(huán)中打印無用的日志
