首页 文章

SQL Server中的INNER JOIN与LEFT JOIN性能

提问于
浏览 次
220

我已经为9个表创建了使用INNER JOIN的SQL命令，无论如何这个命令需要很长时间（超过五分钟） . 因此我的民众建议我将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 等等

Update: 这是我的架构的简要介绍 .

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
sql sql-server performance

8 回答

  • 100

    LEFT JOIN 绝对不比 INNER JOIN 快 . 事实上，它更慢;根据定义，外连接（ LEFT JOIN 或 RIGHT JOIN ）必须完成 INNER JOIN 的所有工作以及对结果进行空值扩展的额外工作 . 还期望返回更多行，仅仅由于结果集的较大尺寸而进一步增加总执行时间 .

    （即使 LEFT JOIN 由于一些难以想象的因素汇合而在特定情况下更快，但它在功能上并不等同于 INNER JOIN ，因此您不能简单地将一个实例更换为另一个！）

    很可能您的性能问题存在于其他地方，例如没有正确的候选键或外键索引 . 9桌是很多加入，所以减速几乎可以在任何地方 . 如果您发布架构，我们可能会提供更多详细信息 .

    Edit:

    为了进一步反思这一点，我可以想到一个情况， 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 更差的执行计划的唯一情况 .

    回复于
  • 16

    有一个重要方案可能导致外连接比尚未讨论的内连接更快 .

    使用外部联接时，如果连接列是外部表的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上执行内部连接 .

    这是一个简单的示例，对于独立查询不实用 . 如果你不需要，为什么要加入一张 table 呢？

    但在设计视图时，这可能是一个非常重要的设计考虑因素 . 通常会构建一个“do-everything”视图，它将用户可能需要的所有内容与中央表相关联 . （特别是如果有天真的用户正在做ad-hoc查询不理解关系模型）视图可能包含许多表中的所有相关列 . 但最终用户可能只访问视图中表的子集中的列 . 如果表与外连接连接，则优化器可以（并且确实）从计划中删除不需要的表 .

    确保使用外部联接的视图提供正确的结果至关重要 . 正如Aaronaught所说 - 你不能盲目地将OUTER JOIN替换为INNER JOIN并期望得到相同的结果 . 但有时候，在使用视图时，出于性能原因，这可能很有用 .

    最后一点 - 我没有根据上述情况测试对性能的影响，但理论上，如果你还添加条件<FOREIGN_KEY> IS NOT NULL，你似乎应该可以安全地用OUTER JOIN替换INNER JOIN . 到where子句 .

    回复于
  • 7

    如果一切正常，它应该不应该，但是我们都知道一切都不会以它应该的方式工作，尤其是在查询优化器，查询计划缓存和统计时 .

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

    我遇到过一些左连接比内连接快的情况 .

    根本原因是：如果你有两个表，并且你加入了一个带索引的列（在两个表上） . 无论你是否在第一表上的索引中循环并与第二表上的索引匹配，内连接都会产生相同的结果，就好像你会反过来一样：循环表二中的索引中的条目并与索引匹配在表一 . 问题是当您有误导性统计信息时，查询优化器将使用索引的统计信息来查找具有最少匹配条目的表（基于您的其他条件） . 如果你有两个表，每个表有100万个，在表1中你有10行匹配，在表2中你有100000行匹配 . 最好的方法是在表1上进行索引扫描，在表2中进行10次匹配 . 反过来的是一个索引扫描，它循环超过100000行，并尝试匹配100000次，只有10次成功 . 因此，如果统计信息不正确，优化器可能会选择错误的表和索引进行循环 .

    如果优化器选择按写入顺序优化左连接，则它将比内连接执行得更好 .

    但是，优化器还可以优化地将左连接优化为左半连接 . 要使其选择您想要的那个，您可以使用强制订单提示 .

    回复于
  • 1

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

    如果 INNER JOIN 开始执行速度与 LEFT JOIN 一样快，那是因为：

    • 在完全由 INNER JOIN 组成的查询中，连接顺序无关紧要 . 这使查询优化器可以自由地按照它认为合适的顺序对连接进行排序，因此问题可能依赖于优化器 .

    • 使用 LEFT JOIN ，情况并非如此，因为更改连接顺序将改变查询结果 . 这意味着引擎必须遵循您在查询中提供的连接顺序，这可能优于优化的连接顺序 .

    不知道这是否能回答你的问题，但我曾经参与过一个项目，该项目包含高度复杂的查询计算，这完全搞砸了优化器 . 我们遇到的情况是 FORCE ORDER 会将查询的执行时间从5分钟减少到10秒 .

    回复于
  • 360

    在左外连接和内连接之间进行了多次比较，但未能找到一致的差异 . 有很多变数 . 我正在处理一个报告数据库，其中有数千个表，其中包含大量字段，随着时间的推移会有很多变化（供应商版本和本地工作流程） . 无法创建覆盖索引的所有组合以满足各种查询和处理历史数据的需要 . 已经看到内部查询会导致服务器性能下降，因为两个大的（数百万到数千万行）表都是内部连接，同时拉动了大量字段并且不存在覆盖索引 .

    但是，最大的问题似乎并未出现在上述讨论中 . 也许你的数据库设计精良，有触发器和精心设计的事务处理，以确保良好的数据 . 我经常有NULL值，它们不是预期的 . 是的，表定义可以强制执行no-Null，但这不是我环境中的选项 .

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

    您可以非常快速地获取90％的所需数据，而不是发现内部联接已经默默地删除了信息 . 有时内连接可能会更快，但我不相信任何人做出这种假设，除非他们已经审查了执行计划 . 速度很重要，但准确性更重要 .

    回复于
  • 4

    您的性能问题更可能是因为您正在进行的连接数以及您加入的列是否具有索引 .

    最糟糕的情况是，您可以轻松地为每个连接执行9次全表扫描 .

    回复于
  • 5

    在视图中使用时，外连接可提供卓越的性能 .

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

    如果这10个表一起是 inner-joined ，那么查询优化器就必须将它们全部连接起来，即使你的查询本身没有't need 7 out of 10 of the tables. That'，因为内部连接本身可能会过滤掉数据，使它们成为计算必不可少的 .

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

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

    回复于
  • 20

    在检查内部联接是否比左联接更快时，我在SQL服务器中发现了一些有趣的东西 .

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

    如果确实在select语句中包含左连接表，则具有相同查询的内连接等于或快于左连接 .

    回复于

相关问题