【架构实战】百万级Excel数据导入的“坑”与“填坑”指南（上）：痛点剖析与破局利器 EasyExcel

前言大家好这里是程序员阿亮今天来给大家讲解一下在传统企业中报表和数据处理业务非常常见的工具-Excel在后端的使用和场景引言从一个看似简单的需求说起在日常的 B2B 业务、ERP 系统或者后台管理系统中“Excel 导入导出”几乎是一个标配功能。通常情况下业务流程是这样的产品经理提了一个需求要求运营人员能够通过 Excel 批量上传商品信息、用户名单或者历史对账数据。开发人员接到需求后往往第一反应是这题我会引入 Apache POI写几行代码解析 Workbook然后写个 for 循环存入数据库搞定然而当系统的业务量级不断攀升运营人员上传的 Excel 文件从几千条膨胀到几十万甚至上百万条时原本岁月静好的系统就会开始频繁报警页面卡死、请求超时、甚至直接 OOM内存溢出导致服务宕机。面对百万级数据的 Excel 导入这不仅是一个简单的功能点更是一道综合了内存管理、多线程并发、数据库优化与容错处理的系统性架构题。本文将分为上下两篇本篇上篇将深入剖析百万级数据导入过程中面临的“三座大山”并详细拆解我们为何选择 EasyExcel 作为破局的利器。在下篇中我将结合具体的代码实战手把手教你如何通过“EasyExcel 多线程 数据库批量插入”实现一套高性能的导入方案。一、 灾难现场百万级 Excel 导入面临的“三座大山”在动手写代码之前我们要学会拆解问题。百万级数据从 Excel 读取并插入到数据库到底会遇到哪些问题1. 第一座大山OOM内存溢出—— 悬在 JVM 头上的达摩克利斯之剑这是处理大文件时最致命的问题。传统的 Excel 处理例如基于 Apache POI 的 XSSFWorkbook采用的是DOM文档对象模型解析模式。这意味着程序会将整个 Excel 文件的内容本质上是庞大的 XML 文件结构一次性完整地加载到内存中构建成一棵巨大的对象树。你可以算一笔账一个几十 MB 的 Excel 文件由于包含了大量的样式、单元格属性、空行等冗余信息被 POI 解析成 Java 对象驻留在堆内存中时其占用的内存大小可能会膨胀数倍甚至十倍。对于一个包含百万行数据的 Excel瞬间吃掉几个 G 的内存是常有的事。如果此时系统正好处于高并发期JVM 根本来不及进行垃圾回收GC直接就会抛出 java.lang.OutOfMemoryError: Java heap space导致整个服务崩溃。2. 第二座大山龟速的性能 —— 让人绝望的等待百万级数据的处理如果按照传统的“单线程读取 - 单条校验 - 单条 Insert”模式性能是极其低下的。读取慢单线程线性解析百万行数据本身就非常耗时。网络与 IO 瓶颈如果在循环里逐条调用 INSERT 语句意味着程序要和数据库进行一百万次网络通信经历一百万次事务的开启与提交。即使数据库性能再好也会被频繁的网络 IO 彻底拖垮。用户体验极差导入请求通常通过 HTTP 发起处理时间过长不仅会导致前端请求超时Timeout还会长期占用服务器的连接池资源。3. 第三座大山脆弱的错误处理 —— 牵一发而动全身在文件的读取和数据库写入过程中业务逻辑往往是复杂的。由于 Excel 数据是由人工填写的你永远无法预知用户会输入什么奇葩数据数据格式错误本该填数字的地方填了汉字。数据缺失必填字段为空。数据重复Excel 内部包含重复数据或者与数据库已有数据冲突违反唯一性约束。传统模式的痛点在于如果在导入了 99 万条数据时第 990001 条数据发生了异常此时该怎么办如果是整体包裹在一个大事务中直接回滚Rollback会导致前面的 99 万条处理时间全部浪费且大事务极易锁死数据库如果不回滚就会产生“部分成功、部分失败”的脏数据后续的重试和数据清洗将是一场灾难。二、 破局思路从“全局掌控”到“分批流水线”面对这三座大山我们的架构思路必须发生转变。解决 OOM放弃“一口吃成胖子”改用“流水线处理”。百万级数据绝不能一次性读入内存必须采用基于流式读取Streaming的方式读一行处理一行丢弃一行让内存始终保持在一个极低的平稳状态。解决性能问题引入“多线程并发”与“批量处理”。读阶段我们可以将百万数据分散在 Excel 的不同 Sheet 中利用多线程同时读取不同的 Sheet提升解析速度。写阶段放弃单条插入在内存中暂存一小批数据例如 1000 条然后利用 MyBatis 或 JDBC 的批量插入Batch Insert功能极大地减少数据库交互次数。这就是经典的生产者-消费者模型。解决容错问题剥离校验与入库实施“容错与日志补偿”策略。分为两步走。第一步先做数据基础格式校验第二步在入库时不建议对百万级数据使用大事务回滚。更合理的做法是捕获异常跳过错误数据通过日志记录下失败的行号和原因或记录到异常表中让正确的数据先入库失败的数据后续由人工根据日志进行修正和重新导入。明确了思路接下来就是技术选型。为了实现上述的“流式读取”我们引入了阿里开源的神器 ——EasyExcel。三、 内存杀手的克星EasyExcel 深度解析1. 为什么不直接用 Apache POI 的 SAX 模式前面提到 POI 的 DOM 模式会导致 OOM。其实 POI 官方也知道这个问题所以提供了一种底层基于 SAXSimple API for XML的事件驱动解析模式。SAX 模式确实可以做到逐行读取解决内存溢出。但是POI 的 SAX 模式 API 极其底层且反人类开发者需要自己去处理繁杂的 XML 标签处理行、列的索引映射甚至还要自己处理不同 Excel 版本xls vs xlsx的底层差异。写一个健壮的 SAX 解析器代码量巨大且极其容易出 Bug。2. EasyExcel 是什么EasyExcel是阿里巴巴开源的一个基于 Java 的、简单、省内存的读写 Excel 的开源项目。在技术选型上我们毫不犹豫地选择了 EasyExcel因为它是特别针对大数据量和复杂 Excel 文件处理进行优化的。它的核心优势可以用一句话概括在尽可能节约内存的情况下提供极其极其简单的 API 让开发者轻松读写大 Excel。3. EasyExcel 的核心工作原理为什么它不内存溢出EasyExcel 能够轻松搞定百万级数据的核心秘密在于其彻底重写了 POI 对 xlsx 文件的解析过程。当 EasyExcel 解析 Excel 时不构建完整 DOM 树它绝对不会将整个 Excel 文件一次性全部加载到内存中。基于磁盘与流的逐行解析它从磁盘上一行一行地读取数据流。EasyExcel 会将每一行解析出来的数据转换为我们定义的 Java 实体类POJO。事件驱动回调核心每次解析完一行数据它就会自动触发回调一个名为 ReadListener 的接口。即用即毁在 ReadListener 中处理完这行数据后这行数据占用的内存就会失去强引用随时可以被 JVM 垃圾回收器GC回收掉。通过这种“细水长流”的方式无论你的 Excel 文件是 10 万行还是 1000 万行它占用的内存都仅仅是当前正在解析的那几行数据所占用的极小空间完美避开了 OOM 的雷区。4. 灵魂组件ReadListener 监听器理解 EasyExcel 的精髓就在于理解 ReadListener。这就好比一条工厂的流水线EasyExcel 解析引擎就是传送带它负责把 Excel 里的每一行数据变成零件源源不断地送过来。ReadListener就是站在传送带旁边的工人。你可以自定义这个工人要做什么。在实际开发中我们通常会实现这个接口主要关注其中的两个方法invoke(T data, AnalysisContext context)这是最核心的方法。每读取到一行数据就会调用一次此方法。我们可以在这里进行数据的合法性校验并将有效的数据暂存到一个本地集合如 List中。当集合大小达到我们设定的阈值例如 1000 条这就是上文提到的分批次处理时触发一次数据库的批量插入然后清空集合。doAfterAllAnalysed(AnalysisContext context)当整个 Excel或当前 Sheet的所有数据都读取完毕后会调用此方法。因为最后一次读取的数据往往凑不够一个批次比如最后只剩下 300 条我们必须在这个方法里进行“收尾工作”将最后剩余的数据也插入到数据库中。四、 总结与下篇预告到这里我们已经清晰地梳理了百万级 Excel 数据导入的背景和痛点。我们明确了不能使用传统的“一把梭”读写方式而是要构建一套基于流式读取、内存批处理、多线程并发协作的完整方案。而 EasyExcel 凭借其优秀的事件驱动架构和极致的内存管理成为了我们承载数据读取任务的最佳载体。方案整体的宏观蓝图已经绘就利用多线程池并发读取含有百万级数据 Excel 的不同 Sheet。借助 EasyExcel 的 ReadListener 逐行解析避免内存溢出。在 Listener 中维持一个缓冲池满 1000 条则利用 MyBatis 触发一次批量插入大幅降低数据库 I/O 压力。抛弃全局事务回滚针对冲突数据采用“跳过记录日志”的降级容错方案。纸上得来终觉浅绝知此事要躬行。在接下来的《百万级数据Excel导入的“坑”与“填坑”指南下多线程与MyBatis批量入库实战》中我将放出真正的核心代码。我们将详细探讨如何优雅地通过线程池并发读取多 Sheet 代码实现自定义 ReadListener 的编写规范与内部重试机制应该怎么写MyBatis 批量插入的 XML 是如何配置的关注我我们下篇文章见真章