1. 从一次内存溢出说起为什么单次全量读取Excel会翻车很多人第一次处理Excel导入数据库的需求时脑子里蹦出来的方案特别直接用EasyExcel的read方法一把梭把整个文件读成一个List然后循环insert进数据库。数据量小的时候这套逻辑跑得飞快几百行、几千行毫无压力。可一旦文件膨胀到几十万行甚至上百万行问题就来了——JVM堆内存被瞬间撑爆OutOfMemoryError直接糊脸服务进程当场挂掉。我印象特别深的一次是帮一个做电商后台的朋友排查导入功能。他们的运营导出订单明细一个xlsx文件大概四十多万行用原来的写法跑本地开发机16G内存都扛不住线上容器只给了2G堆直接崩。后来改成EasyExcel的分批读取模式内存占用稳定在几十兆跑完整个文件也就几分钟。这个对比太强烈了所以我觉得有必要把分批读取这件事从头到尾讲透。EasyExcel之所以能扛住海量数据核心在于它没有把整个文件一次性加载进内存。它底层用的是SAX解析模式逐行读取、逐行回调读一行处理一行处理完就释放。这跟传统的Apache POI把整个工作簿对象树建在内存里完全是两码事。POI的XSSFWorkbook会把每个单元格、每行、每个样式都实例化成对象一个几十万行的文件轻松吃掉几个G。而EasyExcel的模型是流式的内存里同时存在的行对象非常有限。那分批读取具体怎么落地关键就是自定义监听器配合批量阈值。监听器在invoke方法里逐行接收数据我们自己在监听器内部维护一个List缓冲区攒够比如500条或1000条就触发一次批量入库然后把缓冲区清空。这样内存里最多只有阈值数量的对象数据库那边也是批量提交效率比单条insert高出一个数量级。这里有个容易被忽略的点分批的批到底该设多大。设太小比如50条数据库交互次数太多网络往返和事务开销把性能拖垮设太大比如5000条内存虽然还能扛但单次SQL的包体可能超过数据库的max_allowed_packet限制直接报错。我实测下来MySQL场景下500到1000是比较舒服的区间具体还要看单行字段的多少和字段长度。提示分批读取解决的是读的内存问题批量入库解决的是写的效率问题这两件事要分开设计不要混为一谈。还有一个认知误区得提前打破不是用了EasyExcel就万事大吉。如果你在监听器里把每行数据都塞进一个成员变量的大List里那跟一次性读取没区别照样OOM。分批的精髓在于缓冲区要定期清空这个清空动作必须由我们自己控制框架不会帮你做。2. 监听器里的缓冲区设计攒批、清空与事务边界理解了分批读取的宏观思路接下来要啃的是最核心的实现细节——监听器内部到底怎么攒批、什么时候清空、事务怎么控制。这块设计得好不好直接决定了导入功能是稳如老狗还是时不时抽风。2.1 用成员变量维护一个可控的缓冲区监听器类会被EasyExcel实例化invoke方法每读一行就被回调一次。我们要在这个类里放一个ListT作为缓冲区再加一个int记录当前批次已攒的数量。每次invoke进来先把解析出来的实体加进List然后判断list.size() BATCH_SIZE满足就执行入库、清空List。public class OrderImportListener extends AnalysisEventListenerOrderExcelDTO { private static final int BATCH_SIZE 800; private final ListOrderExcelDTO buffer new ArrayList(BATCH_SIZE); private final OrderService orderService; public OrderImportListener(OrderService orderService) { this.orderService orderService; } Override public void invoke(OrderExcelDTO data, AnalysisContext context) { buffer.add(data); if (buffer.size() BATCH_SIZE) { flush(); } } Override public void doAfterAllAnalysed(AnalysisContext context) { // 处理最后不足一批的尾巴数据 flush(); } private void flush() { if (buffer.isEmpty()) { return; } orderService.batchInsert(new ArrayList(buffer)); buffer.clear(); } }这段代码看着简单但有几个坑点值得展开说。第一flush里传给service的是new ArrayList(buffer)而不是buffer本身。为什么因为如果直接把buffer引用传进去service层如果异步处理或者延迟执行等你buffer.clear()之后service拿到的就是个空List。复制一份是最稳妥的做法代价就是多一次浅拷贝对于几百条数据来说完全可以忽略。第二doAfterAllAnalysed这个回调千万别漏。假设文件有1000行阈值是800那么前800行触发一次flush剩下200行如果不在doAfterAllAnalysed里再flush一次这200行就永远躺在缓冲区里进不了库。这个bug特别隐蔽因为程序不报错只是数据少了一截排查起来很费劲。2.2 事务边界别让一个批次拖垮整个导入批量入库的事务怎么划是个需要权衡的问题。最粗放的做法是整个导入过程包一个大事务所有批次要么全成功要么全回滚。听起来很美好但实际很危险几十万行数据在一个事务里数据库的undo log会膨胀得非常大锁持有时间极长一旦中途失败回滚代价高得吓人而且长事务会阻塞其他业务。更合理的做法是每个批次一个独立事务。800条一个事务提交快、锁释放快即使某一批失败也只影响这一批前面成功的批次已经落库。当然这带来一个新问题导入到一半失败了数据库里是半截数据怎么办我的处理经验是引入导入任务记录表。每次导入生成一个任务ID记录总行数、已处理行数、状态进行中/成功/失败。每批入库成功后更新已处理行数。如果失败任务状态标记为失败并记录失败的行号范围。这样后续可以基于任务记录做断点续传或者人工核对而不是两眼一抹黑。事务策略优点缺点适用场景单一大事务强一致失败全回滚长事务、锁重、回滚代价大数据量小万级以内每批独立事务提交快、锁轻、吞吐高失败时数据半截海量数据导入每批独立任务表可追溯、可续传实现稍复杂生产环境推荐2.3 缓冲区大小与数据库参数的联动前面提到批次大小建议500到1000这个数字不是拍脑袋来的它跟数据库的几个参数强相关。以MySQL为例max_allowed_packet默认在4M到64M之间不同版本不一样如果你的单行数据平均2KB800行就是1.6M加上SQL语句本身的开销还在安全范围内。但如果单行有大量文本字段平均10KB800行就是8M就可能顶到上限。所以批次大小要根据单行平均大小反推。我的做法是先估算单行字节数然后用max_allowed_packet的70%除以单行字节数得到一个理论上限再结合内存和性能实测微调。另外批量insert的SQL是insert into t values (...),(...),(...)这种形式拼接的SQL字符串本身也占内存批次太大时字符串拼接的开销也不容忽视。注意不同数据库对批量insert的语法和参数上限支持不同。MySQL靠max_allowed_packetOracle的IN列表有1000个元素限制用批量绑定可以绕过PostgreSQL对单条SQL的参数个数也有上限。跨库迁移时这块要重新验证。3. 从Excel列到数据库字段映射、校验与脏数据处理数据能读进来、能批量写进去只是完成了搬运。真正让导入功能好用的是中间那层字段映射和校验。Excel是给人填的格式千奇百怪数据库是给程序用的要求严格规整。这中间的鸿沟就是导入功能最容易出问题的地方。3.1 用注解做声明式映射但别迷信它EasyExcel提供了ExcelProperty注解可以把Excel表头跟Java字段对应起来。比如ExcelProperty(订单编号)标在orderNo字段上读取时框架自动按表头名匹配。这套机制在表头规范的情况下非常好用代码干净。public class OrderExcelDTO { ExcelProperty(订单编号) private String orderNo; ExcelProperty(下单时间) private String orderTime; ExcelProperty(金额) private BigDecimal amount; }但现实往往不按套路出牌。运营给的Excel表头可能叫订单号、订单ID、订单编号 注意末尾有空格甚至同一列在不同批次的文件里名字都不一样。这时候纯靠注解匹配就会读成null。我的经验是注解打底兜底逻辑补位对于关键字段如果注解读出来是null再尝试用列索引去取或者用模糊匹配表头。还有一种情况是表头行不在第一行。有些模板前面有标题、说明、合并单元格真正的表头在第3行甚至第5行。EasyExcel的headRowNumber参数可以指定表头行号这个一定要根据实际模板设置否则整个映射全乱。3.2 校验要分层别一股脑塞进监听器数据校验如果全写在监听器的invoke里代码会变得又臭又长而且校验失败的处理逻辑跳过、记录、中断混在一起很难维护。我习惯把校验分成三层第一层是格式校验在监听器里做轻量判断比如必填字段是否为空、数字字段是否能解析。这一层只做最基础的过滤发现明显非法的行直接记录到错误列表不加入缓冲区。第二层是业务校验放到入库前的service层。比如订单编号是否重复、商品SKU是否存在、金额是否在合理区间。这一层可能需要查数据库放在监听器里会拖慢读取速度所以攒批之后统一校验更合适。第三层是数据库约束靠唯一索引、外键、非空约束兜底。这是最后一道防线前面校验漏掉的这里会报错。但要注意批量insert时如果有一条违反约束整个批次都会失败所以要么用insert ignore要么在应用层把脏数据提前剔掉。校验层级执行位置校验内容失败处理格式校验监听器invoke非空、类型转换记入错误列表跳过业务校验service批量前重复性、关联性记入错误列表跳过数据库约束DB层唯一、外键、非空批次回滚或ignore3.3 错误数据的收集与反馈导入功能最怕的就是闷头跑完用户不知道哪些行失败了。所以错误数据的收集和反馈机制必须做好。我的做法是在监听器里维护一个ListErrorRow每条错误记录包含行号、原始数据、失败原因。导入结束后把这个列表返回给前端或者写到一个错误明细的Excel里让用户下载。这里有个细节行号要准确。EasyExcel的AnalysisContext里可以拿到当前行索引但要注意它是从0开始还是从1开始以及是否包含了表头行。算错行号会让用户对着错误提示找不到对应的Excel行体验极差。我一般会在监听器里自己维护一个计数器从表头下一行开始算确保跟用户在Excel里看到的行号一致。另外错误列表本身也可能很大。如果几万行都错了全塞内存里也是隐患。可以设个上限比如只保留前1000条错误明细超出的只统计数量。或者边收集边写入一个临时文件避免内存堆积。4. 性能调优实战批次大小、线程与数据库写入方式功能跑通只是及格线海量数据场景下性能才是硬指标。同样是一百万行数据不同的参数配置和写入方式耗时可能差好几倍。这一章我把实测过的调优手段摊开讲。4.1 批次大小的实测对比我拿一个50万行的订单Excel做过一组对比测试单行平均1.5KBMySQL数据库硬件是4核8G的容器。批次大小分别设100、500、1000、2000、5000记录总耗时和内存峰值。批次大小总耗时内存峰值备注1004分20秒80M数据库交互太频繁5002分50秒110M比较均衡10002分35秒150M推荐区间20002分40秒230M内存开始上升50003分10秒480MSQL包体大GC压力明显从数据看1000左右是甜点区。批次太小瓶颈在数据库往返批次太大瓶颈在内存和SQL解析。当然这个结论跟单行大小、数据库配置、网络延迟都有关不能照搬但趋势是通用的存在一个最优区间需要实测确定。4.2 多线程读取能提速但别乱用EasyExcel本身是单线程逐行读的读取速度受限于磁盘IO和解析开销。如果文件特别大读取本身成了瓶颈可以考虑多线程分片读取。思路是把一个大文件按行数切成多个片段每个线程读一段。但这里有个大坑xlsx本质是个zip包随机访问某一行并不容易切分逻辑很复杂。更实际的做法是读取单线程入库多线程。监听器攒够一批后把批次数据丢给一个线程池去异步入库读取线程继续往下读。这样读取和写入并行整体吞吐能提升不少。但要注意异步入库后事务边界和错误处理会变复杂而且如果入库速度跟不上读取速度队列会堆积内存照样爆。所以线程池要有界队列满了就阻塞读取线程形成背压。private final ExecutorService executor new ThreadPoolExecutor( 4, 4, 60, TimeUnit.SECONDS, new ArrayBlockingQueue(20), new ThreadPoolExecutor.CallerRunsPolicy() // 队列满时由调用线程执行形成背压 );CallerRunsPolicy这个拒绝策略在这里特别合适队列满了就让读取线程自己去执行入库任务这样读取自然就慢下来不会无限往队列里塞数据。4.3 数据库写入方式的取舍批量入库有几种常见写法性能差异明显。第一种是MyBatis的foreach拼接批量insert一条SQL插入多条简单直接。第二种是用JDBC的addBatch和executeBatch走PreparedStatement的批量接口。第三种是用rewriteBatchedStatementstrue参数配合JDBC批量让MySQL驱动把多条insert重写成一条多值insert。实测下来开启rewriteBatchedStatementstrue的JDBC批量性能最好比MyBatis的foreach还要快一些因为它在驱动层做了优化而且避免了超长SQL字符串的拼接开销。配置方式是在JDBC URL上加参数jdbc:mysql://host:3306/db?rewriteBatchedStatementstrueuseServerPrepStmtstrue提示rewriteBatchedStatements只对insert和update的批量操作生效而且要求SQL是参数化的PreparedStatement用字符串拼接的SQL不享受这个优化。另外导入期间可以临时关闭数据库的自动提交或者调整innodb_flush_log_at_trx_commit参数生产环境慎改减少磁盘刷写次数。但这些属于数据库层面的调优要结合具体环境和DBA的规范来不能擅自乱动。5. 那些年踩过的坑OOM、数据丢失与格式陷阱前面讲的都是应该怎么做这一章专门讲哪里容易翻车。这些坑我基本都亲身踩过有的排查了大半天才找到原因写出来给大家省点时间。5.1 监听器被复用导致的串数据EasyExcel在读多个sheet或者多次读取时如果监听器实例被复用缓冲区里的数据可能串到一起。比如你读第一个sheet攒了300条还没到阈值接着读第二个sheet这300条就跟第二个sheet的数据混在一起入库了。解决办法是每个sheet或每次读取都new一个监听器实例别图省事复用。还有一种情况是监听器里用了静态变量存缓冲区。静态变量是类级别的多个导入任务并发时会互相干扰数据错乱得一塌糊涂。缓冲区必须是实例变量而且监听器实例不能跨任务共享。5.2 最后一批数据凭空消失这个坑前面提过但值得再强调一遍。doAfterAllAnalysed是处理尾巴数据的唯一时机漏写这个方法最后不足一批的数据就永远进不了库。更隐蔽的是如果读取过程中抛异常doAfterAllAnalysed可能不会执行缓冲区里的数据也就丢了。所以异常处理里也要考虑是否要flush一次或者把缓冲区数据记录到日志里。5.3 日期和数字的格式陷阱Excel里的日期本质是个数字显示成日期只是格式问题。EasyExcel读日期字段时如果单元格格式不标准可能读出来是个数字串或者直接抛转换异常。数字字段也类似有的单元格是文本格式存的数字有的是数值格式读出来类型不一致。我的处理办法是日期和数字字段统一用String接收然后在业务层自己做解析和转换。这样虽然多了一步但能兼容各种奇葩格式而且解析失败时能给出明确的错误提示而不是框架抛一个看不懂的异常。解析日期时用DateTimeFormatter配合多种模式尝试比如先试yyyy-MM-dd HH:mm:ss失败再试yyyy/MM/dd再失败试Excel的序列号转换。5.4 大文件读取超时与进度反馈几十万行的文件读取加入库可能要几分钟甚至十几分钟。如果这是个HTTP接口前端早就超时了。所以海量导入不适合同步接口应该改成异步任务接口只负责接收文件、创建任务、返回任务ID实际导入在后台线程跑前端轮询任务进度。进度反馈的实现可以在监听器里每处理一批就更新一次任务表的已处理行数前端定时查这个数字算百分比。这样用户能看到进度条在动体验好很多也不会因为等待而重复提交。6. 一套可复用的海量导入骨架讲了这么多原理和坑最后给一套我平时用的骨架代码把关键点都串起来。这套骨架的核心思想是读取单线程流式、缓冲区攒批、批量入库、任务表跟踪、错误明细收集。6.1 任务表设计CREATE TABLE import_task ( id BIGINT PRIMARY KEY AUTO_INCREMENT, file_name VARCHAR(255), total_rows INT DEFAULT 0, processed_rows INT DEFAULT 0, status TINYINT COMMENT 0进行中 1成功 2失败, error_msg TEXT, create_time DATETIME, update_time DATETIME );6.2 监听器骨架public class BatchImportListenerT extends AnalysisEventListenerT { private static final int BATCH_SIZE 1000; private final ListT buffer new ArrayList(BATCH_SIZE); private final ConsumerListT batchConsumer; private final Long taskId; private final TaskService taskService; private int rowCount 0; public BatchImportListener(ConsumerListT batchConsumer, Long taskId, TaskService taskService) { this.batchConsumer batchConsumer; this.taskId taskId; this.taskService taskService; } Override public void invoke(T data, AnalysisContext context) { buffer.add(data); rowCount; if (buffer.size() BATCH_SIZE) { flush(); } } Override public void doAfterAllAnalysed(AnalysisContext context) { flush(); taskService.markSuccess(taskId, rowCount); } Override public void onException(Exception exception, AnalysisContext context) { // 单行解析异常记录后继续不中断整个导入 taskService.recordError(taskId, context.readRowHolder().getRowIndex(), exception.getMessage()); } private void flush() { if (buffer.isEmpty()) { return; } ListT batch new ArrayList(buffer); batchConsumer.accept(batch); buffer.clear(); taskService.updateProgress(taskId, rowCount); } }6.3 调用入口public void importExcel(InputStream inputStream, Long taskId) { BatchImportListenerOrderExcelDTO listener new BatchImportListener(batch - orderService.batchInsert(batch), taskId, taskService); EasyExcel.read(inputStream, OrderExcelDTO.class, listener) .headRowNumber(1) .sheet() .doRead(); }这套骨架跑几十万行数据很稳内存占用可控进度可查错误可追溯。实际使用时根据业务调整批次大小、校验逻辑和错误处理策略就行。6.4 上线前的自检清单最后列一个我每次上线海量导入功能前都会过一遍的清单供参考批次大小是否根据单行大小和数据库参数实测确定doAfterAllAnalysed是否处理了尾巴数据监听器实例是否每次读取都新建没有复用缓冲区是否在flush后清空没有内存泄漏错误行号是否跟Excel实际行号对齐导入是否异步化有任务进度反馈数据库批量参数如rewriteBatchedStatements是否开启异常情况下缓冲区数据是否有兜底记录这套东西我在几个项目里反复用过从几万行到上百万行都扛住了。核心其实就一句话别让数据在内存里堆积读一批、写一批、清一批。把这个原则贯彻到底海量Excel导入就不再是让人头疼的事了。