使用 IN 而非 INNER JOIN 的 Sql 查询优化

Question

给定:

表 y

• id int 聚集索引
• name nvarchar(25)

表另一个表

• id int 聚集索引
• 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

Answer 1

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

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

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 之类的聚合，因此很可能它正在扩展所有复制然后在以后聚合它们，只是做更多的工作。

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