4次迭代!10w行级别数据的Excel导入优化记录
點擊上方?好好學(xué)java?,選擇?星標(biāo)?公眾號
重磅資訊、干貨,第一時間送達今日推薦:2020年7月程序員工資統(tǒng)計,平均14357元,又跌了,扎心個人原創(chuàng)100W+訪問量博客:點擊前往,查看更多鏈接 : https://www.cnblogs.com/keatsCoder/p/13217561.html
excel導(dǎo)入是個很常見的業(yè)務(wù)需求,不同的數(shù)據(jù)級別使用的方案效率差別也會很大,看看作者的4次優(yōu)化,有沒給你帶來一些啟發(fā)!
需求說明
一些細(xì)節(jié)
迭代記錄
第一版:POI + 逐行查詢校對 + 逐行插入
第二版:EasyPOI + 緩存數(shù)據(jù)庫查詢操作 + 批量插入
第三版:EasyExcel + 緩存數(shù)據(jù)庫查詢操作 + 批量插入
第四版:優(yōu)化數(shù)據(jù)插入速度
其他影響性能的內(nèi)容
日志
總結(jié)
需求說明
項目中有一個 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)的思路
一些細(xì)節(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
迭代記錄
第一版:POI + 逐行查詢校對 + 逐行插入
這個版本是最古老的版本,采用原生 POI,手動將 Excel 中的行映射成 ArrayList 對象,然后存儲到 List<ArrayList> ,代碼執(zhí)行的步驟如下:
手動讀取 Excel 成 List<ArrayList>
循環(huán)遍歷,在循環(huán)中進行以下步驟
檢驗字段長度
一些查詢數(shù)據(jù)庫的校驗,比如校驗當(dāng)前行欠費對應(yīng)的房屋是否在系統(tǒng)中存在,需要查詢房屋表
寫入當(dā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,代碼十分冗余,可維護性差。
第二版:EasyPOI + 緩存數(shù)據(jù)庫查詢操作 + 批量插入
針對第一版分析的三個問題,分別采用以下三個方法優(yōu)化
緩存數(shù)據(jù),以空間換時間
逐行查詢數(shù)據(jù)庫校驗的時間成本主要在來回的網(wǎng)絡(luò) IO 中,優(yōu)化方法也很簡單。將參加校驗的數(shù)據(jù)全部緩存到 HashMap 中。直接到 HashMap 去命中。
粉絲福利:教妹子手?jǐn)]了50個項目實戰(zhàn)后,我住院了……
例如:校驗行中的房屋是否存在,原本是要用 區(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é)果集
@Repository public class SessionMapper extends SqlSessionDaoSupport {@Resourcepublic void setSqlSessionFactory(SqlSessionFactory sqlSessionFactory) {super.setSqlSessionFactory(sqlSessionFactory);}// 區(qū)域樓宇單元房號 - 房屋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();@Overridepublic 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 {// 收費標(biāo)準(zhǔn)綁定 區(qū)域樓宇單元房號 - 房屋IDMap<String, Long> getHouseMapByAreaId(@Param("areaId") Long areaId); }示例 Mapper.xml
<select id="getHouseMapByAreaId" resultMap="mapResultLong">SELECTCONCAT( h.bulid_area_name, h.build_name, h.unit_name, h.house_num ) k,h.house_id vFROMbase_house hWHEREh.area_id = #{areaId}GROUP BYh.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 類對應(yīng)的方法即可。
使用 values 批量插入
MySQL insert 語句支持使用 values (),(),() 的方式一次插入多行數(shù)據(jù),通過 mybatis foreach 結(jié)合 java 集合可以實現(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,非常方便,代碼維護起來也容易。
第三版: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 讀取。
第四版:優(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ā)生了磁盤交換。限制了速度,另外測試服務(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 <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)了一個特別影響性能的東西: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ù)邏輯適當(dāng)?shù)氖褂镁彺妗S每臻g換時間
使用 values(),(),() 拼接長 SQL 一次插入多行數(shù)據(jù)
使用多線程插入數(shù)據(jù),利用掉網(wǎng)絡(luò) IO 等待時間 (推薦使用并行流,簡單易用)
避免在循環(huán)中打印無用的日志
如果你覺得閱讀后有收獲,不妨點個推薦吧
最后,再附上我歷時三個月總結(jié)的?Java 面試 + Java 后端技術(shù)學(xué)習(xí)指南,筆者這幾年及春招的總結(jié),github 1.4k star,拿去不謝!下載方式1.?首先掃描下方二維碼2.?后臺回復(fù)「Java面試」即可獲取總結(jié)
以上是生活随笔為你收集整理的4次迭代!10w行级别数据的Excel导入优化记录的全部內(nèi)容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 万字讲解API网关的来龙去脉
- 下一篇: PanDownload复活了!速度60M