Excel高性能导入方案！

苏三说技术

发布于 2025-04-04 16:51:39

3240

文章被收录于专栏：苏三说技术苏三说技术

大家好，我是苏三，又跟大家见面了。

前言

今天要讨论一个让无数人抓狂的话题：如何高效导入百万级Excel数据。

去年有家公司找到我，他们的电商系统遇到一个致命问题：每天需要导入20万条商品数据，但一执行就卡死，最长耗时超过3小时。

更魔幻的是，重启服务器后前功尽弃。

经过半天的源码分析，我们发现了下面这些触目惊心的代码...

1 为什么传统导入方案会崩盘？

很多小伙伴在实现Excel导入时，往往直接写出这样的代码：

// 错误示例：逐行读取+逐条插入
public void importExcel(File file) {
    List<Product> list = ExcelUtils.readAll(file); // 一次加载到内存
    for (Product product : list) {
        productMapper.insert(product); // 逐行插入
    }
}

这种写法会引发三大致命问题：

1.1 内存熔断：堆区OOM惨案

问题：POI的UserModel（如XSSFWorkbook）一次性加载整个Excel到内存
实验：一个50MB的Excel（约20万行）直接耗尽默认的1GB堆内存
症状：频繁Full GC ➔ CPU飙升 ➔ 服务无响应

1.2 同步阻塞：用户等到崩溃

过程：用户上传文件 → 同步等待所有数据处理完毕 → 返回结果
风险：连接超时（HTTP默认30秒断开）→ 任务丢失

1.3 效率黑洞：逐条操作事务

实测数据：MySQL单线程逐条插入≈200条/秒 → 处理20万行≈16分钟
幕后黑手：每次insert都涉及事务提交、索引维护、日志写入

2 性能优化四板斧

第一招：流式解析

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

// 正确写法：分段读取（以HSSF为例）
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); // 不加载全量数据
    }
}

⚠️ 避坑指南：

不同Excel版本需适配（HSSF/XSSF/SXSSF）
避免在解析过程中创建大量对象，需复用数据容器

第二招：分页批量插入

基于MyBatis的批量插入+连接池优化：

// 分页批量插入（每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(); // 清理缓存
    }
}

关键参数调优：

# MyBatis配置
mybatis.executor.batch.size=1000

# 连接池（Druid）
spring.datasource.druid.maxActive=50
spring.datasource.druid.initialSize=10

第三招：异步化处理

架构设计：

前端上传：客户端使用WebUploader等分片上传工具
服务端：
- 生成唯一任务ID
- 写入任务队列（Redis Stream/RabbitMQ）
异步线程池：
- 多线程消费队列
- 处理进度存储在Redis中
结果通知：通过WebSocket或邮件推送完成状态

第四招：并行导入

对于千万级数据，可采用分治策略：

阶段	操作	耗时对比
单线程	逐条读取+逐条插入	基准值100%
批处理	分页读取+批量插入	时间降至5%
多线程分片	按Sheet分片，并行处理	时间降至1%
分布式分片	多节点协同处理（如Spring Batch集群）	时间降至0.5%

3 代码之外的关键经验

3.1 数据校验必须前置

典型代码缺陷：

// 错误：边插入边校验，可能污染数据库
public void validateAndInsert(Product product) {
    if (product.getPrice() < 0) {
        throw new Exception("价格不能为负");
    }
    productMapper.insert(product);
}

✅ 正确实践：

在流式解析阶段完成基础校验（格式、必填项）
入库前做业务校验（数据关联性、唯一性）

3.2 断点续传设计

解决方案：

记录每个分片的处理状态
失败时根据偏移量（offset）恢复

3.3 日志与监控

配置要点：

// Spring Boot配置Prometheus指标
@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) // 统计中位数和95分位
                    .build().merge(config);
            }
        }
    );
}

四、百万级导入性能实测对比

测试环境：

服务器：4核8G，MySQL 8.0
数据量：100万行x15列（约200MB Excel）

方案	内存峰值	耗时	吞吐量
传统逐条插入	2.5GB	96分钟	173条/秒
分页读取+批量插入	500MB	7分钟	2381条/秒
多线程分片+异步批量	800MB	86秒	11627条/秒
分布式分片（3节点）	300MB/节点	29秒	34482条/秒