Nested Loops join时显示no join predicate原因分析以及解决办法

 

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

　　

　　最近遇到一个存储过程在某些特殊的情况下，效率极其低效（同时服务器CPU资源占用急剧上升，导致整个服务器相应缓慢）
　　至于底下到什么程度我现在都没有一个确切的数据，因为预期很快就可以查询出来结果的SQL，实则半个小时都出不来，后面会有截图
　　观察执行计划的时候发现中间有一步中出现一个类似如下非常规的连接提示警告，如下图

　　

 

　 no join predicate 意思就是没有连接谓词，表之间join的时候没有指定连接谓词可以导致no join predicate，
　 但是反过来也是一定成立的吗，明明写了连接条件，仍旧提示no join predicate，为什呢？
　 下面先从no join predicate 入手开始，说明什么时候会出现no join predicate ，以及原因和解决办法。

 

 

 1，未指定连接条件下导致的no join predicate 

　　两个表在没有指定连接条件的情况下，做运算的结果是计算器笛卡尔积，当然是没有连接谓词的，提示no join predicate 也很容易理解
　　上一段简单的代码演示一下，如下创建两张表，#t1，#t2,至于测试数据为什么是这样子，我下面会继续做解释

create table #t1(id int,name varchar(100))
create table #t2(id int,name varchar(100))

insert into #t1 values (1,newid())
insert into #t1 values (1,newid())

insert into #t2 values (1,newid())
insert into #t2 values (1,newid())

首先看计算笛卡尔积的时候的执行计划，Nested Loops 中的红叉叉，就表明是没有连接谓词，当然这个查询SQL中也确实没有连接谓词，这种情况下也很容易理解。

 

2，指定了连接条件下的no join predicate 

　　这里即便是指定了连接条件，仍然提示没有连接谓词，这个原因又是为什么呢？
　　此时就需要看表中的数据特点了，从上面造的测试数据可以看出，#t1表id = 1 的是两行，#t2 表的同样，id = 1的数据也是两行
　　此时两张表的join，是多对多的关系，多对多的情况下就是计算笛卡尔积，这就是这种情况下提示没有连接谓词的原因。
　　详细请参考：http://www.cnblogs.com/liwei225/p/5056460.html，大神早就有详细的分析，感谢liwei225大神的分享

　　

　　

　　不过我这里还有一个疑问，还是上述两张表，指定连接条件，但是不指定查询条件，也就是没有where a.id = 1，此时就没有提示no join predicate
　　这个原因我也没弄懂，后面再想想为什么，希望路过的大神帮忙解释一下，谢谢。

　　

 

 3，指定了连接条件的情况下，某些查询条件下会出现no join predicate 

　　这是一个实际业务的SQL，从存储过程中扣出来的代码，因为有比较多的查询条件，最后组装的动态SQL也不完全一样，绝大多数情况下是没有问题的，
　　但是当在where 条件中添加某一个查询条件之后，效率就开始严重下降，至于下降到什么程度，截图是运行了35分钟之后取消的
　　在这个SQL运行期间，服务器CPU直接飙升至100%，并且是持续性的

　　

　　截图一个对比测试的，仅仅在上面的SQL中加了一个OPTION(FORCE ORDER)查询提示，强制按照书写的表的顺序驱动，结果2秒钟就出来结果了
　　执行计划跟上面是不一样的，同时也没有显示no join predicate，不能说加了一个强制提示就有了连接谓词，不加强制提示就没有连接谓词吧？
　　从对比情况看，可以说明，没有非常严重的外界因素干扰，比如缺少索引，统计信息有问题等等
　　倘若如此，加了OPTION(FORCE ORDER)查询提示的SQL与不加OPTION(FORCE ORDER)查询提示的SQL差别不可能这么大，一定是执行计划的选择出了问题。

　　

 

　　那么就继续分析这个执行计划。
　　通常情况下，我们会首先分析执行计划，什么索引使用（被抑制）了，索引碎片了，参数嗅探了，统计信息过期了（取样不够），都一一分析过，
　　这些额外因素只会在一定程度上拖慢SQL的效率，而不是拖慢到如此相差几个数量级的程度
　　那么来分析，没有加OPTION(FORCE ORDER)为什么会这么慢？
　　实际上，这个SQL的执行计划只能从预估执行计划来看，因为实在等不到这个SQL运行完成而看实际执行计划
　　如题，预估执行计划显式，中间有一步存在一个如上所述的没有连接谓词警告

　　

　　我们看一下这个Nested Loops的详细信息，确实提示没有连接谓词，并且显式的预估行数为126469000行，超过了1亿行了，
　　根据具体的数据分布和查询条件分析，如果不做笛卡尔积，这个中间结果是怎么也达不到亿级别的，这个妥妥的是笛卡尔积
　　如果真的要计算出来超过一亿行这么大一个结果集，代价可想而知。

　　实际上1亿行的笛卡尔积，并需要太多的基数，select 10000*10000就可以达到了，也就是两个过万的结果集做笛卡尔积运算，就可以算出来一亿行的结果
　　结果也证明，第一个SQL在做查询的时候CPU飙升，而并没有很高的物理IO，慢就慢在笛卡尔结果的运算上。

　　

 

　 那么这里的笛卡尔积是怎么出现的？具体数据我不方便分析，这里做一个简单的推倒
　 比如这么一个SQL：
　　select * from TableA a
　　　　inner join TableB b on a.Identifier1 = b.Identifier1
　　　　inner join TableC c on b.Identifier2 = c.Identifier2
　　where a.Column_X = ***
　　　　and b.Column_Y = ***
　　　　and Other Filter Condition

　　连接条件都是有的，我们暂时简化问题，忽略查询条件，从逻辑上分析
　　正常逻辑是A表结果驱动B表（ a.Identifier1 = b.Identifier1 ），
　　用A表和B表join的结果，借助B表的Identifier2 驱动C表（ b.Identifier2 = c.Identifier2 ），这里的A表和C表示没有直接关系的，
　　如果A表和C表结合起来，最后驱动B表，可以想象，因为A表和C表之间没有直接的关系，强制连接的话，A表和C表计算出来的结果必然是笛卡尔积
　　这个笛卡尔积就类似于上面截图Nested Loops中的预估的超过一亿行数的结果集。

　　为什么SQL Server会私自更改表之前的连接方式，从而导致笛卡尔积？
　　执行计划的选择是一个复杂的计算过程。执行计划的生成是跟索引，统计信息，表中的数据分布，系统资源等等多种因素一并计算出来的，
　　SQL Server可能是根据查询条件，选择了自己认为一种“高效”的单个表查询方式，却忽略了表之间驱动的驱动顺序（个人猜测）。
　　因此才会造如上推理的类似于“A表和C表之间没有直接的关系，强制连接”造成的笛卡尔积，
　　根据预估的执行计划和实际表之间的关联关系分析得到，这个执行计划在处理表之间关联的处理上，正是如此。
　　同时，在强制驱动顺序之后，很快地查询出来了结果，也能说明，用类似于A驱动B，A+B的结果驱动C这种方式的效率远远高于A+C计算笛卡尔积再驱动B的

　　Sometimes SQL Server can remove a join predicate from the original query.

 

　　那么，如果避免这种情况的呢？
　　已知的是，上述SQL在执行的时候提示没有连接谓词，并不是真的没有写连接谓词，
　　而是SQL Server改动了表之间驱动顺序，造成了部分没有直接关系的表放在一起生成笛卡尔积的结果

　　方案一：

　　　　　　OPTION(FORCE ORDER)是也验证过了，通过强制驱动顺序来让查询引擎按照顺序来实现，

　　方案二：

　　还是上面的例子来说明：
　　比如原始的SQL类似如下：
　　select * from TableA a
　　　　inner join TableB b on a.Identifier1 = b.Identifier1
　　　　inner join TableC c on b.Identifier2 = c.Identifier2
　　where a.Column_X = ***
　　　　and b.Column_Y = ***
　　　　and Other Filter Condition

　　将这个SQL改写一下
　　　select * from TableA a
　　　　inner join TableB b on a.Identifier1 = b.Identifier1
　　　　CROSS APPLY( select * TableC c where b.Identifier2 = c.Identifier2)
　　where a.Column_X = ***
　　　　and b.Column_Y = ***
　　　　and Other Filter Condition
　　用CROSS APPLY的方式，类似于强制用B表去驱动C表，就不会出现A表和C表结合从而出现笛卡尔积的情况
　　事实也证明了，在改写实际SQL的过程中，这种方式也是切实可行的，效果相当于OPTION(FORCE ORDER)。 

　　方案三：同样是改写SQL，实际上述的SQL并不是太复杂，但也不是那种很简单的逻辑关联，可以通过在一定接住临时表，拆分出一个中间结果集

　　　　　　用中间结果集的方式去驱动另外的表，简化每一步的连接逻辑，也可以避免中间产生笛卡尔积的情况
　　　　　　事实证明，这种方式也是可行的，效果稍微亚于前两种方式，
　　　　　　关于借助临时表做逻辑拆分的，也需要一定的技巧，这里有案例，http://www.cnblogs.com/wy123/p/5712001.html

 

 

总结：上述通过一个实际案例，分析了什么情况下会造成no join predicate，
　　   以及即便是写了连接条件，仍然会出现no join predicate的原因，当面对这种情况的时候，又可以通过什么办法来解决。
　　　当从新手开始，不敢在SSMS查询窗口中写SELECT（怕超过三个表的就写不好，被师傅骂），怕写Update DELETE语句（怕误操作），
　　　到写完一个又一个的SQL，慢慢地掌握了一些基础知识和技巧，再到后面了解了索引，执行计划表，统计信息，会用几个DMV，几个系统表，会看几个性能指标，服务器资源使用等信息
　　　开始做性能分析，性能优化的时候，当大多数问题手到擒来的时候，我觉得自己已经无所不能了，
　　　现实情况屡屡告诉我，你还有很多很多未知的问题，再一次感觉到自己如此的弱逼。
　　　我承诺，我以后再也不敢吹牛逼了。

 

参考：http://www.cnblogs.com/liwei225/p/5056460.html

　　   http://www.scarydba.com/2009/09/15/no-join-predicate/

 　　  http://dba.stackexchange.com/questions/35082/what-exactly-does-no-join-predicate-mean-in-sql-server

　　   http://www.scarydba.com/2009/09/15/no-join-predicate/

 

2017，SQL Server中还有很多很多未知的知识等着去学习和挑战。