企业官网建设流程全解析

凌晨两点被电话叫醒，线上一个回刷任务把主库拖到连接数打满，应用层疯狂报超时。我看了一眼慢日志，罪魁祸首就是一条用了ON DUPLICATE KEY UPDATE的批量写入SQL。表里有几千万历史数据，每天增量几十万，之前一直跑得好好的，但数据量级翻过某个阈值之后，这条曾经“闭眼写”的UPSERT语句突然就成了整个链路的瓶颈。

这篇文章想把这个问题彻底讲透：ON DUPLICATE KEY UPDATE在大数据量下到底贵在哪、为什么会有锁放大和日志放大，以及我实测过后真正有效的几条优化路径和排查锁死锁的完整链路。无论你是DBA、后端开发还是架构师，只要系统里还有批量写入的场景，这篇应该能帮你省下几个凌晨的告警电话。

1. 从一次真实告警聊起：同样的SQL，为什么数据量上来就废了

1.1 当时的SQL长什么样

那次事故的触发任务，核心逻辑其实很简单：拉取上游产出的订单明细，按订单号写入本地库。因为同一笔订单可能重复下发，所以很自然地用了ON DUPLICATE KEY UPDATE，让重复数据直接覆盖而不是报错。

INSERT INTO order_snapshot ( order_no, user_id, status, pay_amount, update_time ) VALUES ('A10001', 1001, 2, 99.00, NOW()), ('A10002', 1002, 1, 199.00, NOW()), ... ON DUPLICATE KEY UPDATE user_id = VALUES(user_id), status = VALUES(status), pay_amount = VALUES(pay_amount), update_time = VALUES(update_time);

一次最多拼5000行VALUES，任务启动后每200ms发一批。这套逻辑从数据量百万级的时候就开始跑，一直没出过问题。但当订单快照表涨到接近两千万行、每天高频访问的活跃数据又集中在最近三个月时，同样一句SQL的执行计划没变，实际代价却完全不是一个量级了。

1.2 我当时判断问题的顺序

告警显示的不是CPU打满，也不是磁盘IO打满，而是活跃连接数飙到500多，大部分会话卡在“Waiting for table level lock”和“updating”状态。第一反应是看SHOW PROCESSLIST，发现大量同一模式的INSERT语句在“Sending data”阶段，执行时间从几百毫秒一路涨到几十秒，越积越多。

紧接着看SHOW ENGINE INNODB STATUS，里面锁等待的TRANSACTIONS一长串，已经有死锁被自动回滚了。这里有个关键点：死锁回滚本身会释放连接，但如果每条SQL都持有锁很久、等待链又长，系统就会越来越接近崩溃边缘。

我当时把问题归结为三件事：

• 数据量变大后，唯一索引冲突检测需要走的索引页更深，随机IO成本上升；
• 每条冲突记录都要执行一次更新路径，更新涉及的回表和二级索引维护成本被成倍放大；
• 批量语句的锁范围比想象中大，事务内等待放大了并发冲突的概率。

2. ON DUPLICATE KEY UPDATE的真正成本：冲突是常态，更新一条比插入贵得多

2.1 它表面上是一句SQL，内部其实有两条路径

很多人对ON DUPLICATE KEY UPDATE的理解就是“存在就更新，不存在就插入”。这个理解不错，但它忽略了在InnoDB内部，这条语句默认走的是“先插入，后处理冲突”的路径，而不是“先查一下，决定插入还是更新”。

当你执行一条INSERT包含的批量行时，InnoDB会尝试真正插入。如果发现唯一键或主键冲突，这时才转而执行更新。在这个过程里，数据库要做的事情是这样的：

1. 对每一行确认使用哪个唯一键，索引查找并加锁；
2. 尝试插入新行，写入聚簇索引，维护二级索引；
3. 如果插入阶段撞上重复键，需要抛错并回退插入操作，转成定位旧行；
4. 对旧行加X锁，然后更新字段，同时把旧行的二级索引相关条目更新掉。

问题就出在第二步和第三步之间。冲突一多，数据库实际上是在“插入尝试失败、再回退、再更新”这个循环里反复横跳。相比一条干净的INSERT，每次冲突都多付出了索引探测、锁冲突处理和undo日志记录的成本。

2.2 批量越大，锁与日志的放大越明显

批量UPSERT最容易被低估的是锁和日志的放大效应。

在默认的REPEATABLE READ隔离级别下，InnoDB为了保证当前读的一致性，会在扫描到的索引范围上增加间隙锁（Gap Lock）。对于批量写入来说，这个“范围”可能会覆盖你VALUES里所有唯一键的周边区间。这意味着，两个并发事务如果各自携带一部分相同区间的唯一键，即使它们的键并没有完全重叠，也可能互相阻塞。

日志方面同样很大。每一条冲突更新，在redo log里要记录页面的修改，在undo log里要记录旧版本链，在binlog里还会以ROW格式记录前后镜像。同样的数据，如果走“先删后插”或者“直接覆盖”，日志量会小不少。真实场景里，同一行被同一批任务重复UPSERT了多次，binlog可能膨胀到实际有效数据量的3到5倍。

2.3 什么场景真正需要它，什么场景不该依赖它

我用一张表来总结我判断后的结论：

一句话：这个语法本身没错，错的是“任何场景都无脑用它”。数据量小的时候，一切都能被硬件掩盖；数据量上来了，每一行冲突的成本都会变成压垮系统的真金白银。

3. 我实测过的四套优化方案，按效果排了序

3.1 方案A：临时表整体合并，再一次性刷入

这套方案适合“离线批量回刷”和“日志聚合导入”这类场景。核心思路是：不让业务SQL直接面对目标大表，先把所有数据导入一张临时表，在临时表里完成去重和聚合，再一次性合并进目标表。

-- 1. 建立临时表，结构相同 CREATE TEMPORARY TABLE tmp_order_snapshot LIKE order_snapshot; -- 2. 把原始数据快速导入临时表，导入过程不做任何冲突判断 LOAD DATA / INSERT INTO tmp_order_snapshot ...; -- 3. 在临时表上按业务唯一键去重，保留最新状态 DELETE t1 FROM tmp_order_snapshot t1 JOIN tmp_order_snapshot t2 ON t1.order_no = t2.order_no AND t1.id < t2.id; -- 4. 用JOIN方式合并到目标表 INSERT INTO order_snapshot (order_no, user_id, status, pay_amount, update_time) SELECT tmp.order_no, tmp.user_id, tmp.status, tmp.pay_amount, tmp.update_time FROM tmp_order_snapshot tmp ON DUPLICATE KEY UPDATE user_id = tmp.user_id, status = tmp.status, pay_amount = tmp.pay_amount, update_time = tmp.update_time;

这套流程关键的一步是第三步：在临时表里先去掉重复的order_no。比如同一订单在原始数据里出现了7次，如果不合并，目标表就要做7次冲突更新；合并成1条后，目标表只需要处理一次插入或更新。加了一次临时表的去重开销，换来了目标表几倍的写入压力下降，性价比很高。

实测在2000万行目标表、单次导入100万行的场景下，原来直接拼大VALUES跑半小时都跑不完，改成临时表方案后整体耗时降到6分钟以内，其中耗时大头变成了临时表本身的写入和去重，目标表的锁持有时间大幅缩短。

3.2 方案B：按唯一键排序 + 分批 + 精确窗口

临时表方案不适合实时链路，实时场景下我推荐用“排序 + 分批 + 窗口”的组合拳。

先说排序。死锁的根源往往是两个事务以不同顺序申请同一批唯一键的锁。比如事务A先锁order_no='A10001'再锁A10002'，事务B先锁A10002'再锁A10001'，这种情况下就很容易互相等待。解决思路很简单：批与批之间，尽可能保证相同的前缀顺序。具体做法是在应用层按order_no做哈希分桶，或者直接排序后再分批发送。

再说分批。不要无限加大VALUES的条数。我测过同一份50万行的数据，用500条一批、2000条一批、5000条一批分别跑：

5000条一批看上去总耗时最短，但锁等待和死锁概率大幅增加。原因是单个事务持有锁的时间太长，一旦出现流量波峰，等待链会指数级恶化。所以不要把“单批耗时最短”作为唯一指标，要看整体稳定性。

精确窗口是指：如果目标表有明显的自增ID或时间分区，尽量在SQL里带一个“本次只更新近N天数据”的边界条件。这样InnoDB在判断冲突时能把索引扫描范围缩小，减少无谓的页访问。

3.3 方案C：INSERT IGNORE + UPDATE JOIN 分流

这套方案适用于“冲突率很低但偶尔会有重复”的实时场景，核心思路是把“可能更新”和“一定插入”的两部分流量拆开处理。

-- 第一步：忽略重复，只插新增 INSERT IGNORE INTO order_snapshot ( order_no, user_id, status, pay_amount, update_time ) VALUES ('A10001', 1001, 2, 99.00, NOW()), ('A10002', 1002, 1, 199.00, NOW()); -- 第二步：对存在的数据单独UPDATE，用JOIN限定范围 UPDATE order_snapshot t JOIN ( SELECT 'A10001' AS order_no, 1001 AS user_id, 5 AS status, 129.00 AS pay_amount UNION ALL SELECT 'A10002', 1002, 1, 199.00 ) s ON t.order_no = s.order_no SET t.status = s.status, t.pay_amount = s.pay_amount, t.update_time = NOW();

第一句INSERT IGNORE比ON DUPLICATE KEY UPDATE轻量，因为遇到重复键时它直接忽略这一行，不进入更新路径，不需要像UPDATE那样消耗大量锁和日志。第二句UPDATE虽然是更新，但它是独立执行的，不会和INSERT语句搅在同一个事务里产生又插入又更新的锁状态。

这套方案的收益要看冲突率。冲突率在5%以下时，INSERT IGNORE的收益非常明显；如果冲突率超过20%，第二句UPDATE JOIN的代价本身就很高，优势就被抵消了。

3.4 方案D：不改SQL，改表结构和同步链路

有些问题不是SQL写法能解决的，而是表结构设计本身放大了写入代价。我在排查时发现那张订单快照表有7个索引，但业务查询真正用到的只有order_no和status两个。剩下几个二级索引每次UPSERT都要同步维护，看似无害，实际上写放大得很严重。

在大数据量写多读少的场景下，我的原则是：

• 只保留必要索引，尤其是唯一索引；能用组合索引覆盖的，就不要拆成多个单列索引；
• 高频更新的字段和低频更新的字段拆分到不同的表；
• 如果同一行每天被更新几十次，考虑把“实时状态”和“累积快照”分离，而不是所有变更都打在同一行上。

还有一种思路是从源头减少UPSERT次数。比如上游本身已经发了重复消息，应用层做一个内存去重或短窗口聚合，很多重复更新根本不需要到达数据库。这个优化不花数据库一分钱性能，效果却最明显。

3.5 方案取舍表

4. 高并发下的锁与死锁排查链路：别再只盯着慢查询

4.1 那天的死锁是怎么被逼出来的

我在验证方案的时候故意用压测工具模拟了8个并发线程持续向同一张表写入，每个线程的VALUES里都混着一批重叠的order_no。压测跑了不到两分钟，慢日志里就开始出现Deadlock found when trying to get lock; try restarting transaction。这说明问题不是偶发，而是并发度上来后必然触发。

死锁的直接原因是两个事务都先插入了一部分行，然后在更新另一部分行时，需要对方已经锁定的行。ON DUPLICATE KEY UPDATE让每个事务同时持有“插入意向锁”和“更新X锁”，锁的种类多、持有时间长，死锁概率自然比纯INSERT高得多。

4.2 完整排查命令组合

排查死锁和锁等待，我固定用下面这几条命令，按顺序执行：

-- 1. 看当前哪些SQL卡在最前面 SHOW FULL PROCESSLIST; -- 2. 看InnoDB引擎状态，重点看LATEST DETECTED DEADLOCK SHOW ENGINE INNODB STATUS\G; -- 3. 看当前所有锁等待关系 SELECT * FROM performance_schema.data_lock_waits\G; -- 4. 看具体事务持有哪些锁 SELECT * FROM performance_schema.data_locks\G;

SHOW ENGINE INNODB STATUS里最关键的是LATEST DETECTED DEADLOCK和TRANSACTIONS两部分。死锁段落会打印两个事务各自的SQL、持有和等待的锁类型，以及被回滚的那个事务。多数情况下，锁类型里会出现RECORD LOCKS和GAP关键字。

data_lock_waits视图能帮我看到完整的等待链：哪个事务在等哪个事务的锁、等待的是哪一行、索引名是什么。这套信息比单纯看慢日志要精准得多。

4.3 读懂死锁信息的关键字段

死锁信息里最容易误导人的是它不会直接说“这两条SQL写错了”，它只会告诉你两个事务的锁相互冲突了。你需要自己判断：

• WAITING FOR THIS LOCK TO BE GRANTED：当前事务在等哪个锁；
• HOLDS THE LOCK(S)：哪个事务持有这把锁；
• 锁类型是RECORD还是GAP：RECORD锁说明是同一行冲突，GAP锁说明是区间冲突；
• 索引名：如果等待发生在二级索引上，往往说明排序或索引设计有问题。

有一次排查发现死锁等待的锁在idx_user_id上，但业务SQL根本没有按user_id过滤。原因是二级索引插入时需要维护索引条目，两个并发事务写入的user_id恰好落在相邻区间，产生了间隙锁冲突。这就是索引设计不当导致的隐性锁竞争。

4.4 降低死锁的落地动作

基于这些排查经验，我在项目里落地了下面几条规则：

• 应用层对同一批唯一键做排序，保证不同线程以相同顺序加锁；
• 降低隔离级别为READ COMMITTED，消除大部分间隙锁；
• 缩短单事务的执行时间，也就是减小批量大小；
• 对AUTO-INC锁设置调整为innodb_autoinc_lock_mode=2（需要binlog为ROW格式）；
• 把高频UPSERT的SQL拆成INSERT和UPDATE两段，减少单语句内锁类型的混合。

其中降低隔离级别这条需要业务方确认，不是所有场景都能接受READ COMMITTED的语义。但对于订单快照这种“以覆盖写入为主”的表，完全没有影响。

5. 参数、索引和事务层面的配套调优：优化SQL只是其中一半

5.1 自增主键碎片的隐藏影响

大量UPSERT带来的一个隐蔽问题是自增主键的碎片化。由于冲突后要走更新路径，主键值的分配并不会像纯插入那样紧凑。时间长了，聚簇索引的页会变得碎片化，范围查询和批量写入都要读更多页。

我在优化时对目标表做了ALTER TABLE ... ENGINE=InnoDB重建，重建后同一批导入的耗时又下降了15%左右。这不算是SQL优化，但属于配套调优里不可或缺的一步。建议在数据量大的表上，周期性检查information_schema.tables里的DATA_FREE字段，碎片率超过30%就值得处理。

5.2 我调整过的几个关键参数

需要提醒的是，innodb_flush_log_at_trx_commit=2意味着每次事务提交只写操作系统缓存，掉电可能丢最后一秒的事务。这套配置只建议在回刷任务时段临时开启，或者用于非核心库。核心交易库不要盲目照搬。

5.3 联合索引设计怎么配合UPSERT

ON DUPLICATE KEY UPDATE判断冲突时只会用主键或唯一索引做等值匹配。如果你的表里存在多个唯一键，InnoDB会选择一个优先判断，但更新时仍然需要回表定位其他唯一键对应的行，过程比想象中复杂。

我的建议是：业务上只保留一个真正的“业务唯一键”，比如order_no。如果有多个维度需要保证唯一，可以考虑拆表或使用生成列+唯一索引。索引不是越多越好，每多一个二级索引，UPSERT的写放大就多一分。

5.4 事务边界与批量大小怎么量化

不要凭感觉定批量大小。我在压测环境里对同一批50万行数据做了多组实验：

可以看到，总耗时最短的是5000条一批，但这时候锁等待率已经非常高了。真正健康的区间在500到1000条一批之间：总耗时接近最优，锁等待率低，出故障的概率也低。这个区间和表的大小、硬件配置、并发线程数都有关系，建议你上线前花一小时压一组数据。

6. 如果已经出问题，线上止血手段和最后一点经验

6.1 临时降低并发 + 暂停边缘任务

告警发生时，第一件事不是去改SQL，而是把引发问题的任务停掉或降速。再好的SQL优化也救不了正在打满连接数的库。我的做法是：先把批量线程数从8降到2，同时把每批大小从5000降到500，让数据库先缓过来。

6.2 重建索引缓解碎片

如果问题持续，可以安排低峰期做一次ALTER TABLE ... ENGINE=InnoDB重建。这个操作在8.0里可以用ALGORITHM=INPLACE减少锁表时间，但大表依然会有IO压力，需要维护窗口。

6.3 用任务队列拉长执行时间

回刷类任务不一定非得在凌晨一口气跑完。把它拆成多个小任务放进队列，按数据库实时负载动态调整消费速度，对系统整体稳定性更友好。

到这里，关于ON DUPLICATE KEY UPDATE的优化链路基本讲完了。我现在的习惯是：每写一条批量UPSERT之前，先问自己三个问题——这批数据会有多少比例重复？单事务锁持有时间会不会太长？如果出问题，有没有降级方案？这三个问题想清楚，大部分性能事故都能在设计阶段被挡在门外。