LEFT JOIN比INNER JOIN快得多

Question

我有一个表格（MainTable），其中包含超过600,000条记录。它通过第二个表格（JoinTable）以父/子类型关系连接到自己：

SELECT   Child.ID, Parent.ID
FROM     MainTable
AS       Child
JOIN     JoinTable
      ON Child.ID = JoinTable.ID
JOIN     MainTable
AS       Parent
      ON Parent.ID = JoinTable.ParentID
     AND Parent.SomeOtherData = Child.SomeOtherData

我知道每个子记录都有一个父记录，JoinTable中的数据是准确的。

当我运行这个查询时，需要几分钟才能完成。但是如果我使用左连接加入Parent，则只需要不到1秒钟即可完成：

SELECT   Child.ID, Parent.ID
FROM     MainTable
AS       Child
JOIN     JoinTable
      ON Child.ID = JoinTable.ID
LEFT JOIN MainTable
AS       Parent
      ON Parent.ID = JoinTable.ParentID
     AND Parent.SomeOtherData = Child.SomeOtherData
WHERE    ...[some info to make sure we don't select parent records in the child dataset]...

我理解 INNER JOIN 和 LEFT JOIN 结果之间的区别。在这种情况下，返回的结果与每个子项都有父项相同。如果让两个查询都运行，可以比较数据集，它们完全相同。

为什么 LEFT JOIN 运行速度比 INNER JOIN 快那么多？

---

更新： 检查了查询计划，使用内连接时它从父数据集开始。当使用左连接时，它从子数据集开始。

使用的索引都是相同的。

我能否强制它始终从子数据集开始？使用左连接可以解决问题，但感觉不太对。

---

之前这里有类似的问题，但似乎没有回答我的问题。

例如，在INNER JOIN vs LEFT JOIN performance in SQL Server中所选的答案指出，左连接总是比内连接慢。这个论点很有道理，但它并不符合我的观察结果。

- Greg

Answer 1

左连接看起来更快，因为SQL被迫先做较小的选择，然后再加入这个较小的记录集。由于某种原因，优化器不自然地想要这样做。

3种强制使连接按正确顺序进行的方法:

1. 将第一个子集数据选择到临时表（或表变量）中，然后在其上进行连接
2. 使用左连接（记住，这可能返回不同的数据，因为它是左连接而不是内连接）
3. 使用FORCE ORDER关键字。请注意，如果表大小或模式更改，则查询计划可能不正确（请参见https://dba.stackexchange.com/questions/45388/forcing-join-order）

- Greg

---

我知道现在可能有点晚了，但你可能想确保你的数据库统计信息是最新的。如果查询优化器不知道相关表的大小和连接列中的值分布情况，它可能会做出错误的查询计划决策（SQL Server 的查询优化器在我使用的所有数据库中都表现得最为一致）。以下是一个关于此主题的博客文章：http://blogs.msdn.com/b/buckwoody/archive/2009/08/18/sql-server-best-practices-auto-create-and-auto-update-statistics-should-be-on-most-of-the-time.aspx - Curt

我知道现在有点晚了，但可能对其他人有帮助。我唯一不同意的是关于表变量建议的部分。无论表中有多少行，表变量始终返回一个估计行数为1的值。这可能会极大地扭曲计划。请阅读此文章http://blogs.msdn.com/b/psssql/archive/2014/08/11/if-you-have-queries-that-use-table-variables-sql-server-2012-sp2-can-help.aspx，然而，在2012 SP2中有一个跟踪标志可以帮助。 - Eugene Niemand

LEFT JOIN 并不比 INNER JOIN 更快。事实上，它更慢；按定义，外连接（LEFT JOIN 或 RIGHT JOIN）必须完成所有内连接的工作加上扩展结果的额外工作。由于返回的行数更多，预计也会增加总执行时间，仅因结果集大小更大。 - Shafizadeh

@Sajad，我理解你的意思，但正如我在这个答案中提到的那样，连接的顺序在查询计划中发生了变化，这将查询运行时间从几分钟缩短到亚秒级别。左连接意味着它会在选择内部连接和（可能的话）过滤之后执行。 - Greg

你的最后一句话对我来说有点奇怪，我不知道，也许你是对的，但我认为“内连接”意味着在选择“左连接”并且（如果可能）过滤后执行。 - Shafizadeh

Answer 2

试试这个。同样的结果，不同的方法：

SELECT c.ID, p.ID 
FROM
(SELECT   Child.ID, JoinTable.ParentID
FROM     MainTable
AS       Child
JOIN     JoinTable
      ON Child.ID = JoinTable.ID) AS c
INNER JOIN 
(SELECT   Parent.ID, JoinTable.ID
FROM     MainTable
AS       Parent
JOIN     JoinTable
      ON Parent.ID = JoinTable.ParentID
     AND Parent.SomeOtherData = Child.SomeOtherData) AS p
ON c.ParentID = p.ID

如果这没起到作用，使用cte:

;WITH cte AS
(SELECT   Child.ID, JoinTable.ParentID
FROM     MainTable
AS       Child
JOIN     JoinTable
      ON Child.ID = JoinTable.ID)
SELECT cte.ID, Parent.ID
FROM cte INNER JOIN 
MainTable
AS       Parent
      ON Parent.ID = cte.ParentID
     AND Parent.SomeOtherData = cte.SomeOtherData

- cha

---

CTE似乎没有帮助，但将其强制转换为表变量可以解决问题。如果没有其他答案出现，那我就采用这种方法。 - Greg