主页 / dba / 问题 / 296111
Accepted
Daniel Hutmacher
Asked: 2021-07-25 06:46:57 +0800 CST

在我看来如何实现谓词下推

  • 772

我有一个报表（大约 10 亿行）和一个很小的维度表：

CREATE TABLE dbo.Sales_unpartitioned (
    BusinessUnit    int NOT NULL,
    [Date]          date NOT NULL,
    SKU             varchar(8) NOT NULL,
    Quantity        numeric(10, 2) NOT NULL,
    Amount          numeric(10, 2) NOT NULL,
    CONSTRAINT PK_Sales_unpartitioned PRIMARY KEY CLUSTERED (BusinessUnit, [Date], SKU)
);

--- Demo data:
INSERT INTO dbo.Sales_unpartitioned
SELECT severity AS BusinessUnit,
       DATEADD(day, message_id, '2000-01-01') AS [Date],
       LEFT([text], 3) AS SKU,
       1000.*RAND(CHECKSUM(NEWID())) AS Quantity,
       10000.*RAND(CHECKSUM(NEWID())) AS Amount
FROM sys.messages
WHERE [language_id]=1033;

--- Artificially inflate statistics of demo data:
UPDATE STATISTICS dbo.Sales_unpartitioned WITH ROWCOUNT=1000000000;

--- Dimension table:
CREATE TABLE dbo.BusinessUnits (
    BusinessUnit    int NOT NULL,
    SalesManager    nvarchar(250) NULL,
    PRIMARY KEY CLUSTERED (BusinessUnit)
);

INSERT INTO dbo.BusinessUnits (BusinessUnit)
SELECT DISTINCT BusinessUnit FROM dbo.Sales;

...我在其中添加了应用程序用于 OLTP 样式报告的报告视图。

CREATE OR ALTER VIEW dbo.SalesReport_unpartitioned
AS

SELECT bu.BusinessUnit,
       s.[Date],
       s.SKU,
       s.Quantity,
       s.Amount
FROM dbo.BusinessUnits AS bu
CROSS APPLY (
    --- Regular sales
    SELECT t.BusinessUnit, t.[Date], t.SKU, t.Quantity, t.Amount
    FROM dbo.Sales_unpartitioned AS t
    WHERE t.BusinessUnit=bu.BusinessUnit
      AND t.SKU LIKE 'T%'

    UNION ALL

    --- This is a special reporting entry. We only
    --- want to see today's row. In case of duplicates,
    --- get the row with the first "SKU".
    SELECT TOP (1) s.BusinessUnit, s.[Date], s.SKU, s.Quantity, s.Amount
    FROM dbo.Sales_unpartitioned AS s
    WHERE s.BusinessUnit=bu.BusinessUnit
      AND s.[Date]=CAST(SYSDATETIME() AS date)
      AND s.SKU LIKE 'S%'
    ORDER BY s.BusinessUnit, s.[Date], s.SKU
) AS s

这个想法是，用户应用程序将使用 SELECT 查询来查询此视图，该查询过滤一系列日期和一个或多个业务单位。为此，我选择了一种CROSS APPLY模式，以便查询可以“循环”每个业务单元，查找日期范围，并在 SKU 上应用残差过滤器。

示例应用查询：

DECLARE @from date='2021-01-01', @to date='2021-12-31';

SELECT *
FROM dbo.SalesReport_unpartitioned
WHERE BusinessUnit=16
  AND [Date] BETWEEN @from AND @to
ORDER BY BusinessUnit, [Date], SKU;

我希望查询计划看起来像这样： Desired plan具有谓词下推且无过滤器运算符的所需查询计划

但是，计划结果如下： 实际计划实际查询计划仅在 SKU 上具有剩余谓词的 BusinessUnit 上查找，将过滤器添加到计划的末尾

我希望 SQL Server 在 Date 列上执行“谓词下推”，允许 Clustered Index Seek 查找单个 BusinessUnit 和日期范围，然后在 SKU 上应用剩余谓词。这适用于“s”分支中的 Seek（带有 的分支TOP）——可能是因为它在查询中有一个硬编码的 Date 谓词——但不适用于“t”分支。

但是，在“t”分支上，SQL Server 只寻找具有 SKU 上的剩余谓词的特定 BusinessUnit，有效地检索所有日期。只有在计划结束时，它才会应用过滤日期列的过滤运算符。

在一个大表中，这会带来非常显着的性能损失——当您只需要一周的时间时，您最终可能会从磁盘读取 20 年的数据。

我尝试过的事情

解决方法：

  • 将视图转换为具有过滤“s”和“t”查询的@fromDate 和@toDate 参数的内联表值函数将根据需要启用Seek on (BusinessUnit, Date)，但需要重写应用程序代码。
  • 将(from to )UNION ALL移出将启用谓词下推。它在 BusinessUnit 表上又进行了一次搜索，这是完全可以接受的。CROSS APPLYCROSS APPLY (UNION)CROSS APPLY() UNION CROSS APPLY()

修复了 Seek，但改变了结果：

  • 令人惊讶的是，删除“s”查询的TOP (1)andORDER BY会使谓词下推对“t”起作用，但会从“s”返回太多行。
  • UNION ALL通过删除“s”或“t”查询来消除将启用谓词下推，但会产生不正确的结果。

无变化或不可行：

  • 替换TOP (1)为ROW_NUMBER()模式不会改变 Seek。
  • 将 the 更改CROSS APPLY为强制INNER LOOP JOIN修复了“t”上的 Seek，但实际上将“s”更改为 Scan，这甚至更糟。
  • 添加跟踪标志 8780 以允许优化器在计划上工作更长时间不会改变任何事情。该计划已经完全优化，没有提前终止。

一个常见的线程似乎是更改/简化“s”查询（删除TOP, ORDER BY）解决了“t”查询的问题，这对我来说感觉违反直觉。

我在寻找什么

我试图了解这是否是优化器的缺点，是否是故意成本/优化机制的结果，或者我是否只是忽略了某些东西。

optimization query-performance

2 个回答

  1. Best Answer

    我试图了解这是否是优化器的缺点，是否是故意成本/优化机制的结果，或者我是否只是忽略了某些东西。

    这是所有这些的一小部分。

    提出的查询中有很多内容——真的太多了——所以为了避免写半本书，我将把它归结为导致你没有得到你所追求的计划的主要因素：

    优化器不会将谓词下推到应用的内侧。

    对apply之上的关系选择（过滤器、谓词）进行操作的规则很自然地被称为 .SELonApply它执行以下逻辑替换：

    选择（A 应用 B）-> 选择（选择 A 应用 B）

    它获取涉及 A 和 B 的潜在复杂选择的一部分，并将那些它可以向下推到驱动表 A 的部分。没有选择的部分被推到 B。选择的部分不能被推倒留在后面。

    这听起来像是一个令人震惊的疏忽，与经验背道而驰。那是因为它不是完整的故事。

    优化器尝试在编译过程的早期将应用转换为等效连接（在简化期间，在琐碎计划和基于成本的优化之前）。它能够将选择向下推送到join的任一侧，这是安全的。在基于成本的优化过程中，该连接又可以转化为物理应用。

    所有这一切的效果是让优化器看起来像是将谓词推到了应用的内侧：

    1. 书面应用转换为连接。
    2. 谓词下推连接的任一侧。
    3. 加入转换为应用。

    让我给你看一个例子：

    DECLARE @T1 table (pk integer PRIMARY KEY, c1 integer NOT NULL INDEX ic1);
DECLARE @T2 table (fk integer NOT NULL, c2 integer NOT NULL, PRIMARY KEY (fk, c2));

SELECT 
    T1.*,
    T2.*
FROM @T1 AS T1
CROSS APPLY 
(
    SELECT T2.* 
    FROM @T2 AS T2
    WHERE T2.fk = T1.pk
) AS T2
WHERE 
    1 = 1
    AND T1.c1 = 1
    AND T2.c2 = 2;

    推下谓词的计划

    如果您仔细查看该计划，您会看到 T2 上的谓词被推到了内部查找，并且嵌套循环连接是一个应用（它具有外部引用）。这是唯一可能的，因为优化器最初能够将应用重写为连接，推送谓词，然后稍后转换回应用。

    我们可以使用未记录的跟踪标志 9114 禁用 apply-to-join 重写：

    DECLARE @T1 table (pk integer PRIMARY KEY, c1 integer NOT NULL INDEX ic1);
DECLARE @T2 table (fk integer NOT NULL, c2 integer NOT NULL, PRIMARY KEY (fk, c2));

SELECT 
    T1.*,
    T2.*
FROM @T1 AS T1
CROSS APPLY 
(
    SELECT T2.* 
    FROM @T2 AS T2
    WHERE T2.fk = T1.pk
) AS T2
WHERE 
    1 = 1
    AND T1.c1 = 1
    AND T2.c2 = 2
OPTION (QUERYTRACEON 9114);

    这意味着 onlySELonApply可以使用，它只推送到驱动表 A：

    带有卡住谓词的计划

    请注意，T2.c2 上的选择部分在过滤器中“卡在”应用上方。（内侧查找仅在应用内指定的 fk/pk 相等性上。）

    优化器建立在关系原则之上。它赞赏关系模式设计和使用关系结构的查询。Apply（横向连接）是一个相对较新的扩展。与 apply 相比，优化器知道更多的 join 技巧，因此需要尽早进行重写。

    当您使用诸如 apply 或非关系 Top 之类的东西时，您隐含地对最终计划形状承担了更多责任。换句话说，您通常必须以不同的方式表达您的查询（如在您的解决方法中）以获得良好的结果。

    作为记录，我的偏好是使用带有显式谓词放置的内联表值函数。如果我要重写视图，我可能会选择：

    CREATE OR ALTER VIEW dbo.SalesReport_unpartitioned
AS
--- Regular sales
SELECT
    BU.BusinessUnit,
    RS.[Date],
    RS.SKU,
    RS.Quantity,
    RS.Amount
FROM dbo.BusinessUnits AS BU
JOIN dbo.Sales_unpartitioned AS RS
    ON RS.BusinessUnit = BU.BusinessUnit
WHERE 
    RS.SKU LIKE 'T%'

UNION ALL

--- This is a special reporting entry.
SELECT
    BU.BusinessUnit,
    SR.[Date],
    SR.SKU,
    SR.Quantity,
    SR.Amount
FROM dbo.BusinessUnits AS BU
JOIN dbo.Sales_unpartitioned AS SR
    ON SR.BusinessUnit = BU.BusinessUnit
WHERE
    1 = 1
    AND SR.SKU LIKE 'S%'
    --- We only want to see today's row.
    AND SR.[Date] = CONVERT(date, SYSDATETIME())
    --- In case of duplicates, get the row with the first "SKU".
    AND SR.SKU =
    (
        SELECT 
            MIN(SR2.SKU) 
        FROM dbo.Sales_unpartitioned AS SR2
        WHERE 
            SR2.BusinessUnit = SR.BusinessUnit
            AND SR2.[Date] = SR.[Date]
            AND SR2.SKU LIKE 'S%'
    );
GO

    对于提供的测试查询：

    DECLARE @from date='2021-01-01', @to date='2021-12-31';

SELECT *
FROM dbo.SalesReport_unpartitioned
WHERE BusinessUnit=16
  AND [Date] BETWEEN @from AND @to
ORDER BY BusinessUnit, [Date], SKU;

    执行计划是：

    视图重写计划

    橙色部分是常规销售。黄色部分用于特殊报告条目。

    • 8

  2. 这只是没有编写优化器来处理这种特殊复杂要求的情况之一，因为它无法判断推送date谓词不会影响top子查询中的任何内容。

    有一种方法，使用连接并将其重写top 1为针对分区row_number分析的过滤器。BusinessUnit在分区中包含row_number表示下推是合法的。

    CREATE OR ALTER VIEW dbo.SalesReport_unpartitioned
AS

SELECT bu.BusinessUnit,
       s.[Date],
       s.SKU,
       s.Quantity,
       s.Amount
FROM dbo.BusinessUnits AS bu
JOIN (
    --- Regular sales
    SELECT t.BusinessUnit, t.[Date], t.SKU, t.Quantity, t.Amount
    FROM dbo.Sales_unpartitioned AS t
    WHERE  t.SKU LIKE 'T%'

    UNION ALL

    --- This is a special reporting entry. We only
    --- want to see today's row. In case of duplicates,
    --- get the row with the first "SKU".
    SELECT t.BusinessUnit, t.[Date], t.SKU, t.Quantity, t.Amount
    FROM  (SELECT s.BusinessUnit, s.[Date], s.SKU, s.Quantity, s.Amount, row_number() over (partition by s.BusinessUnit order by s.SKU) rn
           FROM dbo.Sales_unpartitioned AS s
           WHERE s.[Date]=CAST(SYSDATETIME() AS date)
             AND s.SKU LIKE 'S%'
          ) t
     WHERE   rn = 1
) AS s
ON bu.BusinessUnit = s.BusinessUnit

    粘贴计划

    • 1

相关问题