Excel高性能導(dǎo)入方案！你學(xué)會了嗎？
作者：蘇三
今天要討論一個讓無數(shù)人抓狂的話題：如何高效導(dǎo)入百萬級Excel數(shù)據(jù)。去年有家公司找到我，他們的電商系統(tǒng)遇到一個致命問題：每天需要導(dǎo)入20萬條商品數(shù)據(jù)，但一執(zhí)行就卡死，最長耗時超過3小時。
前言
今天要討論一個讓無數(shù)人抓狂的話題：如何高效導(dǎo)入百萬級Excel數(shù)據(jù)。
去年有家公司找到我，他們的電商系統(tǒng)遇到一個致命問題：每天需要導(dǎo)入20萬條商品數(shù)據(jù)，但一執(zhí)行就卡死，最長耗時超過3小時。
更魔幻的是，重啟服務(wù)器后前功盡棄。
經(jīng)過半天的源碼分析，我們發(fā)現(xiàn)了下面這些觸目驚心的代碼...
1.為什么傳統(tǒng)導(dǎo)入方案會崩盤？
很多小伙伴在實(shí)現(xiàn)Excel導(dǎo)入時，往往直接寫出這樣的代碼：
// 錯誤示例：逐行讀取+逐條插入
public void importExcel(File file) {
List<Product> list = ExcelUtils.readAll(file); // 一次加載到內(nèi)存
for (Product product : list) {
productMapper.insert(product); // 逐行插入
}
}這種寫法會引發(fā)三大致命問題：
1.1 內(nèi)存熔斷：堆區(qū)OOM慘案
- 問題：POI的
UserModel（如XSSFWorkbook）一次性加載整個Excel到內(nèi)存 - 實(shí)驗(yàn)：一個50MB的Excel（約20萬行）直接耗盡默認(rèn)的1GB堆內(nèi)存
- 癥狀：頻繁Full GC ? CPU飆升 ? 服務(wù)無響應(yīng)
1.2 同步阻塞：用戶等到崩潰
- 過程：用戶上傳文件 → 同步等待所有數(shù)據(jù)處理完畢 → 返回結(jié)果
- 風(fēng)險：連接超時（HTTP默認(rèn)30秒斷開）→ 任務(wù)丟失
1.3 效率黑洞：逐條操作事務(wù)
- 實(shí)測數(shù)據(jù)：MySQL單線程逐條插入≈200條/秒 → 處理20萬行≈16分鐘
- 幕后黑手：每次insert都涉及事務(wù)提交、索引維護(hù)、日志寫入
2.性能優(yōu)化四板斧
第一招：流式解析
使用POI的SAX模式替代DOM模式：
// 正確寫法：分段讀?。ㄒ訦SSF為例）
OPCPackage pkg = OPCPackage.open(file);
XSSFReader reader = new XSSFReader(pkg);
SheetIterator sheets = (SheetIterator) reader.getSheetsData();
while (sheets.hasNext()) {
try (InputStream stream = sheets.next()) {
Sheet sheet = new XSSFSheet(); // 流式解析
RowHandler rowHandler = new RowHandler();
sheet.onRow(row -> rowHandler.process(row));
sheet.process(stream); // 不加載全量數(shù)據(jù)
}
}?? 避坑指南：
- 不同Excel版本需適配（HSSF/XSSF/SXSSF）
- 避免在解析過程中創(chuàng)建大量對象，需復(fù)用數(shù)據(jù)容器
第二招：分頁批量插入
基于MyBatis的批量插入+連接池優(yōu)化：
// 分頁批量插入（每1000條提交一次）
public void batchInsert(List<Product> list) {
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
ProductMapper mapper = sqlSession.getMapper(ProductMapper.class);
int pageSize = 1000;
for (int i = 0; i < list.size(); i += pageSize) {
List<Product> subList = list.subList(i, Math.min(i + pageSize, list.size()));
mapper.batchInsert(subList);
sqlSession.commit();
sqlSession.clearCache(); // 清理緩存
}
}關(guān)鍵參數(shù)調(diào)優(yōu)：
# MyBatis配置
mybatis.executor.batch.size=1000
# 連接池（Druid）
spring.datasource.druid.maxActive=50
spring.datasource.druid.initialSize=10第三招：異步化處理
架構(gòu)設(shè)計：
圖片
- 前端上傳：客戶端使用WebUploader等分片上傳工具
- 服務(wù)端：
- 生成唯一任務(wù)ID
- 寫入任務(wù)隊(duì)列（Redis Stream/RabbitMQ）
- 異步線程池：
- 多線程消費(fèi)隊(duì)列
- 處理進(jìn)度存儲在Redis中
- 結(jié)果通知：通過WebSocket或郵件推送完成狀態(tài)
第四招：并行導(dǎo)入
對于千萬級數(shù)據(jù)，可采用分治策略：
階段 | 操作 | 耗時對比 |
單線程 | 逐條讀取+逐條插入 | 基準(zhǔn)值100% |
批處理 | 分頁讀取+批量插入 | 時間降至5% |
多線程分片 | 按Sheet分片，并行處理 | 時間降至1% |
分布式分片 | 多節(jié)點(diǎn)協(xié)同處理（如Spring Batch集群） | 時間降至0.5% |
3 代碼之外的關(guān)鍵經(jīng)驗(yàn)
3.1 數(shù)據(jù)校驗(yàn)必須前置
典型代碼缺陷：
// 錯誤：邊插入邊校驗(yàn)，可能污染數(shù)據(jù)庫
public void validateAndInsert(Product product) {
if (product.getPrice() < 0) {
throw new Exception("價格不能為負(fù)");
}
productMapper.insert(product);
}? 正確實(shí)踐：
- 在流式解析階段完成基礎(chǔ)校驗(yàn)（格式、必填項(xiàng)）
- 入庫前做業(yè)務(wù)校驗(yàn)（數(shù)據(jù)關(guān)聯(lián)性、唯一性）
3.2 斷點(diǎn)續(xù)傳設(shè)計
解決方案：
- 記錄每個分片的處理狀態(tài)
- 失敗時根據(jù)偏移量（offset）恢復(fù)
3.3 日志與監(jiān)控
配置要點(diǎn)：
// Spring Boot配置Prometheus指標(biāo)
@Bean
public MeterRegistryCustomizer<PrometheusMeterRegistry> metrics() {
return registry -> registry.config().meterFilter(
new MeterFilter() {
@Override
public DistributionStatisticConfig configure(Meter.Id id, DistributionStatisticConfig config) {
return DistributionStatisticConfig.builder()
.percentiles(0.5, 0.95) // 統(tǒng)計中位數(shù)和95分位
.build().merge(config);
}
}
);
}四、百萬級導(dǎo)入性能實(shí)測對比
測試環(huán)境：
- 服務(wù)器：4核8G，MySQL 8.0
- 數(shù)據(jù)量：100萬行x15列（約200MB Excel）
方案 | 內(nèi)存峰值 | 耗時 | 吞吐量 |
傳統(tǒng)逐條插入 | 2.5GB | 96分鐘 | 173條/秒 |
分頁讀取+批量插入 | 500MB | 7分鐘 | 2381條/秒 |
多線程分片+異步批量 | 800MB | 86秒 | 11627條/秒 |
分布式分片（3節(jié)點(diǎn)） | 300MB/節(jié)點(diǎn) | 29秒 | 34482條/秒 |
總結(jié)
Excel高性能導(dǎo)入的11條軍規(guī)：
- 決不允許全量加載數(shù)據(jù)到內(nèi)存 → 使用SAX流式解析
- 避免逐行操作數(shù)據(jù)庫 → 批量插入加持
- 永遠(yuǎn)不要讓用戶等待 → 異步處理+進(jìn)度查詢
- 橫向擴(kuò)展比縱向優(yōu)化更有效 → 分片+分布式計算
- 內(nèi)存管理是生死線 → 對象池+避免臨時大對象
- 合理配置連接池參數(shù) → 杜絕瓶頸在數(shù)據(jù)源
- 前置校驗(yàn)絕不動搖 → 臟數(shù)據(jù)必須攔截在入口
- 監(jiān)控務(wù)必完善 → 掌握全鏈路指標(biāo)
- 設(shè)計必須支持容災(zāi) → 斷點(diǎn)續(xù)傳+冪等處理
- 拋棄單機(jī)思維 → 擁抱分布式系統(tǒng)設(shè)計
- 測試要覆蓋極端場景 → 百萬數(shù)據(jù)壓測不可少
如果你正在為Excel導(dǎo)入性能苦惱，希望這篇文章能為你的系統(tǒng)打開一扇新的大門。
責(zé)任編輯：武曉燕
來源：
蘇三說技術(shù)
