主页 / dba / 问题 / 330663
Accepted
Charlieface
Asked: 2023-08-28 09:59:29 +0800 CST

分区视图聚合查询未优化

  • 772

给出以下两个表：

CREATE TABLE SalesLedger (
  Id int PRIMARY KEY IDENTITY,
  Date date NOT NULL,
  Total decimal(38,18),
  INDEX IX (Date, Total)
);

CREATE TABLE Purchases (
  Id int PRIMARY KEY IDENTITY,
  Date date NOT NULL,
  Total decimal(38,18),
  INDEX IX (Date, Total)
);

以及下面的视图

CREATE VIEW ViewMetrics
AS

Select
  Date,
  'Sale' as Metric,
  Total as Value
From SalesLedger

UNION ALL

Select
  Date,
  'Purchase' as Metric,
  Total as Value
From Purchases;

以下查询使用一Concatenation Sort对：

Select SUM(Value) as Sales, Date
from ViewMetrics
Group By Date;

在此输入图像描述

粘贴计划

而较小的重写可以明显提高性能Merge Concatenation

SELECT SUM(Sales), Date
FROM (
  Select SUM(Value) as Sales, Date
  from ViewMetrics
  Group By Metric, Date
) t
GROUP BY Date;

在此输入图像描述

粘贴计划

编译器可以清楚地看到视图是按 分区的Metric，正如这个查询所示，不需要Sort：

Select SUM(Value) as Sales, Date
from ViewMetrics
where Metric = 'Sale'
Group By Date;

在此输入图像描述

问题是：为什么第一个查询强制 a Sort，而第二个查询可以使用更有效的Merge Concatenation，因为在这两种情况下该Metric列都没有WHERE谓词？

Merge鉴于索引已经排序Date并且分区已打开，编译器是否应该能够看到 a可以工作Metric？或者如果它看不到这一点，为什么GROUP BY Metric, Date突然赋予它这种能力？

数据库<>小提琴

更奇怪的是，正如 @MartinSmith 发现的那样，如果没有数据，那么编译器将使用更好的计划，尽管没有中间聚合Metric, Date。db<>fiddle另一方面，没有部分聚合的合并可能比部分聚合后的排序慢，因为有更多的行需要合并。问题是为什么它不能默认同时进行部分聚合和合并？

我猜测当聚合包含分区时，分区视图有一些特定的优化，因为在这种情况下它使用串联，并且当需要排序时它使用合并串联，请参阅 db<> fiddle。当您想要进一步聚合时，这会有所帮助，因为数据现在已经按正确的顺序排序。但是，如果您不进行中间聚合，它就没有应用它的逻辑。

sql-server

1 个回答

  1. Best Answer

    SQL Server 优化器有两种主要方法来将聚合向下推送到联合全部之上。

    1. 全局下推

    第一条规则是GbAggBelowUniAll。这是一个相当简单的转换，将聚合移动到每个联合输入上。

    只有当并集不相交时，它才能安全地执行此操作 - 也就是说，如果有某种东西使每个输入完全独立，并且该因素出现在子句中GROUP BY。

    它不一定是像您的情况那样的文字值，但它必须是优化器可以识别为完全分离集合的东西，例如非重叠范围。作为转换的一部分，分组子句的常量部分被删除。

    此规则与您的重写有关，因为Metric属性是不相交的并且存在于分组规范中。

    使用 Adventure Works 示例数据库的示例：

    -- Helpful indexes
CREATE INDEX i ON Production.TransactionHistory 
    (Quantity, TransactionDate);
CREATE INDEX i ON Production.TransactionHistoryArchive 
    (Quantity, TransactionDate);

    SELECT 
    U.Quantity, 
    MAX(U.TransactionDate)
FROM 
(
    -- The predicates on quantity make the two union inputs disjoint
    SELECT 
        TH.*
    FROM Production.TransactionHistory AS TH
    WHERE 
        TH.Quantity BETWEEN 50 AND 60

    UNION ALL

    SELECT
        THA.*
    FROM Production.TransactionHistoryArchive AS THA
    WHERE 
        THA.Quantity BETWEEN 10 AND 20
) AS U
-- Grouping by the disjoint element
GROUP BY 
    U.Quantity
ORDER BY 
    U.Quantity
--OPTION (FORCE ORDER)
;

    如果FORCE ORDER提示未注释，优化器将无法移动聚合：

    有提示的计划

    如果没有提示，可以将顶级聚合移动并复制到每个联合输入：

    全球总量低于联合所有

    2. 本地聚合

    第二个转换涉及几个不同的规则。

    首先，GenLGAgg将聚合分为两部分：全局聚合和局部聚合。例如，COUNT聚合将分为本地COUNT聚合和全局SUM聚合，全局聚合将所有本地贡献加在一起以获得正确的结果。

    总体思想适用于串行和并行计划。有时，本地聚合计算其自己线程本地的小计，有时它执行连接下工作的子集。无论如何，总体思路是相同的：尽早完成总体聚合任务的某些部分。

    与许多优化器探索一样，GenLGAgg产生一个或多个可以通过其他规则进一步探索的替代方案。例如，新的本地聚合可能是过去的联接或与索引视图匹配。

    在您的情况下，使用名为 的规则LocalAggBelowUniAll将本地聚合移至UNION ALL. 这就是原始查询所发生的情况。

    重要的是，本地聚合并不是一个正常的聚合。它仅执行部分计算，并且可能最终出现在并行计划中的多个线程之一上。全局聚合始终在单个线程上运行以确保正确的结果。

    在并行计划中，本地聚合可以物理地实现为哈希匹配部分聚合。该运算符仅获得少量固定内存授予，并且永远不会溢出到tempdb。如果内存不足，它就会停止聚合。由于全球汇总，结果仍然是正确的。

    注意事项并不限于此物理操作员或并行计划。一般来说，您应该认为本地聚合与您在 SQL 中编写的普通聚合有点不同。

    这主要是实施细节以及保持本地和全局聚合之间连接的需要的结果。与许多探索规则一样，LocalAggBelowUniAll它表示您可以自己执行的查询重写，但您不应期望它在所有方面的行为与最接近的 T-SQL 表示完全相同。还应该说，优化器的优点是能够根据当前统计信息和元数据动态决定使用哪个重写。对于手动重写来说，通常情况并非如此。

    无论如何，本地聚合有点不同的后果之一是它没有与其分组键相关的唯一性保证。这在许多情况下都很重要，但对于您所需的合并串联运算符尤其如此。

    合并串联

    正如计划运算符的名称所示，Merge Concatenation 只是一个以特殊模式运行的普通 Merge Join 运算符。它需要对“连接键”上的输入进行排序，但具体需要哪种排序顺序可能会受到投影列列表、全局排序要求和任何可用的唯一性保证的影响（请参阅下面的参考资料）。

    提供分组键唯一性保证的普通聚合可以允许合并串联比本地聚合的输入需要更少的繁重排序。

    对于原始查询，优化器确实考虑了合并串联替代方案，但本地聚合意味着需要进行排序才能满足所需的输入属性：

    将串联计划与排序合并

    额外的排序和更高的合并串联成本意味着优化器选择了具有串联和单个排序的更便宜的计划选项。一如既往，这些选择是由成本模型驱动的。

    其他注意事项

    对于空表，优化器使用合并串联选项的成本最低，因为它只合并每个输入中的一行。合并串联的每行较高成本不会抵消串联计划中所需的额外排序的成本。

    您的查询应拒绝空值，或者基表应具有显式的NOT NULLValue属性。这将简化最终计划（并使任何未来的索引视图匹配更容易）。特别是，流聚合将不再需要计算聚合，并且不再需要计算标量。COUNT_BIG(Total)

    您还应该使用架构前缀并避免关键字作为属性名称。

    SELECT 
    TotalSales = SUM(T.Sales), 
    T.[Date]
FROM 
(
    SELECT 
        Sales = ISNULL(SUM(VM.[Value]), 0.0), 
        VM.[Date]
    FROM dbo.ViewMetrics AS VM
    WHERE
        VM.[Value] IS NOT NULL
    GROUP BY
        VM.Metric, 
        VM.[Date]
) AS T
GROUP BY 
    T.[Date];

    最终计划

    进一步阅读

    两篇都是我写的。

    • 6

相关问题