sql - IF EXISTS 在 INSERT、UPDATE、DELETE 之前进行优化

Question

经常出现这样的情况:您需要根据某些条件执行 INSERT、UPDATE 或 DELETE 语句。而我的问题是在命令前添加 IF EXISTS 是否对查询性能有影响。

示例

IF EXISTS(SELECT 1 FROM Contacs WHERE [Type] = 1)
    UPDATE Contacs SET [Deleted] = 1 WHERE [Type] = 1

插入或删除怎么样？

Answer 1

我不完全确定，但我的印象是这个问题实际上是关于upsert，这是以下原子操作:

• 如果该行同时存在于源和目标中，则更新目标；
• 如果该行仅存在于源中，则INSERT 该行到目标中；
• (可选)如果该行存在于目标中但不存在于源中，则从目标中删除该行。

开发人员出身的 DBA 经常天真地逐行编写，如下所示:

-- For each row in source
IF EXISTS(<target_expression>)
    IF @delete_flag = 1
        DELETE <target_expression>
    ELSE
        UPDATE target
        SET <target_columns> = <source_values>
        WHERE <target_expression>
ELSE
    INSERT target (<target_columns>)
    VALUES (<source_values>)

这几乎是你能做的最糟糕的事情，原因如下:

• 它有竞争条件。该行可以在 IF EXISTS 和后续 DELETE 或 UPDATE 之间消失。

• 这是浪费。对于每笔交易，您都会执行额外的操作；也许这很微不足道，但这完全取决于您的索引程度。

• 最糟糕的是 - 它遵循迭代模型，在单行级别考虑这些问题。这将对整体性能产生最大(最差)的影响。

一个非常小的(我强调次要的)优化是无论如何尝试更新；如果该行不存在，@@ROWCOUNT 将为 0，然后您可以“安全”插入:

-- For each row in source
BEGIN TRAN

UPDATE target
SET <target_columns> = <source_values>
WHERE <target_expression>

IF (@@ROWCOUNT = 0)
    INSERT target (<target_columns>)
    VALUES (<source_values>)

COMMIT

最坏的情况下，这仍然会为每笔交易执行两项操作，但至少有机会只执行一项，并且它还消除了(某种)竞争条件。

但真正的问题是，仍在对源中的每一行执行此操作。

在 SQL Server 2008 之前，您必须使用笨拙的三阶段模型在集合级别处理此问题(仍然比逐行更好):

BEGIN TRAN

INSERT target (<target_columns>)
SELECT <source_columns> FROM source s
WHERE s.id NOT IN (SELECT id FROM target)

UPDATE t SET <target_columns> = <source_columns>
FROM target t
INNER JOIN source s ON t.d = s.id

DELETE t
FROM target t
WHERE t.id NOT IN (SELECT id FROM source)

COMMIT

正如我所说，这种方法的性能相当糟糕，但仍然比一次一行的方法好很多。然而，SQL Server 2008 最终推出了MERGE语法，所以现在您所要做的就是:

MERGE target
USING source ON target.id = source.id
WHEN MATCHED THEN UPDATE <target_columns> = <source_columns>
WHEN NOT MATCHED THEN INSERT (<target_columns>) VALUES (<source_columns>)
WHEN NOT MATCHED BY SOURCE THEN DELETE;

就是这样。一份声明。如果您使用的是 SQL Server 2008，并且需要根据行是否已存在执行 INSERT、UPDATE 和 DELETE 的任意序列- 即使只有一行 - 没有借口不使用MERGE。

如果您需要了解事后发生了什么，您甚至可以将受 MERGE 影响的行OUTPUT放入表变量中。简单、快速、无风险。做吧。