SpringBoot實現(xiàn)Excel導入導出，百萬數(shù)據(jù)量，性能爆表！

前言

最近我做過一個MySQL百萬級別數(shù)據(jù)的excel導出功能，已經(jīng)正常上線使用了。

這個功能挺有意思的，里面需要注意的細節(jié)還真不少，現(xiàn)在拿出來跟大家分享一下，希望對你會有所幫助。

原始需求：用戶在UI界面上點擊全部導出按鈕，就能導出所有商品數(shù)據(jù)。

咋一看，這個需求挺簡單的。

但如果我告訴你，導出的記錄條數(shù)，可能有一百多萬，甚至兩百萬呢？

這時你可能會倒吸一口氣。

因為你可能會面臨如下問題：

1. 如果同步導數(shù)據(jù)，接口很容易超時。
2. 如果把所有數(shù)據(jù)一次性裝載到內(nèi)存，很容易引起OOM。
3. 數(shù)據(jù)量太大sql語句必定很慢。
4. 相同商品編號的數(shù)據(jù)要放到一起。
5. 如果走異步，如何通知用戶導出結(jié)果？
6. 如果excel文件太大，目標用戶打不開怎么辦？

我們要如何才能解決這些問題，實現(xiàn)一個百萬級別的excel數(shù)據(jù)快速導出功能呢？

基于 Spring Boot + MyBatis Plus + Vue & Element 實現(xiàn)的后臺管理系統(tǒng) + 用戶小程序，支持 RBAC 動態(tài)權限、多租戶、數(shù)據(jù)權限、工作流、三方登錄、支付、短信、商城等功能

• 項目地址：https://github.com/YunaiV/ruoyi-vue-pro
• 視頻教程：https://doc.iocoder.cn/video/

1.異步處理

做一個MySQL百萬數(shù)據(jù)級別的excel導出功能，如果走接口同步導出，該接口肯定會非常容易超時。

因此，我們在做系統(tǒng)設計的時候，第一選擇應該是接口走異步處理。

說起異步處理，其實有很多種，比如：使用開啟一個線程，或者使用線程池，或者使用job，或者使用mq等。

為了防止服務重啟時數(shù)據(jù)的丟失問題，我們大多數(shù)情況下，會使用job或者mq來實現(xiàn)異步功能。

1.1 使用job

如果使用job的話，需要增加一張執(zhí)行任務表，記錄每次的導出任務。

用戶點擊全部導出按鈕，會調(diào)用一個后端接口，該接口會向表中寫入一條記錄，該記錄的狀態(tài)為：待執(zhí)行。

有個job，每隔一段時間（比如：5分鐘），掃描一次執(zhí)行任務表，查出所有狀態(tài)是待執(zhí)行的記錄。

然后遍歷這些記錄，挨個執(zhí)行。

需要注意的是：如果用job的話，要避免重復執(zhí)行的情況。比如job每隔5分鐘執(zhí)行一次，但如果數(shù)據(jù)導出的功能所花費的時間超過了5分鐘，在一個job周期內(nèi)執(zhí)行不完，就會被下一個job執(zhí)行周期執(zhí)行。

所以使用job時可能會出現(xiàn)重復執(zhí)行的情況。

為了防止job重復執(zhí)行的情況，該執(zhí)行任務需要增加一個執(zhí)行中的狀態(tài)。

具體的狀態(tài)變化如下：

1. 執(zhí)行任務被剛記錄到執(zhí)行任務表，是待執(zhí)行狀態(tài)。
2. 當job第一次執(zhí)行該執(zhí)行任務時，該記錄再數(shù)據(jù)庫中的狀態(tài)改為：執(zhí)行中。
3. 當job跑完了，該記錄的狀態(tài)變成：完成或失敗。

這樣導出數(shù)據(jù)的功能，在第一個job周期內(nèi)執(zhí)行不完，在第二次job執(zhí)行時，查詢待處理狀態(tài)，并不會查詢出執(zhí)行中狀態(tài)的數(shù)據(jù)，也就是說不會重復執(zhí)行。

此外，使用job還有一個硬傷即：它不是立馬執(zhí)行的，有一定的延遲。

如果對時間不太敏感的業(yè)務場景，可以考慮使用該方案。

1.2 使用mq

用戶點擊全部導出按鈕，會調(diào)用一個后端接口，該接口會向mq服務端，發(fā)送一條mq消息。

有個專門的mq消費者，消費該消息，然后就可以實現(xiàn)excel的數(shù)據(jù)導出了。

相較于job方案，使用mq方案的話，實時性更好一些。

對于mq消費者處理失敗的情況，可以增加補償機制，自動發(fā)起重試。

RocketMQ自帶了失敗重試功能，如果失敗次數(shù)超過了一定的閥值，則會將該消息自動放入死信隊列。

基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 實現(xiàn)的后臺管理系統(tǒng) + 用戶小程序，支持 RBAC 動態(tài)權限、多租戶、數(shù)據(jù)權限、工作流、三方登錄、支付、短信、商城等功能

• 項目地址：https://github.com/YunaiV/yudao-cloud
• 視頻教程：https://doc.iocoder.cn/video/

2.使用easyexcel

我們知道在Java中解析和生成Excel，比較有名的框架有Apache POI和jxl。

但它們都存在一個嚴重的問題就是：非常耗內(nèi)存，POI有一套SAX模式的API可以一定程度的解決一些內(nèi)存溢出的問題，但POI還是有一些缺陷，比如07版Excel解壓縮以及解壓后存儲都是在內(nèi)存中完成的，內(nèi)存消耗依然很大。

百萬級別的excel數(shù)據(jù)導出功能，如果使用傳統(tǒng)的Apache POI框架去處理，可能會消耗很大的內(nèi)存，容易引發(fā)OOM問題。

而easyexcel重寫了POI對07版Excel的解析，之前一個3M的excel用POI sax解析，需要100M左右內(nèi)存，如果改用easyexcel可以降低到幾M，并且再大的Excel也不會出現(xiàn)內(nèi)存溢出；03版依賴POI的sax模式，在上層做了模型轉(zhuǎn)換的封裝，讓使用者更加簡單方便。

需要在maven的pom.xml文件中引入easyexcel的jar包：

com.alibaba
easyexcel
3.0.2

之后，使用起來非常方便。

讀excel數(shù)據(jù)非常方便：

@Test
publicvoidsimpleRead(){
StringfileName=TestFileUtil.getPath()+"demo"+File.separator+"demo.xlsx";
//這里需要指定讀用哪個class去讀，然后讀取第一個sheet文件流會自動關閉
EasyExcel.read(fileName,DemoData.class,newDemoDataListener()).sheet().doRead();
}

寫excel數(shù)據(jù)也非常方便：

@Test
publicvoidsimpleWrite(){
StringfileName=TestFileUtil.getPath()+"write"+System.currentTimeMillis()+".xlsx";
//這里需要指定寫用哪個class去讀，然后寫到第一個sheet，名字為模板然后文件流會自動關閉
//如果這里想使用03則傳入excelType參數(shù)即可
EasyExcel.write(fileName,DemoData.class).sheet("模板").doWrite(data());
}

easyexcel能大大減少占用內(nèi)存的主要原因是：在解析Excel時沒有將文件數(shù)據(jù)一次性全部加載到內(nèi)存中，而是從磁盤上一行行讀取數(shù)據(jù)，逐個解析。

3.分頁查詢

百萬級別的數(shù)據(jù)，從數(shù)據(jù)庫一次性查詢出來，是一件非常耗時的工作。

即使我們可以從數(shù)據(jù)庫中一次性查詢出所有數(shù)據(jù)，沒出現(xiàn)連接超時問題，這么多的數(shù)據(jù)全部加載到應用服務的內(nèi)存中，也有可能會導致應用服務出現(xiàn)OOM問題。

因此，我們從數(shù)據(jù)庫中查詢數(shù)據(jù)時，有必要使用分頁查詢。比如：每頁5000條記錄，分為200頁查詢。

publicPagesearchUser(SearchModelsearchModel){
ListuserList=userMapper.searchUser(searchModel);
PagepageResponse=Page.create(userList,searchModel);
pageResponse.setTotal(userMapper.searchUserCount(searchModel));
returnpageResponse;
}

每頁大小pageSize和頁碼pageNo，是SearchModel類中的成員變量，在創(chuàng)建searchModel對象時，可以設置設置這兩個參數(shù)。

然后在Mybatis的sql文件中，通過limit語句實現(xiàn)分頁功能：

limit#{pageStart},#{pageSize}

其中的pagetStart參數(shù)，是通過pageNo和pageSize動態(tài)計算出來的，比如：

pageStart=(pageNo-1)*pageSize;

4.多個sheet

我們知道，excel對一個sheet存放的最大數(shù)據(jù)量，是有做限制的，一個sheet最多可以保存1048576行數(shù)據(jù)。否則在保存數(shù)據(jù)時會直接報錯：

invalidrownumber(1048576)outsideallowablerange(0..1048575)

如果你想導出一百萬以上的數(shù)據(jù)，excel的一個sheet肯定是存放不下的。

因此我們需要把數(shù)據(jù)保存到多個sheet中。

5.計算limit的起始位置

我之前說過，我們一般是通過limit語句來實現(xiàn)分頁查詢功能的：

limit#{pageStart},#{pageSize}

其中的pagetStart參數(shù)，是通過pageNo和pageSize動態(tài)計算出來的，比如：

pageStart=(pageNo-1)*pageSize;

如果只有一個sheet可以這么玩，但如果有多個sheet就會有問題。因此，我們需要重新計算limit的起始位置。

例如：

ExcelWriterexcelWriter=EasyExcelFactory.write(out).build();
inttotalPage=searchUserTotalPage(searchModel);

if(totalPage>0){
Pagepage=Page.create(searchModel);
intsheet=(totalPage%maxSheetCount==0)?totalPage/maxSheetCount:(totalPage/maxSheetCount)+1;
for(inti=0;i"sheet"+i);
intstartPageNo=i*(maxSheetCount/pageSize)+1;
intendPageNo=(i+1)*(maxSheetCount/pageSize);
while(page.getPageNo()>=startPageNo&&page.getPageNo()<=endPageNo)?{
????????page?=?searchUser(searchModel);
????????if(CollectionUtils.isEmpty(page.getList())){
break;
}

excelWriter.write(page.getList(),writeSheet);
page.setPageNo(page.getPageNo()+1);
}
}
}

這樣就能實現(xiàn)分頁查詢，將數(shù)據(jù)導出到不同的excel的sheet當中。

6.文件上傳到OSS

由于現(xiàn)在我們導出excel數(shù)據(jù)的方案改成了異步，所以沒法直接將excel文件，同步返回給用戶。

因此我們需要先將excel文件存放到一個地方，當用戶有需要時，可以訪問到。

這時，我們可以直接將文件上傳到OSS文件服務器上。

通過OSS提供的上傳接口，將excel上傳成功后，會返回文件名稱和訪問路徑。

我們可以將excel名稱和訪問路徑保存到表中，這樣的話，后面就可以直接通過瀏覽器，訪問遠程excel文件了。

而如果將excel文件保存到應用服務器，可能會占用比較多的磁盤空間。

一般建議將應用服務器和文件服務器分開，應用服務器需要更多的內(nèi)存資源或者CPU資源，而文件服務器需要更多的磁盤資源。

7.通過WebSocket推送通知

通過上面的功能已經(jīng)導出了excel文件，并且上傳到了OSS文件服務器上。

接下來的任務是要本次excel導出結(jié)果，成功還是失敗，通知目標用戶。

有種做法是在頁面上提示：正在導出excel數(shù)據(jù)，請耐心等待。

然后用戶可以主動刷新當前頁面，獲取本地導出excel的結(jié)果。

但這種用戶交互功能，不太友好。

還有一種方式是通過webSocket建立長連接，進行實時通知推送。

如果你使用了SpringBoot框架，可以直接引入webSocket的相關jar包：

org.springframework.boot
spring-boot-starter-websocket

使用起來挺方便的。

我們可以加一張專門的通知表，記錄通過webSocket推送的通知的標題、用戶、附件地址、閱讀狀態(tài)、類型等信息。

能更好的追溯通知記錄。

webSocket給客戶端推送一個通知之后，用戶的右上角的收件箱上，實時出現(xiàn)了一個小窗口，提示本次導出excel功能是成功還是失敗，并且有文件下載鏈接。

當前通知的閱讀狀態(tài)是未讀。

用戶點擊該窗口，可以看到通知的詳細內(nèi)容，然后通知狀態(tài)變成已讀。

8.總條數(shù)可配置

我們在做導百萬級數(shù)據(jù)這個需求時，是給用戶用的，也有可能是給運營同學用的。

其實我們應該站在實際用戶的角度出發(fā)，去思考一下，這個需求是否合理。

用戶拿到這個百萬級別的excel文件，到底有什么用途，在他們的電腦上能否打開該excel文件，電腦是否會出現(xiàn)太大的卡頓了，導致文件使用不了。

如果該功能上線之后，真的發(fā)生發(fā)生這些情況，那么導出excel也沒有啥意義了。

因此，非常有必要把記錄的總條數(shù)，做成可配置的，可以根據(jù)用戶的實際情況調(diào)整這個配置。

比如：用戶發(fā)現(xiàn)excel中有50萬的數(shù)據(jù)，可以正常訪問和操作excel，這時候我們可以將總條數(shù)調(diào)整成500000，把多余的數(shù)據(jù)截取掉。

其實，在用戶的操作界面，增加更多的查詢條件，用戶通過修改查詢條件，多次導數(shù)據(jù)，可以實現(xiàn)將所有數(shù)據(jù)都導出的功能，這樣可能更合理一些。

此外，分頁查詢時，每頁的大小，也建議做成可配置的。

通過總條數(shù)和每頁大小，可以動態(tài)調(diào)整記錄數(shù)量和分頁查詢次數(shù)，有助于更好滿足用戶的需求。

9.order by商品編號

之前的需求是要將相同商品編號的數(shù)據(jù)放到一起。

例如：

但我們做了分頁查詢的功能，沒法將數(shù)據(jù)一次性查詢出來，直接在Java內(nèi)存中分組或者排序。

因此，我們需要考慮在sql語句中使用order by 商品編號，先把數(shù)據(jù)排好順序，再查詢出數(shù)據(jù)，這樣就能將相同商品編號，倉庫不同的數(shù)據(jù)放到一起。

此外，還有一種情況需要考慮一下，通過配置的總記錄數(shù)將全部數(shù)據(jù)做了截取。

但如果最后一個商品編號在最后一頁中沒有查詢完，可能會導致導出的最后一個商品的數(shù)據(jù)不完整。

因此，我們需要在程序中處理一下，將最后一個商品刪除。

但加了order by關鍵字進行排序之后，如果查詢sql中join了很多張表，可能會導致查詢性能變差。

那么，該怎么辦呢？

總結(jié)

最后用兩張圖，總結(jié)一下excel異步導數(shù)據(jù)的流程。

如果是使用mq導數(shù)據(jù)：

如果是使用job導數(shù)據(jù)：

這兩種方式都可以，可以根據(jù)實際情況選擇使用。

審核編輯 ：李倩