凌晨两点被电话叫醒线上一个回刷任务把主库拖到连接数打满应用层疯狂报超时。我看了一眼慢日志罪魁祸首就是一条用了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会尝试真正插入。如果发现唯一键或主键冲突这时才转而执行更新。在这个过程里数据库要做的事情是这样的对每一行确认使用哪个唯一键索引查找并加锁尝试插入新行写入聚簇索引维护二级索引如果插入阶段撞上重复键需要抛错并回退插入操作转成定位旧行对旧行加X锁然后更新字段同时把旧行的二级索引相关条目更新掉。问题就出在第二步和第三步之间。冲突一多数据库实际上是在“插入尝试失败、再回退、再更新”这个循环里反复横跳。相比一条干净的INSERT每次冲突都多付出了索引探测、锁冲突处理和undo日志记录的成本。2.2 批量越大锁与日志的放大越明显批量UPSERT最容易被低估的是锁和日志的放大效应。在默认的REPEATABLE READ隔离级别下InnoDB为了保证当前读的一致性会在扫描到的索引范围上增加间隙锁Gap Lock。对于批量写入来说这个“范围”可能会覆盖你VALUES里所有唯一键的周边区间。这意味着两个并发事务如果各自携带一部分相同区间的唯一键即使它们的键并没有完全重叠也可能互相阻塞。日志方面同样很大。每一条冲突更新在redo log里要记录页面的修改在undo log里要记录旧版本链在binlog里还会以ROW格式记录前后镜像。同样的数据如果走“先删后插”或者“直接覆盖”日志量会小不少。真实场景里同一行被同一批任务重复UPSERT了多次binlog可能膨胀到实际有效数据量的3到5倍。2.3 什么场景真正需要它什么场景不该依赖它我用一张表来总结我判断后的结论场景是否适合ON DUPLICATE KEY UPDATE原因低并发离线回刷冲突率低于5%适合简单直接无需额外代码高并发实时写入冲突率偶尔偏高谨慎锁竞争可能成为瓶颈高并发实时写入冲突率很高不适合更新路径加锁时间长死锁概率大增批量数据包含大量重复键且可预聚合很不适合重复更新完全浪费应用层就能合并需要对同一行多次累加如计数器可以但要控制粒度适合小事务不适合超大批量一句话这个语法本身没错错的是“任何场景都无脑用它”。数据量小的时候一切都能被硬件掩盖数据量上来了每一行冲突的成本都会变成压垮系统的真金白银。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_noA10001再锁A10002事务B先锁A10002再锁A10001这种情况下就很容易互相等待。解决思路很简单批与批之间尽可能保证相同的前缀顺序。具体做法是在应用层按order_no做哈希分桶或者直接排序后再分批发送。再说分批。不要无限加大VALUES的条数。我测过同一份50万行的数据用500条一批、2000条一批、5000条一批分别跑批大小总耗时锁等待次数死锁次数5003分20秒10020002分10秒8150001分50秒3565000条一批看上去总耗时最短但锁等待和死锁概率大幅增加。原因是单个事务持有锁的时间太长一旦出现流量波峰等待链会指数级恶化。所以不要把“单批耗时最短”作为唯一指标要看整体稳定性。精确窗口是指如果目标表有明显的自增ID或时间分区尽量在SQL里带一个“本次只更新近N天数据”的边界条件。这样InnoDB在判断冲突时能把索引扫描范围缩小减少无谓的页访问。3.3 方案CINSERT 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 方案取舍表方案最佳场景成本效果临时表JOIN离线回刷、大批量导入中需改ETL高排序分批窗口实时高并发写入低应用层改动高INSERT IGNOREUPDATE JOIN低冲突率实时写入低中表结构/同步链路改造长期高压力场景高涉及设计变更最高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还是GAPRECORD锁说明是同一行冲突GAP锁说明是区间冲突索引名如果等待发生在二级索引上往往说明排序或索引设计有问题。有一次排查发现死锁等待的锁在idx_user_id上但业务SQL根本没有按user_id过滤。原因是二级索引插入时需要维护索引条目两个并发事务写入的user_id恰好落在相邻区间产生了间隙锁冲突。这就是索引设计不当导致的隐性锁竞争。4.4 降低死锁的落地动作基于这些排查经验我在项目里落地了下面几条规则应用层对同一批唯一键做排序保证不同线程以相同顺序加锁降低隔离级别为READ COMMITTED消除大部分间隙锁缩短单事务的执行时间也就是减小批量大小对AUTO-INC锁设置调整为innodb_autoinc_lock_mode2需要binlog为ROW格式把高频UPSERT的SQL拆成INSERT和UPDATE两段减少单语句内锁类型的混合。其中降低隔离级别这条需要业务方确认不是所有场景都能接受READ COMMITTED的语义。但对于订单快照这种“以覆盖写入为主”的表完全没有影响。5. 参数、索引和事务层面的配套调优优化SQL只是其中一半5.1 自增主键碎片的隐藏影响大量UPSERT带来的一个隐蔽问题是自增主键的碎片化。由于冲突后要走更新路径主键值的分配并不会像纯插入那样紧凑。时间长了聚簇索引的页会变得碎片化范围查询和批量写入都要读更多页。我在优化时对目标表做了ALTER TABLE ... ENGINEInnoDB重建重建后同一批导入的耗时又下降了15%左右。这不算是SQL优化但属于配套调优里不可或缺的一步。建议在数据量大的表上周期性检查information_schema.tables里的DATA_FREE字段碎片率超过30%就值得处理。5.2 我调整过的几个关键参数参数原值调整后理由innodb_buffer_pool_size4G32G让热索引页尽量常驻内存innodb_flush_log_at_trx_commit12离线批量场景下降低磁盘fsync频率innodb_autoinc_lock_mode12减少自增锁竞争需配合binlogROWsync_binlog10只限离线任务时段减少binlog刷盘等待binlog_group_commit默认打开组提交提升批量写入吞吐需要提醒的是innodb_flush_log_at_trx_commit2意味着每次事务提交只写操作系统缓存掉电可能丢最后一秒的事务。这套配置只建议在回刷任务时段临时开启或者用于非核心库。核心交易库不要盲目照搬。5.3 联合索引设计怎么配合UPSERTON DUPLICATE KEY UPDATE判断冲突时只会用主键或唯一索引做等值匹配。如果你的表里存在多个唯一键InnoDB会选择一个优先判断但更新时仍然需要回表定位其他唯一键对应的行过程比想象中复杂。我的建议是业务上只保留一个真正的“业务唯一键”比如order_no。如果有多个维度需要保证唯一可以考虑拆表或使用生成列唯一索引。索引不是越多越好每多一个二级索引UPSERT的写放大就多一分。5.4 事务边界与批量大小怎么量化不要凭感觉定批量大小。我在压测环境里对同一批50万行数据做了多组实验批大小行平均单事务耗时单事务锁等待率总耗时10080ms0.1%6分30秒500260ms0.3%3分20秒1000700ms2%2分50秒20001.9s8%2分10秒50007s30%1分50秒死锁导致重试拉高可以看到总耗时最短的是5000条一批但这时候锁等待率已经非常高了。真正健康的区间在500到1000条一批之间总耗时接近最优锁等待率低出故障的概率也低。这个区间和表的大小、硬件配置、并发线程数都有关系建议你上线前花一小时压一组数据。6. 如果已经出问题线上止血手段和最后一点经验6.1 临时降低并发 暂停边缘任务告警发生时第一件事不是去改SQL而是把引发问题的任务停掉或降速。再好的SQL优化也救不了正在打满连接数的库。我的做法是先把批量线程数从8降到2同时把每批大小从5000降到500让数据库先缓过来。6.2 重建索引缓解碎片如果问题持续可以安排低峰期做一次ALTER TABLE ... ENGINEInnoDB重建。这个操作在8.0里可以用ALGORITHMINPLACE减少锁表时间但大表依然会有IO压力需要维护窗口。6.3 用任务队列拉长执行时间回刷类任务不一定非得在凌晨一口气跑完。把它拆成多个小任务放进队列按数据库实时负载动态调整消费速度对系统整体稳定性更友好。到这里关于ON DUPLICATE KEY UPDATE的优化链路基本讲完了。我现在的习惯是每写一条批量UPSERT之前先问自己三个问题——这批数据会有多少比例重复单事务锁持有时间会不会太长如果出问题有没有降级方案这三个问题想清楚大部分性能事故都能在设计阶段被挡在门外。