sql-server - UNION 与 SELECT DISTINCT 和 UNION ALL 性能对比

Question

这两者在性能方面有什么区别吗？

-- eliminate duplicates using UNION
SELECT col1,col2,col3 FROM Table1 
UNION SELECT col1,col2,col3 FROM Table2 
UNION SELECT col1,col2,col3 FROM Table3 
UNION SELECT col1,col2,col3 FROM Table4 
UNION SELECT col1,col2,col3 FROM Table5       
UNION SELECT col1,col2,col3 FROM Table6       
UNION SELECT col1,col2,col3 FROM Table7       
UNION SELECT col1,col2,col3 FROM Table8       

-- eliminate duplicates using DISTINCT    
SELECT DISTINCT * FROM
(     
    SELECT col1,col2,col3 FROM Table1 
    UNION ALL SELECT col1,col2,col3 FROM Table2 
    UNION ALL SELECT col1,col2,col3 FROM Table3 
    UNION ALL SELECT col1,col2,col3 FROM Table4 
    UNION ALL SELECT col1,col2,col3 FROM Table5       
    UNION ALL SELECT col1,col2,col3 FROM Table6       
    UNION ALL SELECT col1,col2,col3 FROM Table7       
    UNION ALL SELECT col1,col2,col3 FROM Table8       
) x   

Answer 1

Union 和 Union all 之间的区别在于 UNION ALL 不会消除重复行，而只是从所有表中提取所有行适合您的查询细节并将它们合并到一个表中。

UNION 语句有效地对结果集执行 SELECT DISTINCT。

如果您选择“Distinct from Union All”结果集，则输出将等于 Union 结果集。

编辑:

CPU 成本性能:

让我用例子来解释:

我有两个疑问。一种是 Union，另一种是 Union All

SET STATISTICS TIME ON
GO
 
select distinct * from (select * from dbo.user_LogTime
union all
select * from dbo.user_LogTime) X 
GO

SET STATISTICS TIME OFF

SET STATISTICS TIME ON
GO
 
select * from dbo.user_LogTime
union
select * from dbo.user_LogTime
GO

SET STATISTICS TIME OFF

我确实在 SMSS 的同一查询窗口中运行了这两者。让我们看看 SMSS 中的执行计划:

发生的情况是，使用 Union All 和 Distinct 的查询将比使用 Union 的查询花费更多的 CPU 成本。

准时表现:

UNION ALL:

(1172 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 39 ms.

联合:

(1172 row(s) affected)

SQL Server Execution Times:
   CPU time = 10 ms,  elapsed time = 25 ms.

因此，Union 在性能方面比 Union All with Distinct 好得多