SqlServer中的UNION操作符在合并資料時去重的原理以及UNION運算子查詢結果預設排序的問題

標籤：end   好的   hellip   開場白   created   date   這一   純粹   ble   

原文: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    endcommitcreate 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的效能上做討論。

SqlServer中的UNION操作符在合并資料時去重的原理以及UNION運算子查詢結果預設排序的問題