EasyExcel海量数据导入：分批读取与批量入库实战

1. 为什么海量 Excel 导入不能一把梭做过后台管理系统的人多半都遇到过这种需求运营丢过来一个几十万行的 Excel要求“导入到数据库里最好今天就能用”。新手的第一反应通常是直接EasyExcel.read(fileName, DemoData.class, new PageReadListener(...)).sheet().doRead()本地跑个几千行没问题一上生产、文件一大内存直接飙到几个 G然后 OOM服务重启用户投诉。这个坑我踩过不止一次后来才慢慢摸清楚EasyExcel 本身是流式解析的真正把内存吃爆的往往不是 Excel 解析而是我们自己写的监听器里那个不断膨胀的 List。先把结论摆出来海量 Excel 读取存入数据库核心就三件事——分批读、分批攒、分批写。EasyExcel 负责“分批读”我们负责“分批攒”和“分批写”三者节奏对齐内存才能稳住。这篇文章就把这套东西从头到尾拆一遍包括为什么这么设计、参数怎么定、代码怎么写、出问题怎么查。适合已经用过 EasyExcel 但一遇到大文件就发怵的同学也适合完全没接触过、想直接抄一套能跑方案的同学。在展开之前先明确一个概念所谓“海量”到底多大我的经验是一万行以内随便玩十万行开始要小心百万行以上必须严格分批。这个分界线不是拍脑袋后面讲内存估算的时候会给出计算过程。理解了这条线你就知道自己的场景该用哪一档方案。2. 整体方案设计与选型思路2.1 为什么是 EasyExcel 而不是 POI老牌的 Apache POI 处理 Excel 有两种模式HSSF/XSSF和SXSSF。XSSF是纯内存模型读一个 10 万行的 xlsx光是把整个 workbook 加载进内存就可能吃掉几百兆甚至上 G因为它把每个单元格都实例化成了对象。SXSSF是 POI 的流式写方案主要解决“写”的问题读大文件依然不友好。EasyExcel 的思路不一样它在解析层做了事件驱动底层基于 SAX 解析 OOXML读一行、回调一行、丢一行理论上内存占用和文件行数无关只和“你一次攒多少行”有关。这就是它能扛住大文件的根本原因。所以选型上读大文件我基本只用 EasyExcelPOI 留给那些需要复杂样式操作、公式计算的小文件场景。不过要提醒一句EasyExcel 的“省内存”是有前提的你的监听器不能把所有行都存起来。我见过太多代码监听器里写个ListDemoData list new ArrayList()invoke里无脑list.add(data)然后在doAfterAllAnalysed里一次性saveBatch。这种写法等于把 EasyExcel 的流式优势全抵消了内存照样爆。所以方案设计的重点其实落在监听器怎么写。2.2 分批读取的核心攒够一批就落库整套方案的主线非常清晰用一句话概括就是监听器里维护一个固定容量的缓冲区每攒够 BATCH_SIZE 条就触发一次批量入库然后清空缓冲区文件读完后把最后不足一批的残余数据再落一次库。这个设计里有三个关键决策点我逐个说下理由。第一个是批次大小怎么定。批次太小比如 100 条数据库交互次数太多网络往返和事务开销会把整体速度拖垮批次太大比如 10000 条单次insert的 SQL 太长可能超过数据库的max_allowed_packet而且内存缓冲区也会变大。我的经验值是1000 到 5000 之间MySQL 场景下 2000 左右比较稳具体还要看单行字段多少。字段特别多的宽表批次要相应调小。第二个是要不要用事务。批量入库如果不加事务中途失败会留下“半截数据”重跑又可能重复插入非常难受。但如果整个文件一个大事务百万行数据的事务日志会撑爆 undo log回滚也慢得离谱。所以正确做法是按批开事务每批独立提交失败的那一批单独处理。这样既保证单批原子性又不会让事务无限膨胀。第三个是重复数据怎么办。实际业务里Excel 导入经常要支持“重复导入”比如运营改了数据重新传一遍。这时候要么用INSERT ... ON DUPLICATE KEY UPDATE要么先按业务主键删再插。这个决策取决于业务语义但一定要在方案设计阶段想清楚别等数据脏了再补。2.3 内存到底吃在哪一次实打实的估算很多人对“内存”没概念觉得“不就几万行数据嘛”。我们来算一笔账。假设一行数据有 20 个字段其中 10 个 String、5 个 Integer、5 个 Date。一个 String 对象在 JVM 里对象头 16 字节加上字符数组引用和实际字符平均一个短字符串按 50 字节算Integer 对象 16 字节Date 对象 24 字节左右。那么一行大概10 个 String10 × 50 500 字节5 个 Integer5 × 16 80 字节5 个 Date5 × 24 120 字节对象本身和引用开销约 100 字节合计约 800 字节一行。10 万行就是 80MB100 万行就是 800MB。这还只是数据本身如果监听器里还存了别的中间结构翻倍很正常。而 JVM 堆如果只给了 1G100 万行一次性攒着必爆。反过来如果批次是 2000 行缓冲区只占 2000 × 800 字节 ≈ 1.6MB可以忽略不计。这就是分批的意义——把内存占用从“和总行数成正比”变成“和批次大小成正比”。提示上面是粗略估算实际受字段内容长度、JVM 对象对齐、是否开启压缩指针等影响。做容量规划时建议留 2 到 3 倍余量。3. 核心细节解析与实操要点3.1 监听器的生命周期与回调时机EasyExcel 的ReadListener有几个关键回调理解它们的调用时机是写好监听器的前提。invoke(T data, AnalysisContext context)每解析一行调用一次这是攒数据的地方。doAfterAllAnalysed(AnalysisContext context)整个 sheet 解析完后调用一次这是处理“最后一批残余数据”的地方。onException(Exception exception, AnalysisContext context)解析出错时调用可以在这里决定是中断还是跳过。hasNext(AnalysisContext context)返回是否继续读一般不用重写。有个细节很多人不知道invoke里拿到的data对象在下一行解析时可能被复用。EasyExcel 为了省内存某些版本会复用行对象。如果你在invoke里只是把数据加进 List没问题但如果你把对象引用存到别的地方延迟处理就可能拿到被覆盖的数据。稳妥做法是在invoke里就把数据转成自己的实体或者直接落库别留引用。3.2 批次缓冲区的线程安全与清空策略监听器默认是单线程回调的所以缓冲区用普通ArrayList就行不需要ConcurrentLinkedQueue这类并发容器。但有个坑清空缓冲区时不要用list.clear()之后继续复用同一个 List 引用去异步处理。如果你在invoke里触发的是异步落库clear()会把正在被异步线程读取的数据也清掉。正确做法是每次攒够一批把当前 List 交给落库逻辑然后new一个新的 List 给下一批用。if (buffer.size() BATCH_SIZE) { ListDemoData batch buffer; buffer new ArrayList(BATCH_SIZE); saveBatch(batch); // 同步或异步取决于你的实现 }这个“换引用而不是清空”的写法是我踩过坑之后固定下来的习惯。同步落库时clear()也能用但统一成换引用更不容易出错。3.3 批量入库的 SQL 与参数配置批量入库的性能很大程度取决于 SQL 怎么写。以 MySQL 为例MyBatis 的foreach拼接批量 insert 是最常见的做法insert idbatchInsert INSERT INTO demo_data (col1, col2, col3) VALUES foreach collectionlist itemitem separator, (#{item.col1}, #{item.col2}, #{item.col3}) /foreach /insert这里有几个参数要盯紧参数建议值说明批次大小1000~5000太大 SQL 超长太小交互频繁rewriteBatchedStatementstrueJDBC 连接串加这个批量 insert 性能翻倍max_allowed_packet视情况调大单条 SQL 超过它会报错事务粒度每批一个避免大事务撑爆 undo logrewriteBatchedStatementstrue这个参数特别值得说。MySQL 的 JDBC 驱动默认会把批量 insert 拆成一条条发加上这个参数后驱动会把它们合并成一条多值 insert性能差距能有几倍。很多人抱怨“批量插入怎么还是慢”八成是没加这个参数。3.4 异常处理与断点续传的取舍海量导入最怕的就是“跑到 80 万行报错了前面全白干”。所以异常处理策略要提前定。我的做法是分两层第一层是行级容错。在invoke里对单行数据做校验比如必填字段为空、格式不对就把这行记到错误列表跳过不落库继续读下一行。这样不会因为一行脏数据中断整个导入。第二层是批级容错。如果某一批落库失败比如数据库连接断了记录下当前批次的位置抛出异常中断。这时候要么整批重试要么人工介入。断点续传实现起来复杂需要记录“已成功处理到第几行”一般业务量不大的场景不值得做直接重跑 幂等去重更省事。注意错误列表也要控制大小。如果错误行有几万条全攒在内存里同样会爆。建议错误信息直接写日志或落一张错误表别在内存里堆。4. 完整实操流程与关键代码4.1 依赖引入与基础配置先上依赖。EasyExcel 的坐标现在叫easyexcel版本建议用较新的稳定版老版本在并发和内存上有一些已知问题。dependency groupIdcom.alibaba/groupId artifactIdeasyexcel/artifactId version3.3.2/version /dependency实体类用注解映射表头index指定列顺序比value匹配表头名更稳因为表头名可能被运营改。Data public class DemoData { ExcelProperty(index 0) private String name; ExcelProperty(index 1) private Integer age; ExcelProperty(index 2) private Date birthday; }4.2 分批监听器的完整实现这是整套方案的核心我把它写成一个可复用的泛型监听器业务方只需要传入“怎么落库”的函数。public class BatchReadListenerT extends AnalysisEventListenerT { private static final int BATCH_SIZE 2000; private ListT buffer new ArrayList(BATCH_SIZE); private final ConsumerListT batchConsumer; private long totalCount 0L; public BatchReadListener(ConsumerListT batchConsumer) { this.batchConsumer batchConsumer; } Override public void invoke(T data, AnalysisContext context) { buffer.add(data); totalCount; if (buffer.size() BATCH_SIZE) { flush(); } } Override public void doAfterAllAnalysed(AnalysisContext context) { flush(); // 处理最后不足一批的残余 } private void flush() { if (buffer.isEmpty()) { return; } ListT batch buffer; buffer new ArrayList(BATCH_SIZE); batchConsumer.accept(batch); } public long getTotalCount() { return totalCount; } }调用侧这样写String fileName /data/import/demo.xlsx; BatchReadListenerDemoData listener new BatchReadListener(demoService::saveBatch); EasyExcel.read(fileName, DemoData.class, listener) .sheet() .doRead(); log.info(导入完成共处理 {} 行, listener.getTotalCount());注意flush()里“换引用”的写法以及doAfterAllAnalysed里必须再flush一次否则最后那几百行永远进不了库。这个 bug 我见过不止一个项目犯表现就是“导入的数据总是少一点”查半天查不出来。4.3 落库逻辑与事务边界落库方法上加事务粒度就是一批。Transactional(rollbackFor Exception.class) public void saveBatch(ListDemoData batch) { demoMapper.batchInsert(batch); }如果批次之间想并行加速可以把batchConsumer换成提交到线程池。但要注意两点一是线程池队列不能无限大否则任务堆积一样吃内存二是并行落库时数据库连接池要够用别把连接打满。我的建议是先用同步跑通确认瓶颈确实在落库再考虑并行多数场景同步已经够快。4.4 一次百万行导入的实测记录说个我实际跑过的场景。文件是 120 万行的 xlsx约 80MB字段 18 个MySQL 单表。机器是 4 核 8GJVM 堆给了 2G。第一版用“全量攒着最后落库”跑到 40 多万行就 OOM 了堆监控显示 List 占了 1.5G 以上。改成批次 2000 的分批方案后堆占用稳定在 300MB 以内其中大部分还是 EasyExcel 解析本身的开销。总耗时约 6 分钟其中解析占 2 分钟落库占 4 分钟。后来把批次调到 3000、加上rewriteBatchedStatementstrue总耗时降到 4 分半。这个数据不是让你照搬而是给你一个量级参考百万行级别分钟级完成是正常的如果你的方案跑了半小时还没完多半是批次太小或者没开批量重写。5. 常见问题与排查技巧实录5.1 导入数据总是少几行这是最高频的问题九成是doAfterAllAnalysed里忘了flush。因为invoke只在攒够一批时才落库最后不足一批的数据一直躺在缓冲区里文件读完了也没人管它。排查方法很简单打印totalCount和数据库实际行数对比如果差值小于批次大小基本就是这个原因。5.2 内存还是爆怎么定位如果已经分批了还 OOM按这个顺序查监听器里是不是还有别的集合在无限增长比如错误列表、去重用的 Set。批次大小是不是设得太大比如有人图省事设成 10 万。是不是用了ReadListener的某些会缓存全部数据的实现比如AnalysisEventListener的某些封装。落库是不是异步的任务在队列里堆积。用jmap -histo:live pid看堆里什么对象最多一眼就能定位。5.3 批量插入报 SQL 太长报错信息通常是Packet for query is too large。两个方向解决调大数据库的max_allowed_packet或者调小批次。我一般优先调小批次因为改数据库参数涉及运维而且批次小一点对内存也更友好。如果单行字段特别多比如 50 个以上批次可能要降到 500。5.4 重复导入产生重复数据这个要在业务层解决。常见方案有三种一是给业务主键加唯一索引用INSERT IGNORE或ON DUPLICATE KEY UPDATE二是导入前按批次先删后插三是导入时先查一遍已存在的 key。第一种性能最好推荐优先考虑。注意ON DUPLICATE KEY UPDATE在批量场景下也有性能损耗数据量大时可以先INSERT IGNORE再单独更新。5.5 常见问题速查表现象可能原因解决方向数据少几行残余批次没落库doAfterAllAnalysed里 flush内存 OOM监听器缓存全量数据改分批检查其他集合插入慢没开批量重写连接串加rewriteBatchedStatementstrueSQL 太长批次过大调小批次或调大 packet数据重复无幂等处理唯一索引 幂等写入解析报错中断脏数据行级校验 跳过记录5.6 几个容易被忽略的实操心得第一个心得先拿真实文件测别拿自己造的假数据测。真实 Excel 里经常有合并单元格、空行、隐藏列、格式错乱这些在假数据里都遇不到。我见过一个文件前面几万行都正常中间突然插了个空行EasyExcel 默认会跳过空行但如果你的校验逻辑假设“每行都有值”就会出问题。第二个心得给导入任务加个进度日志。每处理 10 万行打一条日志记录已处理行数和耗时。这样出问题时你能知道卡在哪而不是干等。日志里别打每行数据会拖慢速度还刷屏。第三个心得大文件导入尽量避开业务高峰期。百万行导入对数据库的 IO 和连接占用不小和生产查询抢资源容易引发连锁反应。如果一定要在高峰期跑把批次调小、加个限流别让导入把数据库打满。第四个心得xls 和 xlsx 要区别对待。xls 是老的二进制格式单 sheet 最多 65536 行超过就得用 xlsx。而且 xls 解析更慢、更吃内存如果运营还在用 xls 传大数据建议先让他们另存为 xlsx。6. 性能调优与扩展思路6.1 批次大小的动态调整固定批次在多数场景够用但如果你的行数据大小差异很大比如有的行字段内容很长固定批次可能导致内存波动。进阶做法是按“字节数”而不是“行数”来触发落库比如缓冲区累计到 1MB 就落一次。实现上可以在invoke里估算当前行的大小累加。这个方案复杂一些适合对内存极其敏感的场景普通业务用固定行数批次就够了。6.2 多 sheet 与多文件的处理一个文件多个 sheet 时每个 sheet 会独立触发doAfterAllAnalysed所以监听器实例要每个 sheet 一个别复用。多文件的话循环调用EasyExcel.read即可注意每个文件处理完及时释放资源。如果文件特别多可以配合线程池并行处理不同文件但要注意数据库连接和内存的总量控制。6.3 从 Excel 到其他数据源的迁移这套“分批读、分批攒、分批写”的模式其实不限于 Excel。CSV 读取、接口分页拉取、消息队列消费本质都是“数据源持续产出消费端要控制节奏”。理解了这套模式换个数据源只是把 EasyExcel 的监听器换成对应的读取逻辑落库部分完全复用。这也是我为什么建议把监听器写成泛型 函数式的原因复用性直接拉满。6.4 监控与告警的补充生产环境的导入任务建议加上几个监控指标已处理行数、当前批次耗时、累计失败行数、JVM 堆使用率。这些指标打到监控系统里出问题能第一时间发现。尤其是堆使用率如果导入过程中堆持续上涨不回落说明有内存泄漏要赶紧查。别等 OOM 了才去看那时候服务已经挂了。最后分享一个我自己的习惯每次写完导入功能我都会用一个“超大文件 脏数据 中途断连”的组合场景压一遍。超大文件测内存脏数据测容错中途断连测事务和恢复。这三关过了上线基本就稳了。很多 bug 不是逻辑写错了而是没在极端场景下验证过。