代码之家 › 专栏 › 技术社区 › David

sql server中的内部连接与左连接性能

performance sql-server sql

228

David · 技术社区 · 14 年前

我已经为9个表创建了使用内部连接的sql命令,但是这个命令需要很长的时间(超过5分钟)。所以我的人建议我将内部连接改为左连接,因为左连接的性能更好,尽管我知道这是第一次。修改后,查询速度有了明显的提高。

我想知道为什么左连接比内连接快?

我的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

8 回复 | 直到 6 年前

368

Aaronaught 14 年前

一 LEFT JOIN 绝对不比 INNER JOIN . 实际上,它比较慢;根据定义,外部连接( 左连接 或 RIGHT JOIN )必须做所有的工作 内连接 加上空扩展结果的额外工作。它还将返回更多的行,进一步增加总执行时间,这仅仅是因为结果集的大小更大。

(即使 左连接 是快于 具体的 由于一些难以想象的因素的汇合,它在功能上并不等同于 内连接 ,因此不能简单地将一个实例的所有实例替换为另一个实例!)

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

编辑:

再仔细想想,我可以想到一种情况,在这种情况下 左连接 可能比 内连接 ,此时:

有些桌子是非常小的(比如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

如果运行此命令并查看执行计划,您将看到 内连接 查询确实比 左连接 ,因为它满足上述两个条件。这是因为SQL Server希望对 内连接 ,但对 左连接 ;前者是 正常地 更快,但是因为行数太少了和没有要使用的索引,哈希操作是查询中最昂贵的部分。

你可以看到同样的效果,用你最喜欢的编程语言编写一个程序,在一个包含5个元素的列表上执行大量的查找,而不是一个包含5个元素的哈希表。由于大小的关系,哈希表的版本实际上较慢。但是增加到50个元素,或者5000个元素,列表版本就会变慢,因为哈希表是o(n)对o(1)。

但将此查询更改为 ID 列而不是 Name 你会看到一个完全不同的故事。在这种情况下,它对两个查询都执行嵌套循环,但是 内连接 版本能够用seek替换其中一个聚集索引扫描,这意味着 数量级 行数较多,速度较快。

因此,结论或多或少是我在上面提到的;这几乎肯定是一个索引或索引覆盖率问题,可能与一个或多个非常小的表结合在一起。在这些情况下,SQL Server 可以有时选择一个更糟糕的执行计划 内连接 比A 左连接 .

104

dbenham 12 年前

有一个重要的场景可以导致外部连接比内部连接更快,而内部连接尚未讨论。

使用外部联接时,如果联接列是外部表的主键,并且没有从外部表中选择任何列,优化器总是可以从执行计划中删除外部联接表。例如 SELECT A.* FROM A LEFT OUTER JOIN B ON A.KEY=B.KEY 而b.key是b的pk,oracle(我相信我使用的是release 10)和sql server(我使用的是2008r2)都从执行计划中删除表b。

对于内部连接,不一定也是这样: SELECT A.* FROM A INNER JOIN B ON A.KEY=B.KEY 根据存在的约束条件,可能需要也可能不需要执行计划中的b。

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

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

这是一个很小的示例,对于独立查询不实用。如果你不需要的话,为什么要加入一张桌子?

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

确保使用外部连接的视图给出正确的结果是至关重要的。正如aaronaught所说-你不能盲目地用外部连接代替内部连接,并期望得到相同的结果。但有时在使用视图时,由于性能原因,它会很有用。

最后一个注意事项-我没有根据上述内容测试对性能的影响,但在理论上,如果您还将条件“foreign_key>不为空”添加到where子句中,则应该能够安全地将内部联接替换为外部联接。

Kvasi 11 年前

如果每件事都能正常工作,那么它就不应该正常工作,但是我们都知道每件事都不能正常工作,尤其是在查询优化器、查询计划缓存和统计方面。

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

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

根本原因是: 如果您有两个表,并且您使用索引(在两个表上)连接到一列上。无论您是否循环遍历表1上的索引项并与表2上的索引匹配,内部联接都将产生相同的结果,就像您执行相反的操作:循环遍历表2上的索引项并与表1中的索引匹配。问题是当您有误导性的统计信息时,查询优化器将使用索引的统计信息来查找具有最少匹配项的表(基于您的其他条件)。如果有两个表,每个表中有100万行,那么在表1中有10行匹配,在表2中有10万行匹配。最好的方法是对表1进行索引扫描,并在表2中匹配10次。相反,索引扫描会循环超过100000行,并尝试匹配100000次,但只有10次成功。因此,如果统计数据不正确,优化器可能会选择错误的表和索引进行循环。

如果优化器选择按编写顺序优化左连接,那么它的性能将优于内部连接。

但是,优化器也可以将左连接次优优化为左半连接。要使它选择您想要的,您可以使用force order提示。

Francisco Pires 11 年前

使用 OPTION (FORCE ORDER) 最后公布结果。 选项(强制命令) 是一个查询提示,强制优化器使用查询中提供的联接顺序构建执行计划。

如果 INNER JOIN 开始以最快的速度 LEFT JOIN ,因为:

在完全由 内连接 S,连接顺序无关紧要。这使得查询优化器可以根据需要对连接进行排序,因此问题可能依赖于优化器。
用 左连接 ,但事实并非如此,因为更改联接顺序将更改查询的结果。这意味着引擎必须遵循您在查询中提供的连接顺序,这可能比优化的顺序更好。

不知道这是否回答了您的问题,但我曾经参与过一个项目,该项目的特点是进行计算的高度复杂的查询,这完全扰乱了优化器。我们有这样的案例 FORCE ORDER 将查询的执行时间从5分钟缩短到10秒。

J.O. 11 年前

已经在左外部连接和内部连接之间做了一些比较,但找不到一致性差异。有很多变数。我正在处理一个包含数千个表的报表数据库,其中许多表包含大量字段,随着时间的推移有许多更改(供应商版本和本地工作流)。不可能创建覆盖索引的所有组合以满足如此广泛的查询和处理历史数据的需要。已经看到内部查询会扼杀服务器性能,因为两个大型(数百万到千万行)表是内部连接的,它们都会拉取大量字段,并且不存在覆盖索引。

不过,最大的问题似乎并没有出现在上述讨论中。也许您的数据库是精心设计的触发器和精心设计的事务处理,以确保良好的数据。我的经常有空值,它们不是预期的。是的,表定义不能强制使用空值,但在我的环境中这不是一个选项。

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

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

Eddie Groves 14 年前

性能问题更可能是因为连接的数量以及连接的列是否有索引。

最坏的情况是,您很容易为每个连接执行9次整表扫描。

MarredCheese Lionia Vasilev 7 年前

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

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

如果那10张桌子 内部连接 这样,即使查询本身不需要十分之七的表,查询优化器也必须将它们全部联接起来。这是因为内部连接本身可能会过滤掉数据,使它们成为计算所必需的。

如果那10张桌子外接相反,查询优化器将只实际连接必要的查询:在本例中,10个查询优化器中的3个。这是因为连接本身不再过滤数据,因此可以跳过未使用的连接。

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

Buzzzzzzz 6 年前

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

如果不包括左联接表的项,则在select语句中,左联接将比具有内部联接的同一查询快。

如果在select语句中包含左联接表,则具有相同查询的内部联接等于或快于左联接。