百萬級(jí)別excel導(dǎo)出功能如何實(shí)現(xiàn)

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

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

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

咋一看，這個(gè)需求挺簡(jiǎn)單的。

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

這時(shí)你可能會(huì)倒吸一口氣。

因?yàn)槟憧赡軙?huì)面臨如下問題：

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

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

1.異步處理

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

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

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

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

1.1 使用job

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

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

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

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

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

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

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

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

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

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

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

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

1.2 使用mq

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

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

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

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

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

2.使用easyexcel

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

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

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

而easyexcel重寫了POI對(duì)07版Excel的解析，之前一個(gè)3M的excel用POI sax解析，需要100M左右內(nèi)存，如果改用easyexcel可以降低到幾M，并且再大的Excel也不會(huì)出現(xiàn)內(nèi)存溢出；03版依賴POI的sax模式，在上層做了模型轉(zhuǎn)換的封裝，讓使用者更加簡(jiǎ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";
    // 這里 需要指定讀用哪個(gè)class去讀，然后讀取第一個(gè)sheet 文件流會(huì)自動(dòng)關(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";
    // 這里 需要指定寫用哪個(gè)class去讀，然后寫到第一個(gè)sheet，名字為模板 然后文件流會(huì)自動(dòng)關(guān)閉
    // 如果這里想使用03 則 傳入excelType參數(shù)即可
    EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
}

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

3.分頁查詢

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

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

因此，我們從數(shù)據(jù)庫(kù)中查詢數(shù)據(jù)時(shí)，有必要使用分頁查詢。比如：每頁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對(duì)象時(shí)，可以設(shè)置設(shè)置這兩個(gè)參數(shù)。

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

limit #{pageStart}, #{pageSize}

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

pageStart = (pageNo - 1) * pageSize;

4.多個(gè)sheet

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

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

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

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

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

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

limit #{pageStart}, #{pageSize}

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

pageStart = (pageNo - 1) * pageSize;

如果只有一個(gè)sheet可以這么玩，但如果有多個(gè)sheet就會(huì)有問題。因此，我們需要重新計(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文件存放到一個(gè)地方，當(dāng)用戶有需要時(shí)，可以訪問到。

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

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

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

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

一般建議將應(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ù)，請(qǐng)耐心等待。

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

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

還有一種方式是通過webSocket建立長(zhǎng)連接，進(jìn)行實(shí)時(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給客戶端推送一個(gè)通知之后，用戶的右上角的收件箱上，實(shí)時(shí)出現(xiàn)了一個(gè)小窗口，提示本次導(dǎo)出excel功能是成功還是失敗，并且有文件下載鏈接。

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

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

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

我們?cè)谧鰧?dǎo)百萬級(jí)數(shù)據(jù)這個(gè)需求時(shí)，是給用戶用的，也有可能是給運(yùn)營(yíng)同學(xué)用的。

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

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

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

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

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

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

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

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

9.order by商品編號(hào)

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

例如：

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

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

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

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

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

但加了order by關(guān)鍵字進(jìn)行排序之后，如果查詢sql中join了很多張表，可能會(huì)導(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)境，原本以為會(huì)非常順利，但后面卻還是出現(xiàn)了性能問題。