百万Excel数据导入如何设计？

业务背景

业务方提供一份Excel，内部有20个左右的Sheet，每一份Sheet下是6万行的数据，大概是十几列，总计是120万行的数据，想要读取并存储到数据库中。

怎么设计？

设计这样一个大规模数据导入系统，主要需要考虑性能、可靠性、数据一致性、资源管理以及用户体验等多个方面。针对120万行、20个Sheet、每个Sheet 6万行数据的场景，下面是我会考虑的几个关键设计点和具体实现方案。

1. 工具选择

选择适合大数据量、内存友好的Excel处理工具来读取数据。EasyExcel是处理大数据量Excel的较优选择，因为它使用流式读取，不会一次性将数据全部加载到内存中，避免了内存溢出的问题。

2. 多线程并发处理

由于Excel的行数和数据量非常大，使用多线程来提高读取和处理效率是一个必要的选择。

• 线程划分：可以使用线程池来控制并发处理，每个线程负责一个Sheet的数据导入，有20个Sheet，可以开启6~10个线程并发处理不同的Sheet。
• 批量读取与批量插入：每个线程读取Sheet数据时，按批次读取，每次读取1000条数据，然后批量插入数据库，减少对数据库的写入压力和事务提交的频次。

3. 数据库操作设计

数据库操作对性能影响非常大，尤其是大数据量导入时，以下几个优化点可以考虑：

• 批量插入：使用 MyBatis，批量插入，每批次插入1000条或更多数据，这样可以减少SQL交互的次数，提升性能。
• 数据库索引管理：导入前，可以暂时禁用非必要的索引，避免导入过程中索引更新带来的性能开销。导入完成后再重新启用索引。
• 数据库事务控制：可以在每批数据插入时使用事务，确保每一批数据的原子性。即：每次插入1000条，如果失败，回滚整个批次。
• 线程池资源关闭：执行完之后，关闭资源。并在try…catch…中设置60min的超时时间，强制关闭；如果等待期被中断，那么强制关闭。

4. 异常处理与容错机制

导入过程中可能会遇到各种异常情况，比如数据格式错误、网络波动、数据库连接失败等，异常处理和容错机制非常重要。

• 日志记录：在每批数据处理时，应该记录日志，记录成功与失败的批次，特别是错误的原因，以便后续进行排查。
• 失败重试机制：可以设计一个失败重试机制，当某一批数据插入数据库失败时，自动重试一定次数。如果依然失败，可以将该批数据记录到一个失败队列中，后续进行人工处理或另行重试。
• 数据校验：在导入过程中可以增加数据校验步骤，提前检查每批数据是否符合预期格式。如果发现数据不符合要求，可以抛出异常，避免脏数据进入数据库。
• 权限校验：有权限的用户，才可以导入数据。

5. 性能优化考虑

由于数据量大，性能优化是关键部分，需要从几个方面着手：

• JVM调优：为避免内存不足导致OutOfMemoryError，合理调整JVM的堆内存大小 (-Xms, -Xmx)。根据系统负载情况，提供足够的内存空间来支撑导入任务。
• 线程池调优：根据服务器的CPU核数（例如4核、8核），调整线程池的大小，确保不会出现过多线程争用CPU资源，导致线程切换开销过高。
• 连接池配置：调整数据库连接池（如HikariCP）的大小，确保数据库在并发情况下能够处理多个并发连接，避免连接池枯竭。

7. 用户体验

对于用户发起导入操作的体验，需要有一定的反馈机制：

• 进度条：可以在前端提供导入进度的反馈，例如通过WebSocket或轮询API，实时告知用户当前的导入进度（例如读取了多少条数据、成功导入了多少条数据、导入失败的记录数等）。
• 导入结果报告：导入完成后，给出一个导入结果的报告，告知用户导入是否成功、多少条数据成功导入、哪些数据导入失败以及原因。

8. 事务与数据一致性保障：

为了确保数据一致性，导入过程中的事务控制尤为重要：每批数据事务隔离：确保每次批量插入是一个事务，避免部分数据成功插入，而部分失败导致的不一致性。

设计的关键点总结：

1. 工具选择：使用EasyExcel进行流式读取，SpringBoot、MyBatis进行数据库操作。
2. 多线程并发：使用线程池来并发处理多个Sheet，每个线程按1000条记录的批次读取和插入数据。
3. 批量插入与事务控制：每批次插入1000条数据，并使用事务确保批量操作的原子性。
4. 性能优化：JVM调优、数据库连接池调优以及线程池的合理配置，确保高并发场景下的性能表现。
5. 用户反馈与异常处理：提供进度反馈机制和导入结果报告，异常时记录日志并进行重试。