大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！
批量操作是日常开发中提升性能的常用手段——把1000条INSERT合并成一条SQL，把1000次UPDATE合并成一批提交。听起来很简单，对不对？
但“批量”不等于“越快越好”。批量大小选错了、事务边界画歪了、错误处理没做好——批量操作就可能从“性能优化”变成“性能灾难”。
今天把批量DML的执行机制、性能曲线、一致性陷阱彻底拆开讲一遍。
一、批量操作为什么快？
先搞清楚批量操作的性能来源。
假设你要插入10000行数据。有两种方式：
- 逐条插入：执行10000次
INSERT，每次都需要网络往返、SQL解析、事务提交、日志刷盘 - 批量插入：执行一次
INSERT，带10000行数据
批量操作快的三个原因：
- 网络RT减少：10000次网络往返变成1次
- SQL解析减少：SQL语句只解析一次，执行计划复用
- 事务提交减少：一次提交刷一次盘，而不是10000次
听起来很完美，对吧？但实际没那么简单。
二、批量大小不是越大越好
这是批量操作中最常见的误区：批量越大越好，一次插完最爽。
实际上，批量大小和性能之间是一条倒U型曲线：
性能 ↑ │ ╭──────────╮ │ ╱ ╲ │ ╱ ╲ │ ╱ ╲ │╱ ╲ └────────────────────→ 批量大小 小 最佳 大
批量太小：网络RT多、事务提交多，性能差
批量逐步增大：网络RT减少，性能上升
批量达到最优区间：性能达到峰值
批量继续增大：单个事务过大，Undo日志膨胀、锁持有时间过长、内存压力增大，性能开始下降
为什么批量太大会变慢？
- Undo日志膨胀：一个事务包含10000行变更，Undo日志需要记录所有变更的旧值。如果事务执行过程中需要回滚，回滚时间可能是几个小时
- 锁持有时间过长：批量操作期间，涉及的行一直被锁住，其他事务被阻塞
- 内存压力：批量操作的中间结果需要缓存在内存中，批量太大可能撑爆内存
- 主从延迟：批量操作产生的Binlog量巨大，从库回放需要更长时间，可能导致主从延迟飙升
最佳批量大小的经验值：
场景 |
推荐批量大小 |
说明 |
简单INSERT（无索引依赖） |
500-1000行/批 |
MySQL官方建议，实测性价比最高 |
复杂INSERT（多索引、触发器） |
200-500行/批 |
索引维护开销大，批量要小一些 |
UPDATE/DELETE |
1000-5000行/批 |
根据WHERE条件的选择性调整 |
大字段（BLOB/TEXT） |
50-100行/批 |
数据量大，批量要小 |
重要提醒：这些是经验值，不是标准答案。最佳批量大小取决于硬件配置、表结构、索引数量、数据行大小。建议在测试环境用不同批量大小做压测，找到最优值。
三、事务边界：一个批量一个事务，还是多个批量一个事务？
这是批量操作设计中最容易被忽视的问题。
方案一：每批一个事务
for batch in split_data(data, batch_size=1000): conn.autocommit = False try: cursor.executemany(insert_sql, batch) conn.commit() except Exception as e: conn.rollback() log_error(batch, e)
特点：每批数据独立提交，一批失败不影响其他批次
方案二：所有批量一个事务
conn.autocommit = False try: for batch in split_data(data, batch_size=1000): cursor.executemany(insert_sql, batch) conn.commit() except Exception as e: conn.rollback()
特点：所有数据要么全部成功、要么全部回滚，原子性强
两种方案的选择：
场景 |
推荐方案 |
理由 |
数据导入（非核心业务） |
每批一个事务 |
部分失败可重试，不影响已成功的数据 |
核心交易（账务、库存） |
所有批量一个事务 |
要求原子性，不能部分成功 |
数据迁移（需要断点续传） |
每批一个事务 |
失败后可从断点继续 |
批量同步（外部系统） |
每批一个事务 |
避免长事务导致锁持有时间过长 |
一个容易被忽视的陷阱：
如果选择“每批一个事务”，但每批的批量大小是10000行，那每批仍然是一个大事务。正确的做法是：批量大小和事务边界要统一——如果每批1000行，那就每1000行提交一次；如果每10000行提交一次，那批量大小就应该设为10000行，而不是把10000行拆成10批但只提交一次。
四、批量操作的错误处理策略
批量操作中最怕的是：第500条数据出错了，前面的499条已经插入了，后面的还没插入。怎么处理？
策略一：遇到错误立即回滚（原子性优先）
整个批量操作作为一个事务，任何一条失败就全部回滚。
适用场景：账务、库存、订单等要求数据绝对一致的场景
策略二：跳过错误继续执行（可用性优先）
记录错误数据，继续处理后续数据，最后统一报告。
适用场景：数据清洗、日志导入、非关键数据同步
策略三：分批回滚（折中方案）
将数据分成多个批次，每个批次独立事务。某个批次失败时只回滚该批次。
适用场景：数据迁移、批量导入，需要平衡一致性和效率
错误处理的代码示例：
def batch_insert_with_retry(data, batch_size=1000, max_retries=3): failed_batches = [] for batch in split_data(data, batch_size): for attempt in range(max_retries): try: conn.autocommit = False cursor.executemany(insert_sql, batch) conn.commit() break # 成功，跳出重试循环 except Exception as e: conn.rollback() if attempt == max_retries - 1: failed_batches.append((batch, str(e))) # 重试失败，记录 else: time.sleep(2 ** attempt) # 指数退避 return failed_batches
五、批量操作的“隐形陷阱”
陷阱1：批量INSERT触发的索引维护风暴
批量INSERT在插入数据的同时，要维护所有二级索引。如果一张表有5个二级索引，插入10000行就要更新50000个索引条目。批量越大，索引维护的瞬时压力越大。
解法：批量操作前评估索引数量，如果索引过多且数据量巨大，可以考虑先删除非必要索引，导入完成后再重建。
陷阱2：批量UPDATE导致锁范围扩大
UPDATE ... WHERE id IN (1,2,3,...)看起来是批量更新，但如果IN列表中的数据分布在不同的数据页上，MySQL可能需要锁住多个数据页，锁范围可能远超预期。
解法：确保WHERE条件能高效走索引，避免全表扫描。如果IN列表过大（超过1000个值），考虑分批执行。
陷阱3：批量DELETE导致主从延迟
批量DELETE是大事务的经典场景。删除100万行数据，Binlog可能达到几百MB，从库回放时间可能是主库执行时间的数倍。
解法：分批删除，每批1000-5000行，每批之间sleep一小段时间，让从库有机会追上。
六、总结
批量操作是性能优化的利器，但不是“无脑批量越大越好”。总结几个关键原则：
- 批量大小要测试，不要拍脑袋：500-1000行是常见经验值，但最佳值取决于硬件和表结构
- 事务边界要清晰：每批一个事务还是所有批量一个事务，取决于业务对原子性的要求
- 错误处理要完善：重试机制、失败记录、断点续传
- 注意隐形陷阱：索引维护风暴、锁范围扩大、主从延迟
批量操作的设计本质是在性能、一致性和可控性之间做权衡。没有“最优”的批量大小，只有“最适合当前场景”的批量策略。
小耶在手，SQL 不愁
还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~
