人人妻人人澡人人爽人人精品av_精品乱码一区内射人妻无码_老司机午夜福利视频_精品成品国色天香摄像头_99精品福利国产在线导航_野花社区在线观看视频_大地资源在线影视播放_东北高大肥胖丰满熟女_金门瓶马车内剧烈运动

首頁>國內(nèi) > 正文

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

2023-06-29 11:05:06來源:碼猿技術(shù)專欄

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

需求說明

項目中有一個 Excel 導(dǎo)入的需求:繳費記錄導(dǎo)入

由實施 / 用戶 將別的系統(tǒng)的數(shù)據(jù)填入我們系統(tǒng)中的 Excel 模板,應(yīng)用將文件內(nèi)容讀取、校對、轉(zhuǎn)換之后產(chǎn)生欠費數(shù)據(jù)、票據(jù)、票據(jù)詳情并存儲到數(shù)據(jù)庫中。


(資料圖片)

在接手之前可能由于之前導(dǎo)入的數(shù)據(jù)量并不多沒有對效率有過高的追求。但是到了 4.0 版本,預(yù)估導(dǎo)入時Excel 行數(shù)會是 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)的思路

一些細節(jié)數(shù)據(jù)導(dǎo)入:導(dǎo)入使用的模板由系統(tǒng)提供,格式是 xlsx (支持 65535+行數(shù)據(jù)) ,用戶按照表頭在對應(yīng)列寫入相應(yīng)的數(shù)據(jù)數(shù)據(jù)校驗:數(shù)據(jù)校驗有兩種:

字段長度、字段正則表達式校驗等,內(nèi)存內(nèi)校驗不存在外部數(shù)據(jù)交互。對性能影響較小

數(shù)據(jù)重復(fù)性校驗,如票據(jù)號是否和系統(tǒng)已存在的票據(jù)號重復(fù)(需要查詢數(shù)據(jù)庫,十分影響性能)

數(shù)據(jù)插入:測試環(huán)境數(shù)據(jù)庫使用 MySQL 5.7,未分庫分表,連接池使用 Druid迭代記錄1. 第一版:POI + 逐行查詢校對 + 逐行插入

這個版本是最古老的版本,采用原生 POI,手動將 Excel 中的行映射成 ArrayList 對象,然后存儲到 List,代碼執(zhí)行的步驟如下:

手動讀取 Excel 成 List循環(huán)遍歷,在循環(huán)中進行以下步驟檢驗字段長度一些查詢數(shù)據(jù)庫的校驗,比如校驗當前行欠費對應(yīng)的房屋是否在系統(tǒng)中存在,需要查詢房屋表寫入當前行數(shù)據(jù)返回執(zhí)行結(jié)果,如果出錯 / 校驗不合格。則返回提示信息并回滾數(shù)據(jù)

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

查詢數(shù)據(jù)庫的校驗對每一行數(shù)據(jù)都要查詢一次數(shù)據(jù)庫,應(yīng)用訪問數(shù)據(jù)庫來回的網(wǎng)絡(luò)IO次數(shù)被放大了 n 倍,時間也就放大了 n 倍寫入數(shù)據(jù)也是逐行寫入的,問題和上面的一樣數(shù)據(jù)讀取使用原生 POI,代碼十分冗余,可維護性差。2. 第二版:EasyPOI + 緩存數(shù)據(jù)庫查詢操作 + 批量插入

針對第一版分析的三個問題,分別采用以下三個方法優(yōu)化

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

逐行查詢數(shù)據(jù)庫校驗的時間成本主要在來回的網(wǎng)絡(luò)IO中,優(yōu)化方法也很簡單。將參加校驗的數(shù)據(jù)全部緩存到 HashMap 中。直接到 HashMap 去命中。關(guān)注公號:碼猿技術(shù)專欄,回復(fù)關(guān)鍵詞:1111 獲取阿里內(nèi)部Java調(diào)優(yōu)手冊

例如:校驗行中的房屋是否存在,原本是要用 區(qū)域 + 樓宇 + 單元 + 房號 去查詢房屋表匹配房屋ID,查到則校驗通過,生成的欠單中存儲房屋ID,校驗不通過則返回錯誤信息給用戶。而房屋信息在導(dǎo)入欠費的時候是不會更新的。并且一個小區(qū)的房屋信息也不會很多(5000以內(nèi))因此我采用一條SQL,將該小區(qū)下所有的房屋以 區(qū)域/樓宇/單元/房號 作為 key,以 房屋ID 作為 value,存儲到 HashMap 中,后續(xù)校驗只需要在 HashMap 中命中

自定義 SessionMapper

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

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

@Repositorypublic class SessionMapper extends SqlSessionDaoSupport {    @Resource    public void setSqlSessionFactory(SqlSessionFactory sqlSessionFactory) {        super.setSqlSessionFactory(sqlSessionFactory);    }    // 區(qū)域樓宇單元房號 - 房屋ID    @SuppressWarnings("unchecked")    public Map getHouseMapByAreaId(Long areaId) {        MapResultHandler handler = new MapResultHandler(); this.getSqlSession().select(BaseUnitMapper.class.getName()+".getHouseMapByAreaId", areaId, handler);        Map 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 {    // 收費標準綁定 區(qū)域樓宇單元房號 - 房屋ID    Map getHouseMapByAreaId(@Param("areaId") Long areaId);}
示例 Mapper.xml
                    

之后在代碼中調(diào)用 SessionMapper 類對應(yīng)的方法即可。

使用 values 批量插入

MySQL insert 語句支持使用 values (),(),() 的方式一次插入多行數(shù)據(jù),通過 mybatis foreach 結(jié)合 java 集合可以實現(xiàn)批量插入,代碼寫法如下:

    insert into table(colom1, colom2)    values         ( ##{item.colom1}, ##{item.colom2})    
使用 EasyPOI 讀寫 Excel

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

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

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

圖片

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

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

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

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

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

圖片

并行插入工具類

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

/** * 功能:利用并行流快速插入數(shù)據(jù) * * @author Keats * @date 2020/7/1 9:25 */public class InsertConsumer {    /**     * 每個長 SQL 插入的行數(shù),可以根據(jù)數(shù)據(jù)庫性能調(diào)整     */    private final static int SIZE = 1000;    /**     * 如果需要調(diào)整并發(fā)數(shù)目,修改下面方法的第二個參數(shù)即可     */    static {        System.setProperty("java.util.concurrent.ForkJoinPool.common.parallelism", "4");    }    /**     * 插入方法     *     * @param list     插入數(shù)據(jù)集合     * @param consumer 消費型方法,直接使用 mapper::method 方法引用的方式     * @param       插入的數(shù)據(jù)類型     */    public static  void insertData(List list, Consumer> consumer) {        if (list == null || list.size() < 1) {            return;        }        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)了一個特別影響性能的東西:info 日志,還是使用 41w行、25列、45.5m 數(shù)據(jù),在開始-數(shù)據(jù)讀取完畢之間每 1000 行打印一條 info 日志,緩存校驗數(shù)據(jù)-校驗完畢之間每行打印 3+ 條 info 日志,日志框架使用 Slf4j 。打印并持久化到磁盤。下面是打印日志和不打印日志效率的差別

打印日志

圖片

不打印日志

圖片

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

圖片

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

總結(jié)

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

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

關(guān)鍵詞:

相關(guān)新聞

Copyright 2015-2020   三好網(wǎng)  版權(quán)所有 聯(lián)系郵箱:435 22 [email protected]  備案號: 京ICP備2022022245號-21