SQL 提示介绍 hash/merge/concat union

　　　　查询提示一直是个很有争议的东西，因为他影响了sql server 自己选择执行计划。很多人在问是否应该使用查询提示的时候一般会被告知慎用或不要使用...但是个人认为善用提示在不修改语句的条件下，是常用手段。另外如果你是一个公司的dba 并且你对你所维护的数据库了如指掌，对业务也有相当深刻的了解那么查询提示也是你的一把利器。

　　　　但是，你所应用的提示是在现在的场景中基于现有的环境下，相对是一个好的方式，不能确保你所给予的提示永久有效，并且随着时间推移，数据量的变更，你所加的提示可能成为噩梦。所以没有充分的把握不要轻易使用提示。

　　　　下面说一下union的几个运算符 hash/merge/concat

　　　　创建两张表(union使用),并插入测试数据

CREATE TABLE [dbo].[t4](
    [a] [int] NULL,
    [b] [datetime] NULL,
    [c] [uniqueidentifier] NOT NULL,
    [d] [nchar](10) NULL,
 CONSTRAINT [PK_t4] PRIMARY KEY CLUSTERED 
(
    [c] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[t5](
    [a] [int] NULL,
    [b] [datetime] NULL,
    [c] [uniqueidentifier] NOT NULL,
    [d] [nchar](10) NULL,
 CONSTRAINT [PK_t5] PRIMARY KEY CLUSTERED 
(
    [c] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO



insert into t4
select 1,getdate(),newid(),'test'
go 2000



insert into t5
select 1,getdate(),newid(),'test'
go 5000

 

 

　　　　我们先看一下正常union all的执行计划,一个正常的串联计划.

　　　　

　　　　

 

　　　　下面我们加上提示:

　　　　

　　　　

 

　　　　发现加了提示一点效果也没有呀!!! 这是闹哪样? 两个查询什么额外操作都没有因否因为这个而让优化器默认选择串联呢?

　　　　下面我们修改一下查询把union all 改成union (这相当于要做一个去重复的操作)

　　　　

 

　　　　运算符 "串联"不见了 变成了 hash匹配,那么现在是不是可以用提示了呢? 我们试一下....

　　　　添加提示option(merge union)

　　　　

　　　　添加提示option(concat union)

　　　　

 

　　　　这三个计划有什么不一样呢？请继续往下看...^_^

　　　　我们继续添加排序操作 order 并去掉c（唯一列的）让去重有效果。

　　　　

　　　　

　　　　

 

 

　　　　上面的三个提示分别选择的不同的计划，那么下面我们对比前一组来综合说明：

• 第一组（提示无效果的） 这是因为优化器还是很智能的他默认两个结果集的拼接不需要任何去重获排序操作所以适用串行直接进行结果集的拼接。
• 第二组 select * 的时候虽然也用了union （去重复操作）但是没有看到任何distinct的操作，这是select 的字段中包含唯一键（newid），个人认为也是优化器智能的表现，但是在提示concat中出现流聚合的去重操作。
• 第三组 这里默认选择的是merge（比较常规的选择，但也视情况而定）相当于使用Distinct Sort排序操作排序并去重以后做合并，这种选择最好使用于表数据量不算太大（因为大表排序是一个很消耗资源的事情）或者排序的字段是已经过排序的比如（索引扫描）
• hash 提示中的计划有点意外因为正常来说是不会出现Distinct Sort排序操作的，但是因为测试数据中重复数据较大所以在第二张表扫描后优化器选择了Distinct Sort排序操作，（优化器之所以这么选择取决于统计信息5000 distinct-〉1509），正常情况下替换掉Distinct Sort排序操作的方式就是哈序聚合。
• concat 操作相当于直接拼接结果集然后再做去重和排序操作。这里两张表重复数据较少所以没有像上面一样先做Distinct Sort， 如下图：
• 另外还有一种情况就是使用union all 并使用显示的order 这时优化器会强制使用 排序-〉merge 的操作，这个就补贴图了。

　　　　　　　　

 

 

 

 

 

　　　　总结：对于union方式的选择，个人认为优化器选择的问题不大，一般很少有情况需要使用提示去干预，并不像上一篇的强制并行经常使用。但我们要充分了解优化器的选择，为什么要选择这样的一种执行方式，这也是本例中几种情况混合并添加优化器智能选择的展示原因。另外替换掉Distinct Sort排序操作的方式就是哈序聚合。Distinct Sort排序操作需要的内存和去除重复之前数据集合的数据量成正比，而哈希聚合需要的内存则是和去除重复之后的结果集成正比！所以如果数据行中重复值很多，那么相比而言通过哈希聚合所消耗的内存会少。