問題式知識庫系列：百萬資料excel導出功能如何實作（持續更新）

前言

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

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

原始需求：使用者在UI界面上點選全部導出按鈕，就能導出所有商品資料。

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

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

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

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

1. 如果同步導資料，接口很容易逾時。
2. 如果把所有資料一次性裝載到記憶體，很容易引起OOM。
3. 資料量太大sql語句必定很慢。
4. 相同商品編号的資料要放到一起。
5. 如果走異步，如何通知使用者導出結果？
6. 如果excel檔案太大，目标使用者打不開怎麼辦？

我們要如何才能解決這些問題，實作一個百萬級别的excel資料快速導出功能呢？

1.異步處理

做一個MySQL百萬資料級别的excel導出功能，如果走接口同步導出，該接口肯定會非常容易逾時。

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

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

為了防止服務重新開機時資料的丢失問題，我們大多數情況下，會使用job或者mq來實作異步功能。

1.1 使用job

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

使用者點選全部導出按鈕，會調用一個後端接口，該接口會向表中寫入一條記錄，該記錄的狀态為：待執行。

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

然後周遊這些記錄，挨個執行。

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

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

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

具體的狀态變化如下：

1. 執行任務被剛記錄到執行任務表，是待執行狀态。
2. 當job第一次執行該執行任務時，該記錄再資料庫中的狀态改為：執行中。
3. 當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商品編号

之前的需求是要将相同商品編号的資料放到一起。

例如：

編号商品名稱倉庫名稱價格1筆記本北京倉72341筆記本上海倉72351筆記本武漢倉72362平闆電腦成都倉72362平闆電腦大連倉3339

但我們做了分頁查詢的功能，沒法将資料一次性查詢出來，直接在Java記憶體中分組或者排序。

是以，我們需要考慮在sql語句中使用order by 商品編号，先把資料排好順序，再查詢出資料，這樣就能将相同商品編号，倉庫不同的資料放到一起。

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

但如果最後一個商品編号在最後一頁中沒有查詢完，可能會導緻導出的最後一個商品的資料不完整。

是以，我們需要在程式中處理一下，将最後一個商品删除。

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

那麼，該怎麼辦呢？

總結

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

如果是使用mq導資料：

如果是使用job導資料：

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

你學廢了嗎

準備面試？

工作遇到問題？

想要積累知識？

快速搜尋無處不在的：7TCoding

往期内容回顧：

1、網際網路知識庫合集【詳見腦圖】

2、知識庫系列：Zookeeper和Mybatis（持續更新）

3、知識庫系列：Dubbo（附腦圖-持續更新）

4、知識庫系列：分布式搜尋和分析引擎Elasticsearch（附腦圖-持續更新）

5、知識庫系列：Redis（附腦圖-持續更新）

6、知識庫系列：分布式的高速緩存系統Memcached（附腦圖-持續更新）

7、知識庫系列：MySQL（附腦圖-持續更新）

8、知識庫系列：Java基礎1+2（附腦圖-持續更新）

9、知識庫系列：5萬長文Java知識Java3+4（附腦圖-持續更新）

10、知識庫系列：Spring 知識庫（一）（附腦圖-持續更新）

11、知識庫系列：微服務系列（附腦圖-持續更新）

持續更新中......

更多精彩，請加入我們哦！