使用EasyExcel實現(xiàn)百萬級別數(shù)據(jù)導出的代碼示例
前言
近期需要開發(fā)一個將百萬數(shù)據(jù)量MySQL8的數(shù)據(jù)導出到excel的功能，查閱相關資料后便整理了這篇實現(xiàn)方案供讀者參考。
需求簡述
該數(shù)據(jù)表是一張用戶表，包含id和name，該用戶表數(shù)據(jù)量在300w左右，以自增id作為主鍵，而功能要求我們在一分鐘之內(nèi)完成百萬數(shù)據(jù)導出到excel。需要注意的是，我們導出的excel格式為xlsx，它的每一個sheet只能容納100w的數(shù)據(jù)，這也就意味著我們的數(shù)據(jù)必須以100w作為批次寫到不同的sheet中。
實現(xiàn)思路
我們先來說說需要解決的問題:
- 如果一次性查詢
300w左右的數(shù)據(jù)可能會占據(jù)大量的內(nèi)存，如果對象字段很多的情況下，很可能出現(xiàn)內(nèi)存溢出，我們要如何解決？ - 每個
excel文件都有sheet，并且每個sheet只能容納100w左右的數(shù)據(jù)，對于這個問題我們要如何解決？ - 數(shù)據(jù)寫入到
excel時，有沒有合適的工具推薦？
對于問題1我們采用分頁查詢的方式進行查詢，參考自己堆內(nèi)存的配置推算每次分頁查詢的數(shù)據(jù)量。因為問題1采用了分頁查詢，我們完全可以通過分頁查詢的次數(shù)推算出一個sheet寫入了多少數(shù)據(jù)，例如我們每次分頁查詢50w的數(shù)據(jù)，那么每兩次就可以視為一個sheet寫滿了，我們就可以創(chuàng)建一個新的sheet寫入數(shù)據(jù)。
這里需要注意一點，因為我們分頁查詢面對的是百萬級別的數(shù)據(jù)，所以隨著分頁的推進勢必出現(xiàn)深分頁導致查詢效率勢降低，所以為了提高分頁查詢的效率，我們可以利用查詢數(shù)據(jù)有序的特性，通過id作為偏移進行分頁查詢。
例如我們第一次分頁查詢的sql語句為:
select * from t_user limit 500000 ;
假如我們不以id作為索引，那么第二次的分頁查詢sql則是:
select * from t_user limit 500000,500000 ;
查看該查詢執(zhí)行計劃，可以看到該查詢一次性查詢到幾乎全表的數(shù)據(jù)，并且還走了全秒掃描性能可想而知:
id|select_type|table |partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra| --+-----------+------+----------+----+-------------+---+-------+---+-------+--------+-----+ 1|SIMPLE |t_user| |ALL | | | | |2993040| 100.0| |
因為我們的數(shù)據(jù)表是id自增的，所以我們查詢的時候完全可以基于該特性通過上一次查詢到的id作為篩選條件進行分頁查詢。
所以我們的分頁查詢可直接改為:
select * from t_user where id > 500000 limit 500000 ;
再次查看執(zhí)行計劃可以發(fā)現(xiàn)該查詢?yōu)榉秶樵?，查詢到的?shù)據(jù)量也少了很多，性能顯著提升:
id|select_type|table |partitions|type |possible_keys|key |key_len|ref|rows |filtered|Extra | --+-----------+------+----------+-----+-------------+-------+-------+---+-------+--------+-----------+ 1|SIMPLE |t_user| |range|PRIMARY |PRIMARY|8 | |1496520| 100.0|Using where|
因為市面上比較多的excel導出工具，常見的就是Apache poi，但是它們的操作對于內(nèi)存的消耗非常嚴重，對于我們這種大數(shù)據(jù)量的寫入不是很友好，所以筆者更推薦使用阿里的EasyExcel，它對poi進行一定的封裝和優(yōu)化，同等數(shù)據(jù)量寫入使用的內(nèi)存更小。
解決上述問題之后，我們就可以說說代碼實現(xiàn)思路了，以本文示例來說，有一張用戶表有300w左右的數(shù)據(jù)，每次查詢時只需查詢id(4字節(jié))和name(10字節(jié))，按照64位的操作系統(tǒng)來說，一個user對象所占用的內(nèi)存大小為:
object header +pointer+id字段+name字段大小=8+8+4+10=30字節(jié)
因為java對象內(nèi)存大小需要16位對齊，需要補齊2個字節(jié)，所以實際大小為32字節(jié)，按照筆者對于堆內(nèi)存的配置，每次查詢50w條數(shù)據(jù)是允許的，所以每次從數(shù)據(jù)庫讀取數(shù)據(jù)并轉為java對象，也只需要32*500000/1024即15M內(nèi)存即可。
確定每次分頁查詢50w條數(shù)據(jù)之后，我們就需要確定一共需要查詢幾個分頁，然后就可以根據(jù)pageSize確定查詢的頁數(shù)。
因為每次查詢50w條數(shù)據(jù)，所以每兩次完成分頁查詢和寫入基本上一個sheet就會滿了，這時候我們就需要創(chuàng)建一個新的sheet進行數(shù)據(jù)寫入了。
總結一下實現(xiàn)步驟:
- 查詢目標數(shù)據(jù)量大小。
- 根據(jù)每次分頁大小確定查詢頁數(shù)。
- 根據(jù)頁數(shù)大小進行遍歷，進行分頁查詢，并將數(shù)據(jù)寫入到文件中。
- 基于頁數(shù)確定
sheet切換時機。
代碼示例
以下便是筆者基于上述思路所實現(xiàn)的代碼，查看日志也可以發(fā)現(xiàn)50w的數(shù)據(jù)查詢和寫入加起來只需6s。最終執(zhí)行耗時也只需45s。
public static void main(String[] args) {
SpringApplication app = new SpringApplication(WebApplication.class);
Environment env = app.run(args).getEnvironment();
logger.info("啟動成功??！");
logger.info("地址: \thttp://127.0.0.1:{}", env.getProperty("server.port"));
TUserMapper userMapper = SpringUtil.getBean(TUserMapper.class);
//計算總的數(shù)據(jù)量
int count = (int) userMapper.countByExample(null);
//獲取分頁總數(shù)
int queryCount = 50_0000;
int pageCount = count % queryCount == 0 ? count / queryCount : count / queryCount + 1;
//設置導出的文件名
String fileName = "result.xlsx";
//設置excel的sheet號碼
int sheetNo = 1;
//設置第一個sheet的名字
String sheetName = "sheet-" + sheetNo;
long start = System.currentTimeMillis();
// 創(chuàng)建writeSheet
WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo, sheetName).build();
//記錄每次分頁查詢的最大值
Long maxId = null;
//指定文件
try (ExcelWriter excelWriter = EasyExcel.write(fileName, TUser.class).build()) {
//寫入每一頁分頁查詢的數(shù)據(jù)
for (int i = 1; i <= pageCount; i++) {
// 分頁去數(shù)據(jù)庫查詢數(shù)據(jù) 這里可以去數(shù)據(jù)庫查詢每一頁的數(shù)據(jù)
long queryStart = System.currentTimeMillis();
TUserExample userExample = new TUserExample();
//如果是第一次則直接進行分頁查詢，反之基于上一次分頁查詢的分頁定位實際偏移量，篩選前n條數(shù)據(jù)以達到分頁效果
if (i == 1) {
PageHelper.startPage(i, queryCount, false);
} else if (maxId != null) {
userExample.createCriteria().andIdGreaterThan(maxId);
PageHelper.startPage(0, queryCount, false);
}
List<TUser> userList = userMapper.selectByExample(userExample);
//更新下一次分頁查詢用的id
if (CollUtil.isNotEmpty(userList)) {
maxId = userList.get(userList.size() - 1).getId();
}
long queryEnd = System.currentTimeMillis();
logger.info("數(shù)據(jù)大小:{},寫入sheet位置:{}，耗時:{}", userList.size(), sheetName, queryEnd - queryStart);
long writeStart = System.currentTimeMillis();
excelWriter.write(userList, writeSheet);
long writeEnd = System.currentTimeMillis();
logger.info("本次寫入耗時:{}", writeEnd - writeStart);
//如果% 2 == 0，則說明一個sheet寫入了50*2即100w的數(shù)據(jù)，需要創(chuàng)建新的sheet進行寫入
if (i % 2 == 0) {
sheetName = "sheet-" + (++sheetNo);
writeSheet = EasyExcel.writerSheet(sheetNo, sheetName).build();
logger.info("寫滿一個sheet,切換到下一個sheet:{}", sheetName);
}
}
}
long total = System.currentTimeMillis() - start;
logger.info("導出結束，總耗時:{}", total);
}
可能會有讀者好奇筆者這個50w的數(shù)值設計思路是什么，除了考慮避免OOM以外，還考慮到每個sheet只能寫入100w條的數(shù)據(jù)，為了方便通過分頁查詢的輪次確定當前寫入的數(shù)據(jù)量大小，筆者嘗試過20w、50w。
最終在壓測結果上看出，50w讀寫耗時雖然是20w的2倍，但是IO次數(shù)卻不到20w查詢的二分之一，通過更少的IO操作獲得更好的執(zhí)行性能。
# 50w的讀寫耗時 com.sharkChili.webTemplate.config.WebApplication :73 [32m [0;39m 數(shù)據(jù)大小:500000,寫入sheet位置:sheet-1，耗時:4719 2023-12-03 10:13:58.675 INFO com.sharkChili.webTemplate.config.WebApplication :78 [32m [0;39m 本次寫入耗時:2911 2023-12-03 10:14:02.517 INFO com.sharkChili.webTemplate.config.WebApplication :73 [32m [0;39m 數(shù)據(jù)大小:500000,寫入sheet位置:sheet-1，耗時:3841 2023-12-03 10:14:04.860 INFO com.sharkChili.webTemplate.config.WebApplication :78 [32m [0;39m 本次寫入耗時:2343
小結
以上便是筆者的百萬級別數(shù)據(jù)導出的落地方案，可以看出筆者著重在分頁查詢大小和分頁查詢sql上進行重點優(yōu)化，通過平衡分頁查詢的數(shù)據(jù)量和IO次數(shù)找到合適的pageSize，再通過上一次分頁查詢結果定位下一次查詢的id作為where條件，避免分頁查詢時的全秒掃描以得到符合業(yè)務需求的高性能sql，從而完成百萬級別數(shù)據(jù)的高效導出。
以上就是使用EasyExcel實現(xiàn)百萬級別數(shù)據(jù)導出的代碼示例的詳細內(nèi)容，更多關于EasyExcel實現(xiàn)數(shù)據(jù)導出的資料請關注腳本之家其它相關文章！
相關文章
Java?基于Hutool實現(xiàn)DES加解密示例詳解
這篇文章主要介紹了Java基于Hutool實現(xiàn)DES加解密，本文通過示例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下2023-08-08
SpringBoot的監(jiān)控(Actuator)功能用法詳解
這篇文章主要介紹了SpringBoot的監(jiān)控(Actuator)功能用法,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2025-03-03
這篇文章主要給大家介紹了關于java中for循環(huán)操作集合使用的相關資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者使用java具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧2019-03-03
這篇文章主要介紹了JDBC查詢Map轉對象實現(xiàn)過程詳解,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2020-10-10
配置文件相對分散。在一個微服務架構下，配置文件會隨著微服務的增多變的越來越多，而且分散 在各個微服務中，不好統(tǒng)一配置和管理。每一個環(huán)境所使用的配置理論上都是不同的，一旦需要修改，就需要我們?nèi)ジ鱾€微服務下手動維護2023-02-02
今天小編就為大家分享一篇關于二叉樹的圖文詳解，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧,希望能對你有所幫助2021-07-07
這篇文章為大家介紹了java基礎開發(fā)中泛型類的詳解，包括泛型的概念以及應用實例有需要的朋友可以借鑒參考下，希望能夠有所幫助2021-10-10
這篇文章主要介紹了SpringMVC Tomcat控制臺亂碼問題解決方案,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2020-05-05
淺談hibernate中對象的3種狀態(tài)_瞬時態(tài)、持久態(tài)、脫管態(tài)
下面小編就為大家?guī)硪黄獪\談hibernate中對象的3種狀態(tài)_瞬時態(tài)、持久態(tài)、脫管態(tài)。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-08-08
這篇文章主要給大家介紹了關于Spring Boot中自動化配置的利弊以及解決方法，文中通過示例代碼介紹的非常詳細，對大家學習或者使用Spring Boot具有一定的參考學習價值，需要的朋友們下面來一起看看吧。2017-08-08