基于 EasyExcel 實現高效導出
近期梳理文章，也看到自己早期寫的關于基于easy excel導出的文章，遂打算重新整理梳理一下，當初對于這個工具類的使用技巧，希望對你有幫助。
本文的需求是將一個百萬數據量MySQL8的數據導出到excel的功能，經查閱資料并結合實際場景需求整理出這樣一套比較精簡且使用的導出方案。
一、簡述案例背景
為了更好演示筆者的方案，這里給出一個演示的需求，該需求是要求導出一個用戶表的數據，該數據表是一張用戶表，包含id和name，該用戶表數據量在300w左右，以自增id作為主鍵，而功能要求我們在一分鐘之內完成百萬數據導出到excel。需要注意的是，我們導出的excel格式為xlsx，它的每一個sheet只能容納100w的數據，這也就意味著我們的數據必須以100w作為批次寫到不同的sheet中。
CREATE TABLE`t_user` (
`id`bigintNOTNULL,
`name`varchar(100) DEFAULTNULL,
`count`intDEFAULTNULL
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb3;二、三個核心問題說明
我們先來說說需要解決的問題：
- 如果一次性查詢300w左右的數據可能會占據大量的內存，如果對象字段很多的情況下，很可能出現內存溢出，我們要如何解決？
- 每個excel文件都有sheet，并且每個sheet只能容納100w左右的數據，對于這個問題我們要如何解決？
- 數據寫入到excel時，有沒有合適的工具推薦？
三、如何解決查詢問題
1. 分頁查詢
對于問題1我們兩套解決方案： 方案1是采用分頁查詢的方式進行查詢，參考自己堆內存的配置推算每次分頁查詢的數據量。因為問題1采用了分頁查詢，我們完全可以通過分頁查詢的次數推算出一個sheet寫入了多少數據，例如我們每次分頁查詢50w的數據，那么每兩次就可以視為一個sheet寫滿了，我們就可以創建一個新的sheet寫入數據。
這里需要注意一點，因為我們分頁查詢面對的是百萬級別的數據，所以隨著分頁的推進勢必出現深分頁導致查詢效率勢降低，所以為了提高分頁查詢的效率，我們可以利用查詢數據有序的特性，通過id作為偏移進行分頁查詢。
例如我們第一次分頁查詢的sql語句為:
select * from t_user limit 500000 ;假如我們不以id作為索引，那么第二次的分頁查詢sql則是:
select * from t_user limit 500000,500000 ;查看該查詢執行計劃，可以看到該查詢一次性查詢到幾乎全表的數據，并且還走了全秒掃描性能可想而知:
id|select_type|table |partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra|
--+-----------+------+----------+----+-------------+---+-------+---+-------+--------+-----+
1|SIMPLE |t_user| |ALL | | | | |2993040| 100.0| |因為我們的數據表是id自增的，所以我們查詢的時候完全可以基于該特性通過上一次查詢到的id作為篩選條件進行分頁查詢。
所以我們的分頁查詢可直接改為:
select * from t_user where id > 500000 limit 500000 ;再次查看執行計劃可以發現該查詢為范圍查詢，查詢到的數據量也少了很多，性能顯著提升:
id|select_type|table |partitions|type |possible_keys|key |key_len|ref|rows |filtered|Extra |
--+-----------+------+----------+-----+-------------+-------+-------+---+-------+--------+-----------+
1|SIMPLE |t_user| |range|PRIMARY |PRIMARY|8 | |1496520| 100.0|Using where|2. 流式查詢
另外一種解決方案就是流式查詢，通過流式查詢將SQL語句直接提交給MySQL服務端，讓服務端按照客戶端程序接受程度不斷推送數據，然后我們的java程序每次收集50w的數據，再寫入到對應的excel文件中：
四、選用合適的導出工具
因為市面上比較多的excel導出工具，常見的就是Apache poi，但是它們的操作對于內存的消耗非常嚴重，對于我們這種大數據量的寫入不是很友好，所以筆者更推薦使用阿里的EasyExcel，它對poi進行一定的封裝和優化，同等數據量寫入使用的內存更小，引用Easy Excel的說法：
Java解析、生成Excel比較有名的框架有Apache poi、jxl。但他們都存在一個嚴重的問題就是非常的耗內存，poi有一套SAX模式的API可以一定程度的解決一些內存溢出的問題，但POI還是有一些缺陷，比如07版Excel解壓縮以及解壓后存儲都是在內存中完成的，內存消耗依然很大。 easyexcel重寫了poi對07版Excel的解析，一個3M的excel用POI sax解析依然需要100M左右內存，改用easyexcel可以降低到幾M，并且再大的excel也不會出現內存溢出；03版依賴POI的sax模式，在上層做了模型轉換的封裝，讓使用者更加簡單方便
所以我們準備引入這個工具的依賴進行excel文件處理：
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>4.0.3</version>
</dependency>解決上述問題之后，我們就可以說說代碼實現思路了，以本文示例來說，有一張用戶表有300w左右的數據，每次查詢時只需查詢id(4字節)和name(10字節)，按照64位的操作系統來說，一個user對象所占用的內存大小為:
object header +pointer+id字段+name字段大小=8+8+4+10=30字節因為java對象內存大小需要16位對齊，需要補齊2個字節，所以實際大小為32字節，按照筆者對于堆內存的配置。
對于實用分頁查方案來說，每次查詢50w條數據是允許的，所以每次從數據庫讀取數據并轉為java對象，也只需要32*500000/1024即15M內存即可。
確定每次分頁查詢50w條數據之后，我們就需要確定一共需要查詢幾個分頁，然后就可以根據pageSize確定查詢的頁數。 因為每次查詢50w條數據，所以每兩次完成分頁查詢和寫入基本上一個sheet就會滿了，這時候我們就需要創建一個新的sheet進行數據寫入了。
五、基于分頁查詢導出的落地示例
對于分頁查詢導出，我們的大體實現步驟為:
- 查詢目標數據量大小。
- 根據每次分頁大小確定查詢頁數（或使用流式查詢）。
- 根據頁數大小進行遍歷，進行分頁查詢，并將數據寫入到文件中。
- 基于頁數確定sheet切換時機。
對應的我們也給出分頁查詢和導出的代碼示例，邏輯和上述說明基本一致：
long start = System.currentTimeMillis();
UserMapper userMapper = SpringUtil.getBean(UserMapper.class);
//計算總的數據量
int count = Math.toIntExact(userMapper.selectCount(Wrappers.emptyWrapper()));
//獲取分頁總數
int queryCount = 50_0000;
int pageCount = count % queryCount == 0 ? count / queryCount : count / queryCount + 1;
log.info("pageCount: {}", pageCount);
//設置導出的文件名
String fileName = "F://tmp/result.xlsx";
//設置excel的sheet號碼
int sheetNo = 1;
//設置第一個sheet的名字
String sheetName = "sheet-" + sheetNo;
// 創建writeSheet
WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo, sheetName).build();
//記錄每次分頁查詢的最大值
Long maxId = null;
//指定文件
try (ExcelWriter excelWriter = EasyExcel.write(fileName, User.class).build()) {
//寫入每一頁分頁查詢的數據
for (int i = 1; i <= pageCount; i++) {
// 分頁去數據庫查詢數據 這里可以去數據庫查詢每一頁的數據
long queryStart = System.currentTimeMillis();
List<User> userList = null;
//如果是第一次則直接進行分頁查詢，反之基于上一次分頁查詢的分頁定位實際偏移量，篩選前n條數據以達到分頁效果
PageHelper.startPage(1, queryCount, false);
if (i == 1) {
userList = userMapper.selectList(Wrappers.emptyWrapper());
} elseif (maxId != null) {
QueryWrapper wrapper = new QueryWrapper();
wrapper.gt("id", maxId);//相當于where id=1
userList = userMapper.selectList(wrapper);
PageHelper.startPage(0, queryCount, false);
}
//更新下一次分頁查詢用的id
if (CollUtil.isNotEmpty(userList)) {
maxId = userList.get(userList.size() - 1).getId();
log.info("maxId: {}", maxId);
}
long queryEnd = System.currentTimeMillis();
log.info("數據大小:{},寫入sheet位置:{}，耗時:{}", userList.size(), sheetName, queryEnd - queryStart);
long writeStart = System.currentTimeMillis();
excelWriter.write(userList, writeSheet);
long writeEnd = System.currentTimeMillis();
log.info("本次寫入耗時:{}", writeEnd - writeStart);
//如果% 2 == 0，則說明一個sheet寫入了50*2即100w的數據，需要創建新的sheet進行寫入
if (i % 2 == 0) {
sheetName = "sheet-" + (++sheetNo);
writeSheet = EasyExcel.writerSheet(sheetNo, sheetName).build();
log.info("寫滿一個sheet,切換到下一個sheet:{}", sheetName);
}
}
}
long total = System.currentTimeMillis() - start;
log.info("導出結束，總耗時:{}", total);可能會有讀者好奇筆者這個50w的數值設計思路是什么，除了考慮避免OOM以外，還考慮到每個sheet只能寫入100w條的數據，為了方便通過分頁查詢的輪次確定當前寫入的數據量大小，筆者嘗試過20w、50w。 最終在壓測結果上看出，50w讀寫耗時雖然是20w的2倍，但是IO次數卻不到20w查詢的二分之一，通過更少的IO操作獲得更好的執行性能。
最終300w數據導出耗時大約35s，整體性能表現還是可以的：
六、使用流式編程導出(推薦)
對應我們也給出流式編程的導出方案，筆者針對查詢語句做了流式編程的配置，通過這些配置保證MySQL服務端基于自己的迭代游標按照客戶端處理效率按照順序的數據流不斷傳輸給客戶端：
@Select("select * from user ")
@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = Integer.MIN_VALUE)
@ResultType(User.class)
void selectListByStream(ResultHandler<User> handler);關于流式查詢的更多內容，建議讀者參考筆者寫的這篇文章：《MySQL 流式查詢的奧秘與應用解析》。
基于上述查詢語句，我們不斷拿到user對象，因為流式查詢避免的頻繁的IO分頁請求，所以真正的寫入瓶頸點在于寫入到excel文件中，所以筆者在流式聚合數據時是通過每1w條進行一次寫入，保持每100w切換一次sheet。
需要注意的是因為筆者的ResultHandler用的是lambda表達式，為了讓編譯器通過編譯所有的計數、sheet等修改操作都是通過原子類CAS完成的，具體讀者可以參考筆者的注釋：
long start = System.currentTimeMillis();
UserMapper userMapper = SpringUtil.getBean(UserMapper.class);
//設置導出的文件名
String fileName = "F://tmp/result.xlsx";
//設置excel的sheet號碼,用原子類保證可以在lambda表達式中通過編譯
AtomicInteger sheetNo = new AtomicInteger(1);
//設置第一個sheet的名字,用原子類保證可以在lambda表達式中通過編譯
AtomicReference<String> sheetName = new AtomicReference<>("sheet-" + sheetNo.get());
// 創建writeSheet,用原子類保證可以在lambda表達式中通過編譯
WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo.get(), sheetName.get()).build();
AtomicReference<WriteSheet> writeSheetRef = new AtomicReference<>(writeSheet);
List<User> userList = new ArrayList<>();
AtomicReference<List<User>> userListRef = new AtomicReference<>(userList);
//記錄導出的size
AtomicInteger atomicCount = new AtomicInteger(0);
//指定文件
try (ExcelWriter excelWriter = EasyExcel.write(fileName, User.class).build()) {
userMapper.selectListByStream(res -> {
//存入list中
User user = res.getResultObject();
userListRef.get().add(user);
atomicCount.incrementAndGet();
//50w執行一次導出寫入
if (userListRef.get().size() % 1_0000 == 0) {
long writeStart = System.currentTimeMillis();
//寫入到文件
excelWriter.write(userListRef.get(), writeSheetRef.get());
//清空列表內部數據
userListRef.get().clear();
long writeEnd = System.currentTimeMillis();
log.info("本次寫入耗時:{}", writeEnd - writeStart);
}
//寫入100w條后，切換sheet
if (atomicCount.get() % 100_0000 == 0) {
//自增sheetNo
sheetNo.incrementAndGet();
//修改sheetName
sheetName.set("sheet-" + sheetNo.get());
//寫入文件
writeSheetRef.set(EasyExcel.writerSheet(sheetNo.get(), sheetName.get()).build());
log.info("寫滿一個sheet,切換到下一個sheet:{}", sheetName);
}
});
//檢查是否還有未寫入的文件
if (CollectionUtil.isNotEmpty(userList)) {
log.info("存在未寫入完成的數據，size:{}", userList.size());
excelWriter.write(userList, writeSheetRef.get());
}
long total = System.currentTimeMillis() - start;
log.info("導出結束，總耗時:{}", total);
} catch (Exception e) {
thrownew RuntimeException(e);
}最終這種方案的執行耗時在最好的情況下差不多30s左右，總的來說流式查詢天然內存友好且游標式順序前進的特定，對于這種并發場景下的數據導出是非常友好的，所以這種方案也是筆者比較推薦的方案：
七、小結
以上便是筆者的百萬級別數據導出的落地方案，可以看出筆者針對分頁查詢導出的方案著重在分頁查詢大小和分頁查詢sql上進行重點優化，通過平衡分頁查詢的數據量和IO次數找到合適的pageSize，再通過上一次分頁查詢結果定位下一次查詢的id作為where條件，避免分頁查詢時的全秒掃描以得到符合業務需求的高性能sql。
對于流式查詢，因為流式查詢的特定，筆者在優化時更著重于找到寫入到文件這塊的耗時上，通過找到IO寫入的平衡點找到最佳寫入閾值，從而完成百萬級別數據的高效導出。
