主页 / dba / 问题 / 226185
Accepted
Erik Darling
Asked: 2019-01-03 10:16:12 +0800 CST

什么时候可以将 SARGable 谓词推送到 CTE 或派生表中？

  • 772

沙袋

在处理 Top Quality Blog Posts®时，我遇到了一些我发现非常有趣的优化器行为。我没有立即得到解释，至少不是我满意的解释，所以我把它放在这里以防有人聪明出现。

如果您想继续学习，可以在此处获取 2013 版的 Stack Overflow 数据转储。我正在使用 Comments 表，上面有一个额外的索引。

CREATE INDEX [ix_ennui] ON [dbo].[Comments] ( [UserId], [Score] DESC );

查询一

当我这样查询表时，我得到一个奇怪的查询计划。

WITH x
    AS
     (
         SELECT   TOP 101
                  c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
         ORDER BY c.Score DESC
     )
SELECT *
FROM   x
WHERE  x.Score >= 500;

坚果

Score 上的 SARGable 谓词未被推入 CTE。它在计划的后期出现在过滤器运算符中。

坚果

我觉得很奇怪，因为它ORDER BY与过滤器位于同一列。

查询二

如果我更改查询，它确实会被推送。

WITH x
    AS
     (
         SELECT   c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
     )
SELECT TOP 101 *
FROM   x
WHERE  x.Score >= 500
ORDER BY x.Score DESC;

查询计划也发生变化，运行速度更快，不会溢出到磁盘。它们都产生相同的结果，谓词在非聚集索引扫描中。

坚果

坚果

查询三

这相当于像这样编写查询：

SELECT   TOP 101
         c.UserId, c.Text, c.Score
FROM     dbo.Comments AS c
WHERE c.Score >= 500
ORDER BY c.Score DESC;

查询四

使用派生表得到与初始 CTE 查询相同的“坏”查询计划

SELECT *
FROM   (   SELECT   TOP 101
                    c.UserId, c.Text, c.Score
           FROM     dbo.Comments AS c
           ORDER BY c.Score DESC ) AS x
WHERE x.Score >= 500;

事情变得更奇怪，当...

我将查询更改为按升序排列数据，并将过滤器更改为<=.

为了避免让这个问题过长，我将把所有内容放在一起。

查询

--Derived table
SELECT *
FROM   (   SELECT   TOP 101
                    c.UserId, c.Text, c.Score
           FROM     dbo.Comments AS c
           ORDER BY c.Score ASC ) AS x
WHERE x.Score <= 500;


--TOP inside CTE
WITH x
    AS
     (
         SELECT   TOP 101
                  c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
         ORDER BY c.Score ASC
     )
SELECT *
FROM   x
WHERE  x.Score <= 500;


--Written normally
SELECT   TOP 101
         c.UserId, c.Text, c.Score
FROM     dbo.Comments AS c
WHERE c.Score <= 500
ORDER BY c.Score ASC;

--TOP outside CTE
WITH x
    AS
     (
         SELECT   c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
     )
SELECT TOP 101 *
FROM   x
WHERE  x.Score <= 500
ORDER BY x.Score ASC;

计划

计划链接。

坚果

请注意，这些查询都没有利用非聚集索引——这里唯一改变的是过滤器运算符的位置。在任何情况下都不会将谓词推送到索引访问。

问题出现！

在某些情况下可以推送 SARGable 谓词而不是在其他情况下是否有原因？按降序排序的查询之间的差异很有趣，但这些查询与按升序排列的查询之间的差异很奇怪。

对于任何感兴趣的人，这里是只有一个索引的计划Score：

sql-server optimization

1 个回答

  1. Best Answer

    这里有几个问题。

    推动谓词过去TOP

    优化器当前无法将谓词推过 a TOP，即使在这样做安全的有限情况下也是如此*。此限制说明了谓词在比 . 更高范围内的问题中所有查询的行为TOP。

    解决方法是手动执行重写。基本问题类似于将谓词推过窗口函数的情况，只是没有相应的专用规则，例如SelOnSeqPrj。

    我个人的看法是，像这样的探索规则SelOnTop仍未实施，因为人们故意编写查询TOP以提供一种“优化围栏”。

    * 通常这意味着谓词应该出现在ORDER BY与 the 关联的子句中TOP，并且任何不等式的方向应该与排序的方向一致。转换还需要考虑 SQL Server 中 NULL 的排序行为。总的来说，这些限制可能意味着这种转变在实践中通常不够有用，无法证明额外的探索努力是合理的。

    成本核算问题

    问题中剩余的执行计划可以解释为基于成本的选择，这是由于Score列中值的分布（<= 500 的行多于 >= 500 的行），以及TOP.

    例如，查询：

    --Written normally
SELECT TOP (101)
    c.UserId, 
    c.[Text],
    c.Score
FROM dbo.Comments AS c
WHERE
    c.Score <= 500
ORDER BY
    c.Score ASC;

    ...在过滤器中生成一个带有明显未推送谓词的计划：

    由于行目标而延迟过滤

    请注意，排序估计会产生 101 行。这是Top添加的行目标的效果。这会影响 Sort 和 Filter 的估计成本，足以使它看起来像是更便宜的选择。该计划的估计成本为2401.39单位。

    如果我们使用查询提示禁用行目标：

    --Written normally
SELECT TOP (101)
    c.UserId, 
    c.[Text],
    c.Score
FROM dbo.Comments AS c
WHERE
    c.Score <= 500
ORDER BY
    c.Score ASC
OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'));

    ...产生的执行计划是：

    没有行目标的计划

    该谓词已作为剩余不可搜索谓词被推入扫描，整个计划的成本为2402.32个单位。

    请注意，<= 500谓词不应过滤掉任何行。如果您选择了一个较小的数字，例如<= 50，优化器会更喜欢推送谓词计划，而不管行目标效果如何。

    对于带有谓词的Score DESC查询Score >= 500：

    --Written normally
SELECT TOP (101)
    c.UserId, 
    c.[Text],
    c.Score
FROM dbo.Comments AS c
WHERE
    c.Score >= 500
ORDER BY
    c.Score DESC;

    现在预计谓词非常有选择性，因此优化器选择推送谓词并使用非聚集索引进行查找：

    选择性谓词

    同样，优化器考虑了多个备选方案，并像往常一样选择了这个显然最便宜的选项。

    • 13

相关问题