13.7 批量写入、批量更新性能优化

在实际业务中，批量写入和批量更新的性能往往直接影响系统的吞吐量。一条一条地执行 INSERT 或 UPDATE，不仅效率低下，还会给数据库带来不必要的压力。掌握批量操作的优化技巧，是后端开发的基本功。

13.7.1 批量写入的核心优化思路

批量写入性能的关键在于减少与数据库的交互次数和降低事务开销。下面按优先级从高到低介绍优化手段。

1. 使用批量插入语法

最直接的方式是用一条 INSERT 语句插入多行数据：

-- 不推荐：逐条插入
INSERT INTO t_user (name, age) VALUES ('张三', 25);
INSERT INTO t_user (name, age) VALUES ('李四', 30);

-- 推荐：批量插入
INSERT INTO t_user (name, age) VALUES
('张三', 25),
('李四', 30),
('王五', 28);

这样做的好处是：一次网络往返、一次 SQL 解析、一次事务提交。对于上千条数据，性能差距可以达到数十倍。

需要注意，MySQL 默认的 max_allowed_packet 限制单次请求的大小（通常为 4MB 或 16MB），过长的 SQL 可能被拒绝。实践中一般按每批 500~2000 行进行拆分，避免单条 SQL 过长。

2. 手动控制事务

很多开发框架默认开启了自动提交（autocommit=1），这意味着每条 INSERT 都是一个独立事务，每条都要刷一次 Redo Log，开销极大。改为手动提交事务可以大幅提升速度：

// 不推荐（伪代码）：每次插入都自动提交
for (User user : list) {
    mapper.insert(user);
}

// 推荐：批量在一个事务中提交
Connection conn = dataSource.getConnection();
conn.setAutoCommit(false);
try {
    for (User user : list) {
        mapper.insert(user);
    }
    conn.commit();
} finally {
    conn.setAutoCommit(true);
}

将一批插入放在同一个事务里，Redo Log 的刷盘次数从每行一次降为每批一次，性能提升非常明显。通常 1000 条数据逐条插入需要数秒，放在一个事务中可能只需几十毫秒。

3. LOAD DATA INFILE：大批量导入的终极方案

如果要导入几十万甚至上百万行数据，INSERT 语句的解析开销仍然很高。MySQL 提供了 LOAD DATA INFILE 命令，它会直接读取格式化文本文件，以接近磁盘写入速度的极限将数据导入表中：

LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE t_user
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

它的速度是 INSERT 语句的 10~20 倍，因为它跳过了 SQL 解析、优化器、表达式计算等环节，直接将文本解析为存储引擎能识别的格式写入。

使用时需注意：

需要 FILE 权限，且 secure_file_priv 参数限制文件读取路径。
如果目标是复制表，可以在操作前临时关闭 autocommit、unique_checks 和 foreign_key_checks 进一步加速，但完成后务必恢复。
数据文件中的格式必须严格对应，通常先用小批量验证再全量导入。

4. 其他辅助优化

关闭不必要的约束检查：在批量导入前可以临时执行 SET unique_checks=0; 和 SET foreign_key_checks=0;，减少每行插入时的约束校验开销。注意：这必须在你能保证数据完整性的前提下使用，导入后立即恢复。
适当调大 Buffer Pool：批量写入会产生大量脏页，缓冲池大小直接影响刷脏效率。
调整 Redo Log 大小：大事务会写满 Redo Log 导致频繁刷脏，适当增大 innodb_redo_log_capacity（8.0）或 innodb_log_file_size（5.7）可以缓解。

13.7.2 批量更新的优化策略

批量更新的场景更复杂，因为更新通常依赖条件，很难像插入那样靠一条 UPDATE ... WHERE id IN (...) 直接解决。下面是几种常见情况及优化思路。

1. 用 CASE WHEN 合并更新

如果要对不同 ID 的记录设置不同的字段值，可以用一条 UPDATE 配合 CASE WHEN 完成：

-- 不推荐：多条 UPDATE
UPDATE t_user SET age = 25 WHERE id = 1;
UPDATE t_user SET age = 30 WHERE id = 2;

-- 推荐：一条合并
UPDATE t_user SET age = CASE id
    WHEN 1 THEN 25
    WHEN 2 THEN 30
END
WHERE id IN (1, 2);

这种方式把多次交互合并为一次，但要注意 CASE WHEN 的嵌套层次不能太深，否则 SQL 过长影响解析效率。通常每批不超过几百行。

2. 利用 INSERT ... ON DUPLICATE KEY UPDATE

如果更新的逻辑是“存在则更新，不存在则插入”（即 UPSERT），这条语法是最佳选择：

INSERT INTO t_user (id, name, age) VALUES
(1, '张三', 25),
(2, '李四', 30)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    age = VALUES(age);

优点是一次性完成判断与写入，减少查询和分支操作。需要注意：自增主键在这种操作下可能会连续消耗，即使实际执行的是更新，AUTO_INCREMENT 值也会递增（取决于 innodb_autoinc_lock_mode）。

3. 先用临时表再联表更新

当更新条件复杂（如需要关联其他表或经过计算），可以先将要更新的数据批量插入临时表，然后通过关联更新目标表：

CREATE TEMPORARY TABLE tmp_update (
    id INT PRIMARY KEY,
    new_age INT
);

INSERT INTO tmp_update VALUES (1, 25), (2, 30);

UPDATE t_user u
INNER JOIN tmp_update t ON u.id = t.id
SET u.age = t.new_age;

DROP TEMPORARY TABLE tmp_update;

这种方式可以将复杂的逻辑移到插入临时表之前处理，最终的 UPDATE 语句保持简洁高效。MySQL 8.0 支持 CTE，也可以用来代替临时表，但临时表在可重复读隔离级别下更稳定。

4. 批量更新的事务与锁优化

和批量插入一样，批量更新必须放在同一个事务中手动提交。除此之外，还要留意锁范围。如果一次更新的行数过多，可能导致间隙锁范围过大甚至锁表，阻塞其他事务。建议：

分批更新，每批不超过几千行，避免长时间持锁。
更新条件尽量走主键或唯一索引，确保行锁精确，避免因锁范围扩大导致死锁。
监控 innodb_lock_wait_timeout 和 innodb_deadlock_detect，必要时调整参数。

5. 使用 REPLACE INTO（慎用）

REPLACE INTO 也能实现“存在则替换”的效果，但它的逻辑是先 DELETE 后 INSERT，这会导致：

自增 ID 可能变化（如果主键不是自增则无影响）。
外键级联操作可能引发意外删除。
索引需要二次维护，性能略差。

除非你明确希望整行替换且不在意上述副作用，否则推荐使用 INSERT ... ON DUPLICATE KEY UPDATE。

13.7.3 实际项目中的经验数据

以下是基于 InnoDB 引擎、普通 SSD 环境下的一些参考数值，帮助你在设计批量操作时建立直观感知：

单条 INSERT 提交：约 200~500 行/秒（主要受事务刷盘限制）。
批量 INSERT 在一个事务中：5000~20000 行/秒，改进一个数量级。
LOAD DATA INFILE：通常可达 5 万~20 万行/秒，具体取决于字段宽度和索引数量。
批量 UPDATE（CASE WHEN 或临时表联表）：几千到上万行/秒，但仍大幅优于逐条更新。

这些数值并非绝对，但传递出一个清晰的信号：批量操作的性能优劣可以相差 10~100 倍，而优化成本往往只是改几行代码。

13.7.4 批量操作的生产实践建议

合理分批：单批写入/更新不要过大。建议每批 500~2000 行，在应用层循环抓取数据并提交。这样既能利用事务批处理优势，又不至于造成长事务、锁持有时间过长或主从延迟。
记录失败批次：当某一批数据因主键冲突、约束违反等原因失败时，记录该批并继续处理后续批次，而不是整个任务回滚。
监控从库延迟：大量批量写入会产生大量 Binlog，可能造成主从延迟。可以通过半同步复制或控制批量速率来缓解。
索引维护成本：每新增一条数据，所有索引都要相应更新。在大量写入时，减少非必要索引可以显著提升速度。可以在批量导入前删除无关索引，导入结束后再重建。
避免交互式客户端批量执行：不要用 Navicat 等工具粘贴几万行 SQL 执行，那会把 GUI 卡住且效率极低。应使用命令行 mysql 客户端执行 .sql 文件，或通过程序代码操作。

总之，批量写入和批量更新的核心心法就是：减少网络往返、合并事务提交、降低单行解析开销。一旦掌握了这几条，绝大多数批量操作的性能问题都能迎刃而解。