主页 / dba / 问题 / 292746
Accepted
HeyJude
Asked: 2021-06-04 14:12:37 +0800 CST

了解表假脱机和相关子查询之间的关系

  • 772

我正在学习执行计划，并且对表假脱机和相关子查询之间的关系有疑问（我正在关注本教程，不要介意拼写错误）。

我创建了下表：

CREATE TABLE student (
  ID INT IDENTITY(1, 1),
  CX_Name VARCHAR(50),
  CX_PhoneNum VARCHAR(50),
  CX_Address VARCHAR(MAX),
  CX_Credit INT
)

然后插入值：

INSERT INTO student
VALUES (
  'Alen',
  '9625788954',
  'London',
  500
) 
GO 100
 
INSERT INTO student
VALUES (
  'Frank',
  '962445785',
  'Germany',
  1400
)
GO 100

然后执行以下查询，其中包括一个相关的子查询：

SELECT ID, CX_Name, CX_Credit
FROM student CX1 
WHERE CX_Credit >= (
    SELECT AVG(CX_Credit)
    FROM student CX2
    WHERE CX1.ID = CX2.ID
)

执行计划是：

在此处输入图像描述

本教程解释（粗体是我的）：

SQL Server Engine 先从表中读取数据，对数据进行排序，然后再将其划分为段，然后创建一个临时表来存储数据组。

在解释计划的另一部分，SQL Server 引擎从 Table Spool 中读取数据，然后使用 Stream Aggregate 运算符计算每个组的平均信用值。

最后一个 Table Spool 操作员将读取分组数据并将其连接以检索高于平均值的值。

三个 Table Spool 操作员将使用第一次创建的同一个临时表。

我不明白加粗的句子，有两种方式：

  1. 必须为每个学生记录重新执行相关子查询。那么分组在这里有什么作用呢？
  2. 一个组以什么方式“存储”在表线轴内？
sql-server execution-plan

1 个回答

  1. Best Answer

    执行计划如何运作

    段假脱机一次存储一组的行。子树每组执行一次。在每个组的处理结束时，假脱机被截断，并且对下一组行重复处理。

    我在Partitioning and the Common Subexpression Spool中写了完整的细节。

    Segment Spool迭代器始终显示为Segment迭代器的直接父级。计划中显示的两个叶级表假脱机迭代器是辅助假脱机，它只重放主假脱机保存的行。

    Segment Spool懒惰地将行写入其工作表，直到发出新组的开始信号。一旦Segment Spool在其工作表中有一个完整的组，就会将一行（不是整个组）返回给其父级（本示例中的顶级嵌套循环运算符）。

    这一行中存储的数据值并不重要；它们对最终结果没有贡献。关键是这一单行是在父嵌套循环迭代器的外部输入上接收的。这导致迭代器每组执行一次其内部输入。

    你的例子

    在您的示例中，分组是由以下相关性暗示的ID：

    WHERE CX1.ID = CX2.ID

    CX1.ID外部参考在哪里。

    给定原始查询（缺少的 CX2 别名添加到 中AVG）：

    SELECT ID, CX_Name, CX_Credit
FROM student CX1 
WHERE CX_Credit >= (
    SELECT AVG(CX2.CX_Credit)
    FROM student CX2
    WHERE CX1.ID = CX2.ID
)

    是的，原则上，来自 CX1 的每一行都会计算出来自 CX2 的所有行的平均值，其中与外行中ID的当前ID值匹配。正是在这个意义上，群体才形成。

    一般来说，以这种方式逐字执行查询效率很低，并且会导致多次计算相同的平均值。这就是我们有优化器的原因；找到产生相同逻辑结果的等效物理计划，但效率更高。在这种情况下，这意味着计算一次组平均值并通过重放假脱机将其交叉连接到当前组中的行。

    更重要的是，这里的假脱机解决了在流中还没有看到的行上计算聚合的问题。考虑到最终计划只访问一次基表，尽管在原始查询中有两次引用它。将组中的行保存一次并重放它们会更有效，而不是每个外部行访问一次基表。

    例如，假设我们阻止优化器将查询规范转换为“分组应用”：

    SELECT ID, CX_Name, CX_Credit
FROM student CX1 
WHERE CX_Credit >= (
    SELECT AVG(CX2.CX_Credit)
    FROM student CX2
    WHERE CX1.ID = CX2.ID
)
OPTION (QUERYRULEOFF GenGbApplySimple);

    执行计划现在有两个表访问：

    没有 group-by 应用

    如果我们进一步限制可用的优化技巧，我们会更接近原文的字面解释：

    SELECT ID, CX_Name, CX_Credit
FROM student CX1 
WHERE CX_Credit >= (
    SELECT AVG(CX2.CX_Credit)
    FROM student CX2
    WHERE CX1.ID = CX2.ID
)
OPTION 
(
    QUERYRULEOFF GenGbApplySimple, 
    LOOP JOIN, 
    FORCE ORDER, 
    NO_PERFORMANCE_SPOOL
);

    相当字面

    您可能会发现更直观的等效查询规范是：

    SELECT
    S1.ID,
    S1.CX_Name,
    S1.CX_Credit
FROM 
(
    SELECT 
        S.*, 
        avg_credit = AVG(S.CX_Credit) OVER (
            PARTITION BY S.ID)
    FROM dbo.student AS S
) AS S1
WHERE
    S1.CX_Credit >= S1.avg_credit;

    给出的示例不是很有用，因为ID它实际上是独一无二的。如果没有某种约束，优化器无法保证这一点，因此它会防御性地添加一个假脱机。如果我们确保ID是唯一的：

    CREATE UNIQUE INDEX i ON dbo.student (ID);

    原始查询生成一个没有聚合的连接计划（因为最多一行的聚合是多余的）：

    加入

    请尝试以下我的博客文章中的示例。您可以使用https://dbfiddle.uk/，它可以选择每次都从 AdventureWorks 数据库的新副本开始。

    进一步阅读

    我的其他相关帖子：

    • 2

相关问题