主页 / dba / 问题 / 188448
Accepted
GriGrim
Asked: 2017-10-14 12:19:55 +0800 CST

为什么优化器在这里选择嵌套循环而不是合并连接？

  • 772

我有 3 张桌子。#a是主要的一个和两个辅助表，#b并且#c.

create table #a (a int not null, primary key (a asc)) ;
create table #b (b int not null, primary key (b asc)) ;
create table #c (c int not null, primary key (c asc)) ;

insert into #a (a)
select x*10 + y
from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))x(x)
cross join (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))y(y) ;

insert into #b (b)
select a from #a where a % 5 > 0 ;

insert into #c (c)
select a from #a where a % 4 > 0 ;

如果我#a只用一个辅助表连接主表，查询计划中就会有 Merge Join。

select *
from #a a
inner join #b b on a = b ;

在此处输入图像描述

但是，如果我将主表#a与两个辅助表一起加入，则将只有嵌套循环。

select *
from #a a
inner join #b b on a = b
inner join #c c on a = c ;

在此处输入图像描述

为什么它会这样工作，我应该怎么做才能获得两个合并连接？

无inner merge join提示。

sql-server join

3 个回答

  1. Best Answer

    为什么它会这样工作，我应该怎么做才能获得两个合并连接？

    使用三个表引用（最低要求），查询符合基于成本的优化的事务处理（又名搜索 0）阶段。

    此阶段针对 OLTP 查询，这些查询通常受益于导航（基于索引）策略。嵌套循环连接是可用的主要物理连接类型（只有在此阶段找不到有效的嵌套循环计划时才考虑散列和合并）。

    如果这个阶段找到了一个低成本（足够好）的计划，那么基于成本的优化就到此为止了。这可以防止在优化上花费更多时间，我们可以期望节省到目前为止找到的最佳解决方案。如果成本超过阈值，优化器将进入快速计划（搜索 1）、并行快速计划和完全优化（搜索 2）阶段。

    具有两个表引用的查询不符合事务处理的条件，并直接进入Quick Plan，其中 Merge 和 Hash 连接可用。

    有关更多信息，请参阅我的Query Optimizer Deep Dive系列。

    无inner merge join提示。

    如果您绝对必须提示物理连接类型，强烈建议使用OPTION (MERGE JOIN). 这允许优化器仍然考虑更改连接顺序。

    加入提示，例如INNER MERGE JOIN带有隐含的OPTION (FORCE ORDER)，这严重限制了优化器的自由度，其后果是大多数人（包括专家）不理解。

    • 8

  2. 我认为原因是因为您的表中没有足够的数据，所以 SQL Server 没有选择BEST计划，而是选择Good Enough计划来执行您的查询。

    我尝试关注

    create table #a (a int not null, primary key (a asc))
create table #b (b int not null, primary key (b asc))
create table #c (c int not null, primary key (c asc))

insert into #a (a)
select h+10*g
from  
(values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))g(g)
cross join (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))h(h)


insert into #b (b)
select a from #a where a % 5 > 0

insert into #c (c)
select a from #a where a % 4 > 0

    现在如果我跑

    select *
from #a a
inner join #b b on a = b
inner join #c c on a = c

    这是执行计划在此处输入图像描述

    如果我使用合并连接提示，我会得到以下执行计划。

    select *
from #c c
inner merge join #a a on a = c
inner merge join #b b on a = b

    在此处输入图像描述

    所以很明显，在这种情况下合并连接会更好。对于带有合并连接的查询，估计的 subTree 成本较低，但是这两个查询都足够快，两者都只进行 12 次逻辑读取，因此 SQL Server 认为 NESTED LOOP 连接也是很好的解决方案。

    不要向我们的表中添加更多数据。

    create table #a (a int not null, primary key (a asc))
create table #b (b int not null, primary key (b asc))
create table #c (c int not null, primary key (c asc))

insert into #a (a)
select h+10*g + 100*f + 1000*e+ 10000*z + 100000*y + 1000000*x
from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))x(x)
cross join (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))y(y)
cross join (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))z(z)
cross join (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))e(e)
cross join (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))f(f)
cross join (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))g(g)
cross join (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))h(h)


insert into #b (b)
select a from #a where a % 5 > 0

insert into #c (c)
select a from #a where a % 4 > 0

    现在我们在#a 中有 1000 万个数据，#b 中有 800 万个数据，#c 中有 600 万个数据。如果我在没有任何提示的情况下运行原始查询，我会按预期获得合并连接。

    select *
from #a a
inner join #b b on a = b
inner join #c c on a = c

    在此处输入图像描述

    如果一个连接输入很小（少于 10 行）而另一个连接输入相当大并且在其连接列上建立索引，则索引嵌套循环连接是最快的连接操作，因为它们需要最少的 I/O 和最少的比较。

    如果两个连接输入不小但按连接列排序（例如，如果它们是通过扫描排序索引获得的），则合并连接是最快的连接操作。

    散列连接可以有效地处理大的、未排序的、非索引的输入。

    高级查询调优概念

    LOOP、HASH 和 MERGE 连接类型

    了解 SQL Server 物理连接

    • 2

  3. 优化器根据现有统计信息、表大小和索引的存在在合并/嵌套循环/散列连接之间进行选择。一般来说，如果输入比其他输入小得多，则嵌套循环更可取，并且它们都在连接列上被索引，如果两个输入的大小非常相等并且被索引，则合并会更好。填充涉及更多值（几十万行）的表很可能会使优化器选择合并连接算法。您可以在此处找到有关它如何在 SQLServer 中实现的更多详细信息。
    您也可以尝试提示（例如FORCE ORDER），但即使它一次有效，也不能保证它总是会生成相同的计划。

    另外，请记住，优化器的任务不是找到最好的计划，而是及时返回足够好的计划。

    • 0

相关问题