测试设置
设置脚本，包括一些实际统计信息：
DROP TABLE IF EXISTS #left;
DROP TABLE IF EXISTS #right;
CREATE TABLE #left (
a char(4) NOT NULL,
b char(2) NOT NULL,
c varchar(13) NOT NULL,
d bit NOT NULL,
e char(4) NOT NULL,
f char(25) NULL,
g char(25) NOT NULL,
h char(25) NULL
--- and a few other columns
);
CREATE UNIQUE CLUSTERED INDEX IX ON #left (a, b, c, d, e, f, g, h)
UPDATE STATISTICS #left WITH ROWCOUNT=63800000, PAGECOUNT=186000;
CREATE TABLE #right (
a char(4) NOT NULL,
b char(2) NOT NULL,
c varchar(13) NOT NULL,
d bit NOT NULL,
e char(4) NOT NULL,
f char(25) NULL,
g char(25) NOT NULL,
h char(25) NULL
--- and a few other columns
);
CREATE CLUSTERED INDEX IX ON #right (a, b, c, d, e, f, g, h)
UPDATE STATISTICS #right WITH ROWCOUNT=55700000, PAGECOUNT=128000;
repro
当我将这两个表连接到它们的集群键时，我希望一对多的MERGE连接，如下所示：
SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
l.a=r.a AND
l.b=r.b AND
l.c=r.c AND
l.d=r.d AND
l.e=r.e AND
l.f=r.f AND
l.g=r.g AND
l.h=r.h
WHERE l.a='2018';
这是我想要的查询计划：
（不要担心警告，他们必须这样做带有虚假统计信息。）
但是，如果我更改联接中各列的顺序，例如：
SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
l.c=r.c AND -- used to be third
l.a=r.a AND -- used to be first
l.b=r.b AND -- used to be second
l.d=r.d AND
l.e=r.e AND
l.f=r.f AND
l.g=r.g AND
l.h=r.h
WHERE l.a='2018';
。 ..发生这种情况：
Sort运算符似乎根据联接的声明顺序（即
c, a, b, d, e, f, g, h）对流进行排序，这为查询添加了阻塞操作计划。我看过的东西
我尝试将列更改为
NOT NULL，结果相同。已创建原始表与
ANSI_PADDING OFF一起创建使用ANSI_PADDING ON不会影响此计划。我尝试使用
INNER JOIN而不是LEFT JOIN，没有任何变化。我在2014 SP2 Enterprise上发现了它，并在2017 Developer（当前CU）上创建了副本。 />删除前导索引列上的WHERE子句确实会生成一个好的计划，但它会影响结果。.:)
最后，我们得到一个问题
这是故意的吗？
我可以在不更改查询的情况下消除排序吗（这是供应商代码，所以我宁愿不要...）。我可以更改表和索引。
#1 楼
这是故意的吗？
这是设计使然。不幸的是，当微软退休了Connect反馈站点时，此断言的最佳公开来源丢失了，这抹去了SQL Server团队开发人员的许多有用评论。
无论如何，当前的优化器设计并没有积极地寻求避免不必要的排序本身。这是开窗函数之类最常遇到的情况，但在对排序特别是对排序符之间保持顺序敏感的其他运算符中也可以看到。
尽管如此，优化器还是相当不错的（在许多情况下）避免不必要的排序，但是通常会出于积极尝试使用不同排序组合的原因而导致此结果。从这个意义上讲，与其说是“搜索空间”，还不如说是正交优化器功能之间复杂的交互作用，它已被证明可以以可接受的成本提高总体计划质量。
，通常可以简单地通过将排序要求（例如，顶级
ORDER BY）与现有索引进行匹配来避免排序。在您的情况中，这很简单，这可能意味着添加ORDER BY l.a, l.b, l.c, l.d, l.e, l.f, l.g, l.h;，但是这过于简化了（并且不可接受，因为您不想更改查询）。更一般而言，每个备注组可能与必填或所需的属性，其中可能包括输入顺序。当没有明显的理由强制执行特定命令时（例如，满足
ORDER BY或确保对命令敏感的物理操作员确保正确的结果），则涉及“运气”元素。我在避免合并合并串联中的排序中写了更多有关合并联接（在联合或联接模式下）的细节。其中大部分超出了产品支持的表面积，因此应将其视作参考，并可能随时更改。是的，在您的特定情况下，您可以按照jadarnel27的建议调整索引以避免出现这种情况；尽管没有什么理由真正喜欢此处的合并联接。您还可以根据计划对数据的了解以及最佳，最差和平均情况下的性能之间的权衡，使用计划指南在使用
OPTION(HASH JOIN, LOOP JOIN)的哈希或循环物理联接之间进行选择，而无需更改查询。 > 最后，出于好奇，请注意，可以使用简单的
ORDER BY l.b避免排序，但以b上可能效率较低的多对多合并联接为代价，并且残留复杂。我主要是为了说明我之前提到的优化器功能与顶级需求可以传播的方式之间的相互作用。#2 楼
是否可以在不更改查询的情况下消除排序（这是供应商代码，所以我宁愿不...）。我可以更改表和索引。
如果可以更改索引，则更改
#right上索引的顺序以匹配联接中过滤器的顺序将删除排序（对我而言）：CREATE CLUSTERED INDEX IX ON #right (c, a, b, d, e, f, g, h)
令人惊讶的是（至少对我来说），这导致两个查询都没有以排序结束。
这是故意的吗？
看看一些奇怪的跟踪标志的输出，最终的备忘录结构有一个有趣的区别：
如您在顶部的“根组”中所见，两个查询都可以选择使用合并联接作为执行此查询的主要物理操作。
很好的查询
不进行排序的联接由组29选项1和组31选项1驱动（每个都是对所涉及索引的范围扫描）。它由组27（未显示）进行过滤，该组是过滤联接的一系列逻辑比较操作。
错误查询
具有排序功能的是由（新）选项3，这两个组（29和31）中的每一个都有。选项3对前面提到的范围扫描的结果（每个组的选项1）执行物理排序。
为什么？
出于某种原因，选择直接使用29.1和31.1作为合并联接的源在第二个查询中甚至对优化器不可用。否则，我认为它将在其他选项中的根目录下列出。如果可以使用的话，那肯定会从昂贵得多的排序操作中选择。
我只能得出以下结论：
这是优化器的搜索算法中的错误（或更可能是限制）
将索引和联接更改为仅具有5个键将删除第二个查询的排序（6、7和8个键均具有排序）。
这意味着具有8个键的搜索空间是如此之大，以至于优化程序没有时间以“找到足够好的计划”为理由而提前终止非排序解决方案，就没有时间将其确定为可行的选择
连接条件的顺序对优化器的搜索过程有很大影响，这对我来说似乎有点小毛病，但这确实让我有点头疼
为了确保结果的正确性
这似乎不太可能，因为当键较少或键以不同顺序指定时，查询可以不进行排序而运行
/>
希望有人能来解释为什么需要这种排序，但是我认为Memo大楼中的差异很有趣，可以作为答案。
评论
我相信您对搜索空间的评论实际上就是这种情况。为了仅使用索引，优化器必须验证它们是否满足条件，过去的5个键在退回之前有太多的检查可能性。我很好奇，如果查询的所有顺序组合都被枚举，那么优化器将在vs上成功回退多少
– Mindor先生
18年8月16日在17:38
是的，不一致看上去确实有点小问题，但它可能完全取决于用于验证索引是否足够的算法。如果对所有组合进行了测试，则您可能可以看到结果中的模式并确定使用哪种算法。我敢打赌它被编写为在最典型的用例中表现最佳。可能存在一种替代方法，它能够在时限内可靠地找到8键解决方案，但是当少于3-4个键时，它比当前解决方案要慢。
– Mindor先生
18年8月16日在17:47