在SQL Server中，INNER JOIN和LEFT JOIN的性能区别

Question

我已经创建了一个SQL命令，使用了9个表格的INNER JOIN联接方式。然而，这个命令执行时间非常长（超过五分钟）。所以我的同事建议我将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

- Anonymous

---

你是否从 coUOM 投影出任何属性？如果没有，你可以使用半连接。如果有，你可以使用 UNION 作为替代方案。只发布你的 FROM 子句在这里是不充分的信息。 - onedaywhen

我经常想知道这个问题（因为我总是看到）。 - Paul Draper

你的简要模式中是否遗漏了 Order By？最近我遇到了一个问题，将 INNER JOIN 更改为 LEFT OUTER JOIN 可以将查询速度从 3 分钟加快到 10 秒。如果你的查询确实有 Order By，我会在回答中进一步解释。看起来所有的答案都没有真正解释我所面临的情况。 - Phuah Yee Keat

Answer 1

LEFT JOIN 并不比 INNER JOIN 更快。事实上，它更慢；按定义，一个外连接 (LEFT JOIN 或 RIGHT JOIN) 需要执行所有的内连接 (INNER JOIN) 的工作以及扩展结果的 null 值。它还预期返回更多行，由于结果集更大，总执行时间进一步增加。

（即使在某些难以想象的因素汇合的特定情况下，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)，而哈希表的时间复杂度为O(1)。

但是，如果将此查询更改为使用ID列而不是Name列，则情况将完全不同。在这种情况下，两个查询都是使用嵌套循环，但INNER JOIN版本能够使用查找替换其中一个聚集索引扫描 - 这意味着当行数很大时，这个查询将比前面提到的情况快上一个数量级。

因此，结论基本上与我在几段文字中提到的一样; 这几乎肯定是一个索引或索引覆盖问题，可能还包括一个或多个非常小的表。这些都是SQL Server可能会在INNER JOIN查询中选择更差的执行计划而不是LEFT JOIN的唯一情况。

- Aaronaught

---

有一种情况可能会导致 OUTER JOIN 比 INNER JOIN 更高效。请参见我的下面回答。 - dbenham

我想指出的是，基本上没有数据库文档支持内连接和外连接性能存在不同的想法。 外连接比内连接略微昂贵，因为数据量和结果集的大小。 但是，两种连接类型的底层算法（http://msdn.microsoft.com/en-us/library/ms191426(v=sql.105).aspx）是相同的。 当它们返回类似数量的数据时，性能应该是相似的。 - Gordon Linoff

@Aaronaught……这个回答被引用在一个评论中，该评论大致说“外连接的性能比内连接差得多”。我发表评论只是为了确保这种误解不会传播。 - Gordon Linoff

我认为这个答案在一个重要方面是误导性的：因为它陈述了“LEFT JOIN绝对不比INNER JOIN快”。 这句话不正确。理论上，LEFT JOIN没有INNER JOIN快。但它并不是“绝对不比INNER JOIN快”。问题专门涉及性能问题。在实践中，我现在见过一些系统（由非常大的公司！）INNER JOIN比OUTER JOIN慢得离谱。 理论和实践是非常不同的。 - David Frenkel

这是相当不可能的。如果你认为存在这样的差异，我会要求看到一份A/B比较报告，并查看执行计划。可能与缓存查询/执行计划或糟糕的统计信息有关。 - Aaronaught

Answer 2

有一种情况可以使外连接比内连接更快，这种情况还没有讨论过。

当使用外连接时，如果连接的列是外部表的主键，并且在外连接本身之外没有引用任何外部表列，则优化器总是可以自由地从执行计划中删除外部连接的表。例如SELECT A.* FROM A LEFT OUTER JOIN B ON A.KEY=B.KEY，其中B.KEY是B的主键。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并期望获得相同的结果。但是，在使用视图时，有时候出于性能原因会有用。

最后一点-在上述情况下，我还没有测试性能的影响，但理论上似乎可以安全地将INNER JOIN替换为OUTER JOIN，如果你也在WHERE子句中添加条件 IS NOT NULL。

- dbenham

---

当我构建极其动态的查询时，我遇到了这个问题。我留下了一个我正在使用但没有获取数据的内部连接，在出于好奇心将其切换为左连接后，查询实际上运行得更快了。 - Erik Philips

编辑 - 澄清优化器从执行计划中删除外连接表所必须存在的条件。 - dbenham

对你的回答进行一个小澄清：当外键列为非空时，INNER JOIN和LEFT JOIN在语义上是等价的（即你建议的WHERE子句是多余的）；唯一的区别在于执行计划。 - Douglas

尽管这个例子看起来微不足道，但这是一个非常有见地的答案！ - pbalaga

我在几个查询中遇到了这种情况，当我使用内连接与一些大表时，内连接会导致查询计划溢出到tempdb（我认为是由于上述原因 - 因为我的服务器缺乏足够的RAM来存储所有内容）。改为左连接消除了对tempdb的溢出，结果是我的一些需要20-30秒才能完成的查询现在只需要几分之一秒就可以运行。这是一个非常重要的提示，因为大多数人似乎都默认内连接更快。 - phosplait

Answer 3

如果一切按照预期进行，那么不应有问题。但我们都知道，当涉及到查询优化器、查询计划缓存和统计信息时，一切并不总是按照预期发生。

首先我建议重建索引和统计信息，然后清除查询计划缓存，只是为了确保这些操作不会影响性能。然而，即使完成了这些操作，我也曾遇到过问题。

我曾遇到过左连接比内连接更快的情况。

根本原因在于：如果你有两个表，它们通过一个带有索引的列进行连接（两个表都有该索引），内连接将产生相同的结果，无论你是否在第一个表中循环遍历索引条目并与第二个表中的索引匹配，还是反之：在第二个表中循环遍历索引条目并与第一个表中的索引匹配。问题在于，如果统计信息不准确，查询优化器将使用索引的统计信息来查找匹配条目最少的表（基于其他标准）。假设你有两个各有100万行的表，在第一个表中有10行匹配，在第二个表中有100,000行匹配。最好的方式是对第一个表进行索引扫描，并在第二个表中进行10次匹配。反之，则需要扫描100,000行并尝试进行100,000次匹配，但只有10次成功。因此，如果统计信息不正确，优化器可能会选择错误的表和索引进行循环遍历。

如果优化器按照写入的顺序优化左连接，则性能将更好。

但是，优化器也可能将左连接子优化为左半连接。为了使其选择你想要的那个选项，可以使用FORCE ORDER提示。

- Kvasi

Answer 4

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

如果INNER JOIN的执行速度与LEFT JOIN一样快，则有以下原因：

• 在完全由INNER JOIN组成的查询中，连接顺序无关紧要。这使查询优化器可以自由地按其认为合适的方式对连接进行排序，因此问题可能取决于优化器。
• 对于LEFT JOIN，情况并非如此，因为更改连接顺序将更改查询的结果。这意味着引擎必须遵循您在查询中提供的连接顺序，这可能比优化后的顺序更好。

我不知道这是否回答了您的问题，但我曾经参与过一个具有高度复杂的计算的项目，这完全混乱了优化器。我们曾经遇到过使用FORCE ORDER可以将查询的执行时间从5分钟降至10秒的情况。

- Francisco Pires

Answer 5

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

比如说你有一个查询涉及到一个由10个表连接而成的视图，但是你的查询只需要用到其中3个表的列。

如果这10个表使用内连接，那么即使你的查询只需要7张表之外的数据，查询优化器也会强制连接它们。因为内连接本身可能会过滤掉数据，所以它们是计算必需的。

如果这10个表使用外连接连接在一起，那么查询优化器只会连接必要的表：在这种情况下是其中的3个表。因为连接本身不再过滤数据，因此未使用的连接可以跳过。

来源： http://www.sqlservercentral.com/blogs/sql_coach/2010/07/29/poor-little-misunderstood-views/

- MarredCheese

---

你关于“外连接”的陈述是误导性的，可能是不正确的。外连接意味着另一侧的数据可能不存在-如果不存在，则用NULL替换。在特定情况下，关系型数据库管理系统可能会“跳过”它们（请参见dbenham的上面的答案）。但是-外部与内部可能会导致查询返回完全不同的结果。内连接意味着给出A和B都存在的项目结果。左外连接意味着所有的A，以及可选的B（如果存在）。第一种情况-你会得到一些行，第二种情况中你会得到所有的行。 - ripvlan

@ripvlan 当然，外连接和内连接并不总是可以互换的。原始问题是关于性能的，这意味着我们正在讨论使用任何一种连接都将返回相同结果集的情况。 - MarredCheese

是的，OUTER可能会导致性能问题，因为它会导致返回所有行（更多数据）。您假设查询结果相同是合理的，但在一般情况下和特定于每个数据库设计时并不正确。对于那些不完全熟悉关系代数的人可能会带来麻烦。我的观点只是为了向阅读此处寻求建议的人提供更多见解，并且LEFT/RIGHT不会神奇地解决问题，反而可能会引起更多问题。这是留给300级别的高手使用的强大工具 :-) - ripvlan

Answer 6

已经对左外连接和内连接进行了多次比较，但没有找到一致的差异。由于有许多变量，我正在处理一个包含成千上万个表格的报告数据库，其中许多表格具有大量字段，并且随着时间推移（供应商版本和本地工作流程）发生了许多更改。无法创建所有组合的覆盖索引来满足如此广泛的查询需求并处理历史数据。我曾经看过内部查询因为两个大型表格（数百万到数千万行）进行内联接并且没有覆盖索引存在而导致服务器性能下降。

然而，最大的问题似乎并未在上述讨论中提及。也许您的数据库已经设计良好，具有触发器和良好设计的事务处理以确保良好的数据。我的数据库经常出现意料之外的NULL值。是的，表格定义可以强制执行非NULL，但在我的环境中不是一个选项。

所以问题是...您是否仅为了速度而设计查询，将事务处理作为更高优先级运行相同代码数千次。还是您要追求左外连接提供的准确性？请记住，内连接必须在两侧找到匹配项，因此意外的NULL不仅会从两个表格中删除数据，而且可能会删除整行信息。这样做得很好，没有错误消息。

您可以非常快地获取所需数据的90％，但未发现内部连接已默默删除信息。有时内部连接可能更快，但我认为除非他们已经审查了执行计划，否则不会有人那么假设。速度很重要，但准确性更重要。

- J.O.

Answer 7

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

最坏情况下，每次连接都可能轻松执行9个整个表扫描。

- Eddie Groves

Answer 8

在比较内连接和左连接的速度时，我在SQL Server中发现了一些有趣的事情。

如果在select语句中不包括左连接表的项，则左连接比具有相同查询的内连接更快。

如果在select语句中包括左连接表，那么具有相同查询的内连接将等于或快于左连接。

- Buzzzzzzz

Answer 9

从我的比较中，我发现它们具有完全相同的执行计划。有三种情况：

1. 如果它们返回相同的结果，则速度相同。但是，我们必须记住它们不是相同的查询，并且LEFT JOIN可能会返回更多的结果（当一些ON条件未满足时）---这就是为什么它通常较慢的原因。

2. 当主表（执行计划中第一个非const表）具有限制条件（WHERE id =？），并且相应的ON条件为NULL值时，“右”表不会被连接---这时LEFT JOIN更快。

3. 如点1所述，通常INNER JOIN更加严格，返回的结果更少，因此更快。

两者都使用（相同的）索引。

- Jiulin Teng