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