检查是否存在EXISTS胜过COUNT个！ ……不？

LEFT JOIN (
    SELECT
        someID
        , COUNT(*)
    FROM someTable
    GROUP BY someID
) AS Alias ON (
    Alias.someID = mainTable.ID
)

#1 楼

我经常阅读何时必须检查行是否存在的情况，应该始终使用EXISTS而不是使用COUNT。


很少有东西总是存在确实如此，尤其是在数据库方面。有多种方法可以在SQL中表达相同的语义。如果有一个有用的经验法则，可能是使用最自然的可用语法编写查询（是的，这是主观的），并且仅在您获得的查询计划或性能不可接受时才考虑进行重写。

对于它的价值，我个人认为，存在查询最自然地使用EXISTS表示。根据我的经验，EXISTS倾向于比OUTER JOIN拒绝NULL替代方案更好。使用COUNT(*)并在=0上进行过滤是另一种选择，它恰好在SQL Server查询优化器中提供了一些支持，但是我个人发现这在更复杂的查询中是不可靠的。无论如何，对我来说，EXISTS似乎比它们中的任何一个都自然得多。


我想知道EXISTS是否存在未预料到的缺陷，可以对测量进行完全理解我已经完成了


您的特定示例很有趣，因为它突出了优化器处理CASE表达式（尤其是EXISTS测试）中的子查询的方式。

CASE表达式中的子查询

请考虑以下（完全合法的）查询：

DECLARE @Base AS TABLE (a integer NULL);
DECLARE @When AS TABLE (b integer NULL);
DECLARE @Then AS TABLE (c integer NULL);
DECLARE @Else AS TABLE (d integer NULL);

SELECT
    CASE
        WHEN (SELECT W.b FROM @When AS W) = 1
            THEN (SELECT T.c FROM @Then AS T)
        ELSE (SELECT E.d FROM @Else AS E)
    END
FROM @Base AS B;

CASE的语义是通常对WHEN/ELSE子句进行求值按文本顺序。在上面的查询中，如果满足ELSE子句，如果WHEN子查询返回的行多于一行，则SQL Server返回错误是不正确的。为了尊重这些语义，优化器会生成一个使用传递谓词的计划：



仅当直通谓词返回false时，才评估嵌套循环连接的内侧。总体效果是，按顺序测试CASE表达式，并且仅当不满足先前的表达式时才评估子查询。

带有EXISTS子查询的CASE表达式

其中CASE子查询使用EXISTS，逻辑存在测试被实现为半联接，但是如果后面的子句需要它们，则必须保留通常会被半联接拒绝的行。流过这种特殊类型的半联接的行将获得一个标志，以指示半联接是否找到匹配项。该标志被称为探针列。实现的详细信息是，逻辑子查询被探针列的相关联接（“应用”）代替。该工作是通过查询优化器中的简化规则RemoveSubqInPrj（在投影中删除子查询）执行的。我们可以使用跟踪标志8606看到详细信息：

SELECT
    T1.ID,
    CASE
        WHEN EXISTS 
        (
            SELECT 1
            FROM #T2 AS T2
            WHERE T2.ID = T1.ID
        ) THEN 1 
    ELSE 0
    END AS DoesExist
FROM #T1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8606);

输入树的一部分显示了EXISTS测试，如下所示：

ScaOp_Exists 
    LogOp_Project
        LogOp_Select
            LogOp_Get TBL: #T2
            ScaOp_Comp x_cmpEq
                ScaOp_Identifier [T2].ID
                ScaOp_Identifier [T1].ID

通过RemoveSubqInPrj将其转换为以下结构为首的结构：

LogOp_Apply (x_jtLeftSemi probe PROBE:COL: Expr1008)

这是带有上述探针的左半连接应用。到目前为止，此初始转换是SQL Server查询优化器中唯一可用的转换，如果禁用此转换，编译将完全失败。逻辑结构：



最终的Compute Scalar使用探测列值评估CASE表达式的结果：



当优化为半联接考虑其他物理联接类型时，将保留计划树的基本形状。仅合并连接支持探针列，因此，尽管在逻辑上可行，但不考虑哈希半连接：



请注意，合并后会输出一个标记为Expr1008的表达式（名称与之前相同是一个巧合），尽管该计划中的任何运算符都没有定义。这只是探针列。和以前一样，最终的Compute Scalar使用此探测值来评估CASE。问题是优化器无法完全探索只有通过合并（或哈希）半联接才有意义的替代方案。在嵌套循环计划中，检查T2中的行是否与每次迭代的范围匹配没有好处。使用合并或哈希计划，这可能是有用的优化。

如果我们在查询中向BETWEEN添加匹配的T2谓词，则所有操作都是针对每一行作为残差执行此检查在合并半联接上（很难在执行计划中发现，但确实存在）：

SELECT
    T1.ID,
    CASE
        WHEN EXISTS 
        (
            SELECT 1
            FROM #T2 AS T2
            WHERE T2.ID = T1.ID
            AND T2.ID BETWEEN 5000 AND 7000 -- New
        ) THEN 1 
    ELSE 0
    END AS DoesExist
FROM #T1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000;

我们希望相反，将BETWEEN谓词下推至T2导致搜索。通常，优化器会考虑这样做（即使查询中没有多余的谓词）。它可以识别隐含谓词（BETWEEN上的T1以及T1和T2之间的连接谓词一起隐含BETWEEN上的T2），而它们没有出现在原始查询文本中。不幸的是，apply-probe模式意味着没有对此进行探讨。

有一些方法可以编写查询，以在合并半联接的两个输入上生成搜索。一种方法涉及以非常不自然的方式编写查询（击败了我通常偏爱EXISTS的原因）：

WITH T2 AS
(
    SELECT TOP (9223372036854775807) * 
    FROM #T2 AS T2 
    WHERE ID BETWEEN 5000 AND 7000
)
SELECT 
    T1.ID, 
    DoesExist = 
        CASE 
            WHEN EXISTS 
            (
                SELECT * FROM T2 
                WHERE T2.ID = T1.ID
            ) THEN 1 ELSE 0 END
FROM #T1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000;

我不会在生产环境中编写该查询，只是为了证明所需的计划形状是可能的。如果您需要编写的实际查询以这种特殊方式使用CASE，并且由于合并半联接的探针侧未进行查找而导致性能下降，那么您可以考虑使用产生正确结果的不同语法编写查询，并且更有效的执行计划。

#2 楼

“ COUNT（*）vs EXISTS”参数与检查记录是否存在有关。例如：

WHERE (SELECT COUNT(*) FROM Table WHERE ID=@ID)>0

vs

WHERE EXISTS(SELECT ID FROM Table WHERE ID=@ID)

您的SQL脚本不使用COUNT(*)作为记录存在检查，因此我不会说它适用于您的情况。