百萬數據 excel 導出功能如何實現？

前言

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

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

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

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

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

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

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

如果同步導數據，接口很容易超時。
如果把所有數據一次性裝載到內存，很容易引起 OOM。
數據量太大 sql 語句必定很慢。
相同商品編號的數據要放到一起。
如果走異步，如何通知用戶導出結果？
如果 excel 文件太大，目標用戶打不開怎麼辦？

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

1. 異步處理

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

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

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

爲了防止服務重啓時數據的丟失問題，我們大多數情況下，會使用job或者mq來實現異步功能。

1.1 使用 job

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

用戶點擊全部導出按鈕，會調用一個後端接口，該接口會向表中寫入一條記錄，該記錄的狀態爲：待執行。

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

然後遍歷這些記錄，挨個執行。

需要注意的是：如果用 job 的話，要避免重複執行的情況。比如 job 每隔 5 分鐘執行一次，但如果數據導出的功能所花費的時間超過了 5 分鐘，在一個 job 週期內執行不完，就會被下一個 job 執行週期執行。

所以使用 job 時可能會出現重複執行的情況。

爲了防止 job 重複執行的情況，該執行任務需要增加一個執行中的狀態。

具體的狀態變化如下：

執行任務被剛記錄到執行任務表，是待執行狀態。
當 job 第一次執行該執行任務時，該記錄再數據庫中的狀態改爲：執行中。
當 job 跑完了，該記錄的狀態變成：完成或失敗。

這樣導出數據的功能，在第一個 job 週期內執行不完，在第二次 job 執行時，查詢待處理狀態，並不會查詢出執行中狀態的數據，也就是說不會重複執行。

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

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

1.2 使用 mq

用戶點擊全部導出按鈕，會調用一個後端接口，該接口會向mq服務端，發送一條mq消息。

有個專門的mq消費者，消費該消息，然後就可以實現 excel 的數據導出了。

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

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

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

2. 使用 easyexcel

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

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

百萬級別的 excel 數據導出功能，如果使用傳統的 Apache POI 框架去處理，可能會消耗很大的內存，容易引發OOM問題。

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

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

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

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

讀 excel 數據非常方便：

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

寫 excel 數據也非常方便：

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

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

3. 分頁查詢

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

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

因此，我們從數據庫中查詢數據時，有必要使用分頁查詢。比如：每頁 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 類中的成員變量，在創建 searchModel 對象時，可以設置設置這兩個參數。

然後在Mybatis的 sql 文件中，通過limit語句實現分頁功能：

limit #{pageStart}, #{pageSize}

其中的 pagetStart 參數，是通過 pageNo 和 pageSize 動態計算出來的，比如：

pageStart = (pageNo - 1) * pageSize;

4. 多個 sheet

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

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

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

因此我們需要把數據保存到多個 sheet 中。

5. 計算 limit 的起始位置

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

limit #{pageStart}, #{pageSize}

其中的 pagetStart 參數，是通過 pageNo 和 pageSize 動態計算出來的，比如：

pageStart = (pageNo - 1) * pageSize;

如果只有一個 sheet 可以這麼玩，但如果有多個 sheet 就會有問題。因此，我們需要重新計算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);
     }
   }
}

這樣就能實現分頁查詢，將數據導出到不同的 excel 的 sheet 當中。

6. 文件上傳到 OSS

由於現在我們導出 excel 數據的方案改成了異步，所以沒法直接將 excel 文件，同步返回給用戶。

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

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

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

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

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

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

7. 通過 WebSocket 推送通知

通過上面的功能已經導出了 excel 文件，並且上傳到了OSS文件服務器上。

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

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

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

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

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

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

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

使用起來挺方便的。

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

能更好的追溯通知記錄。

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

當前通知的閱讀狀態是未讀。

用戶點擊該窗口，可以看到通知的詳細內容，然後通知狀態變成已讀。

8. 總條數可配置

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

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

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

如果該功能上線之後，真的發生發生這些情況，那麼導出 excel 也沒有啥意義了。

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

比如：用戶發現 excel 中有 50 萬的數據，可以正常訪問和操作 excel，這時候我們可以將總條數調整成 500000，把多餘的數據截取掉。

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

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

通過總條數和每頁大小，可以動態調整記錄數量和分頁查詢次數，有助於更好滿足用戶的需求。

9.order by 商品編號

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

例如：

WPFjIr

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

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

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

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

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

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

那麼，該怎麼辦呢？

總結

最後用兩張圖，總結一下 excel 異步導數據的流程。

如果是使用 mq 導數據：

如果是使用 job 導數據：

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

我們按照這套方案的開發了代碼，發到了 pre 環境，原本以爲會非常順利，但後面卻還是出現了性能問題。

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

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/4QbhTs_OEpWKQTXP48j6kQ

前言