主页 / dba / 问题 / 30165
Accepted
孔夫子
Asked: 2012-12-12 03:34:45 +0800 CST

为什么 SQL Server 不优化 UNION？

  • 772

考虑这些查询（SQL Fiddle）：

查询一：

SELECT * INTO #TMP1 FROM Foo
UNION
SELECT * FROM Boo
UNION
SELECT * FROM Koo;

查询 2：

SELECT * INTO #TMP2 FROM Foo
UNION
SELECT * FROM Boo
UNION ALL
SELECT * FROM Koo;

请注意，Koo 与 Boo/Foo 不重叠，因此最终结果是相同的。问题是为什么第一个UNION / UNION组合没有合并到单个 SORT 操作中？

sql-server performance

2 个回答

  1. Best Answer

    查询优化器确实有 n 元运算符，尽管执行引擎的数量要少得多。为了说明，我将使用您的表的简化版本 - (SQL Fiddle)。

    SELECT DISTINCT
    number
INTO foo
FROM master..spt_values
WHERE 
    number < 1000;

SELECT DISTINCT
    number
INTO boo
FROM master..spt_values
WHERE 
    number between 300 and 1005;

SELECT DISTINCT
    number
INTO koo
FROM master..spt_values
WHERE 
    number > 1006;

ALTER TABLE dbo.foo ADD PRIMARY KEY (number);
ALTER TABLE dbo.boo ADD PRIMARY KEY (number);
ALTER TABLE dbo.koo ADD PRIMARY KEY (number);

    给定这些表和数据，让我们看一下三向UNION查询的输入树：

    SELECT f.number FROM dbo.foo AS f
UNION
SELECT b.number FROM dbo.boo AS b
UNION
SELECT k.number FROM dbo.koo AS k
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8605);

LogOp_Union
    OUTPUT(COL: Union1006 )
    CHILD(QCOL: [f].number)
    CHILD(QCOL: [b].number)
    CHILD(QCOL: [k].number)
    LogOp_Project
        LogOp_Get TBL: dbo.foo(alias TBL: f)
        AncOp_PrjList 
    LogOp_Project
        LogOp_Get TBL: dbo.boo(alias TBL: b)
        AncOp_PrjList 
    LogOp_Project
        LogOp_Get TBL: dbo.koo(alias TBL: k)
        AncOp_PrjList

    逻辑联合运算符具有一个输出和三个子输入。经过基于成本的优化后，选择的物理树是具有三个输入的合并联合：

    SELECT f.number FROM dbo.foo AS f
UNION
SELECT b.number FROM dbo.boo AS b
UNION
SELECT k.number FROM dbo.koo AS k
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8607);

PhyOp_MergeUnion
    PhyOp_Range TBL: dbo.foo(alias TBL: f)(1) ASC
    PhyOp_Range TBL: dbo.boo(alias TBL: b)(1) ASC
    PhyOp_Range TBL: dbo.koo(alias TBL: k)(1) ASC

    优化器的输出被重新设计成执行引擎（没有 n 元合并联合）可以处理的形式：

    合并工会计划

    优化后重写将 n 元展开PhyOp_MergeUnion为多个合并联合运算符。请注意所有估计成本如何保持与“原始”联合运营商相关联 - 其他人的成本估计为零。

    优化器对使用 n 元运算符的联合的原因提供了一个解释，解释了为什么它不考虑将第一个示例重写为与第二个示例相同的计划（三向联合是单个树节点）。

    第二个原因是没有强制执行“缺乏重叠”的限制。在约束到位之前，不能保证和之间的联合不会产生重复，因此我们得到一个重复删除计划（在这种情况下为合并联合）boo：koo

    SELECT b.number FROM dbo.boo AS b
UNION
SELECT k.number FROM dbo.koo AS k;

    boo/koo 没有限制

    添加以下约束可确保在不使查询的缓存计划无效的情况下不会违反非重叠条件：

    ALTER TABLE dbo.foo WITH CHECK ADD CHECK (number < 1000);
ALTER TABLE dbo.boo WITH CHECK ADD CHECK (number BETWEEN 300 AND 1005);
ALTER TABLE dbo.koo WITH CHECK ADD CHECK (number > 1006);

    现在优化器可以安全地简单地连接：

    有约束的 boo/koo

    然而，即使有了这些约束，三路联合查询仍然显示为三个联合，因为优化器通常不会考虑拆分 n 元运算符来探索替代方案。n 元运算符对于控制搜索空间非常有用；考虑到优化器的目标是快速找到一个好的计划，将其拆分通常会适得其反。

    SELECT f.number FROM dbo.foo AS f
UNION
SELECT b.number FROM dbo.boo AS b
UNION
SELECT k.number FROM dbo.koo AS k;

    合并带有约束的联合计划

    当写成UNIONandUNION ALL时，不能再使用 n 元运算符（类型不匹配），因此树有单独的节点：

    SELECT f.number FROM dbo.foo AS f
UNION
SELECT b.number FROM dbo.boo AS b
UNION ALL
SELECT k.number FROM dbo.koo AS k
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8605);

LogOp_UnionAll
    OUTPUT(COL: Union1007 )
    CHILD(COL: Union1004 )
    CHILD(QCOL: [k].number)

    LogOp_Union
        OUTPUT(COL: Union1004 )
        CHILD(QCOL: [f].number)
        CHILD(QCOL: [b].number)

        LogOp_Project
            LogOp_Get TBL: dbo.foo(alias TBL: f)
            AncOp_PrjList 

        LogOp_Project
            LogOp_Get TBL: dbo.boo(alias TBL: b)
            AncOp_PrjList 

    LogOp_Project
        LogOp_Get TBL: dbo.koo(alias TBL: k)
        AncOp_PrjList
    • 18

  2. SQL Server确实有 3-way set 操作；CONCATENATION 运算符接受n 个输入。例如，给定十个表：

    CREATE TABLE Test01 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80)); 
CREATE TABLE Test02 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80));
CREATE TABLE Test03 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80));
CREATE TABLE Test04 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80));
CREATE TABLE Test05 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80));
CREATE TABLE Test06 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80));
CREATE TABLE Test07 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80));
CREATE TABLE Test08 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80));
CREATE TABLE Test09 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80));
CREATE TABLE Test10 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80));

    以及一个联合所有内容以查找每个表中具有相同键的任何行的查询：

    SELECT * FROM
(
SELECT * FROM Test01 UNION ALL
SELECT * FROM Test02 UNION ALL
SELECT * FROM Test03 UNION ALL
SELECT * FROM Test04 UNION ALL
SELECT * FROM Test05 UNION ALL
SELECT * FROM Test06 UNION ALL
SELECT * FROM Test07 UNION ALL
SELECT * FROM Test08 UNION ALL
SELECT * FROM Test09 UNION ALL
SELECT * FROM Test10
) AS Bunch
WHERE SomeKey = 39;

    我们将看到一个查询计划，它获取匹配的行（在 TABLE SCAN 运算符中使用谓词下推），然后将所有结果连接到SELECT运算符中。

    您没有计划合并然后排序的原因是因为它会非常慢，并且排序不是实现UNION操作所必需的。在您的 BOO、FOO 和 KOO 表中，您已经声明了一个主键。当 CLUSTERED INDEX SCAN 访问器枚举行时，它们是按照底层聚集索引的顺序生成的——保证。连接两个集合然后对结果进行排序比使用 MERGE JOIN 运算符要慢得多，并且 MJ 运算符可以很容易地使用，因为两个集合都已排序和索引。

    • 3

相关问题