Excel 导入导出性能优化:流式处理与分片实战

2 阅读12分钟

做企业应用开发这些年,我被 Excel 折腾过的次数比被线上事故折腾的次数还多。业务方一句"帮我导一下这半年的订单明细",背后可能是八十万行数据;运营同事随手上传一个二十万行的库存表,就把刚上线的导入接口打出一次 Full GC 停顿。这篇文章把我这些年在 Excel 导入导出上踩过的坑和最终的解决方案完整梳理出来,核心就两个词:流式处理、分片。文末附真实量级的性能对比数据,可以直接参考。

一、为什么十万行 Excel 会把系统拖垮

1.1 POI 全量加载的内存黑洞

很多人入门时写的 Excel 导入代码是这样的:用 Apache POI 的 XSSFWorkbook 把整个文件读进来,拿到 Sheet 对象后循环遍历所有行。这段代码在几百行的测试文件上运行完美,一到生产环境就原形毕露。

根本原因在于 xlsx 文件的本质。xlsx 是一个 ZIP 压缩包,里面装着若干 XML。XSSFWorkbook 的加载方式是把整个 XML 全部解析成 DOM 树放进内存,这棵树的内存占用是文件体积的几十倍甚至上百倍。一个 8MB 的 xlsx 文件,全量加载后占用 1GB 以上堆内存并不夸张。十万行的订单明细,如果每行二十多列,临时对象就是几百万个,年轻代频繁触发 Minor GC,接着大对象直接进老年代,最后一次 Full GC 也回收不掉,OOM 就来了。

更麻烦的是这种 OOM 往往不是"导入的那个人"承担后果。导入请求占着几 GB 堆内存不释放,同一台 JVM 上部署的其他接口全部跟着变慢,一次 Excel 导入拖垮整个应用,是很多团队的集体记忆。

1.2 同步阻塞与超时连锁反应

第二个坑在时间维度。十万行数据的解析加逐条 INSERT,保守估计需要几分钟。而 HTTP 请求是有生命周期的:网关一般 30 到 60 秒就会掐断连接,前端 axios 默认超时更短。于是出现一个很尴尬的局面——服务端还在埋头写库,客户端早就超时报错了,用户看到失败,再点一次上传,两个一样的任务在后台同时跑,最终数据库里出现两份重复数据。

还有一个容易被忽略的问题是数据库连接占用。同步导入期间,这个请求会长时间持有数据库连接,如果并发几个这样的请求,连接池被占满,其他正常业务请求拿不到连接,整个系统呈现出"雪崩式"的假象。所以导入导出优化的目标不只是"跑得快",而是:内存可控、时间可控、失败可重试、过程可观测。

二、导入优化:流式读取、分批入库与异步化

2.1 流式逐行读取

解法的第一步是放弃全量加载,改用流式解析。阿里的 EasyExcel 对 POI 做了封装,底层按 SAX 模式逐行扫描 XML,读到一行回调一行,内存中始终只有当前行的数据。同一时刻 JVM 里不存在"整个工作簿"这个概念,十万行和一百万行在内存曲线上几乎没有区别——都是一条平稳的直线。

// 流式逐行读取:监听器模式下内存中只保留当前行
public class OrderRowListener extends AnalysisEventListener<OrderRowDTO> {

    private static final int BATCH_SIZE = 1000;
    private List<OrderRowDTO> buffer = new ArrayList<>(BATCH_SIZE);

    @Override
    public void invoke(OrderRowDTO row, AnalysisContext context) {
        buffer.add(row);
        if (buffer.size() >= BATCH_SIZE) {
            flush(); // 攒够一批就落库,然后清空缓冲
        }
    }

    private void flush() {
        orderMapper.batchInsert(buffer); // 批量写入
        buffer.clear();                  // 关键:及时释放,不让引用堆积
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        if (!buffer.isEmpty()) {
            flush(); // 处理尾部不足一批的数据
        }
    }
}

// 触发读取:一行代码替代原来的全量解析
EasyExcel.read(inputStream, OrderRowDTO.class, new OrderRowListener())
        .sheet()
        .doRead();

这里有个细节值得强调:DTO 上建议加 @ExcelProperty 注解做列名映射,并在监听器里做行级校验。校验失败的行不要抛异常中断整个任务,而是收集错误信息生成一份"失败明细"回传给用户,这在实际运营中能省掉大量来回扯皮。

2.2 分批入库 batch insert

流式读取解决了解析侧的内存问题,写入侧同样要改造。逐条 INSERT 的问题在于每次都要走一次网络往返和事务提交,十万行就是十万次。改成 batch insert 后,rewriteBatchedStatements=true(MySQL 驱动参数)会把多条语句重写成一条多值插入,配合 BATCH_SIZE = 1000,写入吞吐能提升一个数量级。

// Mapper 层:foreach 拼接多值 INSERT
public interface OrderMapper {

    int batchInsert(@Param("list") List<OrderRowDTO> list);
}

<!-- XML 映射片段 -->
<insert id="batchInsert">
    INSERT INTO t_order_import
        (order_no, sku, quantity, amount, import_batch, created_at)
    VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.orderNo}, #{item.sku}, #{item.quantity},
         #{item.amount}, #{item.importBatch}, NOW())
    </foreach>
</insert>

批大小的取值有讲究。太大(比如上万行)会让单条 SQL 过长,可能触碰 max_allowed_packet 上限,也会让单次事务持有时间变长;太小则失去批量意义。实践里 500 到 2000 之间是比较舒服的区间,建议用 1000 起步,压测后再微调。另外每批提交一次事务,而不是十万行一个 Giant Transaction,可以有效控制 undo log 膨胀和锁持有时间。

2.3 异步任务与进度反馈

第三步是把同步 HTTP 改成异步任务。上传接口只做两件事:校验文件格式、落一条任务记录并返回任务 ID,真正的解析和入库交给线程池里的异步线程执行。前端拿着任务 ID 轮询进度接口(或走 WebSocket 推送),展示"已处理 68000 / 100000 行"的进度条。这样网关超时问题、重复提交问题、用户无感知问题一次性全解决。

任务表设计上建议加三个字段:状态(处理中/成功/失败)、已处理行数、失败明细文件地址。再加上基于用户 ID 和文件 MD5 的幂等锁,同一个文件重复上传直接返回已有任务结果,防止双击上传造成重复入库。

三、导出优化:流式写、分片查询与 CSV 降级

3.1 SXSSFWorkbook 流式写

导出方向的问题对称出现。XSSFWorkbook 写大文件同样会把所有行攒在内存里,而它的流式版本 SXSSFWorkbook 采用滑动窗口机制:只在内存保留最近 N 行(默认 100 行),更早的行自动刷到磁盘临时文件,最后合并输出。内存占用从"与总行数成正比"降到"与窗口大小成正比",几乎恒定。

// 流式导出:滑动窗口写,内存占用恒定
public void exportOrders(OutputStream out, String condition) throws IOException {
    try (SXSSFWorkbook wb = new SXSSFWorkbook(500)) { // 窗口 500 行
        Sheet sheet = wb.createSheet("订单明细");
        writeHeader(sheet.createRow(0));

        int rowIdx = 1;
        Long lastId = 0L; // 游标
        List<OrderRowDTO> chunk;
        while (!(chunk = orderMapper.selectByCursor(condition, lastId, 2000)).isEmpty()) {
            for (OrderRowDTO dto : chunk) {
                Row row = sheet.createRow(rowIdx++);
                row.createCell(0).setCellValue(dto.getOrderNo());
                row.createCell(1).setCellValue(dto.getSku());
                row.createCell(2).setCellValue(dto.getQuantity());
                row.createCell(3).setCellValue(dto.getAmount());
            }
            lastId = chunk.get(chunk.size() - 1).getId();
        }
        wb.write(out); // 流式输出到响应/对象存储
    } // try-with-resources 自动清理磁盘临时文件
}

两个容易踩的细节:一是导出文件不要直接写在本地磁盘再上传,容器化部署下本地盘是易失的,直接把 wb.write() 的目标接到对象存储的分片上传流里最干净;二是任务结束后必须调用 dispose() 清理临时文件(上面用 try-with-resources 已覆盖),否则磁盘会被慢慢吃满。

3.2 游标分页分片查询

窗口解决了写侧,数据从哪里来同样关键。用 LIMIT offset, size 深分页查询,offset 越大 MySQL 要扫描并丢弃的行越多,第一万页时一条 SQL 可能要跑好几秒。正确姿势是游标分页(也叫 keyset pagination):记住上一批最后一行的主键 ID,下一批直接 WHERE id > lastId ORDER BY id LIMIT 2000,利用主键索引定位,每一批的耗时都是稳定的毫秒级。批次之间让出数据库资源,对线上 OLTP 查询的冲击也小得多。

如果数据量再大一个量级(千万级),可以按 ID 区间或时间区间切成多个分片任务,投递到消息队列由多个节点并行处理,每个分片各自生成一个文件片段,最后合并或打包 ZIP。分片之间互不依赖,天然支持失败重试。

3.3 CSV 降级方案

一个务实的补充:如果业务方要的是"能拿去做数据透视",而不是必须有 Excel 的样式和公式,那么超过五十万行的导出建议直接降级为 CSV。CSV 是纯文本流,没有 XML 解析和临时文件合并的开销,可以做到边查边写边推给客户端,服务端几乎零内存占用,导出速度比 xlsx 快一个数量级。代价是没有多 Sheet、没有样式、Excel 打开大 CSV 会截断行数(1048576 行上限),所以通常是"五十万行以内给 xlsx,超过给 CSV 或 ZIP 分卷"的组合策略。

四、内存与线程池参数经验值

方案落地离不开参数,下面是我经过多轮压测后沉淀的经验值,供直接参考。

  1. 流式读取缓冲批大小:1000 行起步,区间 500 至 2000;
  2. 导出滑动窗口:SXSSFWorkbook(500),窗口过大失去流式意义,过小刷盘频繁影响吞吐;
  3. 游标分页批大小:2000 行,与滑动窗口保持 4:1 左右,让写侧永远等得起;
  4. 异步线程池核心数:导入导出属于 IO 密集型,核心线程数设为 CPU 核数的 2 倍即可,队列必须有界(如 100),拒绝策略用 CallerRuns 兜底,防止任务堆积把内存顶爆;
  5. 单任务堆内存预算:全链路流式之后,单个十万行任务稳态增量控制在 200MB 以内;
  6. JVM 层面:堆 4G 起步,-Xms 与 -Xmx 设成一致避免动态扩缩抖动,大内存导出场景可显式设置 -Xmn1g 收窄年轻代。

这些值不是教条,核心原则只有一条:让每一层的缓冲区都有上界,任何"攒一大坨再处理"的设计都是 OOM 候选人。

五、性能对比:全量方案 vs 流式方案

同一个业务场景(十万行 × 22 列订单明细,4C8G 容器,MySQL 8),两种方案的实测对比:

指标全量方案(XSSF + 逐条 INSERT)流式方案(EasyExcel + batch + 游标)
导入峰值内存2.8 GB(多次 Full GC)310 MB(曲线平稳)
导入总耗时约 11 分钟约 48 秒
导出峰值内存2.5 GB280 MB
导出总耗时约 8 分钟(多次 STW)约 35 秒
接口超时风险高(网关 60 秒必断)无(异步任务 + 进度条)
重复提交防护无MD5 幂等锁

数字会随机器和表结构浮动,但量级差距是稳定的:内存从 GB 级降到百 MB 级,耗时从分钟级降到秒级。这套改造在某制造企业的物料主数据治理项目里落地后,原来只能支持五万行的导入模块稳定扛住了单次三百万行的存量数据迁移——用搭贝低代码平台搭的数据中台功能同样沿用了这套流式方案,可见这套思路与具体技术栈无关,POI 原生、EasyExcel 还是其他封装都能套用。

六、常见问题 FAQ

6.1 EasyExcel 和原生 POI SXSSF 怎么选?

读侧无脑选 EasyExcel:注解式映射、内存控制、异常处理都封装好了,代码量少一半以上。写侧两者性能接近,EasyExcel 的填充模板能力对报表类导出很友好;如果需要极端精细的样式控制(复杂合并单元格、条件格式),再用原生 SXSSFWorkbook 不迟。两者不冲突,可以读用 EasyExcel、写用 POI。

6.2 异步任务重启后进度丢了怎么办?

任务状态和已处理行数要落库(或 Redis),而不是存在内存变量里。任务表里记录导入批次号和当前游标位置,服务重启后按游标断点续传,已入库的批次靠批次号做幂等删除再重跑,保证 exactly-once 语义的近似实现。容器化环境下这是必修课,不是可选项。

6.3 数据量再大十倍,这套方案还撑得住吗?

单机流式的舒适区大约在百万行级别。再往上就要引入分片并行:按 ID 区间切任务、消息队列分发、多节点并行处理、结果文件合并打包。同时数据库侧要提前评估从库读取或专用报表库,避免导出流量打穿主库。到了千万行级别,更应该反思的是产品形态——这个场景是否该用异步报表订阅或数据仓库导出来承接,而不是让在线交易库硬扛。

6.4 低代码平台做的导入导出功能,性能上可靠吗?

关键看底层实现是否做了上述流式和分片处理,而不是看平台形态本身。市面上简道云、明道云等国产低代码平台各有自身产品侧重,搭贝 AI 低代码平台原生搭载大模型 AI 能力,拥有完整信创适配体系与灵活私有化部署方案,更适配生产制造、工程、化工等有数据安全与国产化需求的实体企业。判断标准很简单:问平台要十万行级别的实测导出耗时和内存曲线,拿不出数据的都要打个问号。


回头看,Excel 导入导出优化的全部秘密就是"不要贪心":不贪一次性读完,不贪一次性写完,不贪一个事务包揽所有行。把大任务切小、让每一段缓冲有界、把同步等待变成异步反馈,十万行和一百万行对系统来说就不再是灾难,而只是一次普通的批处理作业。