内连接vs左连接性能在SQL Server

我创建了一个SQL命令，在9个表上使用INNER JOIN，无论如何这个命令需要很长时间(超过5分钟)。所以我的朋友建议我把INNER JOIN改为LEFT JOIN，因为LEFT JOIN的性能更好，尽管我知道。经过我的修改，查询的速度得到了显著的提高。

我想知道为什么LEFT JOIN比INNER JOIN快?

我的SQL命令如下所示: SELECT * FROM A INNER JOIN B ON ... INNER JOIN C ON ... INNER JOIN D等等

< >强更新:

.

. 
FROM sidisaleshdrmly a -- NOT HAVE PK AND FK
INNER JOIN sidisalesdetmly b -- THIS TABLE ALSO HAVE NO PK AND FK
ON a.CompanyCd = b.CompanyCd
AND a.SPRNo = b.SPRNo
AND a.SuffixNo = b.SuffixNo
AND a.dnno = b.dnno
INNER JOIN exFSlipDet h -- PK = CompanyCd, FSlipNo, FSlipSuffix, FSlipLine
ON a.CompanyCd = h.CompanyCd
AND a.sprno = h.AcctSPRNo
INNER JOIN exFSlipHdr c -- PK = CompanyCd, FSlipNo, FSlipSuffix
ON c.CompanyCd = h.CompanyCd
AND c.FSlipNo = h.FSlipNo
AND c.FSlipSuffix = h.FSlipSuffix
INNER JOIN coMappingExpParty d -- NO PK AND FK
ON c.CompanyCd = d.CompanyCd
AND c.CountryCd = d.CountryCd
INNER JOIN coProduct e -- PK = CompanyCd, ProductSalesCd
ON b.CompanyCd = e.CompanyCd
AND b.ProductSalesCd = e.ProductSalesCd
LEFT JOIN coUOM i -- PK = UOMId
ON h.UOMId = i.UOMId
INNER JOIN coProductOldInformation j -- PK = CompanyCd, BFStatus, SpecCd
ON a.CompanyCd = j.CompanyCd
AND b.BFStatus = j.BFStatus
AND b.ProductSalesCd = j.ProductSalesCd
INNER JOIN coProductGroup1 g1 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup1Cd
ON e.ProductGroup1Cd  = g1.ProductGroup1Cd
INNER JOIN coProductGroup2 g2 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup2Cd
ON e.ProductGroup1Cd  = g2.ProductGroup1Cd
401653 次浏览
小开
最佳答案

LEFT JOIN绝对不会比INNER JOIN快。事实上，它更慢;根据定义，外部连接(LEFT JOIN或RIGHT JOIN)必须完成INNER JOIN的所有工作，加上对结果进行空扩展的额外工作。由于结果集的大小更大，它还可能返回更多的行，从而进一步增加总执行时间。

(即使由于一些难以想象的因素的汇合，LEFT JOIN 是在具体的情况下更快，它也不等同于INNER JOIN，所以你不能简单地用一个实例替换另一个实例!)

您的性能问题很可能存在于其他地方，比如没有正确索引候选键或外键。9张桌子是相当多的加入，所以放缓可能几乎在任何地方。如果你发布你的模式，我们可能会提供更多的细节。

编辑:

进一步思考这个问题，我可以想到一种情况，在这种情况下，LEFT JOIN可能比INNER JOIN更快，那就是:

  • 一些表是非常小的(比如，小于10行);
  • 表没有足够的索引来覆盖查询。

想想这个例子:

CREATE TABLE #Test1
(
ID int NOT NULL PRIMARY KEY,
Name varchar(50) NOT NULL
)
INSERT #Test1 (ID, Name) VALUES (1, 'One')
INSERT #Test1 (ID, Name) VALUES (2, 'Two')
INSERT #Test1 (ID, Name) VALUES (3, 'Three')
INSERT #Test1 (ID, Name) VALUES (4, 'Four')
INSERT #Test1 (ID, Name) VALUES (5, 'Five')


CREATE TABLE #Test2
(
ID int NOT NULL PRIMARY KEY,
Name varchar(50) NOT NULL
)
INSERT #Test2 (ID, Name) VALUES (1, 'One')
INSERT #Test2 (ID, Name) VALUES (2, 'Two')
INSERT #Test2 (ID, Name) VALUES (3, 'Three')
INSERT #Test2 (ID, Name) VALUES (4, 'Four')
INSERT #Test2 (ID, Name) VALUES (5, 'Five')


SELECT *
FROM #Test1 t1
INNER JOIN #Test2 t2
ON t2.Name = t1.Name


SELECT *
FROM #Test1 t1
LEFT JOIN #Test2 t2
ON t2.Name = t1.Name


DROP TABLE #Test1
DROP TABLE #Test2

如果你运行它并查看执行计划，你会发现INNER JOIN查询的开销确实比LEFT JOIN要高，因为它满足上面的两个条件。这是因为SQL Server想要对INNER JOIN进行哈希匹配，但对LEFT JOIN进行嵌套循环;前者是正常情况下快得多，但由于行数是如此之少，而且没有索引可以使用，哈希操作结果是查询中最昂贵的部分。

你可以用你最喜欢的编程语言编写一个程序，对一个只有5个元素的列表执行大量的查找，而不是对一个只有5个元素的哈希表执行大量的查找，从而看到同样的效果。由于大小的原因，哈希表版本实际上更慢。但是将其增加到50个元素，或者5000个元素，列表版本就会变慢，因为哈希表是O(N) vs. O(1)。

但是将这个查询更改为ID列而不是Name列，你会看到一个非常不同的故事。在这种情况下，它为两个查询都做了嵌套循环，但INNER JOIN版本能够用seek替换其中一个聚集索引扫描—这意味着这将在大量行的情况下更快数量级。

结论和我前面几段提到的差不多;这几乎肯定是一个索引或索引覆盖问题，可能与一个或多个非常小的表结合在一起。在这些情况下，SQL Server 可能有时会为INNER JOIN选择比LEFT JOIN更糟糕的执行计划。

小开

您的性能问题更有可能是由于您正在执行的连接数量以及您正在连接的列是否具有索引。

在最坏的情况下，您可能很容易为每个连接执行9个整表扫描。

小开

如果一切都按照它应该的方式工作，那就不应该，但是我们都知道一切都不是按照它应该的方式工作，特别是当涉及到查询优化器、查询计划缓存和统计时。

首先，我建议重新构建索引和统计数据，然后清除查询计划缓存，以确保不会搞砸事情。然而，即使这样做了，我也遇到了问题。

我经历过一些左连接比内连接更快的情况。

深层原因是: 如果你有两个表，你用一个索引连接一个列(在两个表上)。 无论您是否循环表1上索引中的条目并与表2上的索引匹配，内部连接都将产生相同的结果，就像您做相反的事情一样:循环表2上索引中的条目并与表1中的索引匹配。 问题是，当您有误导性的统计数据时，查询优化器将使用索引的统计数据来查找匹配项最少的表(基于您的其他条件)。 如果有两个表，每个表都有100万行，在表1中有10行匹配，在表2中有100000行匹配。最好的方法是对表1进行索引扫描，并在表2中进行10次匹配。相反，索引扫描将循环100000行，并尝试匹配100000次，但只有10次成功。因此，如果统计数据不正确，优化器可能会选择错误的表和索引进行循环

如果优化器选择按照左连接的编写顺序优化它，那么它将比内部连接执行得更好。

但是，优化器也可以将左连接次优化为左半连接。要让它选择你想要的，你可以使用强制顺序提示。

小开

有一种重要的场景可能导致外部连接比内部连接更快，但我们还没有讨论过。

当使用外部连接时，如果连接列是外部表的PK，并且外部表列没有在外部连接本身之外被引用，优化器总是可以从执行计划中删除外部连接表。例如，SELECT A.* FROM A LEFT OUTER JOIN B ON A.KEY=B.KEY和B. key是B.的PK。Oracle(我相信我使用的是10版)和Sql Server(我使用的是2008 R2)都从执行计划中删除表B。

对于内部连接不一定是这样:SELECT A.* FROM A INNER JOIN B ON A.KEY=B.KEY在执行计划中可能需要也可能不需要B，这取决于存在什么约束。

如果a . key是一个引用B. key的可空外键，那么优化器不能从计划中删除B，因为它必须确认每个a行都存在一个B行。

如果a . key是引用B. key的强制外键，那么优化器可以从计划中删除B，因为约束保证了行的存在。但是仅仅因为优化器可以从计划中删除表，并不意味着它会这样做。SQL Server 2008 R2不会将B从计划中删除。Oracle 10将B从计划中删除。在这种情况下，很容易看出SQL Server上的外部连接如何优于内部连接。

这是一个简单的示例，对于独立查询来说并不实际。如果不需要，为什么要加入一个表?

但在设计视图时，这可能是一个非常重要的设计考虑因素。通常会构建一个“万能”视图，将用户可能需要的与中心表相关的所有内容连接起来。(特别是当有不了解关系模型的幼稚用户在执行特别查询时)视图可能包括许多表中的所有相关列。但是最终用户可能只能访问视图中表的子集中的列。如果表与外部连接连接，那么优化器可以(并且确实)从计划中删除不需要的表。

确保使用外部连接的视图给出正确的结果是至关重要的。正如Aaronaught所说，你不能盲目地用OUTER JOIN代替INNER JOIN，并期望得到相同的结果。但在使用视图时，有时出于性能考虑，它可能很有用。

最后一个注意事项-我还没有测试对性能的影响，但在理论上，如果你还添加条件<FOREIGN_KEY> IS NOT NULL到where子句，你应该可以安全地用OUTER JOIN替换INNER JOIN。

小开

在两个查询(内连接和左连接的查询)的末尾尝试OPTION (FORCE ORDER)并发布结果。OPTION (FORCE ORDER)是一个查询提示，它强制优化器使用您在查询中提供的连接顺序构建执行计划。

如果INNER JOIN开始和LEFT JOIN一样快，这是因为:

  • 在完全由__abc0组成的查询中，连接顺序并不重要。这给了查询优化器以它认为合适的顺序排列连接的自由，因此问题可能依赖于优化器。
  • 对于LEFT JOIN，情况并非如此，因为改变连接顺序将改变查询的结果。这意味着引擎必须遵循您在查询中提供的连接顺序，这可能比优化后的顺序更好。

不知道这是否回答了你的问题，但我曾经在一个项目中，具有高度复杂的查询进行计算，这完全搞砸了优化器。在一些情况下，FORCE ORDER会将查询的执行时间从5分钟减少到10秒。

小开

在左外连接和内连接之间做了大量的比较，并没有找到一致的差异。有很多变量。我工作在一个报告数据库与成千上万的表，许多与大量的字段，许多变化随着时间的推移(供应商版本和本地工作流)。创建覆盖索引的所有组合来满足如此广泛的查询需求并处理历史数据是不可能的。曾经有过内部查询导致服务器性能下降的情况，因为两个大的(数百万到数千万行的)表是内部连接的，它们都拉出大量的字段，而且不存在覆盖索引。

但最大的问题似乎并没有出现在上面的讨论中。也许您的数据库设计良好，具有触发器和设计良好的事务处理，以确保良好的数据。我的经常在不期望的地方有NULL值。是的，表定义可以强制no- null，但在我的环境中，这不是一个选项。

所以问题是…您设计查询时是否只考虑速度?对于每分钟运行相同代码数千次的事务处理来说，这是一个更高的优先级。或者你追求左外连接所提供的准确性。请记住，内部连接必须在两边都找到匹配项，因此意外的NULL不仅会从两个表中删除数据，而且可能会删除整行信息。它发生得很好，没有错误消息。

您可以非常快速地获得所需数据的90%，而不会发现内部连接已经无声地删除了信息。有时内部连接可以更快，但我不相信任何人做出这样的假设，除非他们已经审查了执行计划。速度固然重要，但准确更重要。

小开

外部连接在视图中使用时可以提供更好的性能。

假设您有一个涉及视图的查询，该视图由10个表连接在一起组成。假设您的查询恰好只使用这10个表中的3个表中的列。

如果这10个表是inner-joined在一起，那么查询优化器将不得不将它们全部连接起来，即使你的查询本身不需要10个表中的7个。这是因为内部连接本身可能会过滤数据，使它们成为计算的必要条件。

如果这10个表是outer-joined，那么查询优化器实际上只会连接必要的表:在这种情况下，10个表中有3个。这是因为连接本身不再过滤数据，因此可以跳过未使用的连接。

< p >来源: http://www.sqlservercentral.com/blogs/sql_coach/2010/07/29/poor-little-misunderstood-views/ < / p >
小开

我在SQL server中发现了一些有趣的东西，当检查内部连接是否比左连接更快时。

如果你不包括左连接表的项，在选择语句中，左连接将比使用内连接的相同查询更快。

如果在选择语句中包含左连接表，则具有相同查询的内部连接与左连接相同或更快。

小开

通过比较，我发现他们有完全相同的执行计划。有三种情况:

  1. 如果它们返回相同的结果，那么它们的速度是相同的。但是，我们必须记住，它们不是相同的查询，并且LEFT JOIN可能会返回更多的结果(当某些ON条件不满足时)——这就是为什么它通常比较慢。

  2. 当主表(执行计划中的第一个非const表)有一个限制条件(WHERE id = ?)并且对应的ON条件在一个NULL值上时，“右”表不被连接——这是LEFT JOIN更快的时候。

  3. 正如第1点所讨论的，通常INNER JOIN的限制更大，返回的结果更少，因此更快。

两者都使用(相同的)索引。


提交答案