Sql查询优化使用IN over INNER JOIN

Question

给定：

表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)

vs：

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

问题：

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

执行计划

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，但这些估计不一定非常可靠，因为联接谓词是不可聚合的。

Answer 2

您的第二个查询有点有趣——您能试试这个吗？？

SELECT y.name 
FROM dbo.y
INNER JOIN dbo.AnotherTable a ON a.id = dbo.SomeFunction(y.id) 

这有什么区别吗？

否则：看看执行计划！并可能将它们张贴在这里。如果不了解更多关于表(数据量和分布等(和系统(RAM、磁盘等(的信息，就很难给出一个"全局"有效的语句

Answer 3

首先：去掉dbo.SomeFunction(y.id)所隐含的标量UDF。那会让你的表现大打折扣。即使用单行内联表值函数替换它，效果也会更好。

至于你的实际问题，我在其他情况下也发现了类似的结果，也同样感到困惑。优化器只是以不同的方式对待它们；我很想看看其他人能提供什么答案。