mysql - 使用 vanilla sql 代码与 upsert 相比有优势吗

Question

我不是数据库专家所以我希望有人能给我指点。使用 Vanilla 方法与更新插入方法相比有优势吗？我发现普通方法更清晰，没有明显的副作用。

此表是从耗时的读取和计算(平均 3-4 秒完成)创建的汇总表。这个表是一次性的(它可以随时被截断)。该表接收大量更新和读取，插入最少。

最初每次读取访问都会更新然后读取数据。为了提高性能，我在更新之间添加了 5 分钟的延迟

• 组发生更新后，在接下来的 5 分钟内，该组中的用户将仅读取数据(节省 3-4 秒)
• 5 分钟后的第一次访问将触发组数据的完整更新

最初我实现了一个 upsert 算法。然而，upsert-insert 的一个副作用是每次执行都会更新自动增量 pk 字段。这导致该 key 在几个小时内跃升了 100000。我关心的不是差距，而是达到 int 的最大值。我可以通过删除自动增量字段或每晚发出截断命令来管理它。我还看到了一些其他创造性的解决方案来防止自动递增。

但是，我不想控制副作用。我决定尝试使用不同的方法来解决这个问题。特别是因为这是一个以读取和更新为中心的表。

修改后的方法

    $lastInsertId=0;
    $q->beginTransaction(); 
    
    $sql = 'UPDATE data_snapshots SET field1=:field1,field2=:field2,field3=:field3,id = LAST_INSERT_ID(id) WHERE groupId=:groupId';
    $args=[':groupId'=>$groupId,':field1'=>$field1,':field2'=>$field2,':field3'=>$field3];
    $q->prepare ( $sql );
    $result = $q->execute ( $args );
    $lastInsertId = $q->lastInsertId();
    
    if($lastInsertId == 0){
        $sql='INSERT INTO data_snapshots (groupId,field1,field2,field3)';
        $q->prepare ( $sql );
        $result = $q->execute ( $args );
        $lastInsertId = $q->lastInsertId();
    }
    
    if($result == true && $lastInsertId  > 0){
        $q->commit();
        $modified=true;
    }
    else{
        $q->rollBack();
    }

更新插入方法

    $sql='INSERT INTO data_snapshots
    (groupId,field1,field2,field3)
    VALUES
    (:groupId,:field1,:field2,:field3)
    ON DUPLICATE KEY UPDATE
    groupId=:groupId_dup,field1=:field1_dup,field2=:field2_dup,field3=:field3_dup'];

    $args=[':groupId'=>$groupId,':field1'=>$field1,':field2'=>$field2,':field3'=>$field3,
    ':groupId_dup'=>$groupId,':field1_dup'=>$field1,':field2_dup'=>$field2,':field3_dup'=>$field3,]
    
    $q->prepare ( $sql );
    $result = $q->execute ( $args );

Answer 1

INSERT...ON DUPLICATE KEY UPDATE 不会增加自动递增计数器。

您的“普通方法”存在竞争条件问题。您尝试更新，但如果它不影响任何行，那么您将插入。但是，如果其他一些客户端在您的 UPDATE 和 INSERT 之间的短暂时间内插入了该行怎么办？

您可能认为这不太可能达到您不必担心的地步，但我们经常开发每秒运行许多交易的代码，并来自许多并发客户端。有一句古老的警示语，“下周二是百万分之一”，这意味着即使您假设竞争条件不太可能发生，但鉴于大量样本，它肯定会比您想象的更快发生。

---

顺便说一句，您可以从准备好的语句中删除所有*_dup 参数。在您的 ON DUPLICATE KEY 子句中，像这样使用 VALUES() :

INSERT INTO data_snapshots (groupId,field1,field2,field3)
VALUES (:groupId,:field1,:field2,:field3)
ON DUPLICATE KEY UPDATE
groupId=VALUES(groupId), field1=VALUES(field1), field2=VALUES(field2), field3=VALUES(field3);

此语法意味着如果执行 ON DUPLICATE KEY 子句，要更新的值将与您在上面的 VALUES() 子句中的参数中提供的值相同。如果这是典型情况，则您不必将每个值传递两次。