Excel 百万数据如何快速导入？

作者：不在线第一只蜗牛

2025-04-03
福建
本文字数：2233 字
阅读完需：约 7 分钟

前言

今天要讨论一个让无数人抓狂的话题：如何高效导入百万级 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=50spring.datasource.druid.initialSize=10

复制代码

第三招：异步化处理

架构设计：

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

第四招：并行导入

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

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指标@Beanpublic 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）

总结

Excel 高性能导入的 11 条军规：

决不允许全量加载数据到内存 → 使用 SAX 流式解析
避免逐行操作数据库 → 批量插入加持
永远不要让用户等待 → 异步处理+进度查询
横向扩展比纵向优化更有效 → 分片+分布式计算
内存管理是生死线 → 对象池+避免临时大对象
合理配置连接池参数 → 杜绝瓶颈在数据源
前置校验绝不动摇 → 脏数据必须拦截在入口
监控务必完善 → 掌握全链路指标
设计必须支持容灾 → 断点续传+幂等处理
抛弃单机思维 → 拥抱分布式系统设计
测试要覆盖极端场景 → 百万数据压测不可少

如果你正在为 Excel 导入性能苦恼，希望这篇文章能为你的系统打开一扇新的大门。

如果你有其他想了解的技术难题，欢迎在评论区留言！

文章转载自：苏三说技术
原文链接：https://www.cnblogs.com/12lisu/p/18805646
体验地址：http://www.jnpfsoft.com/?from=001YH

发布于: 4 小时前阅读数: 7

不在线第一只蜗牛

关注

还未添加个人签名 2023-06-19 加入

还未添加个人简介

发布

暂无评论

创作场景

Excel 百万数据如何快速导入？

前言

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

1.1 内存熔断：堆区 OOM 惨案

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

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

2 性能优化四板斧

第一招：流式解析

第二招：分页批量插入

第三招：异步化处理

第四招：并行导入

3 代码之外的关键经验

3.1 数据校验必须前置

3.2 断点续传设计

3.3 日志与监控

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

总结

不在线第一只蜗牛

评论