MySQL如何优雅处理批量新增和更新?

发布时间:2025-12-30 00:12

蓝易云：MySQL 如何优雅处理批量新增与批量更新？（少锁、少回滚、少熬夜🙂）

所谓“优雅”，在数据库语境里就是三件事：吞吐高、一致性稳、可运维。批量写入别用“循环一条条写”硬刚，这会把网络往返、日志压力、锁竞争一起拉满，最后你以为是业务慢，实际上是你在跟数据库的耐心对赌。

1）批量新增：优先用多行 INSERT，减少 RTT ✅

INSERT INTO user_profile (user_id, nick, level)
VALUES
  (1001, 'a', 1),
  (1002, 'b', 3),
  (1003, 'c', 2);
​

解释：

• 一条 SQL 写多行，比循环执行 N 条 INSERT 更省网络往返（RTT）与解析成本。
• 适合“纯新增”且数据量中等（几百到几千行一批）的场景。
• 批次大小建议可控（例如 500～2000 行/批），避免单条 SQL 过大触发 max_allowed_packet 或造成长事务。

2）批量新增+更新：用UPSERT（一条语句解决“存在就更，不存在就插”）🚀

前提：表上必须有唯一索引（如 user_id 唯一），否则“重复判定”无从谈起。

INSERT INTO user_profile (user_id, nick, level)
VALUES
  (1001, 'a2', 2),
  (1004, 'd', 1)
AS new
ON DUPLICATE KEY UPDATE
  nick  = new.nick,
  level = new.level;
​

解释：

• ON DUPLICATE KEY UPDATE：命中唯一键冲突时执行更新，否则插入。
• AS new：给本次插入的数据起别名，更新时直接引用（比旧写法更清晰，避免可读性差）。
• 优点：业务层不用“先查再写”，天然减少并发竞态与锁时间。
• 注意：更新列越多、二级索引越多，写放大越明显；只更新必要字段最“优雅”。

3）只想“重复就跳过”：用INSERT IGNORE（但别滥用）🙂

INSERT IGNORE INTO user_profile (user_id, nick, level)
VALUES (1001, 'x', 9);
​

解释：

• IGNORE：遇到唯一键冲突等可忽略错误时直接跳过，不报错。
• 适合“幂等导入、允许丢重复数据”的场景。
• 风险：它会把某些写入异常也“静默处理”，排错成本上升；生产建议配合写入统计与告警。

4）批量更新：少量行可用CASE WHEN 一把梭 ✅

UPDATE user_profile
SET level = CASE user_id
  WHEN 1001 THEN 5
  WHEN 1002 THEN 2
  WHEN 1003 THEN 9
  ELSE level
END
WHERE user_id IN (1001, 1002, 1003);
​

解释：

• 一条 UPDATE 同时更新多行不同值，避免循环更新。
• ELSE level：未命中时保持原值，防止误改。
• 适合更新行数不多、字段不多的批处理；行数太大 SQL 会变长，不再优雅。

5）大批量更新/新增：最稳的企业方案是临时表/中间表 + JOIN 合并（可控、可回滚、可审计）🏗️

步骤 A：建临时表承接批数据

CREATE TEMPORARY TABLE tmp_user_profile (
  user_id BIGINT PRIMARY KEY,
  nick    VARCHAR(64),
  level   INT
);
​

解释：

• 临时表只在当前会话存在，天然“用完即走”，不会污染正式库结构。
• PRIMARY KEY 让后续 JOIN 更新更快、更稳定。

步骤 B：把批数据先灌进临时表（可多行 INSERT 或批导入）

INSERT INTO tmp_user_profile (user_id, nick, level)
VALUES
  (1001, 'a2', 2),
  (1004, 'd', 1);
​

解释：

• 先落地到 tmp，等于给主表做“缓冲区”，便于失败回滚与重复执行（幂等）。

步骤 C：用 JOIN 批量更新主表

UPDATE user_profile u
JOIN tmp_user_profile t ON u.user_id = t.user_id
SET u.nick = t.nick,
    u.level = t.level;
​

解释：

• JOIN 更新由 MySQL 在引擎内完成，减少应用层循环。
• 锁更集中、执行计划更可控，适合万级、十万级批更新。

步骤 D：把主表不存在的记录再批量插入

INSERT INTO user_profile (user_id, nick, level)
SELECT t.user_id, t.nick, t.level
FROM tmp_user_profile t
LEFT JOIN user_profile u ON u.user_id = t.user_id
WHERE u.user_id IS NULL;
​

解释：

• 先更新已存在的，再插入不存在的，语义清晰，失败也容易定位。
• 这种“分阶段合并”比在主表上直接硬怼更耐用。

6）让批处理更“优雅”的底层原则（决定你是否稳定）✅

• 控制事务粒度：一批一提交，避免超长事务拖垮回滚段与锁竞争。
• 建好唯一索引：没有唯一约束，UPSERT 就是空中楼阁。
• 写入尽量“少列更新”：减少二级索引维护与 redo/undo 压力。
• 允许重试：批量写入高并发下出现死锁并不罕见，业务侧要有“失败重试”的心理准备（别把偶发当灾难）。

快速选型（你可以直接照这个用）

如果你给我：表结构（主键/唯一键）、批量规模（1万/10万/100万）、以及是否要求强一致，我可以把“批次大小、索引策略、事务边界、失败重试策略”给你直接定成一套生产可用的写入方案。