★ FEATURED ARTICLE 1. 海量Excel入库这件事坑比想象中多做过数据导入的人都有一个共识Excel这东西小文件是朋友大文件是敌人。几百行数据用POI的XSSFWorkbook一把梭代码写起来舒服跑起来也没毛病。可一旦文件膨胀到几十万行、上百兆甚至一批文件加起来几百万行再用老办法就是给自己找麻烦——内存直接飙到几个GGC疯狂工作最后OutOfMemoryError一抛前功尽弃。EasyExcel这个库就是冲着这个痛点来的。它基于SAX解析模式逐行读取不在内存里保留整个工作簿对象所以处理大文件时内存占用非常平稳。但光会用它读文件还不够真正落地到“海量Excel数据读取并存入数据库”这个场景要考虑的东西远不止调个API那么简单分批大小怎么定、每批数据怎么攒、事务边界怎么划、异常了怎么续、数据库写入怎么提速、多线程能不能上、文件格式不统一怎么办。这些问题一个没处理好轻则慢得让人想砸键盘重则数据丢一半你还不知道。这篇内容适合两类人看一类是刚接触EasyExcel、准备做数据导入功能的开发者另一类是已经用上了但发现性能不理想、想进一步优化的老手。我会从整体设计思路讲起把核心参数的计算过程、分批读取的实现细节、数据库写入的优化手段、以及实际踩过的坑都摊开来说。代码以Java为主数据库以MySQL为参照但思路对其它语言和数据库同样适用。2. 整体方案设计与技术选型思路2.1 为什么是EasyExcel而不是原生POI原生POI处理Excel有两种模式XSSFWorkbook用户模式和XSSFReader事件模式。用户模式把整个文件加载成对象树写代码方便但内存占用和文件大小成正比。一个50MB的xlsx文件加载到内存后可能膨胀到500MB甚至更多因为每个单元格、每行、每个样式都是对象。事件模式内存友好但API极其难用要自己处理SAX事件、维护行状态、处理共享字符串表写起来痛苦且容易出错。EasyExcel在事件模式之上做了一层封装对外暴露监听器接口你只需要关心“读到一行数据后做什么”底层的解析、类型转换、异常处理它帮你兜住。同时它支持注解映射把Excel列直接映射到Java对象字段省去大量手工取值代码。这就是选它的核心理由内存可控 开发效率可接受。2.2 分批读取的核心逻辑所谓分批读取本质上是“读一批、处理一批、清一批”。监听器在invoke方法里逐行接收数据我们自己维护一个List作为缓冲区当缓冲区大小达到预设阈值时触发一次批量入库操作然后清空缓冲区。这样内存里始终只有一批数据不会随着文件行数增长而膨胀。这里有个关键点EasyExcel的监听器是单线程回调的读一行调一次invoke。所以缓冲区的攒批逻辑不需要考虑并发问题但入库操作如果放在invoke里同步执行会阻塞读取过程。对于海量数据这种串行方式吞吐量上不去。后面会讲怎么用异步或者批量提交来缓解。2.3 数据库写入策略的取舍批量入库有三条路可走逐条insert、JDBC批量提交、以及数据库特定的批量加载工具。逐条insert在数据量大时就是灾难每条一次网络往返几万条就能跑几分钟。JDBC的addBatch/executeBatch能把多条insert合并成一次网络传输性能提升明显是通用性最好的方案。MySQL还可以用rewriteBatchedStatementstrue参数进一步优化把多条insert改写成一条多values的insert速度再上一个台阶。至于LOAD DATA INFILE这类工具速度确实快但依赖文件系统权限、格式要求严格、跨数据库移植性差一般项目里不太愿意引入。所以主流做法还是JDBC批量提交配合合理的批次大小和事务控制。3. 核心细节解析与实操要点3.1 批次大小的确定不是越大越好批次大小是这套方案里最需要拿捏的参数。太小比如100条一批网络往返和事务开销占比高整体吞吐上不去太大比如10000条一批单次内存占用高事务执行时间长一旦失败回滚代价大而且可能撞上数据库的max_allowed_packet限制。我的经验值是500到2000之间具体要看单行数据的宽度。如果一行有几十个字段、每个字段都是长文本那批次要往小了取500左右如果一行就十来个短字段2000甚至3000都可以。可以这样估算假设单行数据序列化后约2KB批次2000条就是4MB加上JDBC驱动和数据库端的缓冲峰值内存大概在10MB以内完全可控。如果单行20KB批次2000就是40MB这就偏大了应该降到500。另外批次大小和事务提交频率是绑定的。我通常让一个批次对应一个事务即每批提交一次。这样失败时最多丢一批重试成本低。如果业务要求强一致那就整文件一个事务但海量数据下这种长事务会带来锁等待和回滚段膨胀问题不推荐。3.2 监听器里的攒批与触发逻辑监听器的结构大致是这样一个ListT作为缓冲区一个int记录当前批次已攒数量一个int记录总处理数。每次invoke把转换后的对象加入缓冲区计数加一。当计数达到阈值调用入库方法然后clear缓冲区计数归零。doAfterAllAnalysed方法里处理最后一批不足阈值的数据确保不丢尾巴。这里有个容易忽略的点invoke方法可能被调用多次但EasyExcel不保证每次传进来的对象是新的。如果你在监听器里直接持有对象引用做后续处理要注意对象可能被复用。稳妥做法是在invoke里做深拷贝或者立即把需要的数据取出来放进自己的结构里。不过对于“读出来就入库”的场景直接在invoke里把对象加入缓冲区是安全的因为入库是同步消费不会跨批次持有。3.3 数据校验与清洗的插入点海量数据里难免有脏数据空行、格式不对的日期、超长的字符串、非法字符。如果等到入库时才发现事务已经开了回滚代价大。更好的做法是在invoke里做轻量校验把不合规的行记录下来跳过入库最后统一输出错误报告。校验要轻不能做复杂计算或远程调用否则会拖慢读取速度。一般就是判空、长度检查、正则匹配。对于日期和数字EasyExcel的转换器会抛异常可以在监听器的onException方法里捕获记录行号和错误原因然后继续读下一行。这样一份文件读完好数据入库了坏数据有清单运维可以针对性处理。3.4 数据库连接与事务管理批量入库时数据库连接最好用连接池不要每次入库都新建连接。连接池大小要和批次处理速度匹配一般5到10个连接足够。如果用了异步入库连接数要相应增加但不要超过数据库的max_connections限制。事务方面如果每批一个事务代码里要显式控制autoCommit为false执行完一批后commit异常时rollback。用Spring的Transactional要注意传播行为和代理失效问题在监听器这种非Spring管理的对象里直接调用带事务注解的方法可能不生效。稳妥做法是手动管理Connection或者把入库逻辑抽到一个Spring Bean里通过ApplicationContext获取代理对象来调用。4. 完整实操流程与关键环节实现4.1 环境准备与依赖引入Maven项目里引入EasyExcel和数据库驱动。EasyExcel的版本建议用3.x对xlsx和xls的支持都比较完善。数据库驱动用MySQL的话mysql-connector-java选8.x版本连接池用HikariCP。dependency groupIdcom.alibaba/groupId artifactIdeasyexcel/artifactId version3.3.2/version /dependency dependency groupIdcom.mysql/groupId artifactIdmysql-connector-j/artifactId version8.0.33/version /dependency dependency groupIdcom.zaxxer/groupId artifactIdHikariCP/artifactId version5.0.1/version /dependency4.2 定义Excel映射实体假设Excel里是用户订单数据列有订单号、用户ID、金额、下单时间、备注。用注解映射public class OrderExcelDTO { ExcelProperty(订单号) private String orderNo; ExcelProperty(用户ID) private Long userId; ExcelProperty(金额) private BigDecimal amount; ExcelProperty(下单时间) private Date orderTime; ExcelProperty(备注) private String remark; // getter/setter 省略 }注意ExcelProperty的value要和Excel表头文字完全一致包括空格。如果表头有换行或特殊字符建议用index方式指定列号更稳妥。4.3 监听器的完整实现监听器继承AnalysisEventListenerOrderExcelDTO核心字段包括缓冲区、批次大小、入库服务引用、错误列表。public class OrderExcelListener extends AnalysisEventListenerOrderExcelDTO { private static final int BATCH_SIZE 1000; private ListOrderExcelDTO buffer new ArrayList(BATCH_SIZE); private int totalCount 0; private ListString errorRows new ArrayList(); private OrderService orderService; public OrderExcelListener(OrderService orderService) { this.orderService orderService; } Override public void invoke(OrderExcelDTO data, AnalysisContext context) { // 轻量校验 if (data.getOrderNo() null || data.getOrderNo().trim().isEmpty()) { errorRows.add(第 context.readRowHolder().getRowIndex() 行订单号为空); return; } buffer.add(data); totalCount; if (buffer.size() BATCH_SIZE) { flush(); } } private void flush() { if (buffer.isEmpty()) return; try { orderService.batchInsert(buffer); } catch (Exception e) { // 记录失败批次实际项目可落库或写文件 errorRows.add(批次入库失败 e.getMessage()); } finally { buffer.clear(); } } Override public void doAfterAllAnalysed(AnalysisContext context) { flush(); // 处理最后一批 } Override public void onException(Exception exception, AnalysisContext context) { errorRows.add(第 context.readRowHolder().getRowIndex() 行解析异常 exception.getMessage()); } public int getTotalCount() { return totalCount; } public ListString getErrorRows() { return errorRows; } }4.4 批量入库服务的实现入库服务用JdbcTemplate或者原生JDBC。这里用JdbcTemplate演示配合batchUpdate。Service public class OrderService { Autowired private JdbcTemplate jdbcTemplate; Transactional public void batchInsert(ListOrderExcelDTO list) { String sql INSERT INTO t_order (order_no, user_id, amount, order_time, remark) VALUES (?, ?, ?, ?, ?); jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() { Override public void setValues(PreparedStatement ps, int i) throws SQLException { OrderExcelDTO dto list.get(i); ps.setString(1, dto.getOrderNo()); ps.setLong(2, dto.getUserId()); ps.setBigDecimal(3, dto.getAmount()); ps.setTimestamp(4, new Timestamp(dto.getOrderTime().getTime())); ps.setString(5, dto.getRemark()); } Override public int getBatchSize() { return list.size(); } }); } }MySQL连接URL加上rewriteBatchedStatementstrue这样驱动会把批量insert改写成多values形式性能提升非常明显。实测下来1000条一批开启这个参数后入库耗时能降到原来的三分之一左右。4.5 读取入口与多文件处理读取单个文件的代码很简洁String fileName D:/data/orders_202401.xlsx; OrderExcelListener listener new OrderExcelListener(orderService); EasyExcel.read(fileName, OrderExcelDTO.class, listener).sheet().doRead(); System.out.println(总处理 listener.getTotalCount()); System.out.println(错误 listener.getErrorRows());如果是一批文件外面套一层循环每个文件新建一个监听器实例避免状态串扰。文件多的话可以用线程池并行处理但要注意数据库连接池大小和磁盘IO瓶颈。一般建议并行度控制在CPU核数以内磁盘和数据库往往先成为瓶颈。4.6 参数计算与性能预估假设一个文件100万行单行数据平均1KB批次1000条。内存方面缓冲区峰值约1MB加上EasyExcel内部缓冲和JDBC批处理缓冲总内存增量在20MB以内非常安全。时间方面读取速度受磁盘和解析复杂度影响SSD上大约每秒5万到10万行入库速度受数据库影响开启批量重写后每秒大约1万到3万条。整体算下来100万行大约需要2到5分钟具体看硬件和数据库负载。如果发现读取快、入库慢瓶颈在数据库可以考虑加大批次、开启rewriteBatchedStatements、或者用多线程分片入库。如果读取慢检查是不是文件在机械硬盘上或者单元格样式太复杂导致解析变慢。5. 常见问题与排查技巧实录5.1 内存溢出依然发生有人用了EasyExcel还是OOM多半是监听器里把数据攒着没释放。比如把每行数据都加到一个全局List里最后统一处理那和POI用户模式没区别。记住缓冲区必须在每批处理后清空且缓冲区大小要有上限。另外如果Excel里有大量图片或嵌入对象EasyExcel默认也会处理可以通过ignoreEmptyRow和自定义转换器跳过不需要的内容。5.2 日期和数字格式解析错误Excel里的日期本质是数字不同版本的Excel基准日期可能不同。EasyExcel的默认转换器能处理大部分情况但如果单元格格式是文本或者日期格式不标准就会解析失败。解决办法是自定义转换器在convertToJavaData里做兼容处理。数字方面金额字段如果Excel里是文本格式直接映射到BigDecimal可能报错可以在转换器里先转成字符串再解析。5.3 批量入库时主键冲突或唯一索引报错海量数据导入时重复数据很常见。如果直接insert遇到唯一键冲突整批失败。两种处理方式一是入库前先查重但海量数据下查重本身很慢二是用INSERT IGNORE或ON DUPLICATE KEY UPDATE让数据库处理冲突。前者忽略重复后者更新已有记录。根据业务需求选。注意INSERT IGNORE会忽略所有错误包括数据截断要谨慎使用。5.4 事务回滚导致已入库数据丢失如果整文件一个事务中途失败全部回滚之前读的几十分钟白费。所以强烈建议分批提交。每批一个事务失败只影响当前批。对于失败批次可以记录到重试队列后续单独处理。这样整体鲁棒性高很多。5.5 多线程读取时的线程安全问题EasyExcel的read方法本身是线程安全的但监听器实例不是。如果多个线程共用一个监听器缓冲区会混乱。正确做法是每个线程独立创建监听器和ExcelReader实例。另外数据库连接从连接池获取每个线程独立获取和释放不要跨线程传递Connection。5.6 常见问题速查表问题现象可能原因排查方向解决手段OOM缓冲区未清空或全局持有数据检查监听器字段每批flush后clear限制缓冲区大小解析慢文件在慢速磁盘或样式复杂看IO和CPU占用换SSD跳过样式解析入库慢未开启批量重写或批次太小看数据库执行日志加rewriteBatchedStatements调大批次日期错误单元格格式不标准打印原始单元格值自定义转换器兼容处理唯一键冲突数据重复查重复数据来源用INSERT IGNORE或先清洗事务回滚丢数据整文件单事务检查事务边界改分批提交多线程数据错乱监听器共享检查线程模型每线程独立监听器5.7 几个实测有效的避坑技巧第一读取前先用EasyExcel.read(file).headRowNumber(1)确认表头行号有些文件前面有标题行或空行不设置会导致映射错位。第二对于超宽表列数超过100建议用index方式映射而不是表头名称因为表头匹配是字符串比较列多了容易出错且慢。第三入库时如果字段有大量空值PreparedStatement的setNull要指定类型否则某些数据库会报错。第四监控方面可以在监听器里每处理10万行打一条日志方便观察进度和估算剩余时间。6. 进阶优化与扩展思路6.1 异步入库提升吞吐同步入库时读取线程要等数据库返回才能继续读下一批。如果数据库响应慢读取就被拖累。可以引入一个阻塞队列监听器只负责往队列里放批次数据后台一个或多个消费者线程从队列取数据入库。这样读取和入库解耦整体吞吐量能提升不少。队列要有界防止生产快消费慢导致内存暴涨。消费者数量根据数据库承载能力调整一般2到4个。6.2 分片读取与并行处理单个超大文件如果读取慢可以考虑按Sheet分片或者按行号范围分片需要文件支持随机读取xlsx本质是zip不太容易。更实际的做法是多文件并行每个文件一个线程。如果必须单文件并行可以先用工具把大文件拆成多个小文件再并行读取。拆分本身有开销但摊到后续并行处理上还是划算的。6.3 数据质量监控与报告海量数据导入最怕的是“静默失败”——数据没入库但没人知道。建议在监听器里统计成功数、失败数、跳过数导入结束后生成一份报告包含错误行号和原因。这份报告可以存到数据库表里也可以输出成文件。对于失败的数据提供重试入口避免人工翻日志。6.4 不同数据库的适配要点MySQL的批量优化主要靠rewriteBatchedStatements。PostgreSQL可以用reWriteBatchedInsertstrue效果类似。Oracle的JDBC驱动对批量提交支持很好但要注意executeBatch返回的是更新计数数组要检查是否有失败。SQL Server可以用bulkCopy但那是另一套API了。通用原则是优先用JDBC批量再根据数据库特性加参数。我在实际项目里遇到过一次MySQL的max_allowed_packet限制批次设了5000单行数据又大拼出来的SQL超过了默认的4MB直接报错。后来把批次降到1000同时调大了max_allowed_packet到64MB问题解决。所以批次大小不是拍脑袋定的要结合单行大小和数据库配置算一下。另外一个体会是导入前最好把表的索引先禁用导入完再重建尤其是唯一索引多的时候能省不少时间。当然这要求导入期间没有其他写入适合离线批量场景。
阅读完成 · 觉得有帮助？
