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