主页 / dba / 问题 / 112300
Accepted
Geoff Patterson
Asked: 2015-08-27 09:29:52 +0800 CST

在两个表中查找不同的行：完全外连接比联合更有效？

  • 772

在我们不一定要确保已预先排序的两个表中查找不同的行时，使用 aFULL OUTER JOIN而不是 a是个好主意UNION吗？这种方法有什么缺点吗？如果它始终更快，为什么查询优化器不为 UNION 选择FULL OUTER JOIN将使用的相同计划？

通过将 a 重写UNION为FULL OUTER JOIN. AUNION似乎是编写逻辑的更直观的方式，但在探索这两个选项后，我发现它在FULL OUTER JOIN内存和 CPU 使用方面都更有效。

如果您想运行我们的生产查询的简化和匿名版本，请参阅以下脚本：

设置脚本

-- Create a 500K row table
SELECT TOP 500000 ROW_NUMBER() OVER (ORDER BY NEWID()) AS id, v1.number % 5 AS val
INTO #t1
FROM master..spt_values v1
CROSS JOIN master..spt_values v2

-- Create a 5MM row table that will match some, but not all, of the 500K row table
SELECT TOP 5000000 ROW_NUMBER() OVER (ORDER BY NEWID()) AS id, v1.number % 5 AS val
INTO #t2
FROM master..spt_values v1
CROSS JOIN master..spt_values v2

-- Optionally, key both tables to see the impact it has on query plans and performance
-- Both queries end up with essentially the same plan and performance in this case
-- So that means that at least there is not a downside to using the FULL OUTER JOIN when the data is sorted
--ALTER TABLE #t1
--ADD UNIQUE CLUSTERED (id)
--ALTER TABLE #t2
--ADD UNIQUE CLUSTERED (id)

全外连接

选择两个表中较小的FULL OUTER JOIN一个作为哈希连接的构建端，这意味着内存使用量与较小表的大小（500K 行）成正比。

-- CPU time = 3058 ms,  elapsed time = 783 ms.
-- MaxUsedMemory: 29016 KB
-- Table '#t1'. Scan count 5, logical reads 1301, physical reads 0
-- Table '#t2'. Scan count 5, logical reads 12989, physical reads 0
SELECT COUNT(*), AVG(id), AVG(val)
FROM (
    SELECT COALESCE(t1.id, t2.id) AS id, COALESCE(t1.val, t2.val) AS val
    FROM #t1 t1
    FULL OUTER JOIN #t2 t2
        ON t2.id = t1.id
        AND t2.val = t1.val
) x
GO

在此处输入图像描述

联盟

为整个数据集上的UNION哈希聚合构建一个哈希表，这意味着内存使用与不同行的总数成正比（在这种情况下为 5.4MM 行；通常，至少是较大的行数中的行数）两张表）。内存使用量是 10 倍以上FULL OUTER JOIN，而且 CPU 时间和运行时间也更慢。如果我将其扩展到哈希聚合无法容纳单个查询的内存授权的程度，性能差异将变得巨大（就像在我们的大型生产查询中一样）。

-- CPU time = 4651 ms,  elapsed time = 1188 ms.
-- MaxUsedMemory: 301600 KB
-- Table '#t1'. Scan count 5, logical reads 1301, physical reads 0
-- Table '#t2'. Scan count 5, logical reads 12989, physical reads 0
SELECT COUNT(*), AVG(id), AVG(val)
FROM (
    SELECT t1.id, t1.val
    FROM #t1 t1
    UNION 
    SELECT t2.id, t2.val
    FROM #t2 t2
) x

在此处输入图像描述

sql-server performance

1 个回答

  1. Best Answer

    两个查询的语义不同 -UNION删除重复项，而FULL OUTER JOIN不会：

    DECLARE @T1 AS table (id bigint NULL, val integer NULL);
DECLARE @T2 AS table (id bigint NULL, val integer NULL);

INSERT @T1 (id, val) VALUES (1, 1);
INSERT @T1 (id, val) VALUES (1, 1);
INSERT @T2 (id, val) VALUES (1, 1);
INSERT @T2 (id, val) VALUES (1, 1);

SELECT COALESCE(t1.id, t2.id) AS id, COALESCE(t1.val, t2.val) AS val
FROM @t1 t1
FULL OUTER JOIN @t2 t2
    ON t2.id = t1.id
    AND t2.val = t1.val;

SELECT t1.id, t1.val
FROM @t1 t1
UNION 
SELECT t2.id, t2.val
FROM @t2 t2;

    输出：

    ╔════╦═════╗
║ id ║ val ║
╠════╬═════╣
║  1 ║   1 ║
║  1 ║   1 ║
║  1 ║   1 ║
║  1 ║   1 ║
╚════╩═════╝

╔════╦═════╗
║ id ║ val ║
╠════╬═════╣
║  1 ║   1 ║
╚════╩═════╝

    也就是说，优化器并不知道很多FOJN技巧，因此总有可能有比自然查询更好的方式来表达查询UNION。仅实施常用且始终正确的转换。

    请注意，仅在较大表上具有唯一约束时，优化器会选择哈希联合，而不会对探测输入进行昂贵的重复删除，这使得它在问题示例中选择 Concat Union All：

    ALTER TABLE #t2 
ADD CONSTRAINT UQ2 
UNIQUE CLUSTERED (id);

SELECT COUNT(*), AVG(x.id), AVG(x.val)
FROM (
    SELECT t1.id, t1.val
    FROM #t1 t1
    UNION
    SELECT t2.id, t2.val
    FROM #t2 t2
) AS x;

    哈希联合计划

    FOJN在您知道每个输入集中不能重复的情况下，重写可能很有用，但是这种情况不是通过唯一约束或索引强制执行的（特别是在大输入上）。

    如果确实存在这样的唯一性保证，但优化器没有选择哈希联合，您可以尝试OPTION (HASH UNION)提示，看看它是如何比较的。

    • 9

相关问题