大数据量 Excel 导入的性能与内存优化实战

大数据量 Excel 导入的性能与内存优化实战

一 核心原则

• 使用流式/事件驱动读取（如EasyExcel、POI SAX），避免XSSFWorkbook一次性将整表加载进内存，内存占用可做到与文件大小基本无关。
• 采用分批处理 + 批量写入，每批积累到一定条数（如1000–5000）再提交入库，避免逐条插入与超大事务。
• 引入异步任务 + 线程池，上传接口快速返回taskId，导入在后台执行，避免阻塞 HTTP 线程。
• 对多Sheet文件可按Sheet 并发读取，配合生产者-消费者模型提升吞吐。
• 做好数据校验与错误隔离（跳过/覆盖/报错策略）、重试机制与导入回执，保证稳定性与可观测性。

二 读取与解析层优化

• 优先选型：使用EasyExcel ReadListener或POI SAX事件模型，逐行解析，内存占用稳定；避免XSSFWorkbook/WorkbookFactory全量加载。
• 批处理阈值：在 Listener 中累积到batchSize（建议1000–3000，视单条数据大小与内存而定）就触发一次业务处理并清空缓存。
• 多 Sheet 并发：一个文件含多Sheet时，可为每个Sheet提交一个任务并行解析，线程池大小与Sheet 数或 CPU 核数匹配。
• 轻量校验：在 Listener 内做必填/格式等轻校验；复杂规则与关联查询放到批处理或落库前统一处理。

三 数据库写入层优化

• 批量插入：使用JDBC Batch或MyBatis ExecutorType.BATCH，每批提交（如1000–5000条），显著减少网络往返与日志开销。
• 连接与并发：合理设置连接池大小与并发线程数，避免连接耗尽与上下文切换过多。
• 事务策略：避免“一导入一事务”的大事务，改为按批提交；对失败批次可重试 2–3 次后记录错误明细。
• 唯一性冲突：在数据库设置唯一约束，冲突时按业务选择覆盖/跳过/报错策略。
• 极致场景：将清洗后的数据先落CSV/临时表，再用LOAD DATA INFILE或数据库原生批量导入工具，速度常优于逐条 ORM 插入。

四 架构与工程化优化

• 异步化：上传接口立即返回taskId，导入任务进入线程池/消息队列执行；前端轮询或WebSocket查询进度与结果。
• 背压与限流：对并发导入数、单文件大小、单批次大小做限流与熔断，保护服务稳定性。
• 错误回执与重试：导入结束后生成成功/失败明细下载；失败批次支持定位与重放。
• 监控与告警：监控JVM GC/内存、线程池队列、数据库连接、导入耗时，异常及时告警。

五 参数与配置建议

• 批次大小：从2000起步，结合单条数据体积与内存做压测，通常控制在1000–5000区间。
• 并发度：多Sheet可按Sheet 数并行；无Sheet并行时，控制读取线程:写入线程 ≈ 1:2~1:4，避免写库成为瓶颈。
• JVM 与容器：适当增大堆内存（如-Xmx4G/-Xmx8G），但根本仍依赖流式处理而非堆扩容。
• 数据库：开启批处理优化（如 MySQL 的rewriteBatchedStatements=true），合理设置fetchSize、事务隔离级别。
• 超时与池化：调大HTTP 超时、连接池最大连接/空闲、线程池队列，防止长导入被中断。

六 落地代码示例

• 批量模式监听器（EasyExcel）

publicclassBatchExcelListener<T>extendsAnalysisEventListener<T>{privatefinalintbatchSize;privatefinalList<T>batch=newArrayList<>(batchSize);privatefinalConsumer<List<T>>processor;privatefinalAtomicIntegertotal=newAtomicInteger();privatefinalAtomicIntegerfailed=newAtomicInteger();publicBatchExcelListener(intbatchSize,Consumer<List<T>>processor){this.batchSize=Math.max(500,batchSize);this.processor=processor;}@Overridepublicvoidinvoke(Tdata,AnalysisContextctx){if(isValid(data))batch.add(data);elsefailed.incrementAndGet();if(batch.size()>=batchSize)processBatch();total.incrementAndGet();}@OverridepublicvoiddoAfterAllAnalysed(AnalysisContextctx){if(!batch.isEmpty())processBatch();}privatevoidprocessBatch(){try{processor.accept(newArrayList<>(batch));// 批处理（如批量入库）batch.clear();}catch(Exceptione){failed.addAndGet(batch.size());// 可加入重试：最多3次}}privatebooleanisValid(Td){returnd!=null;}// 简化示例}

• 服务与并发读取多个 Sheet

@ServicepublicclassExcelImportService{@AutowiredprivateYourDataServicedataService;privatefinalExecutorServiceexecutor=Executors.newFixedThreadPool(8);// 按CPU/IO调整publicvoidimportMultiSheet(InputStreamin){List<Future<?>>futures=newArrayList<>();for(inti=0;i<20;i++){// 假设20个SheetintsheetNo=i;Future<?>f=executor.submit(()->{EasyExcel.read(in,RowDto.class,newBatchExcelListener<>(2000,batch->dataService.batchInsert(batch))).sheet(sheetNo).doRead();});futures.add(f);}// 等待全部完成for(Future<?>f:futures){try{f.get();}catch(Exceptionignore){}}executor.shutdown();}}

• 异步任务编排（Spring Boot）

@RestControllerpublicclassImportController{@AutowiredprivateExcelImportServiceimportService;@AutowiredprivateTaskServicetaskService;@PostMapping("/import")publicCommonResultstart(@RequestParam("file")MultipartFilefile){StringtaskId=taskService.createTask();CompletableFuture.runAsync(()->{try(InputStreamin=file.getInputStream()){importService.importMultiSheet(in);taskService.complete(taskId,"SUCCESS");}catch(Exceptione){taskService.fail(taskId,e.getMessage());}},taskExecutor());returnCommonResult.ok(taskId);}@Bean("taskExecutor")publicExecutortaskExecutor(){ThreadPoolTaskExecutorex=newThreadPoolTaskExecutor();ex.setCorePoolSize(4);ex.setMaxPoolSize(8);ex.setQueueCapacity(50);ex.setThreadNamePrefix("import-");ex.initialize();returnex;}}

• 数据库批量插入（MyBatis 示例）

<insertid="batchInsert"parameterType="list">INSERT INTO your_table(col1, col2) VALUES<foreachcollection="list"item="e"separator=",">(#{e.col1}, #{e.col2})</foreach></insert>

：通过流式读取 + 分批批量写入 + 异步并发，可稳定支撑十万至百万级数据导入；在合理参数与数据库优化配合下，导入耗时与内存占用均可控。