sql - 有什么更好的选择-SELECT TOP(1)或INNER JOIN？

Question

假设我有以下查询:

SELECT Id, Name, ForeignKeyId, 
(SELECT TOP (1) FtName FROM ForeignTable WHERE FtId = ForeignKeyId) 
FROM Table

如果使用JOIN编写查询，该查询的执行速度会更快吗？

SELECT Id, Name, ForeignKeyId, FtName
FROM Table t
LEFT OUTER JOIN ForeignTable ft
ON ft.FtId = t.ForeignTableIf

只是好奇...而且，如果JOIN的速度更快，在所有情况下(具有很多列，大量行的表)它还会更快吗？

编辑:我编写的查询仅用于说明TOP(1)vs JOIN的概念。是的-我知道SQL Server中的查询执行计划，但是我不打算优化单个查询-我试图了解SELECT TOP(1)vs JOIN之后是否存在某些理论，并且由于速度原因是否首选某些方法(不是出于个人喜好或可读性)。

编辑2:我要感谢Aaron的详细回答，并鼓励人们检查他在回答中提到的公司的 SQL Sentry Plan Explorer免费工具。

Answer 1

最初，我写道:

The first version of the query is MUCH less readable to me. Especially since you don't bother aliasing the matched column inside the correlated subquery. JOINs are much clearer.

我仍然相信并坚持这些声明，但是我想根据添加到问题中的新信息添加到我的原始回复中。您问过，是否有关于最佳性能的一般规则或理论(TOP(1)或JOIN，不考虑可读性和偏好)？我将重申，不，没有一般性规则或理论。当您有一个特定的示例时，很容易证明哪种方法更好。让我们来看看这两个查询，它们与您的查询类似，但是它们针对我们都可以验证的系统对象运行:

-- query 1:

SELECT name,
   (SELECT TOP (1) [object_id] 
        FROM sys.all_sql_modules 
        WHERE [object_id] = o.[object_id]
   )
FROM sys.all_objects AS o;

-- query 2:

SELECT o.name, m.[object_id]
    FROM sys.all_objects AS o
    LEFT OUTER JOIN sys.all_sql_modules AS m
    ON o.[object_id] = m.[object_id];

这些返回的结果完全相同(我的系统上为3179行)，但是我的意思是相同的数据和相同的行数。一个线索，他们不是真正地相同的查询(或至少不遵循相同的执行计划)，是结果以不同的顺序返回。虽然我不希望维护或遵守某个顺序，因为我没有在任何地方包含 ORDER BY，但我希望SQL Server如果实际上使用相同的计划，则选择相同的顺序。

但事实并非如此。我们可以通过检查计划并进行比较来看到这一点。在这种情况下，我将使用 SQL Sentry Plan Explorer，这是我公司的免费执行计划分析工具-您可以从Management Studio中获得一些此类信息，但在计划资源管理器中更容易获得其他部分(例如实际工期和CPU) 。最上面的计划是子查询版本，最下面的计划是联接。同样，子查询在顶部，联接在底部:

[ click for full size]

[ click for full size]

实际的执行计划:运行两个查询的总成本的85％在子查询版本中。这意味着它的价格是联接的5倍以上。子查询版本的CPU和I/O都更高-查看所有这些读取! 6600多个页面返回约3,000行，而联接版本使用更少的I/O(仅110页)返回数据。

但为什么？因为子查询版本在本质上类似于标量函数，所以您要从另一个表中获取TOP匹配行，但要对原始查询中的每一行都执行此操作。通过查看“Top Operations”选项卡，我们可以看到该操作发生了3179次，该选项卡显示了每个操作的执行次数。再次，更昂贵的子查询版本位于最上面，联接版本如下:



我将为您提供更详尽的分析，但总的来说，优化器知道它在做什么。说明您的意图(在这些表之间进行这种连接)，并在99％的时间中自行说明实现此目的的最佳基础方法是什么(例如执行计划)。如果您试图使智能优化器不至于智能化，请记住您正在冒险进入相当先进的领域。

每个规则都有异常(exception)，但是在这种特定情况下，子查询绝对不是一个好主意。这是否意味着在第一个查询中建议的语法始终是个坏主意？绝对不。在某些情况下，子查询版本和联接一样有效。我不能认为有很多子查询可以更好地工作的地方。因此，我会选择更易读的 和更易读的来代替。即使您发现子查询版本更具可读性，我也认为它没有任何优势，因为它很可能会导致性能下降。

通常，我强烈建议您坚持使用更具可读性的自文档语法，除非您发现优化器未正确执行的情况(我敢打赌，其中99％的情况是统计数据错误或参数嗅探问题，而不是查询语法问题)。我会怀疑，在这些情况之外，您可以重现那些在比复杂的查询更直接和更合理的查询效果更好的情况下所产生的再现性。您试图找到这些情况的动机应与您对非直观语法的偏爱相比，对一般公认的“最佳实践”语法的偏爱相同。