使用 IN over INNER JOIN 的 SQL 查询优化

鉴于:

表 y

id int clustered index

name nvarchar(25)

表另一个表

id int clustered Index

name nvarchar(25)

表 someFunction

做一些数学运算然后返回一个有效的 ID

比较:

SELECT y.name
  FROM y
 WHERE dbo.SomeFunction(y.id) IN (SELECT anotherTable.id
                                    FROM AnotherTable)

对比:

SELECT y.name
  FROM y
  JOIN AnotherTable ON dbo.SomeFunction(y.id) ON anotherTable.id

问题:

在对这两个查询计时时，我发现在大数据集中，使用 IN 的第一个查询比使用 INNER JOIN 的第二个查询快得多。我不明白为什么有人可以帮忙解释一下。

Execution Plan

最佳答案

一般来说，IN 与 JOIN 的不同之处在于 JOIN 可以返回额外的行，其中一行在 JOIN -ed 表中有多个匹配项。

从您估计的执行计划中可以看出，在这种情况下，2 个查询在语义上是相同的

SELECT
        A.Col1
        ,dbo.Foo(A.Col1)
        ,MAX(A.Col2)
        FROM A
        WHERE dbo.Foo(A.Col1)  IN (SELECT Col1 FROM B)
    GROUP BY
        A.Col1,
        dbo.Foo(A.Col1)

相对

SELECT
        A.Col1
        ,dbo.Foo(A.Col1)
        ,MAX(A.Col2)
        FROM A
        JOIN B ON dbo.Foo(A.Col1) = B.Col1
    GROUP BY
        A.Col1,
        dbo.Foo(A.Col1)

即使 JOIN 引入了重复项，也会被 GROUP BY 删除，因为它只引用左侧表中的列。此外，这些重复的行不会改变结果，因为 MAX(A.Col2) 不会改变。然而，并非所有聚合都如此。如果您要使用 SUM(A.Col2) (或 AVG 或 COUNT )，则重复项的存在会改变结果。

似乎 SQL Server 没有任何逻辑来区分诸如 MAX 之类的聚合和 SUM 之类的聚合，因此很可能它会扩展所有重复项，然后再聚合它们，然后再做更多的工作。

估计聚合的行数是 2893.54 的 IN 与 28271800 的 JOIN 但这些估计不一定非常可靠，因为连接谓词是 unsargable。

关于使用 IN over INNER JOIN 的 SQL 查询优化，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/7741184/