百万行数据导入：流式读取、批量写入与失败处理
运营上传一个 100 万行的 Excel，服务先是 OOM，改成分批后又慢得超时，好不容易跑完，又发现其中几百行格式错误的数据让整批都没写进去。导入功能的三个难点是内存、速度和错误处理，而且三者的解法互相牵制。
本文先用 JDBC 实测几种写入方式的速度差异，再看一个容易被忽略的细节：开启 rewriteBatchedStatements 后，批处理失败时的行为会完全改变，这直接决定了错误处理该怎么设计。
一、整体流程
- 读取线程流式解析文件，每 1000 行组成一批。
- 校验与转换：格式、必填、取值范围、字典映射，不合法的行直接记入错误报告，不进入队列。
- 有界队列连接读取和写入：写入跟不上时队列变满，读取线程被阻塞，内存不会无限增长。
- 多个写入线程从队列取批次，用批处理写入数据库，每批一个事务。
- 批次失败时降级为逐行写入，定位出错的行。
- 导入结束生成结果：成功行数、失败行数、每个失败行的行号与原因。
二、读取：别把整个文件读进内存
Apache POI 的 XSSFWorkbook 会把整个工作簿解析成对象树。实测一个 100 万行、10 列、约 63 MB 的 xlsx：XSSFWorkbook 在 6 GB 堆下抛出 OutOfMemoryError，给到 8 GB 才读完；同一个文件用下面的 Fesod 按批读取，256 MB 堆就够了，堆使用峰值约 159 MB。流式读取只在内存中保留当前这一批。
常用的做法：
- Excel：EasyExcel 是国内使用最广的流式读取库，已停止维护；原作者团队后续的 FastExcel 在 2025 年 9 月进入 Apache 孵化器，改名为 Apache Fesod。新项目可以直接使用 Fesod，入口类是
FesodSheet。 - CSV：能让用户上传 CSV 就优先 CSV，用
BufferedReader逐行读取即可，解析成本远低于 Excel。
使用 Fesod 按批读取的写法如下（代码片段，依赖 org.apache.fesod:fesod-sheet:2.0.2-incubating，同样的调用方式在配套实验中编译运行过）：
BlockingQueue<List<OrderRow>> queue = new ArrayBlockingQueue<>(16); // 最多积压 16 批
FesodSheet.read(file, OrderRow.class, new PageReadListener<OrderRow>(batch -> {
try {
queue.put(validate(batch)); // 队列满时阻塞读取线程
} catch (InterruptedException e) {
Thread.currentThread().interrupt();
throw new IllegalStateException(e);
}
}, 1000)).sheet().doRead();常见建议中的「多线程并发读取多个 Sheet」只适用于数据本来就分布在多个 Sheet 的文件。单个 Sheet 的解析本身是顺序的，瓶颈通常也不在解析，而在写入。
三、写入：几种方式实测
3.1 测试方法
表结构带一个自增主键和一个业务唯一索引，更接近真实业务表：
CREATE TABLE import_order (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
order_no BIGINT NOT NULL,
user_id BIGINT NOT NULL,
sku VARCHAR(32) NOT NULL,
qty INT NOT NULL,
remark VARCHAR(64) NOT NULL,
UNIQUE KEY uk_order_no (order_no)
);每种方式重新建表后写入 20 万行（逐行插入太慢，只写 2 万行），采样 3 次取中位数。环境是 JDK 21、MySQL Connector/J 8.0.27、默认配置的 MySQL 8.4.11（开启 binlog，innodb_flush_log_at_trx_commit 与 sync_binlog 都为 1）。
3.2 结果
| 方式 | 耗时 | 行/秒 | 相对逐行插入 |
|---|---|---|---|
| 逐行插入，自动提交（2 万行） | 10.43s | 1,918 | 1× |
| 批处理 1000 行一批，未开启重写 | 9.78s | 20,454 | 11× |
| 批处理 1000 行一批，开启重写 | 1.54s | 129,786 | 68× |
| 批处理 5000 行一批，开启重写 | 1.31s | 152,323 | 79× |
MyBatis <foreach> 多行插入，1000 行一批，未开启重写 | 2.52s | 79,365 | 41× |
| 4 个线程，每线程 1000 行一批，开启重写 | 0.98s | 203,252 | 106× |
LOAD DATA LOCAL INFILE | 0.62s | 322,061 | 168× |
几个观察：
- 逐行插入慢在每行一次网络往返加一次提交刷盘。 按这个速度，100 万行需要 8 分半。
- 只用批处理、不开启重写，只省掉了提交次数。 驱动仍然逐条发送
INSERT，网络往返次数没变。 - 开启
rewriteBatchedStatements=true后，驱动把一批INSERT改写成一条多行插入INSERT ... VALUES (...), (...), ...，一次往返写入一整批。这是收益最大的一步。 - 批大小从 1000 加到 5000，只快了 17%，但一批失败时要重试的数据变成 5 倍，1000 左右是比较均衡的选择。改写后的单条语句不能超过
max_allowed_packet（MySQL 8.4 默认 64MB），行很宽时要相应减小批大小。 - 线程也不是越多越好：4 个线程比单线程快约一半，但数据库的写入能力有上限，导入线程还会和线上业务争抢连接、锁和 I/O，复制延迟也会随之上升。
- 多线程并行在这台机器上快了约 50%（容器限制 2 CPU）。线程数不是越多越好：数据库的写入能力、唯一索引的检查、自增锁都会成为瓶颈，而且导入会挤占线上业务的数据库资源，要限制并发并安排在低峰期。
LOAD DATA 比 4 线程的批处理还快约 60%，但需要服务端开启 local_infile、客户端开启 allowLoadLocalInfile，出于安全考虑很多生产环境会禁用。在应用内导入时，开启重写的批处理通常已经足够。
3.3 写入代码
// 连接参数：jdbc:mysql://host:3306/db?rewriteBatchedStatements=true
void writeBatch(DataSource ds, List<OrderRow> batch) throws SQLException {
String sql = "INSERT INTO import_order (order_no, user_id, sku, qty, remark) VALUES (?, ?, ?, ?, ?)";
try (Connection c = ds.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
c.setAutoCommit(false);
try {
for (OrderRow r : batch) {
ps.setLong(1, r.orderNo());
ps.setLong(2, r.userId());
ps.setString(3, r.sku());
ps.setInt(4, r.qty());
ps.setString(5, r.remark());
ps.addBatch();
}
ps.executeBatch();
c.commit();
} catch (SQLException e) {
c.rollback();
throw e;
}
}
}使用 MyBatis 时，常见的做法是用 <foreach> 拼接多行 VALUES。实测每批 1000 行、连接不开启重写，每秒 79,365 行：是未重写批处理的 4 倍，但只有开启重写的批处理的六成。用 <foreach> 还要自己控制每批的行数，避免 SQL 超过 max_allowed_packet；能改连接参数时，JDBC 批处理加 rewriteBatchedStatements=true 更快；MyBatis 的 ExecutorType.BATCH 底层走的也是 JDBC 批处理。
四、失败处理：批次失败时发生了什么
4.1 实测：一批里有一行重复
一批 6 行，第 4 行的 order_no 与第 2 行重复，捕获异常后故意提交，观察哪些行被写入：
| 设置 | getUpdateCounts() | 提交后表中的数据 |
|---|---|---|
| 未开启重写 | [1, 1, 1, -3, 1, 1] | 1, 2, 3, 5, 6 |
| 开启重写 | [-3, -3, -3, -3, -3, -3] | 空 |
-3 是 Statement.EXECUTE_FAILED。
- 未开启重写时，驱动逐条执行，出错的那条失败，其余照常执行（Connector/J 的
continueBatchOnError默认为true）。如果捕获异常后提交，就会得到一批「部分成功」的数据。 - 开启重写后，整批是一条语句，一行出错整条语句失败，这一批一行都没有写入。
两种行为都不能直接用来「跳过坏行继续导入」：前者的结果依赖驱动的执行方式，后者整批丢失。
4.2 降级逐行写入
可靠的做法是：批次失败就回滚这一批，再逐行重试。
void writeWithFallback(DataSource ds, List<OrderRow> batch, ImportReport report) {
try {
writeBatch(ds, batch);
report.success(batch.size());
} catch (SQLException batchError) {
for (OrderRow row : batch) {
try {
writeBatch(ds, List.of(row));
report.success(1);
} catch (SQLException rowError) {
report.fail(row.lineNo(), rowError.getMessage());
}
}
}
}降级只发生在出错的批次上，正常批次仍然走批处理。坏数据很多时降级会很慢，所以要把能提前发现的问题放到校验阶段：格式、必填、长度、枚举值、文件内部的重复键。
4.3 让导入可以重跑
导入可能在任何位置中断：应用发布、超时、数据库切换。设计成可重跑，比实现精确的断点续传简单得多：
- 用业务唯一键（如订单号）建唯一索引；
- 按需求选择冲突时的行为：
INSERT IGNORE跳过已存在的行，或INSERT ... ON DUPLICATE KEY UPDATE覆盖为文件中的值； - 记录导入任务的状态和已处理的批次号，重跑时可以跳过已确认完成的批次，作为优化而不是正确性的保证。
注意 INSERT IGNORE 会把一些其他错误也降级为警告，例如数据被截断。对数据质量要求严格时，用 ON DUPLICATE KEY UPDATE，并在校验阶段拦住格式问题。
4.4 关于「整个导入放在一个事务里」
常见的「用 @Transactional 保证失败时整体回滚」，对百万行导入不适用：一个事务写 100 万行会产生巨大的 Undo 和 Binlog，锁持有时间长，失败回滚同样耗时，replica 也会出现明显的复制延迟，原因与 千万级大表怎么清理数据 中的大删除相同。
如果业务确实要求「全部成功才生效」，可以先导入到临时表或带批次号的暂存表，校验通过后再用一条语句切换状态，或者分批从暂存表迁移到正式表。
4.5 先落暂存表：保留原始值，给每个坏行一个原因
5.3 提到 INSERT IGNORE 会把错误降级为警告。实测（MySQL 8.4.11，严格模式开启）一份 7 行的文件，其中 5 行各有一种问题，用 INSERT IGNORE 直接写入带类型的表：
| 文件里的值 | 表里存成了 |
|---|---|
金额 12.5abc | 12.50 |
日期 2026-02-30 | 0000-00-00 |
状态 UNKNOWN（列是 ENUM('PAID','REFUNDED')） | 空串 |
订单号 ' A005 '，金额 -5 | 原样写入，带空格、负数 |
| 与第 1 行重复的订单号 | 跳过 |
7 行写入了 6 行，没有一行报错，其中 4 行的内容是错的，而且事后无法从表里知道原来的值是什么。
另一种做法是分两步。第一步把文件原样写进暂存表：所有列都是字符串，加上批次号和行号，不做任何转换，所以不会失败。第二步用规则给每一行分类：
INSERT INTO rejects (batch_id, line_no, rule_version, reason)
SELECT batch_id, line_no, 'r1', reason FROM (
SELECT batch_id, line_no, CASE
WHEN order_no NOT REGEXP '^[A-Z][0-9]{3}$' THEN '订单号格式不符'
WHEN amount NOT REGEXP '^-?[0-9]+(\\.[0-9]{1,2})?$' THEN '金额不是数字'
WHEN CAST(amount AS DECIMAL(10,2)) < 0 THEN '金额为负'
…
WHEN ROW_NUMBER() OVER (PARTITION BY batch_id, order_no ORDER BY line_no) > 1 THEN '文件内订单号重复'
END AS reason
FROM raw_orders WHERE batch_id = ?) c
WHERE reason IS NOT NULL;没有进拒绝表的行才写入目标表。同一份文件的结果：
| 行数 | 内容 | |
|---|---|---|
| 写入目标表 | 2 | 两个合格的订单 |
| 拒绝表 | 5 | 第 2 行金额不是数字；第 3 行日期不存在；第 4 行状态不在允许范围；第 5 行文件内订单号重复；第 6 行订单号格式不符 |
| 对账 | 原始 7 = 写入 2 + 拒绝 5 |
这样得到三样直接写入得不到的东西：
- 原始值还在。 暂存表里第 2 行的金额仍是
12.5abc。规则写错了可以改完重新分类，不需要重新要一份文件。 - 每个坏行有原因和规则版本。 可以直接导出给数据的提供方；规则调整后，能区分「按旧规则拒绝的」和「按新规则拒绝的」。
- 可以对账。 原始行数等于写入加拒绝，不相等就说明有行被悄悄丢掉或重复处理了。重跑同一批，实测目标表和拒绝表的行数都不变。
规则写在 SQL 里还是应用代码里都可以，要点是「原样落地」和「分类」分成两步。暂存表按批次保留一段时间后清理。
4.6 全量快照里的删除，与迟到的旧数据
ON DUPLICATE KEY UPDATE 只处理了「新增」和「修改」，还有两种情况它不管。
来源删除了一行。 来源每次给的是全量快照，第 2 批里少了一个订单。只做 upsert，目标表里那个订单还在，而且永远不会再被更新，成了一条陈旧数据。做法是让每一行记录「最后一次出现在哪个批次」，导入完成后把没有出现在本批的行标记为已删除：
UPDATE orders_target SET deleted = 1 WHERE last_batch < ?; -- 本批的批次号实测标记之后，未删除的是本批出现的两个订单，缺失的那个被标记。这只适用于全量快照；增量文件里没有某一行不代表它被删了，需要来源显式给出删除记录。标记而不直接删除，是为了在来源文件本身有问题（比如只传了一半）时还能恢复：发现本批行数比上一批少了很多，应该先停下来确认，而不是标记掉一大片。
旧数据后到。 重传、乱序、补数都会让一份较旧的文件在较新的之后到达。目标表里某订单已经是来源的第 2 版（金额 120），这时来了它的第 1 版（金额 100）：
| 写法 | 结果 |
|---|---|
| 无条件覆盖 | 金额变回 100，版本变回 1 |
| 只在来源版本更新时才覆盖 | 保持金额 120、版本 2 |
ON DUPLICATE KEY UPDATE
amount = IF(r.source_ver > orders_target.source_ver, r.amount, orders_target.amount),
source_ver = GREATEST(orders_target.source_ver, r.source_ver)来源版本可以是来源系统的更新时间或自增版本号，要由来源提供，不能用导入时间代替。source_ver 的赋值要放在其他列之后：MySQL 的单表赋值一般按从左到右的顺序计算，先改了它，后面的比较用到的就是新值。同样的「旧写入不能覆盖新写入」在应用内存状态的保存里也会遇到，见自动保存显示成功了，最后一次修改却没存上。
五、上线前的检查清单
- [ ] 读取使用流式 API，内存占用与文件大小无关
- [ ] 读写之间有有界队列，写入慢时读取会被阻塞
- [ ] 连接参数开启
rewriteBatchedStatements=true，批大小在 500—2000 之间 - [ ] 写入线程数有上限，导入任务限制在业务低峰期
- [ ] 批次失败时降级逐行写入，错误报告带行号和原因
- [ ] 业务唯一键有唯一索引，导入可以安全重跑
- [ ] 文件先原样落暂存表，坏行进拒绝表并带原因；原始行数 = 写入 + 拒绝
- [ ] 全量快照处理了来源删除的行；覆盖已有数据时比较来源版本
- [ ] 导入是异步任务，前端轮询进度，不占用 HTTP 请求线程等待
- [ ] 文件大小和行数有上限，防止恶意或误操作上传超大文件
配套实验
- codesphere-labs/storage/mysql-bulk-import：七种写入方式（含 MyBatis
<foreach>）各采样 3 次，以及一批 6 行中有 1 行主键冲突时两种驱动设置下的失败语义（验证记录） - codesphere-labs/storage/import-data-contract：MySQL 8.4.11 上
INSERT IGNORE对五种坏数据的静默转换、暂存表分类与对账、重跑、全量快照里的缺失行、迟到的旧版本（验证记录） - codesphere-labs/storage/excel-import-memory：100 万行 xlsx 用 POI
XSSFWorkbook与 Apache Fesod 读取的堆内存（验证记录）
参考资料