MySQL数据同步性能优化：ON DUPLICATE KEY UPDATE踩坑与解决

很多做数据同步的同学应该都有过这种经历业务量不大的时候一条INSERT ... ON DUPLICATE KEY UPDATE写得飞起既能插入又能更新简洁又好用。可一旦数据量上来这条SQL就会变成噩梦——先是越来越慢接着是锁等待、死锁告警最后是主从延迟飙升整个库都被拖垮。我前阵子接手一个订单数据同步的需求每小时要从第三方接口拉一批数据写进本地MySQL峰值在30万行左右。刚上线那两天一切正常到了第三天凌晨同步任务直接把源库拖到慢查询Top1单次执行耗时从最初的20秒涨到了将近15分钟。查了performance_schema和慢日志之后问题直指ON DUPLICATE KEY UPDATE我把它彻底拆了一遍从原理到执行计划到最终方案都过了一轮。这篇文章就把这段排查和优化的完整过程记录下来包括为什么它会慢、瓶颈到底在哪、有哪些可落地的优化手段以及我最后用的是什么方案。如果你是做数据同步、批量导入、接口回写这类场景的而且正在跟MySQL的写入性能较劲这篇文章应该能让你少踩几个坑。1. 问题还原一条好用的SQL是怎么一步步变慢的1.1 从真香到真坑的全过程先说业务场景。我这边需要维护一张业务订单表结构大概是这样的CREATE TABLE order_sync ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(64) NOT NULL COMMENT 业务单号, customer_id bigint NOT NULL COMMENT 客户ID, amount decimal(12,2) DEFAULT NULL, status tinyint NOT NULL DEFAULT 0, update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;同步逻辑很简单第三方接口每次返回一批订单数据我拿着这批数据做存在就更新、不存在就插入的操作。最初用的就是最经典的写法INSERT INTO order_sync (order_no, customer_id, amount, status) VALUES (A001, 1001, 99.50, 1) ON DUPLICATE KEY UPDATE customer_id VALUES(customer_id), amount VALUES(amount), status VALUES(status);刚开始一天也就几万条数据跑起来没什么感觉。后来随着业务增长单次同步量涨到十几万、几十万行问题开始暴露了。最开始是同步耗时线性上涨后来干脆出现了Deadlock found when trying to get lock的报错再后来连带着主库的CPU和IO都跟着飙高。这里有个很迷惑的点从数据量来看30万行对MySQL来说根本不算什么就算一条条UPDATE也不至于慢成这样。那问题到底出在哪1.2 一条SQL背后MySQL实际做了两件事先不要急着优化SQL写法得先搞清楚ON DUPLICATE KEY UPDATE的执行路径到底是怎么走的。当这条SQL执行时MySQL的优化器会生成两套执行计划一套是按INSERT路径走尝试把新数据插入表中另一套是按UPDATE路径走在发生主键或唯一键冲突时更新已有记录。也就是说每一条记录在真正执行前MySQL都要先评估两条路径的成本然后选一条。这个评估过程本身就要消耗CPU和优化器的时间。当单条插入的时候这个开销微不足道但当你在一个INSERT语句里带上成千上万条VALUES或者循环一条条执行的时候这个成本就被无限放大了。更要命的是一旦发生唯一键冲突InnoDB内部的执行流程是这样的尝试插入发现唯一索引冲突立即返回错误而不是直接走更新获取冲突行的共享锁S锁判断是否满足更新条件如果满足将S锁升级为排他锁X锁执行更新写入undo log和redo log如果没有满足更新条件比如更新后值不变还需要处理一些特殊逻辑。反复的尝试插入、冲突、加锁、升级、更新、记日志这个链路比单纯走一次UPDATE要重得多。如果你的表上有多个唯一键冲突判断的次数还会成倍增加性能进一步恶化。1.3 自增主键暴涨一个容易被忽视的副作用这次排查中我注意到一个有意思的细节order_sync表的自增ID增长速度和实际插入行数完全不成比例。同步了20万行数据自增ID却从100万涨到了130万涨了30万。原因也很简单每次发生唯一键冲突时InnoDB仍然会消耗一个自增ID。自增ID本身是全局计数器在innodb_autoinc_lock_mode1的默认模式下批量插入会锁住自增计数器。你实际更新了10万行但也白白消耗了10万个ID。这个问题在数据量小的时候没人关注一旦数据量上来带来的直接后果是索引页分裂频繁、聚簇索引膨胀加速间接拖累写入性能。所以ON DUPLICATE KEY UPDATE并不是SQL本身写错了而是它在数据量小和数据量大两个场景下运行逻辑完全不同。你把它当成一条简单SQL在用它却背着两倍于UPDATE的包袱在跑。2. 性能瓶颈定位从慢日志到锁等待逐层排查2.1 慢查询日志锁定的表象遇到性能问题第一步永远是先看数据别靠猜。我当时打开了慢查询日志把long_query_time设置成了1秒过了几分钟再查立刻就看到了那条原封不动的同步SQL单条执行时间已经超过了600秒。慢日志只能告诉你哪条SQL慢但为什么慢还得继续往下拆。我接着用了两个工具EXPLAIN和SHOW PROFILE。EXPLAIN看了一下执行计划问题不算意外——主键和唯一键索引都用上了type是const理论上走的是点查应该很快。这恰恰说明性能瓶颈不在找数据的阶段而在写数据的阶段。2.2 SHOW PROFILE暴露的耗时环节MySQL 8.0中可以用SHOW PROFILE看一条SQL在服务器内部各个阶段的耗时占比。我截取了一段采样结果主要耗时集中在两个阶段updating就是真正执行更新操作的阶段占了总耗时的70%左右sending data占了一部分其实这个阶段包含了大量的binlog写入和事务提交前的准备工作query end事务提交和日志刷盘阶段占比偏高。换句话说绝大部分时间都花在了更新已有数据上。这也印证了一个事实当冲突率很高的时候ON DUPLICATE KEY UPDATE本质上就是一条密集的、逐行执行的点更新操作而不是你想象中顺便更新一下的轻量级SQL。2.3 锁等待和死锁大数据量下的必然产物我同时还开了SHOW ENGINE INNODB STATUS去捞锁信息。日志里明确能看到相互等待的记录Transaction A: UPDATE order_sync WHERE order_noA001 ... (holding X lock, waiting for S lock) Transaction B: UPDATE order_sync WHERE order_noA001 ... (holding S lock, waiting for X lock)我之前提到过ON DUPLICATE KEY UPDATE在冲突时先加S锁后续再升级为X锁。这个先S后X的路径天然就比直接加X锁更容易产生死锁尤其是在批量并发写入同一批数据的时候。另外如果你的表除了唯一键之外还有普通索引更新操作还会涉及二级索引的维护加锁范围可能从单行扩大到索引区间锁冲突的概率进一步上升。2.4 主从延迟比慢查询更麻烦的问题慢查询只是自身慢更怕的是拖累整个集群。因为ON DUPLICATE KEY UPDATE本质上是写操作一旦同步任务变慢事务长时间不提交binlog的写入频率和顺序就会出问题。从库在回放时会因为大事务迟迟不提交而卡主主从延迟从几秒变成几分钟最后整个读库的数据都变得不可信。这块如果你在生产环境遇到过应该知道后果有多严重。所以我在优化方案里第一优先级并不是怎么把这个SQL跑得快而是怎么让这个事务足够小、足够短。3. 优化思路从SQL改写到底层参数调优的拆解3.1 升级到8.0.20使用row别名替代VALUES()如果你还在用MySQL 8.0.19或更低版本第一个建议是升级到8.0.20及以上。从8.0.20开始官方弃用了VALUES()函数推荐使用row别名的方式INSERT INTO order_sync (order_no, customer_id, amount, status) VALUES (A001, 1001, 99.50, 1) AS new ON DUPLICATE KEY UPDATE customer_id new.customer_id, amount new.amount, status new.status;这个改法不只是语法变化它在语义上更明确而且在执行计划层面优化器能更准确地判断哪些列真正需要更新减少了一些不必要的行锁冲突判断。更重要的是新版本对ON DUPLICATE KEY UPDATE的锁逻辑做了优化死锁概率明显降低。实测下来同样的同步量在新版本下耗时能缩短20%到30%。3.2 降低冲突检测成本只保留必要的唯一索引在ON DUPLICATE KEY UPDATE的执行路径中每一行都要检查所有唯一键是否冲突。索引越多检查次数越多锁的粒度越粗。我之前的订单表除了主键之外其实还有两个冗余的唯一索引。排查后发现有一个索引从业务角度根本没有唯一性需求纯粹是以前设计时按查询习惯加上去的。删掉这个多余的唯一索引之后同步耗时有非常明显的下降。所以如果你在用ON DUPLICATE KEY UPDATE务必检查这张表上有几个唯一键一个就够多了就是纯负担。如果多个业务字段都需要唯一性约束但你其实只想以其中一个作为冲突判定条件那可以考虑建一张中间映射表把判断冲突和更新数据拆开不要让MySQL白白做多余的索引扫描。3.3 分批提交把大事务拆成小事务这是最立竿见影的优化。一次往一个INSERT语句里塞几万行或者一条一条地执行都不是大数据量场景下的正确姿势。正确做法是控制每批的行数让每个事务的写入量在一个合理范围。我最终采用的是每批2000行左右批量提交。之所以选2000这个值不是随便拍的。我试过500、1000、2000、5000、10000五档观察了事务提交耗时、锁等待次数、主从延迟三个指标2000行左右综合表现最好——事务执行时间短、锁持有时间短、日志写入量适中。-- 每批2000条 INSERT INTO order_sync (order_no, customer_id, amount, status) VALUES (A001, 1001, 99.50, 1), (A002, 1002, 88.00, 0), ... -- 最多2000条 AS new ON DUPLICATE KEY UPDATE customer_id new.customer_id, amount new.amount, status new.status;批大小不是固定的它和你的单行大小、事务并发数、服务器磁盘IO能力都有关系。如果单行字段特别多建议把批大小往下调。你可以做一个简单的基准测试分别用不同批次跑同一批数据观察TPS和锁等待次数找到当前机器上的最优值。3.4 减少无效更新加一个WHERE条件堵住值没变但也要更新的路径这是个很有意思的优化点。ON DUPLICATE KEY UPDATE的更新逻辑是只要发生唯一键冲突不管新的值跟旧的值是否相同默认都会执行一次更新。这意味着就算数据没有任何变化InnoDB也会走一遍加锁、写undo、写redo、标记binlog的完整流程。你可以通过添加WHERE条件让没有变化的行跳过更新INSERT INTO order_sync (order_no, customer_id, amount, status) VALUES (A001, 1001, 99.50, 1) AS new ON DUPLICATE KEY UPDATE customer_id new.customer_id, amount new.amount, status new.status WHERE order_sync.customer_id new.customer_id OR order_sync.amount new.amount OR order_sync.status new.status;这个优化对接口返回的数据大部分是历史数据、只有少数变化的场景尤其有效。我实际测试过在冲突率80%、但实际值变化率只有10%的情况下加入这种WHERE条件后整体耗时下降了接近一半。需要注意的是WHERE条件本身也要执行额外的比对操作如果几乎每行都真的需要更新这个优化反而会带来轻微的性能回退。所以它适合的是冲突很多、变化很少的同步场景。3.5 改走两条路先UPDATE再INSERT有一种很经典的思路是把ON DUPLICATE KEY UPDATE拆成两条SQL先执行UPDATE如果影响行数为0再执行INSERT。逻辑上等价但实际效果要看场景。如果冲突率很低比如低于5%这种方案比ON DUPLICATE KEY UPDATE快很多因为它不会为每一行都做一次尝试插入冲突检测。如果冲突率很高那就完全没有优势了——还是要逐行更新而且还要多加一次网络往返。所以这个方案的适用范围比较窄。我在一次导入历史数据的场景用过当时目标表基本是空的冲突率几乎为0UPDATE基本都影响0行然后再走INSERT效果确实比ON DUPLICATE KEY UPDATE好。但对高频同步、高冲突率的实时场景这个方案反而更慢。如果你打算试建议先统计一下你实际数据中的冲突率。3.6 终极方案临时表 批量合并如果你对实时性要求不高或者同步量特别大还有一个更激进但更稳妥的思路先把数据全部灌入一张临时表然后一次性完成更新和插入。这种基于集合的操作方式完全绕开了ON DUPLICATE KEY UPDATE逐行判断的瓶颈。具体步骤分三步第一步创建一张结构与目标表一致的临时表注意临时表的字段和索引不需要完全复刻一般只保留主键和唯一键。第二步把要同步的数据批量写入临时表比如用LOAD DATA LOCAL INFILE或者分批INSERT每批1万行都很快。第三步执行一次UPDATE JOIN和一次INSERT ... SELECT ... WHERE NOT EXISTS完成合并。-- 更新已有记录 UPDATE order_sync t INNER JOIN order_sync_tmp tmp ON t.order_no tmp.order_no SET t.customer_id tmp.customer_id, t.amount tmp.amount, t.status tmp.status; -- 插入新记录 INSERT INTO order_sync (order_no, customer_id, amount, status) SELECT order_no, customer_id, amount, status FROM order_sync_tmp tmp WHERE NOT EXISTS ( SELECT 1 FROM order_sync t WHERE t.order_no tmp.order_no );这个方案的优势是显而易见的操作变成了两个基于集合的大操作InnoDB可以在索引扫描时做批量处理有效利用顺序IO而不是逐行随机IO。实测30万行数据整个合并过程只需要几秒钟比单个ON DUPLICATE KEY UPDATE动辄几分钟强太多了。劣势也很明显需要多维护一张临时表、占了额外存储空间、SQL复杂度高一些。另外如果你的表数据特别大UPDATE JOIN操作可能会锁很多行建议在低峰期执行并且加好合适的索引。4. 实操案例同一份数据五种方案的耗时对比光说理论没有说服力我把自己踩坑过程中实际跑过的几组数据贴出来方便你直接参考。测试环境MySQL 8.0.288核16GSSD磁盘目标表50万行已有数据每次同步10万行冲突率按业务实际情况模拟为70%即7万行需要更新3万行需要插入。方案单次执行耗时锁等待次数主从延迟影响综合评价原版VALUES()逐条执行约480秒高严重不可用升级语法分批2000条约85秒中轻微可用简单有效分批WHERE跳过无变化行约47秒中轻微推荐效果明显先UPDATE后INSERT约160秒中一般仅适合低冲突率临时表UPDATE JOIN约6秒低几乎无性能最优适合大批量基于这个实验数据总结下我的选择逻辑如果是日常小批量同步比如几千条直接升级语法分批就够了没必要上临时表的复杂度。如果是几十万甚至上百万行的批量同步临时表方案是唯一能在合理时间内跑完的选择。中间那个分批WHERE的方案适合大部分中大规模同步场景代码改动最小收益也最明显。5. 进阶排查与底层调优把数据库调整到最适合同步的状态5.1 用Performance Schema精准定位锁等待如果用了上面那些优化性能还是不达标那就得从数据库整体状态入手了。我一般会用这样一条SQL查当前的锁等待情况SELECT THREAD_ID, EVENT_NAME, SOURCE, TIMER_WAIT / 1000000000 AS wait_ms, LOCK_STATUS FROM performance_schema.events_waits_current WHERE EVENT_NAME LIKE %lock% ORDER BY TIMER_WAIT DESC LIMIT 20;通过这个结果能看出是哪个线程在等哪把锁。再把sys.innodb_lock_waits表查一下基本就能定位到是哪两张表、哪两条SQL在互相阻塞了。5.2 InnoDB参数调优的四个关键项如果你的同步是集中式的、大批量的以下几个参数很值得调。innodb_buffer_pool_size这个是最核心的。它决定InnoDB能把多少索引页和数据页缓存在内存中。如果设置太小每次更新都要先从磁盘读数据页性能会断崖式下降。一般建议设为物理内存的60%到70%。我在测试机上从默认的128M调到6G之后批量更新的速度提升非常明显。innodb_flush_log_at_trx_commit默认值是1意味着每次事务提交都要刷一次redo log到磁盘安全但慢。如果你的场景可以接受极端情况下丢失最近1秒的数据比如只是同步临时的订单中间表而不是支付流水可以改成2。这个改动对写入性能的提升非常明显因为事务提交时不用再同步等待磁盘IO了。innodb_autoinc_lock_mode默认是1改成2之后自增主键的分配不再锁表多个插入可以交错进行并发插入性能更好。缺点是无法保证自增ID的连续性和可预测性但对大多数同步表来说ID本来就不是业务语义的一部分无所谓。max_allowed_packet如果一次INSERT的批大小比较大或者单行数据里有大字段比如JSON、text容易超过默认的4M限制。建议提高到64M以上避免因为SQL太大直接被拒。这里多提醒一句参数调优不是越多越好有些参数之间还互相影响。上面这些是我在同步场景下试过确实有效果的但如果你不确定自己的场景适不适合改可以先只动innodb_buffer_pool_size这个是相对安全的。5.3 事务隔离级别与锁竞争的关系MySQL默认的隔离级别是REPEATABLE READ。在这个隔离级别下更新操作会需要额外的gap lock间隙锁来防止幻读而间隙锁的本质是锁住一个范围而不是一行这会放大锁冲突的概率。如果你的同步逻辑不依赖事务中多次读取结果的一致性那么把隔离级别改成READ COMMITTED可以减少很多间隙锁的竞争。修改方式SET GLOBAL transaction_isolation READ-COMMITTED;注意这个改动会影响整个实例的所有连接建议在业务低峰期操作或者只在同步任务所在的连接上单独设置。我实测过把隔离级别改成READ COMMITTED后批量更新的死锁报错频率明显下降。5.4 终极兜底换一种写入方式前面所有优化都是基于INSERT ... ON DUPLICATE KEY UPDATE这个语法本身的改良。但当你试完所有招数发现这个写入模式还是不适合你的数据量时就该考虑换一个写入方式了。比如MySQL的LOAD DATA LOCAL INFILE它绕过了常规的SQL解析和优化器路径直接用批量加载的方式导入数据性能比一条SQL插入高一个数量级。配合REPLACE INTO或临时表合并完全可以替代ON DUPLICATE KEY UPDATE承担批量同步的任务。我用过一个方案LOAD DATA灌入临时表然后执行UPDATE JOIN和INSERT SELECT完成合并。整个过程对业务最大的好处是可重放、可控制不像单条ON DUPLICATE KEY UPDATE那样一旦中途失败你都不知道哪些行已经写了、哪些还没写。6. 常见问题速查我在实际排查中遇到过的坑6.1 为什么有时候死锁反而变多了一个很容易踩的坑是把批大小从2000调大到10000死锁反而变多了。原因是批越大每批事务持有锁的时间越长两个事务之间发生锁交叉的概率越高。换句话说大事务不是减少死锁而是增加死锁。建议遇到死锁时第一步不要急着改代码先把批大小调小一档试试。我遇到过很多次调小批大小后死锁直接消失。6.2VALUES()函数已经废弃为什么还能用在MySQL 8.0.20版本中VALUES()虽然标记为废弃但并没有移除所以老代码能继续跑。但官方明确提示后续版本会移除而且新语法在执行计划生成阶段更友好。所以有精力的话尽量在升级版本时顺手改成AS new的写法。6.3 为什么加了WHERE条件反而变慢WHERE条件需要MySQL额外执行一次行值比对如果每行都需要更新这个比对就是纯开销。所以这个优化只适合冲突多、真变化少的场景。你怎么判断自己的场景属于哪一种很简单在优化前后各跑一次同批数据对比耗时就行数据会给你答案。6.4 临时表方案会不会导致主键冲突临时表的数据最终要合并到主表里如果在合并前主表已经插入了相同order_no的记录INSERT ... SELECT确实会报唯一键冲突。解决办法是加一个WHERE NOT EXISTS条件把已经存在的数据过滤掉。但要注意这样会多一次子查询扫描所以临时表上也要建好和主表一致的唯一索引。6.5 数据量特别大的时候UPDATE JOIN会不会锁全表如果关联字段比如order_no在两张表上都建了索引并且主表的更新范围控制得比较好UPDATE JOIN不会锁全表。但如果临时表特别大优化器可能选择全表扫描主表锁的范围就会迅速膨胀。我建议给临时表加一个自增ID用LIMIT分批执行UPDATE JOIN把每一批的锁范围控制住UPDATE order_sync t INNER JOIN ( SELECT id, order_no, customer_id, amount, status FROM order_sync_tmp WHERE id ? AND id ? ) tmp ON t.order_no tmp.order_no SET t.customer_id tmp.customer_id, t.amount tmp.amount, t.status tmp.status;这个写法看着多了一层嵌套但胜在每一批Update的行数是可控的不会出现一次UPDATE锁了十万行的爆炸场面。6.6 为什么从库延迟还是很高如果主库优化完了从库延迟依然很大那问题多半出在binlog格式上。在ROW格式下一条UPDATE JOIN更新了5万行binlog里就会记录5万个UPDATE语句从库要逐条回放。这种场景下从库的SQL线程往往会成为瓶颈。一个解决办法是把大任务拆成多个小任务从库回放完一批再执行下一批另一个办法是升级硬件或者考虑并行复制但那是另一套配置了。这里先提醒你主库执行时间和从库延迟是两码事优化的时候要同时盯住这两个指标。7. 另一种思路如果OLTP扛不住考虑一下换个存储如果你已经在大数据量下反复优化ON DUPLICATE KEY UPDATE但始终觉得MySQL在高并发下的表现不如意这时候值得停下来想想是不是选错了工具MySQL在中小规模数据量下是很好的OLTP数据库但当你需要高频同步几百万行甚至上千万行数据时单点写入的瓶颈会非常明显。我见过一些场景数据同步量太大MySQL的Rows_EXAMINED和锁等待都已经到了一个很不健康的状态最后团队直接把同步链路迁移到了专门的OLAP存储或者消息队列里MySQL只保留最终结果数据。对于同步类需求一个更合理的架构可能是数据先进消息队列比如Kafka或RocketMQ再由消费者写入OLAP存储或者向量数据库最后把汇总结果回写到MySQL。这样MySQL永远只承担最终结果的读写而不是过程数据的冲洗。当然这个方案更重对团队的基础设施要求也更高。如果你的数据量还没到那个级别老老实实用我上面说的那几种优化方案就够了。8. 一些实操细节和最终经验到这里关于ON DUPLICATE KEY UPDATE性能优化的主要内容已经过了一遍。最后再把我在实际项目中确认过的几个优先级策略列一下方便你直接拿去做方案决策。第一优先级升级MySQL到8.0.20使用AS new语法。这个改动纯收益没有任何副作用闭着眼睛改就行。第二优先级控制批量大小拆小事务。一般建议单批500到2000行具体值通过压测确定。这个收益非常直观几乎不需要改业务逻辑。第三优先级评估冲突率和真实变化率。如果你的同步数据大部分和线上一致加上WHERE条件过滤无效更新能省掉一半以上的写入压力。如果冲突率极低考虑先UPDATE后INSERT的方案。第四优先级临时表合并。这个方案性能最好但也最重适合定时批量同步不适合高频实时接口。在整个排查和优化过程中我最深的体会是解决性能问题不能只盯着执行计划要往事务的维度去思考。ON DUPLICATE KEY UPDATE真正吃掉的不是那一行更新的CPU而是它背后锁的持有时间、日志的写入量、binlog的传播压力。只要你能把这些隐形开销压缩下来性能自然就上去了。如果你现在也在被ON DUPLICATE KEY UPDATE的性能问题折磨不妨先从最简单的一步开始升级语法、缩小批次。完成之后再看看慢查询日志大概率已经能明显感受到变化了。剩下的优化可以根据自己业务对实时性、一致性的要求按上面的优先级逐步推进。