100000行級(jí)別數(shù)據(jù)的 Excel 導(dǎo)入優(yōu)化之路

作者：不才陳某 2023-06-29 08:22:43

由實(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ù)庫(kù)中。

今天分享一篇大數(shù)據(jù)量Excel導(dǎo)入如何優(yōu)化的文章，非常不錯(cuò)。

需求說(shuō)明

項(xiàng)目中有一個(gè) Excel 導(dǎo)入的需求：繳費(fèi)記錄導(dǎo)入

在接手之前可能由于之前導(dǎo)入的數(shù)據(jù)量并不多沒(méi)有對(duì)效率有過(guò)高的追求。但是到了 4.0 版本，預(yù)估導(dǎo)入時(shí)Excel 行數(shù)會(huì)是 10w+ 級(jí)別，而往數(shù)據(jù)庫(kù)插入的數(shù)據(jù)量是大于 3n 的，也就是說(shuō) 10w 行的 Excel，則至少向數(shù)據(jù)庫(kù)插入 30w 行數(shù)據(jù)。因此優(yōu)化原來(lái)的導(dǎo)入代碼是勢(shì)在必行的。逐步分析和優(yōu)化了導(dǎo)入的代碼，使之在百秒內(nèi)完成(最終性能瓶頸在數(shù)據(jù)庫(kù)的處理速度上，測(cè)試服務(wù)器 4g 內(nèi)存不僅放了數(shù)據(jù)庫(kù)，還放了很多微服務(wù)應(yīng)用。處理能力不太行)。具體的過(guò)程如下，每一步都有列出影響性能的問(wèn)題和解決的辦法。

導(dǎo)入 Excel 的需求在系統(tǒng)中還是很常見(jiàn)的，優(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)列寫(xiě)入相應(yīng)的數(shù)據(jù)
數(shù)據(jù)校驗(yàn)：數(shù)據(jù)校驗(yàn)有兩種：

字段長(zhǎng)度、字段正則表達(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ù)庫(kù)，十分影響性能)

數(shù)據(jù)插入：測(cè)試環(huán)境數(shù)據(jù)庫(kù)使用 MySQL 5.7，未分庫(kù)分表，連接池使用 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)字段長(zhǎng)度
一些查詢數(shù)據(jù)庫(kù)的校驗(yàn)，比如校驗(yàn)當(dāng)前行欠費(fèi)對(duì)應(yīng)的房屋是否在系統(tǒng)中存在，需要查詢房屋表
寫(xiě)入當(dāng)前行數(shù)據(jù)

返回執(zhí)行結(jié)果，如果出錯(cuò) / 校驗(yàn)不合格。則返回提示信息并回滾數(shù)據(jù)

顯而易見(jiàn)的，這樣實(shí)現(xiàn)一定是趕工趕出來(lái)的，后續(xù)可能用的少也沒(méi)有察覺(jué)到性能問(wèn)題，但是它最多適用于個(gè)位數(shù)/十位數(shù)級(jí)別的數(shù)據(jù)。存在以下明顯的問(wèn)題：

查詢數(shù)據(jù)庫(kù)的校驗(yàn)對(duì)每一行數(shù)據(jù)都要查詢一次數(shù)據(jù)庫(kù)，應(yīng)用訪問(wèn)數(shù)據(jù)庫(kù)來(lái)回的網(wǎng)絡(luò)IO次數(shù)被放大了 n 倍，時(shí)間也就放大了 n 倍
寫(xiě)入數(shù)據(jù)也是逐行寫(xiě)入的，問(wèn)題和上面的一樣
數(shù)據(jù)讀取使用原生 POI，代碼十分冗余，可維護(hù)性差。

2. 第二版：EasyPOI + 緩存數(shù)據(jù)庫(kù)查詢操作 + 批量插入

針對(duì)第一版分析的三個(gè)問(wèn)題，分別采用以下三個(gè)方法優(yōu)化

緩存數(shù)據(jù)，以空間換時(shí)間

逐行查詢數(shù)據(jù)庫(kù)校驗(yàn)的時(shí)間成本主要在來(lái)回的網(wǎng)絡(luò)IO中，優(yōu)化方法也很簡(jiǎn)單。將參加校驗(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)通過(guò)，生成的欠單中存儲(chǔ)房屋ID，校驗(yàn)不通過(guò)則返回錯(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é)果直接寫(xiě)人一個(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 語(yǔ)句支持使用 values (),(),() 的方式一次插入多行數(shù)據(jù)，通過(guò) mybatis foreach 結(jié)合 java 集合可以實(shí)現(xiàn)批量插入，代碼寫(xiě)法如下：

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

使用 EasyPOI 讀寫(xiě) Excel

http://doc.wupaas.com/docs/easypoi/easypoi-1c0u4mo8p4ro8 采用基于注解的導(dǎo)入導(dǎo)出,修改注解就可以修改Excel，非常方便，代碼維護(hù)起來(lái)也容易。

3. 第三版：EasyExcel + 緩存數(shù)據(jù)庫(kù)查詢操作 + 批量插入

第二版采用 EasyPOI 之后，對(duì)于幾千、幾萬(wàn)的 Excel 數(shù)據(jù)已經(jīng)可以輕松導(dǎo)入了，不過(guò)耗時(shí)有點(diǎn)久(5W 數(shù)據(jù) 10分鐘左右寫(xiě)入到數(shù)據(jù)庫(kù))不過(guò)由于后來(lái)導(dǎo)入的操作基本都是開(kāi)發(fā)在一邊看日志一邊導(dǎo)入，也就沒(méi)有進(jìn)一步優(yōu)化。但是好景不長(zhǎng)，有新小區(qū)需要遷入，票據(jù) Excel 有 41w 行，這個(gè)時(shí)候使用 EasyPOI 在開(kāi)發(fā)環(huán)境跑直接就 OOM 了，增大 JVM 內(nèi)存參數(shù)之后，雖然不 OOM 了，但是 CPU 占用 100% 20 分鐘仍然未能成功讀取全部數(shù)據(jù)。故在讀取大 Excel 時(shí)需要再優(yōu)化速度。莫非要我這個(gè)渣渣去深入 POI 優(yōu)化了嗎？別慌，先上 GITHUB 找找別的開(kāi)源項(xiàng)目。這時(shí)阿里 EasyExcel 映入眼簾：

圖片

emmm，這不是為我量身定制的嗎！趕緊拿來(lái)試試。EasyExcel 采用和 EasyPOI 類似的注解方式讀寫(xiě) Excel，因此從 EasyPOI 切換過(guò)來(lái)很方便，分分鐘就搞定了。也確實(shí)如阿里大神描述的：41w行、25列、45.5m 數(shù)據(jù)讀取平均耗時(shí) 50s，因此對(duì)于大 Excel 建議使用 EasyExcel 讀取。

4. 第四版：優(yōu)化數(shù)據(jù)插入速度

在第二版插入的時(shí)候，我使用了 values 批量插入代替逐行插入。每 30000 行拼接一個(gè)長(zhǎng) SQL、順序插入。整個(gè)導(dǎo)入方法這塊耗時(shí)最多，非常拉跨。后來(lái)我將每次拼接的行數(shù)減少到 10000、5000、3000、1000、500 發(fā)現(xiàn)執(zhí)行最快的是 1000。結(jié)合網(wǎng)上一些對(duì) innodb_buffer_pool_size 描述我猜是因?yàn)檫^(guò)長(zhǎng)的 SQL 在寫(xiě)操作的時(shí)候由于超過(guò)內(nèi)存閾值，發(fā)生了磁盤(pán)交換。關(guān)注公號(hào)：碼猿技術(shù)專欄，回復(fù)關(guān)鍵詞：1111 獲取阿里內(nèi)部Java調(diào)優(yōu)手冊(cè)；限制了速度，另外測(cè)試服務(wù)器的數(shù)據(jù)庫(kù)性能也不怎么樣，過(guò)多的插入他也處理不過(guò)來(lái)。所以最終采用每次 1000 條插入。

每次 1000 條插入后，為了榨干數(shù)據(jù)庫(kù)的 CPU，那么網(wǎng)絡(luò)IO的等待時(shí)間就需要利用起來(lái)，這個(gè)需要多線程來(lái)解決，而最簡(jiǎn)單的多線程可以使用并行流來(lái)實(shí)現(xiàn)，接著我將代碼用并行流來(lái)測(cè)試了一下：

10w行的 excel、42w 欠單、42w記錄詳情、2w記錄、16 線程并行插入數(shù)據(jù)庫(kù)、每次 1000 行。插入時(shí)間 72s，導(dǎo)入總時(shí)間 95 s。

圖片

并行插入工具類

并行插入的代碼我封裝了一個(gè)函數(shù)式編程的工具類，也提供給大家

/**
 * 功能：利用并行流快速插入數(shù)據(jù)
 *
 * @author Keats
 * @date 2020/7/1 9:25
 */
public class InsertConsumer {
    /**
     * 每個(gè)長(zhǎng) SQL 插入的行數(shù)，可以根據(jù)數(shù)據(jù)庫(kù)性能調(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 的博客。方法使用起來(lái)很簡(jiǎn)單

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

其他影響性能的內(nèi)容

日志

避免在 for 循環(huán)中打印過(guò)多的 info 日志

在優(yōu)化的過(guò)程中，我還發(fā)現(xiàn)了一個(gè)特別影響性能的東西：info 日志，還是使用 41w行、25列、45.5m 數(shù)據(jù)，在 開(kāi)始-數(shù)據(jù)讀取完畢 之間每 1000 行打印一條 info 日志，緩存校驗(yàn)數(shù)據(jù)-校驗(yàn)完畢 之間每行打印 3+ 條 info 日志，日志框架使用 Slf4j 。打印并持久化到磁盤(pán)。下面是打印日志和不打印日志效率的差別

打印日志

圖片

不打印日志

圖片

我以為是我選錯(cuò) Excel 文件了，又重新選了一次，結(jié)果依舊

圖片

緩存校驗(yàn)數(shù)據(jù)-校驗(yàn)完畢不打印日志耗時(shí)僅僅是打印日志耗時(shí)的 1/10 ！

總結(jié)

提升Excel導(dǎo)入速度的方法：

使用更快的 Excel 讀取框架(推薦使用阿里 EasyExcel)
對(duì)于需要與數(shù)據(jù)庫(kù)交互的校驗(yàn)、按照業(yè)務(wù)邏輯適當(dāng)?shù)氖褂镁彺妗Ｓ每臻g換時(shí)間
使用 values(),(),() 拼接長(zhǎng) SQL 一次插入多行數(shù)據(jù)
使用多線程插入數(shù)據(jù)，利用掉網(wǎng)絡(luò)IO等待時(shí)間(推薦使用并行流，簡(jiǎn)單易用)
避免在循環(huán)中打印無(wú)用的日志

責(zé)任編輯：武曉燕來(lái)源：碼猿技術(shù)專欄

數(shù)據(jù)Excel 模板