前言

今天要討論一個(gè)讓無(wú)數(shù)人抓狂的話題：如何高效導(dǎo)入百萬(wàn)級(jí)Excel數(shù)據(jù)。

去年有家公司找到我，他們的電商系統(tǒng)遇到一個(gè)致命問(wèn)題：每天需要導(dǎo)入20萬(wàn)條商品數(shù)據(jù)，但一執(zhí)行就卡死，最長(zhǎng)耗時(shí)超過(guò)3小時(shí)。

更魔幻的是，重啟服務(wù)器后前功盡棄。

經(jīng)過(guò)半天的源碼分析，我們發(fā)現(xiàn)了下面這些觸目驚心的代碼...

1.為什么傳統(tǒng)導(dǎo)入方案會(huì)崩盤(pán)？

很多小伙伴在實(shí)現(xiàn)Excel導(dǎo)入時(shí)，往往直接寫(xiě)出這樣的代碼：

// 錯(cuò)誤示例：逐行讀取+逐條插入
public void importExcel(File file) {
    List<Product> list = ExcelUtils.readAll(file); // 一次加載到內(nèi)存
    for (Product product : list) {
        productMapper.insert(product); // 逐行插入
    }
}

這種寫(xiě)法會(huì)引發(fā)三大致命問(wèn)題：

1.1 內(nèi)存熔斷：堆區(qū)OOM慘案

• 問(wèn)題：POI的UserModel（如XSSFWorkbook）一次性加載整個(gè)Excel到內(nèi)存
• 實(shí)驗(yàn)：一個(gè)50MB的Excel（約20萬(wàn)行）直接耗盡默認(rèn)的1GB堆內(nèi)存
• 癥狀：頻繁Full GC ? CPU飆升 ? 服務(wù)無(wú)響應(yīng)

1.2 同步阻塞：用戶等到崩潰

• 過(guò)程：用戶上傳文件 → 同步等待所有數(shù)據(jù)處理完畢 → 返回結(jié)果
• 風(fēng)險(xiǎn)：連接超時(shí)（HTTP默認(rèn)30秒斷開(kāi)）→ 任務(wù)丟失

1.3 效率黑洞：逐條操作事務(wù)

• 實(shí)測(cè)數(shù)據(jù)：MySQL單線程逐條插入≈200條/秒 → 處理20萬(wàn)行≈16分鐘
• 幕后黑手：每次insert都涉及事務(wù)提交、索引維護(hù)、日志寫(xiě)入

2.性能優(yōu)化四板斧

第一招：流式解析

使用POI的SAX模式替代DOM模式：

// 正確寫(xiě)法：分段讀?。ㄒ訦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）
• 避免在解析過(guò)程中創(chuàng)建大量對(duì)象，需復(fù)用數(shù)據(jù)容器

第二招：分頁(yè)批量插入

基于MyBatis的批量插入+連接池優(yōu)化：

// 分頁(yè)批量插入（每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è)計(jì)：

圖片

1. 前端上傳：客戶端使用WebUploader等分片上傳工具
2. 服務(wù)端：

• 生成唯一任務(wù)ID
• 寫(xiě)入任務(wù)隊(duì)列（Redis Stream/RabbitMQ）

3. 異步線程池：

• 多線程消費(fèi)隊(duì)列
• 處理進(jìn)度存儲(chǔ)在Redis中

4. 結(jié)果通知：通過(guò)WebSocket或郵件推送完成狀態(tài)

第四招：并行導(dǎo)入

對(duì)于千萬(wàn)級(jí)數(shù)據(jù)，可采用分治策略：

3 代碼之外的關(guān)鍵經(jīng)驗(yàn)

3.1 數(shù)據(jù)校驗(yàn)必須前置

典型代碼缺陷：

// 錯(cuò)誤：邊插入邊校驗(yàn)，可能污染數(shù)據(jù)庫(kù)
public void validateAndInsert(Product product) {
    if (product.getPrice() < 0) {
        throw new Exception("價(jià)格不能為負(fù)");
    }
    productMapper.insert(product);
}

? 正確實(shí)踐：

1. 在流式解析階段完成基礎(chǔ)校驗(yàn)（格式、必填項(xiàng)）
2. 入庫(kù)前做業(yè)務(wù)校驗(yàn)（數(shù)據(jù)關(guān)聯(lián)性、唯一性）

3.2 斷點(diǎn)續(xù)傳設(shè)計(jì)

解決方案：

• 記錄每個(gè)分片的處理狀態(tài)
• 失敗時(shí)根據(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)計(jì)中位數(shù)和95分位
                    .build().merge(config);
            }
        }
    );
}

四、百萬(wàn)級(jí)導(dǎo)入性能實(shí)測(cè)對(duì)比

測(cè)試環(huán)境：

• 服務(wù)器：4核8G，MySQL 8.0
• 數(shù)據(jù)量：100萬(wàn)行x15列（約200MB Excel）

總結(jié)

Excel高性能導(dǎo)入的11條軍規(guī)：

1. 決不允許全量加載數(shù)據(jù)到內(nèi)存 → 使用SAX流式解析
2. 避免逐行操作數(shù)據(jù)庫(kù) → 批量插入加持
3. 永遠(yuǎn)不要讓用戶等待 → 異步處理+進(jìn)度查詢
4. 橫向擴(kuò)展比縱向優(yōu)化更有效 → 分片+分布式計(jì)算
5. 內(nèi)存管理是生死線 → 對(duì)象池+避免臨時(shí)大對(duì)象
6. 合理配置連接池參數(shù) → 杜絕瓶頸在數(shù)據(jù)源
7. 前置校驗(yàn)絕不動(dòng)搖 → 臟數(shù)據(jù)必須攔截在入口
8. 監(jiān)控務(wù)必完善 → 掌握全鏈路指標(biāo)
9. 設(shè)計(jì)必須支持容災(zāi) → 斷點(diǎn)續(xù)傳+冪等處理
10. 拋棄單機(jī)思維 → 擁抱分布式系統(tǒng)設(shè)計(jì)
11. 測(cè)試要覆蓋極端場(chǎng)景 → 百萬(wàn)數(shù)據(jù)壓測(cè)不可少

如果你正在為Excel導(dǎo)入性能苦惱，希望這篇文章能為你的系統(tǒng)打開(kāi)一扇新的大門(mén)。