前言

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

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

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

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

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

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

因?yàn)槟憧赡軙媾R如下問題：

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

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

1.異步處理

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

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

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

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

1.1 使用job

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

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

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

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

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

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

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

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

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

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

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

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

1.2 使用mq

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

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

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

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

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

2.使用easyexcel

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

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

百萬級別的excel數(shù)據(jù)導(dǎo)出功能，如果使用傳統(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包：

<dependency>
????<groupId>com.alibaba</groupId>
????<artifactId>easyexcel</artifactId>
????<version>3.0.2</version>
</dependency>

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

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

@Test
public?void?simpleRead()?{
????String?fileName?=?TestFileUtil.getPath()?+?"demo"?+?File.separator?+?"demo.xlsx";
????//?這里?需要指定讀用哪個class去讀，然后讀取第一個sheet?文件流會自動關(guān)閉
????EasyExcel.read(fileName,?DemoData.class,?new?DemoDataListener()).sheet().doRead();
}

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

?@Test
public?void?simpleWrite()?{
????String?fileName?=?TestFileUtil.getPath()?+?"write"?+?System.currentTimeMillis()?+?".xlsx";
????//?這里?需要指定寫用哪個class去讀，然后寫到第一個sheet，名字為模板?然后文件流會自動關(guān)閉
????//?如果這里想使用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ù)全部加載到應(yīng)用服務(wù)的內(nèi)存中，也有可能會導(dǎo)致應(yīng)用服務(wù)出現(xiàn)OOM問題。

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

public?Page<User>?searchUser(SearchModel?searchModel)?{
????List<User>?userList?=?userMapper.searchUser(searchModel);
????Page<User>?pageResponse?=?Page.create(userList,?searchModel);
????pageResponse.setTotal(userMapper.searchUserCount(searchModel));
????return?pageResponse;
}

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

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

limit?#{pageStart},?#{pageSize}

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

pageStart?=?(pageNo?-?1)?*?pageSize;

4.多個sheet

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

invalid?row?number?(1048576)?outside?allowable?range?(0..1048575)

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

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

5.計(jì)算limit的起始位置

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

limit?#{pageStart},?#{pageSize}

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

pageStart?=?(pageNo?-?1)?*?pageSize;

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

例如：

ExcelWriter?excelWriter?=?EasyExcelFactory.write(out).build();
int?totalPage?=?searchUserTotalPage(searchModel);

if(totalPage?>?0)?{
???Page<User>?page?=?Page.create(searchModel);
???int?sheet?=?(totalPage?%?maxSheetCount?==?0)???totalPage?/?maxSheetCount:?(totalPage?/?maxSheetCount)?+?1;
???for(int?i=0;i<sheet;i++)?{
??????WriterSheet?writeSheet?=?buildSheet(i,"sheet"+i);
??????int?startPageNo?=?i*(maxSheetCount/pageSize)+1;
??????int?endPageNo?=?(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);
?????}
???}
}

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

6.文件上傳到OSS

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

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

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

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

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

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

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

7.通過WebSocket推送通知

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

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

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

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

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

還有一種方式是通過webSocket建立長連接，進(jìn)行實(shí)時通知推送。

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

<dependency>
??<groupId>org.springframework.boot</groupId>
??<artifactId>spring-boot-starter-websocket</artifactId>
</dependency>

使用起來挺方便的。

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

能更好的追溯通知記錄。

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

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

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

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

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

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

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

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

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

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

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

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

通過總條數(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ù)做了截取。

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

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

但加了order by關(guān)鍵字進(jìn)行排序之后，如果查詢sql中join了很多張表，可能會導(dǎo)致查詢性能變差。

那么，該怎么辦呢？

總結(jié)

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

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

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

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

我們按照這套方案的開發(fā)了代碼，發(fā)到了pre環(huán)境，原本以為會非常順利，但后面卻還是出現(xiàn)了性能問題。

后來，我們用了兩招輕松解決了性能問題。

以上就是詳解Java如何實(shí)現(xiàn)百萬數(shù)據(jù)excel導(dǎo)出功能的詳細(xì)內(nèi)容，更多關(guān)于Java數(shù)據(jù)excel導(dǎo)出的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論