SqlServer中的UNION操作符在合并数据时去重的原理以及UNION运算符查询结果默认排序的问题

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介: 原文:SqlServer中的UNION操作符在合并数据时去重的原理以及UNION运算符查询结果默认排序的问题  本文出处:http://www.cnblogs.com/wy123/p/7884986.
原文: SqlServer中的UNION操作符在合并数据时去重的原理以及UNION运算符查询结果默认排序的问题

 

本文出处:http://www.cnblogs.com/wy123/p/7884986.html 

 

 

周围又有人在讨论UNION和UNION ALL,对于UNION和UNION ALL,网上说的最多的就是性能问题(实在不想说出来这句话:UNION ALL比UNION快)
其实根本不想炒UNION和UNION ALL这碗剩饭了,
每次看到网上说用这个不用那个,列举的一条一条的那种文章,只要看到说UNION ALL比UNION性能好的就……

对于合并的结果集,UNION是去重的,UNION ALL是不去重的,去重与不去重是两个目的,分别由UNION和UNION ALL实现
两个作用(功能)不同的东西,放一起比性能有什么意义?
这种问题真的是无聊至极,就好比“足球场上的某个中后卫和某个前腰哪个能力更强”一样没有可比性,
他们的作用本身就是不同的,难道说中后卫能力不行,把他撤下来,用一个牛逼的前腰球员替代中后卫,或者是前腰能力不行,撤下他用牛逼的中后卫替代?
这是在功能上的区别,至于性能,我个人认为对比起来没有任何意义。
如果非要放一起比的话,做同样的数据合并,
UNION因为要去重,相对UNION ALL来说,(相对)当然会耗费更多的资源(耗费的资源多少跟性能无关,做的事情多,当然需要更多的资源)
但是一定要弄清楚,合并数据的时候,到底要不要去掉重复数据,这是最终结果对与错的问题,不是性能问题!

这里不讨论UNION和UNION ALL的性能了,
从另外一个点入手来发起问题
UNION与UNION ALL最大的区别就是UNION会去重,那么问题就来了,这个去重是怎么实现的?去重会对查询的默认顺序集产生什么影响?

 

UNION去重的实现

测试一下UNION运算符去重的实现原理

create table TestUnion1
(    
    
    Id1 INT PRIMARY KEY,
    Id2 tinyint,
    Name varchar(100)
);
create table TestUnion2
(
    Id1 INT PRIMARY KEY,
    Id2 tinyint,
    Name varchar(100)
);

insert into TestUnion1 values (500,9,'aaa')
insert into TestUnion1 values (700,3,'ccc')
insert into TestUnion1 values (200,7,'eee')


insert into TestUnion2 values (300,2,'bbb')
insert into TestUnion2 values (800,8,'ddd')
insert into TestUnion2 values (100,5,'fff')

--TestUnionALL1和TestUnionALL2中相同的数据
insert into TestUnion1 values (600,6,'xxx')
insert into TestUnion2 values (600,6,'xxx')

UNION在去重的过程中,使用的执行计划是Merge Join,UNION ALL是不去重的,同样步骤对应的执行计划是Concatenation

这里UNION的去重动作是通过merge实现,这里的merge join并不是表与表之间的merge join
这里可以看出来,UNION产生的merge与 inner join产生的Merge的作用是有差异的

对于UNION的去重的这一动作,去当然不是说只有merge join一种,这里只不过是两个结果的数据都刚好有序才采用merge join来去重罢了

如果查询字段的顺序的第一个字段是聚集索引(或者主键),,正如上文提到的,UNION的双方就会以merge的方式区中
如果查询字段的顺序非聚集索引,UNION的过程是现将两个结果集合并起来(上文提到的Concatenation),然后再做sort排序去重

  


UNION之后结果集的最终排序结果

UNION之后结果集的最终排序结果跟查询字段的顺序有关,
如果查询字段的顺序的第一个字段是聚集索引(或者主键),正如上文提到的,UNION的双方就会以merge的方式区中
如果查询字段的顺序的第一个字段是非聚集索引字段,UNION的过程是现将两个结果集合并起来(上文提到的Concatenation),然后再做sort排序去重
如下的实例能说说明这个问题,当查询字段的顺序发生变化之后,两者的执行计划完全不一致。

或者再看一个case,当Name在最前面的时候,最终的结果就是按照name排序。

   可能有人会怀疑是不是数据量太小了,是不是巧合,这里可以加大测试数据库,在查询条件中,让非聚集索引参与到运算之中

create table TestUnion1
(    
    
    Id1 INT PRIMARY KEY,
    Id2 tinyint,
    Name varchar(100),
    CreateDate datetime
);


create table TestUnion2
(
    Id1 INT PRIMARY KEY,
    Id2 tinyint,
    Name varchar(100),
    CreateDate datetime
);


begin tran
    declare @i int = 0
    while @i<1000000
    begin
        insert into TestUnion1 values (@i,rand()*200,newid(),getdate()-rand()*1000)
        insert into TestUnion2 values (@i,rand()*200,newid(),getdate()-rand()*1000)
        set @i=@i+1
    end
commit


create index idx_CreateDate on TestUnion1(CreateDate)
create index idx_CreateDate on TestUnion2(CreateDate)

参考下图,一旦查询结果集不是按照查询字段聚集索引排序的话,
比如这里走的是createDate时间字段的索引,执行计划都是先按照普通的方式合并结果集,也即Concatenation
然后在利用Sort(Distinct)的方式排序去重,对于去重的结果的最终的排序,跟查询结果的第一个字段有关,且结果总是按照查询的第一个字段排序的。

  换一种查询字段的顺序方式,看一下结果,仍旧是按照查序列的第一个字段排序的

  UNION运算符在去重的时候,
  如果查询字段的第一个字段是聚集索引,那么会用merge join的方式合并+去重。
  如果查询字段的第一个字段不是非聚集索引,那么首先会将两个(或者多个)结果集进行普通的合并,最后通过Sort Distinct的方式去重。  
  且UNION运算之后的默认排序方式,受查询字段前后的方式影响。 

 

总结:

  UNION和UNION ALL的作用是不一样的,放在一起比性能没有任何意义,真不想趟这趟浑水。
  合并结果集,需要去重就用UNION,不需要去重就用UNION ALL,如果两个结果集中没有重复的结果集,就用UNION ALL,
  这纯粹是需求驱动的,而不是UNION和UNION ALL的性能问题。

 

多撤一句:
曾经大晚上接到一个面试电话,没有任何开场白,第一句话是“我们电话面试一下可以吗”,答曰可以,第二句话就是“UNION和UNION ALL的区别是什么,有没有性能差异”。
真的不希望再去对UNION和UNION ALL的性能上做讨论。

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
24天前
|
SQL 存储 数据挖掘
SQL Server 日期格式查询详解
SQL Server 日期格式查询详解
113 2
|
3月前
|
SQL Java 数据库
jsp中使用Servlet查询SQLSERVER数据库中的表的信息,并且打印在屏幕上
该博客文章介绍了在JSP应用中使用Servlet查询SQL Server数据库的表信息,并通过JavaBean封装图书信息,将查询结果展示在Web页面上的方法。
jsp中使用Servlet查询SQLSERVER数据库中的表的信息,并且打印在屏幕上
|
3月前
|
SQL Java 数据库连接
JDBC连接SQL Server2008 完成增加、删除、查询、修改等基本信息基本格式及示例代码
这篇文章提供了使用JDBC连接SQL Server 2008数据库进行增加、删除、查询和修改操作的基本步骤和示例代码。
|
3月前
|
Java 测试技术 容器
从零到英雄:Struts 2 最佳实践——你的Web应用开发超级变身指南!
【8月更文挑战第31天】《Struts 2 最佳实践:从设计到部署的全流程指南》深入介绍如何利用 Struts 2 框架从项目设计到部署的全流程。从初始化配置到采用 MVC 设计模式,再到性能优化与测试,本书详细讲解了如何构建高效、稳定的 Web 应用。通过最佳实践和代码示例,帮助读者掌握 Struts 2 的核心功能,并确保应用的安全性和可维护性。无论是在项目初期还是后期运维,本书都是不可或缺的参考指南。
46 0
|
3月前
|
SQL 存储 安全
Play Framework的安全面纱:揭开隐藏在优雅代码下的威胁
【8月更文挑战第31天】Play Framework 是一款高效、轻量级的 Web 开发框架,内置多种安全特性,助力开发者构建安全稳定的应用。本文详细介绍 Play 如何防范 SQL 注入、XSS 攻击、CSRF 攻击,并提供安全的密码存储方法及权限管理策略,通过具体示例代码展示实施步骤,助您有效抵御常见威胁。
60 0
|
3月前
|
SQL 存储 开发框架
Entity Framework Core 与 SQL Server 携手,高级查询技巧大揭秘!让你的数据操作更高效!
【8月更文挑战第31天】Entity Framework Core (EF Core) 是一个强大的对象关系映射(ORM)框架,尤其与 SQL Server 数据库结合使用时,提供了多种高级查询技巧,显著提升数据操作效率。它支持 LINQ 查询,使代码简洁易读;延迟加载与预先加载机制优化了相关实体的加载策略;通过 `FromSqlRaw` 或 `FromSqlInterpolated` 方法支持原始 SQL 查询;可调用存储过程执行复杂任务;利用 `Skip` 和 `Take` 实现分页查询,便于处理大量数据。这些特性共同提升了开发者的生产力和应用程序的性能。
125 0
|
3月前
|
SQL 存储 数据管理
掌握SQL Server Integration Services (SSIS)精髓:从零开始构建自动化数据提取、转换与加载(ETL)流程,实现高效数据迁移与集成——轻松上手SSIS打造企业级数据管理利器
【8月更文挑战第31天】SQL Server Integration Services (SSIS) 是 Microsoft 提供的企业级数据集成平台,用于高效完成数据提取、转换和加载(ETL)任务。本文通过简单示例介绍 SSIS 的基本使用方法,包括创建数据包、配置数据源与目标以及自动化执行流程。首先确保安装了 SQL Server Data Tools (SSDT),然后在 Visual Studio 中创建新的 SSIS 项目,通过添加控制流和数据流组件,实现从 CSV 文件到 SQL Server 数据库的数据迁移。
133 0
|
3月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
270 0
|
3月前
|
SQL 存储 测试技术
SQL Server 查询超时问题排查
【8月更文挑战第14天】遇到SQL Server查询超时,先检查查询复杂度与索引使用;审视服务器CPU、内存及磁盘I/O负载;审查SQL Server配置与超时设置;检测锁和阻塞状况;最后审查应用代码与网络环境。每步定位问题根源,针对性优化以提升查询效率。务必先行备份并在测试环境验证改动。
230 0
|
3月前
|
SQL 存储 数据管理
解锁 SQL Server 2022的时间序列数据功能
【8月更文挑战第14天】解锁SQL Server 2022的时间序列数据功能需先确认版本支持;接着创建数据库与含时间列的表,如`TimeSeriesData`;然后插入时间序列数据;利用内置函数如窗口函数计算移动平均等统计;最后针对大数据量配置索引及分区以优化性能。这流程助力高效处理时间序列数据。