Excel百萬數(shù)據(jù)高性能導(dǎo)出方案！
前言
在我們的日常工作中，經(jīng)常會有Excel數(shù)據(jù)導(dǎo)出的需求。
但可能會遇到性能和內(nèi)存的問題。
今天這篇文章跟大家一起聊聊Excel高性能導(dǎo)出的方案，希望對你會有所幫助。
1.傳統(tǒng)方案的問題
很多小伙伴門在開發(fā)數(shù)據(jù)導(dǎo)出功能時，習(xí)慣性使用Apache POI的HSSF/XSSF組件。
這類方案在數(shù)據(jù)量超過5萬行時，會出現(xiàn)明顯的性能斷崖式下跌。
根本原因在于內(nèi)存對象模型的設(shè)計缺陷：每個Cell對象占用約1KB內(nèi)存，百萬級數(shù)據(jù)直接導(dǎo)致JVM堆內(nèi)存爆炸。
示例代碼（反面教材）：
// 典型內(nèi)存殺手寫法
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
for (int i = 0; i < 1000000; i++) {
Row row = sheet.createRow(i); // 每行產(chǎn)生Row對象
row.createCell(0).setCellValue("數(shù)據(jù)"+i); // 每個Cell獨立存儲
}這種寫法會產(chǎn)生約100萬個Row對象和1000萬個Cell對象（假設(shè)每行10列），直接導(dǎo)致內(nèi)存占用突破1GB。
更致命的是頻繁Full GC會導(dǎo)致系統(tǒng)卡頓甚至OOM崩潰。
2.流式處理架構(gòu)設(shè)計
高性能導(dǎo)出的核心在于內(nèi)存與磁盤的平衡。
這里給出兩種經(jīng)過生產(chǎn)驗證的方案：
方案一：SXSSFWorkbook
使用SXSSFWorkbook類，它是Apache POI的增強版。
具體示例如下：
// 內(nèi)存中只保留1000行窗口
SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
Sheet sheet = workbook.createSheet();
for (int i = 0; i < 1000000; i++) {
Row row = sheet.createRow(i);
// 寫入后立即刷新到臨時文件
if(i % 1000 == 0) {
((SXSSFSheet)sheet).flushRows(1000);
}
}通過設(shè)置滑動窗口機制，將已處理數(shù)據(jù)寫入磁盤臨時文件，內(nèi)存中僅保留當(dāng)前處理批次。實測百萬數(shù)據(jù)內(nèi)存占用穩(wěn)定在200MB以內(nèi)。
方案二：EasyExcel
EasyExcel是阿里巴巴開源的Excel高性能處理框架，目前在業(yè)界使用比較多。
最近EasyExcel的作者又推出了FastExcel，它是EasyExcel的升級版。
// 極簡流式API示例
String fileName = "data.xlsx";
EasyExcel.write(fileName, DataModel.class)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.sheet("Sheet1")
.doWrite(data -> {
// 分頁查詢數(shù)據(jù)
int page = 0;
while (true) {
List<DataModel> list = queryByPage(page, 5000);
if (CollectionUtils.isEmpty(list)) break;
data.write(list);
page++;
}
});該方案通過事件驅(qū)動模型和對象復(fù)用池技術(shù)，百萬數(shù)據(jù)導(dǎo)出內(nèi)存占用可控制在50MB以下。
其核心優(yōu)勢在于：
- 自動分批加載數(shù)據(jù)（默認每批次5000條）
- 通過反射緩存消除重復(fù)對象創(chuàng)建
- 內(nèi)置樣式優(yōu)化策略避免內(nèi)存碎片
3.數(shù)據(jù)庫查詢的黃金法則
即便導(dǎo)出工具優(yōu)化到位，若數(shù)據(jù)查詢環(huán)節(jié)存在瓶頸，整體性能仍會大打折扣。這里給出三個關(guān)鍵優(yōu)化點：
3.1 解決深度分頁問題
傳統(tǒng)分頁查詢在百萬級數(shù)據(jù)時會出現(xiàn)性能雪崩：
SELECT * FROM table LIMIT 900000, 1000 -- 越往后越慢！正確姿勢應(yīng)使用游標(biāo)方式：
// 基于自增ID的遞進查詢
Long lastId = 0L;
int pageSize = 5000;
do {
List<Data> list = jdbcTemplate.query(
"SELECT * FROM table WHERE id > ? ORDER BY id LIMIT ?",
new BeanPropertyRowMapper<>(Data.class),
lastId, pageSize);
if(list.isEmpty()) break;
lastId = list.get(list.size()-1).getId();
// 處理數(shù)據(jù)...
} while (true);該方案利用索引的有序性，將時間復(fù)雜度從O(N2)降為O(N)。
3.2 減少字段數(shù)量
-- 錯誤寫法：全字段查詢
SELECT * FROM big_table
-- 正確姿勢：僅取必要字段
SELECT id,name,create_time FROM big_table實測顯示，當(dāng)單行數(shù)據(jù)從20個字段縮減到5個字段時，查詢耗時降低40%，網(wǎng)絡(luò)傳輸量減少70%。
3.3 連接池參數(shù)調(diào)優(yōu)
# SpringBoot配置示例
spring:
datasource:
hikari:
maximum-pool-size: 20 # 根據(jù)CPU核數(shù)調(diào)整
connection-timeout: 30000
idle-timeout: 600000
max-lifetime: 1800000導(dǎo)出場景建議使用獨立連接池，避免影響主業(yè)務(wù)。
連接數(shù)計算公式：線程數(shù) = CPU核心數(shù) * 2 + 磁盤數(shù)。
4.生產(chǎn)級進階技巧
4.1 異步分片導(dǎo)出
想要提升Excel數(shù)據(jù)導(dǎo)出的性能，我們必須使用多線程異步導(dǎo)出的方案。
具體示例如下：
@Async("exportExecutor")
public CompletableFuture<String> asyncExport(ExportParam param) {
// 1. 計算分片數(shù)量
int total = dataService.count(param);
int shardSize = total / 100000;
// 2. 并行處理分片
List<CompletableFuture<Void>> futures = new ArrayList<>();
for (int i = 0; i < shardSize; i++) {
int finalI = i;
futures.add(CompletableFuture.runAsync(() -> {
exportShard(param, finalI * 100000, 100000);
}, forkJoinPool.commonPool()));
}
// 3. 合并文件
CompletableFuture.allOf(futures.toArray(new CompletableFuture)
.thenApply(v -> mergeFiles(shardSize));
return CompletableFuture.completedFuture(taskId);
}通過分治策略將任務(wù)拆解為多個子任務(wù)并行執(zhí)行，結(jié)合線程池管理實現(xiàn)資源可控。
4.2 配置JVM參數(shù)
我們需要配置JVM參數(shù)，并且需要對這些參數(shù)進行調(diào)優(yōu)：
// JVM啟動參數(shù)示例
-Xmx4g -Xms4g
-XX:+UseG1GC
-XX:MaxGCPauseMillis=200
-XX:ParallelGCThreads=4
-XX:Cnotallow=2
-XX:InitiatingHeapOccupancyPercent=35這樣可以有效的提升性能。
導(dǎo)出場景需特別注意：
- 年輕代與老年代比例建議2:1
- 避免創(chuàng)建超過50KB的大對象
- 使用對象池復(fù)用DTO實例
4.3 整體方案
Excel高性能導(dǎo)出的方案如下圖所示：
圖片
用戶點擊導(dǎo)出按鈕，會寫入DB，生成一個唯一的任務(wù)ID，任務(wù)狀態(tài)為待執(zhí)行。
然后后臺異步處理，可以分頁將數(shù)據(jù)寫入到Excel中（這個過程可以使用多線程實現(xiàn)）。
將Excel文件存儲到云存儲中。
然后更新任務(wù)狀態(tài)為以完成。
最后通過WebSocket通知用戶導(dǎo)出結(jié)果。
5.總結(jié)
經(jīng)過多個千萬級項目的錘煉，我們總結(jié)出Excel高性能導(dǎo)出的黃金公式：
高性能 = 流式處理引擎 + 分頁查詢優(yōu)化 + 資源管控
具體實施時可參考以下決策樹：
最后給小伙伴們的三個忠告：
- 切忌過早優(yōu)化：在需求明確前不要盲目選擇復(fù)雜方案
- 監(jiān)控先行：務(wù)必埋點記錄導(dǎo)出耗時、內(nèi)存波動等關(guān)鍵指標(biāo)
- 兜底策略：始終提供CSV導(dǎo)出選項作為保底方案
希望本文能幫助大家在數(shù)據(jù)導(dǎo)出的戰(zhàn)場上，真正實現(xiàn)"百萬數(shù)據(jù)，彈指之間"！
