大数据量 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）

public class BatchExcelListener<T> extends AnalysisEventListener<T> {
    private final int batchSize;
    private final List<T> batch = new ArrayList<>(batchSize);
    private final Consumer<List<T>> processor;
    private final AtomicInteger total = new AtomicInteger();
    private final AtomicInteger failed = new AtomicInteger();

    public BatchExcelListener(int batchSize, Consumer<List<T>> processor) {
        this.batchSize = Math.max(500, batchSize);
        this.processor = processor;
    }

    @Override
    public void invoke(T data, AnalysisContext ctx) {
        if (isValid(data)) batch.add(data);
        else failed.incrementAndGet();
        if (batch.size() >= batchSize) processBatch();
        total.incrementAndGet();
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext ctx) {
        if (!batch.isEmpty()) processBatch();
    }

    private void processBatch() {
        try {
            processor.accept(new ArrayList<>(batch)); // 批处理（如批量入库）
            batch.clear();
        } catch (Exception e) {
            failed.addAndGet(batch.size());
            // 可加入重试：最多3次
        }
    }

    private boolean isValid(T d) { return d != null; } // 简化示例
}

• 服务与并发读取多个 Sheet

@Service
public class ExcelImportService {
    @Autowired private YourDataService dataService;
    private final ExecutorService executor = Executors.newFixedThreadPool(8); // 按CPU/IO调整

    public void importMultiSheet(InputStream in) {
        List<Future<?>> futures = new ArrayList<>();
        for (int i = 0; i < 20; i++) { // 假设20个Sheet
            int sheetNo = i;
            Future<?> f = executor.submit(() -> {
                EasyExcel.read(in, RowDto.class,
                        new BatchExcelListener<>(2000, batch -> dataService.batchInsert(batch)))
                        .sheet(sheetNo).doRead();
            });
            futures.add(f);
        }
        // 等待全部完成
        for (Future<?> f : futures) { try { f.get(); } catch (Exception ignore) {} }
        executor.shutdown();
    }
}

• 异步任务编排（Spring Boot）

@RestController
public class ImportController {
    @Autowired private ExcelImportService importService;
    @Autowired private TaskService taskService;

    @PostMapping("/import")
    public CommonResult start(@RequestParam("file") MultipartFile file) {
        String taskId = taskService.createTask();
        CompletableFuture.runAsync(() -> {
            try (InputStream in = file.getInputStream()) {
                importService.importMultiSheet(in);
                taskService.complete(taskId, "SUCCESS");
            } catch (Exception e) {
                taskService.fail(taskId, e.getMessage());
            }
        }, taskExecutor());
        return CommonResult.ok(taskId);
    }

    @Bean("taskExecutor")
    public Executor taskExecutor() {
        ThreadPoolTaskExecutor ex = new ThreadPoolTaskExecutor();
        ex.setCorePoolSize(4); ex.setMaxPoolSize(8);
        ex.setQueueCapacity(50); ex.setThreadNamePrefix("import-");
        ex.initialize();
        return ex;
    }
}

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

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

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