彩蛋：什么场景下批量插入会变成“批量插雷”？

Jan 4 · 60min

说到批量插入，我们总想着“批量操作，效率飞起”，但现实是，当你在写 saveBatch 或 insertBatchSomeColumn 时，数据库君常常悄悄在背后说：“你确定不会炸？”——于是，你满怀期待的批量插入，结果变成了“批量插雷”。

批量插入为什么会炸？是因为它碰到了数据库性能优化的天花板，还是踩了某些让人抓狂的坑？别急，我们一个一个扒光这些“潜伏的细节”，让你在写批量插入的时候少踩坑，多微笑。

1. 索引：加速器 or 拖油瓶？

【现象】

  • 你插入了 10 万条数据，理论上应该在几秒内搞定，但执行时却慢得像蜗牛爬。
  • 查看数据库 CPU，99%；磁盘 I/O，飙红；MySQL 告诉你：“我很忙！”

【原因】

  • 数据库在插入数据时，需要实时维护表上的索引。
  • 每插入一条数据，索引都要更新。如果索引复杂，插入性能就会大打折扣。
  • 特别是联合索引和唯一索引，需要在插入时检查唯一性，可能触发锁等待，进一步拖慢速度。

【解决方法】

  1. 先关索引，后插入，最后重建索引：
ALTER TABLE your_table_name DISABLE KEYS;
-- 批量插入数据
ALTER TABLE your_table_name ENABLE KEYS;
  • 优点：插入性能直接起飞。
  • 缺点：适用于离线导入，线上场景慎用，尤其是涉及并发写入。
  1. 避免频繁触发唯一索引：
  • 批量插入的数据如果存在唯一键冲突，会反复回滚重试。
  • 确保数据在插入前去重，减少冲突。

2. 表结构：字段太多了，数据库要“噎住”了

【现象】

  • 表结构复杂，字段多达 50 个甚至上百个。
  • 每次批量插入都像是给数据库喂了一块大蛋糕，它消化不良。

【原因】

  • 表结构复杂意味着每一条记录都要解析更多的字段，带来更高的解析成本。
  • 特别是包含大字段（如 TEXT 或 BLOB）时，每条插入的 I/O 开销都会大幅增加。

【解决方法】

  1. 减少每次插入的字段数量：
    • 不需要插入的字段可以剔除，减少字段对 SQL 的负担。
    • MyBatis-Plus 的 insertBatchSomeColumn 可以通过 Predicate 实现字段过滤：t -> !t.getFieldFill().equals(FieldFill.UPDATE)
  2. 分表存储：如果表字段过多，可以考虑垂直拆分，把大字段单独拆分到一张表中，插入主表时减少字段负担。
  3. 分批插入：减小单次批量插入的数据量，避免 SQL 语句过长。

3. 事务管理：一次性提交的灾难

【现象】

  • 插入几万条数据后报了错，所有操作回滚。
  • 你以为这是一次小插入操作，数据库却在背后暗暗发力：“我要帮你保证原子性！”
  • 最后，CPU 扛不住了，内存也顶满了，数据库崩了。

【原因】

  • 批量插入通常使用单事务操作，但如果插入数据量太大，事务就会占用大量的内存、锁资源，甚至引发数据库崩溃。
  • 一旦某条数据插入失败，整个事务都会回滚，导致性能浪费。

【解决方法】

  1. 分批事务提交：
    • 把数据分成小批次，每批次提交一次事务。
    sqlSession.flushStatements();
sqlSession.commit();
  2. 合理设置事务隔离级别：
  • 批量插入时，可以选择较低的隔离级别（如 READ COMMITTED），减少锁争用。
  • 注意：这需要结合业务场景评估数据一致性要求。
  1. 跳过错误数据：如果单条数据插入失败，不必回滚整个事务。通过日志记录失败数据，继续处理下一批。

4. 数据库配置：你以为的问题，其实是配置锅

【现象】

  • 你写了一个性能炸裂的批量插入逻辑，结果性能还是不理想，数据库甚至死锁。
  • 怀疑人生之后，发现问题根本不在代码，而是数据库配置！

【原因】

  • 数据库默认配置可能并不适合高性能的批量插入，比如：
    • MySQL 的 innodb_flush_log_at_trx_commit：默认值是 1，每次事务提交都会刷磁盘，保证数据安全，但牺牲了性能。批量插入时，这个配置会显著拖慢速度。
    • MySQL 的 sync_binlog：默认值是 1，每次事务提交都同步 binlog，同样会拖累批量插入性能。

【解决方法】

  • 调整 innodb_flush_log_at_trx_commit：临时设置为 2 或 0，减少刷盘频率;降低刷盘频率会牺牲数据安全性，适合离线任务。
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
  • 调整 sync_binlog,临时设置为 0，提高插入性能：同样适合离线场景。
SET GLOBAL sync_binlog = 0;

总结：批量插入的“雷点指南”

当你觉得批量插入是一件简单的事情时，其实你已经站在“雷区”的边缘了。索引、表结构、事务管理、数据库配置、并发冲突……这些看似不起眼的细节，都会成为你批量插入时的“定时炸弹”。

但只要你提前踩好这些点，批量插入就不再是“插雷”，而是你性能优化的“杀手锏”。江湖路远，批量插入的坑一个接一个，但只要理解这些原理，你就是批量插入的真大侠！


> cd ..
CC BY-NC-SA 4.0 2021-PRESENT © Jaguar Liu