mysql - "INSERT ... ON DUPLICATE KEY UPDATE"与 "UPDATE"相比的性能

Question

99% 的情况下，单个 UPDATE 就足够了，但只有不到 0.01% 的时间我需要在同一键上跟进 INSERT。这可以写成单个 INSERT ... ON DUPLICATE KEY UPDATE 但是，就性能而言，我应该这样做吗？

问题基本上是，如果 INSERT ... ON DUPLICATE KEY UPDATE 预计会比 MySQL 中的 UPDATE 慢。

一些代码来演示:

代码使用 UPDATE

UPDATE foo SET seq = @new_seq WHERE id = X AND seq = @prev_seq;

IF rows_changed = 0 THEN
    INSERT INTO foo (id, seq) VALUES (X, @new_seq)
    IF <duplicate key error> THEN
        -- handle collusion
    END IF
END IF

代码使用 INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO foo (id, seq) VALUES (X, @new_seq)
    ON DUPLICATE KEY UPDATE seq = IF(seq = @prev_seq, VALUES(seq), seq)

IF rows_changed = 0 THEN
    -- handle collusion
END IF

Answer 1

强烈建议您自己对它们进行基准测试。

这很容易做到，而且更有说服力。

---

理论上，我认为INSERT ... ON DUPLICATE KEY UPDATE 不会拖累您。

案例 1:更新没有索引的表

在这种情况下，UPDATE 操作将锁定该表中的所有记录。但是INSERT操作只锁定一个间隙(Insert Intention Lock)和插入的记录。

由于您在此处使用了 INSERT ... ON DUPLICATE KEY UPDATE，因此您更有可能使用索引更新表。

案例 2:使用索引更新表

假设您正在使用主键更新表。 UPDATE 和INSERT 都将支付相同的成本来查找 B+ 树中的特定记录。 INSERT ... ON DUPLICATE KEY UPDATE 也是如此。所以没有理由 INSERT ... ON DUPLICATE KEY UPDATE 会慢得多。

此外，如果发现重复键，INSERT ... ON DUPLICATE KEY UPDATE 不会添加 Insert Intention Lock。它只会在表上添加一个 IX 锁，在特定记录上添加一个 X 锁，就像 UPDATE 所做的那样。