SQL Server优化器特性-隐式谓词

我们都知道,一条SQL语句提交给优化器会产生相应的执行计划然后执行输出结果,但他的执行计划是如何产生的呢?这可能是关系型数据库最复杂的部分了.这里我为大家介绍一个有关SQL Server优化器的特性-隐式谓词,并简单介绍在此特性下如何根据场景控制优化器的行为.

   在这里我通过一个简单的实例来给大家说明下.

code

CREATE TABLE T1 (A INT, B INT)
CREATE TABLE T2 (A INT, B INT)

set showplan_text on

SELECT *
FROM T1 INNER JOIN T2 ON T1.A = T2.A

可以看出我的语句执行计划中优化器为我添加了T2.A=0这个谓词.如图1-1

                                                                图1-1

优化器根据语义逻辑判断,在不改变结果集的前提下认为提前在T2表中过滤出T2.A=0的结果集再参与下面运算可以提升效率,这样在未征得我们同意的情况下他就这样做了:)

这就是隐式谓词

既然这是SQL Server优化器的默认行为,那在我们自己要控制优化器行为的时候就少不了与其默认行为冲突.这里还是通过简单的实例说明.

code

select @@VERSION

SELECT *
FROM T1 inner hash JOIN T2 ON T1.A = T2.A
WHERE T1.A = 0

可以看到,我为sql加了个hash join的hint结果就出现了错误.如图1-2

                                                        图1-2

原因:默认的情况下优化器为我们加上了T2.A=0,t1,t2采用了相同的过滤条件,这时T1.A=T2.A自身的意义就不存在了,而hash join自身又需要等值链接(equijoin),此时报错就出现了.

延伸:其实在此种情况下select中检索凑到一起就可以了,抛错误让开发人员郁闷.微软注意到了这点,SQL2012中上述查询就没有问题了.如图1-3

                                        图1-3

问题来了(不是挖掘机哪家强),如果我用了hint这种情况又该怎么办呢?就我们刚才的语句分析,hash join需要等值链接,可以根据表的定义规避他的隐式谓词特性造成的这个问题.

Where 条件中换成不等值不就可以了:)

Code

SELECT *
FROM T1 inner hash JOIN T2 ON T1.A = T2.A
WHERE T1.A >-1 and t1.A<=0

关于性能

可以看出在参与JOIN操作之前,优化器为我们过滤掉一部分数据,使得Join的消耗减轻,这是件好事儿,但凡事都有两个方面,为过滤掉的这部分数据他不是免费的,有时候可能会加重负担.

这里介绍一个trace flag 2324,他可以使优化器不采取隐式谓词行为,在特殊的场景下可以让我们的执行计划发挥的更好.

这里我通过一个简单的实例说明下.

code

select * from aaa inner join bbb 
on aaa.ProductID=bbb.ProductID
where aaa.ProductID>1000 and aaa.ProductID<1500
go
select * from aaa inner join bbb 
on aaa.ProductID=bbb.ProductID
where aaa.ProductID>1000 and aaa.ProductID<1500
option(querytraceon 2324)----禁用隐性谓词

可以看到由于由于隐式谓词在特殊的场景中(如数据分布比较倾斜.实例中ProductID=1001站了bbb表中的80%数据)过滤后的join反而不如整体数据参加join来得更快.如图1-4

                                                                      图1-4

此处实例只为简单说明作用,实际生产中可能因为统计信息问题使得优化器采用了不合理的运算符(如对bbb中采用seek,则消耗巨大,而统计信息自身又不易更新)使得执行计划不合理影响整体性能.感兴趣的朋友可以自行测试.

注:TF2324只对不等值谓词起作用.等值谓词如果想规避隐性谓词,参考挖掘机的例子:)

结语:凡事都具有两面性,隐式谓词在绝大多数场景中是个很好的策略,微软也在一步步完善.但在优化器无法合理处理时,就需要我们人为介入.