主页 / dba / 问题 / 239865
Accepted
Paul White
Asked: 2019-06-06 06:08:22 +0800 CST

CROSS APPLY 产生外连接

  • 772

在回答SQL 计算不同分区时， Erik Darling 发布了此代码以解决以下问题COUNT(DISTINCT) OVER ()：

SELECT      *
FROM        #MyTable AS mt
CROSS APPLY (   SELECT COUNT(DISTINCT mt2.Col_B) AS dc
                FROM   #MyTable AS mt2
                WHERE  mt2.Col_A = mt.Col_A
                -- GROUP BY mt2.Col_A 
            ) AS ca;

查询使用CROSS APPLY(not OUTER APPLY) 那么为什么执行计划中有外连接而不是内连接？

在此处输入图像描述

另外，为什么取消注释 group by 子句会导致内部联接？

在此处输入图像描述

我认为数据并不重要，但复制了 kevinwhat 在另一个问题上给出的数据：

create table #MyTable (
Col_A varchar(5),
Col_B int
)

insert into #MyTable values ('A',1)
insert into #MyTable values ('A',1)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',3)

insert into #MyTable values ('B',4)
insert into #MyTable values ('B',4)
insert into #MyTable values ('B',5)
sql-server execution-plan

2 个回答

  1. Best Answer

    概括

    SQL Server 使用正确的联接（内部或外部）并在必要时添加投影，以在执行apply和join之间的内部转换时遵守原始查询的所有语义。

    计划中的差异都可以通过SQL Server 中带有和不带有 group by 子句的聚合的不同语义来解释。

    细节

    加入与申请

    我们需要能够区分apply和join：

    • 申请

      apply的内部（下部）输入针对外部（上部）输入的每一行运行，其中一个或多个内侧参数值由当前外部行提供。应用的总体结果是参数化内侧执行产生的所有行的组合（全部联合）。参数意味着apply的存在有时被称为相关连接。

      应用总是由嵌套循环运算符在执行计划中实现。运算符将具有外部引用属性而不是连接谓词。外部引用是在循环的每次迭代中从外侧传递到内侧的参数。

    • 加入

      连接在连接运算符处评估其连接谓词。连接通常可以通过SQL Server 中的Hash Match、Merge或Nested Loops运算符来实现。

      选择嵌套环时，可以通过缺乏外部参考（通常存在连接谓词）来区分它。连接的内部输入从不引用来自外部输入的值 - 对于每个外部行，内部仍然执行一次，但内部执行不依赖于当前外部行中的任何值。

    有关更多详细信息，请参阅我的帖子Apply 与 Nested Loops Join。

    ...为什么执行计划中有外连接而不是内连接？

    当优化器将应用转换为连接（使用名为 的规则ApplyHandler）以查看它是否可以找到更便宜的基于连接的计划时，就会出现外连接。当应用程序包含标量聚合时，连接必须是外部连接以确保正确性。正如我们将看到的，内连接不能保证产生与原始应用相同的结果。

    标量和向量聚合

    • 没有相应GROUP BY子句的聚合是标量聚合。
    • 带有相应GROUP BY子句的聚合是向量聚合。

    在 SQL Server 中，标量聚合将始终生成一行，即使它没有指定要聚合的行。例如，COUNT没有行的标量聚合为零。没有行的向量 COUNT聚合是空集（根本没有行）。

    以下玩具查询说明了差异。您还可以在我的文章Fun with Scalar and Vector Aggregates中阅读有关标量和矢量聚合的更多信息。

    -- Produces a single zero value
SELECT COUNT_BIG(*) FROM #MyTable AS MT WHERE 0 = 1;

-- Produces no rows
SELECT COUNT_BIG(*) FROM #MyTable AS MT WHERE 0 = 1 GROUP BY ();

    db<>小提琴演示

    转型申请加入

    我之前提到过，当原始应用包含标量聚合时，连接必须是外部连接以确保正确性。为了详细说明为什么会出现这种情况，我将使用问题查询的简化示例：

    DECLARE @A table (A integer NULL, B integer NULL);
DECLARE @B table (A integer NULL, B integer NULL);

INSERT @A (A, B) VALUES (1, 1);
INSERT @B (A, B) VALUES (2, 2);

SELECT * FROM @A AS A
CROSS APPLY (SELECT c = COUNT_BIG(*) FROM @B AS B WHERE B.A = A.A) AS CA;

    column 的正确结果c为零，因为它COUNT_BIG是一个标量聚合。当将此应用查询转换为联接表单时，SQL Server 会生成一个内部替代方案，如果它以 T-SQL 表示，则类似于以下内容：

    SELECT A.*, c = COALESCE(J1.c, 0)
FROM @A AS A
LEFT JOIN
(
    SELECT B.A, c = COUNT_BIG(*) 
    FROM @B AS B
    GROUP BY B.A
) AS J1
    ON J1.A = A.A;

    要将 apply 重写为不相关的连接，我们必须GROUP BY在派生表中引入 a（否则可能没有A要连接的列）。联接必须是外部联接，以便表中的每一行@A继续在输出中生成一行。当连接谓词不为真时，左连接将产生一个NULLfor 列。需要将其转换为零才能完成从apply的正确转换c。NULLCOALESCE

    下面的演示展示了如何使用连接作为原始应用查询COALESCE来生成与外部连接相同的结果：

    db<>小提琴演示

    随着GROUP BY

    ...为什么取消注释 group by 子句会导致内部联接？

    继续简化示例，但添加GROUP BY：

    DECLARE @A table (A integer NULL, B integer NULL);
DECLARE @B table (A integer NULL, B integer NULL);

INSERT @A (A, B) VALUES (1, 1);
INSERT @B (A, B) VALUES (2, 2);

-- Original
SELECT * FROM @A AS A
CROSS APPLY 
(SELECT c = COUNT_BIG(*) FROM @B AS B WHERE B.A = A.A GROUP BY B.A) AS CA;

    现在COUNT_BIG是一个向量聚合，因此空输入集的正确结果不再为零，它根本不是行。换句话说，运行上面的语句不会产生任何输出。

    从apply转换为join时，这些语义更容易遵守，因为CROSS APPLY自然会拒绝任何不生成内侧行的外侧行。因此，我们现在可以安全地使用内连接，而无需额外的表达式投影：

    -- Rewrite
SELECT A.*, J1.c 
FROM @A AS A
JOIN
(
    SELECT B.A, c = COUNT_BIG(*) 
    FROM @B AS B
    GROUP BY B.A
) AS J1
    ON J1.A = A.A;

    下面的演示显示内部连接重写产生的结果与使用矢量聚合的原始应用相同：

    db<>小提琴演示

    优化器碰巧选择了与小表的合并内连接，因为它很快找到了一个便宜的连接计划（找到了足够好的计划）。基于成本的优化器可能会继续将连接重写为应用 - 可能会找到更便宜的应用计划，如果使用循环连接或 forceeek 提示，它会在这里 - 但在这种情况下不值得付出努力。

    笔记

    简化示例使用不同内容的不同表格，更清楚地显示语义差异。

    有人可能会争辩说，优化器应该能够推断自联接不能生成任何不匹配（非联接）的行，但它今天不包含该逻辑。无论如何，不​​能保证在查询中多次访问同一个表通常会产生相同的结果，具体取决于隔离级别和并发活动。

    优化器担心这些语义和边缘情况，因此您不必担心。

    奖金：内部申请计划

    SQL Server可以为示例查询生成内部应用计划（不是内部连接计划！），它只是出于成本原因选择不这样做。问题中显示的外部连接计划的成本是我笔记本电脑的 SQL Server 2017 实例上的0.02898个单位。

    您可以使用未记录和不受支持的跟踪标志 9114（禁用等）来强制应用ApplyHandler（相关连接）计划，仅用于说明：

    SELECT      *
FROM        #MyTable AS mt
CROSS APPLY 
(
    SELECT COUNT_BIG(DISTINCT mt2.Col_B) AS dc
    FROM   #MyTable AS mt2
    WHERE  mt2.Col_A = mt.Col_A 
    --GROUP BY mt2.Col_A
) AS ca
OPTION (QUERYTRACEON 9114);

    这会产生一个带有惰性索引假脱机的应用嵌套循环计划。总估计成本为0.0463983（高于所选计划）：

    索引线轴应用计划

    请注意，使用应用嵌套循环的执行计划使用“内连接”语义产生正确的结果，而不管GROUP BY子句是否存在。

    在现实世界中，我们通常会有一个索引来支持在apply的内侧进行查找，以鼓励 SQL Server 自然地选择此选项，例如：

    CREATE INDEX i ON #MyTable (Col_A, Col_B);

    db<>小提琴演示

    • 24

  2. 交叉应用是对数据的逻辑操作。在决定如何获取该数据时，SQL Server 会选择适当的物理运算符来获取您想要的数据。

    没有物理应用运算符，SQL Server 将其转换为适当的且有望高效的连接运算符。

    您可以在下面的链接中找到物理操作员的列表。

    https://learn.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference?view=sql-server-2017

    查询优化器将查询计划创建为由逻辑运算符组成的树。查询优化器创建计划后，查询优化器为每个逻辑运算符选择最有效的物理运算符。查询优化器使用基于成本的方法来确定哪个物理运算符将实现逻辑运算符。

    通常，一个逻辑运算可以由多个物理运算符来实现。但是，在极少数情况下，物理运算符也可以实现多个逻辑运算。

    编辑/看来我理解你的问题是错误的。SQL 服务器通常会选择最合适的运算符。您的查询不需要返回两个表的所有组合的值，这是使用交叉连接的时候。只需计算每行所需的值就足够了，这就是这里所做的。

    • -3

相关问题