我正在研究一个小的性能问题,优化器工具基本上是在说,“嘿,继续,并将该表上的所有列包含在该索引中”,在我看来,这是一个可怕的解决方案。
我的思考过程让我想到“为什么我不继续给它现有索引中的所有内容来执行排序和 TOP(N) 操作并删除这个巨大的 100k 键查找操作,SQL Server 当然可以做到这一点,并且从 100k 个键查找更改为 N 个。”
这不是我看到的,我看到的是根本没有任何变化,它仍然执行所有关键查找并在此之后进行排序。如下
当然,非常简单地删除不在索引中的其他列的选择,就可以将其更改为根本不需要键查找。
我已经看到许多使用 CTE 来解决此问题的解决方法,但我使用实体框架来执行此查询,并且简单地使用查询并不那么容易。
我想这个问题的主要目的是为什么会发生这种情况?如果可以的话,在键查找循环之前执行排序和顶部子句似乎是一个微不足道的操作。不这样做似乎是该平台的一个明显弱点。
我问的是为什么存在这种行为,而不是如何提高此查询的性能。
自爱
您可能可以通过执行自连接来获得您想要的计划形状,这在实体框架中比所有 CTE 内容更容易完成。这也是一个更可靠的选择,因为 CTE 可能不稳定。
如果我们有这个索引:
这个查询:
最终的计划如下所示:
细节
您想要做的是让自连接的一个实例(在本例中为别名
u
)负责 where 子句和 order by,而另一个引用(在本例中为u2
)负责选择列表。您可以在查询计划中看到,在进入联接之前,排序已满足 1000 行目标。
在并行执行计划的情况下(如您问题中的计划),您可能会看到稍高的数字在被 TOP 丢弃之前进入排序。
关于为什么出现在这里,还有一些额外的细节:
主要有以下三个原因:
查找与其父运算符紧密绑定。
逻辑操作是从
GET
关系返回属性。该逻辑操作的物理实现可以采用多种形式:无论选择哪个物理选项,SQL Server 都必须尊重 的原始意图和语义
GET
,包括锁定生命周期和其他一致性保证以及内部不变量。因此,在扫描或查找与任何相关查找之间允许使用的运算符非常少。其中包括对集群键进行排序,以优化查找时的顺序 I/O,以及用于万圣节保护的急切线轴。
“Top”不是关系运算符。大多数优化器都是建立在关系原则和等价基础上的。随着时间的推移,一些特定的支持已经被添加(或故意省略),但这些仍然是少数。
因此,优化器不会过多考虑替代的 Top 运算符放置。
“前 N 排序”是优化后重写,可能会执行替换选择排序,而不是使用通用算法。
作为优化后重写,它不受成本控制,也不构成优化器推理的任何部分。
重写仅限于在优化器选择的执行计划中物理 Top 运算符紧跟在 Sort 运算符之后的情况。
鉴于优化器不会太多地探索在计划树中移动 Top,很容易最终导致 Top 与 Sort 分离(它可以移动更多,但不如真正的关系运算符那么多)。
嗯,确实如此。十年或更长时间以来,人们一直在撰写有关表达查询以获得更好结果的方法。这也不是唯一的弱点。
另一方面,SQL Server优化器的目标是快速找到明显合理的执行计划。它与编程语言中的优化编译器没有相同的目标,编程语言有更多的自由和时间来寻找和应用它们的技巧。
很遗憾听到这个消息。