如何优化SQL中频繁DELETE与INSERT操作？

频繁 DELETE + INSERT 组合，本质上是“先删后插”，但直接这么干极易锁表、拖慢事务、触发大量索引重建——应优先用 REPLACE INTO 或 INSERT ... ON DUPLICATE KEY UPDATE 替代，这是提升SQL性能的关键技巧。

为什么不能直接交替执行 DELETE 和 INSERT？

这里面的门道其实挺多的。连续执行 DELETE 再 INSERT，哪怕主键相同，也意味着索引要被干两遍（删索引 + 插索引）。更重要的是，中间那个短暂的“数据真空期”，如果恰好有并发读取，就可能看到空结果，导致应用逻辑出错。更关键的是，InnoDB 会为这两条语句分别加行锁或间隙锁，锁的持有时间几乎翻倍，在高并发下很容易引发锁等待甚至死锁，这可是线上大忌。

• DELETE 操作需要遍历索引定位并标记删除，还要清理 undo log，开销不小。
• INSERT 操作则需要重新分配数据页、写 redo 日志，以及更新所有相关索引。
• 如果这两步之间没有显式事务包裹，一旦中间出问题，就容易出现部分成功、部分失败的数据不一致状态，极难排查。

用 INSERT ... ON DUPLICATE KEY UPDATE 替代 DELETE+INSERT

好消息是，当目标表有唯一键（比如 UNIQUE 约束或主键）时，INSERT ... ON DUPLICATE KEY UPDATE 就是一把利器。它是一个原子操作：命中冲突就更新，没冲突就插入。从实际效果来看，它只走一次索引查找，完美避开了二次定位和锁竞争的问题，是优化SQL性能的常用手段。

• 前提是冲突列上必须有唯一索引，否则这个语句不生效，而且不会报错，很容易被忽略。
• 更新字段不能包含自增列本身，但可以更新其他列。比如：INSERT INTO user (id, name, status) VALUES (123, 'Alice', 'active') ON DUPLICATE KEY UPDATE name = VALUES(name), status = VALUES(status)。这里的 VALUES(col) 是个语法糖，表示本次 INSERT 语句中该列的值，不是函数调用，别搞混了。
• 如果业务逻辑需要“覆盖全部字段”，那就老老实实把所有非主键字段都列出来；如果只想更新部分字段，比如只更新状态，那就只写对应的字段即可，没必要追求形式上的完整。

REPLACE INTO 的代价与适用边界

至于 REPLACE INTO，它看起来最简洁，但底层逻辑其实还是“DELETE + INSERT”两步，只不过被封装成一条语句了。它会先按唯一键查出旧行并删除（触发 DELETE 触发器、生成 undo 日志），再插入新行。所以，它本质上和手动 DELETE+INSERT 没什么区别，依然会引发双倍索引维护开销，并可能导致自增 ID 跳变。

• 只有在你明确接受自增 ID 不连续，并且表上没有复杂的触发器或外键级联时，才考虑用它。
• 在高并发写入场景下，这个命令尤其危险：两个 REPLACE 同时命中同一行，可能会产生幻读或主键冲突重试，导致意想不到的后果。
• 从性能角度看，它比 ON DUPLICATE KEY UPDATE 多了一次磁盘写入，在 delete 阶段的 page split 或 merge 操作可能更剧烈，对磁盘IO的压力更大。

批量场景下必须控制单次语句长度和事务粒度

即便换成了 ON DUPLICATE KEY UPDATE，也别一次性提交几万行。那会引发长事务、大日志和锁范围扩大的问题，把性能优势全给抵消了。实际部署时，一定要拆分批次，并手动管理事务，这是SQL优化中容易被忽视的细节。

• 单条 INSERT ... ON DUPLICATE KEY UPDATE 语句，建议不超过 1000 行，免得超过 max_allowed_packet 的限制，导致语句被截断。
• 每批执行后，一定要显式 COMMIT，不要依赖自动提交。必要时，用 START TRANSACTION 包裹整批操作，确保事务的边界清晰。
• 避免在事务中混入 SELECT 或其他 DML 操作，防止锁范围意外扩大，影响其他查询。
• 如果目标表有大量二级索引，可以考虑在离线维护窗口里，临时 DROP INDEX 再重建，这能显著提升批量写入的性能。

话说到最后，其实真正难的不是写出替代语句，而是判断“哪些字段必须更新”“哪些冲突才算有效”“是否允许短暂不一致”。这些逻辑一旦嵌入到SQL里，就很难动态调整了。所以，在最终落地时，往往需要把冲突判定逻辑收回到应用层做兜底校验，才能真正做到万无一失。