手机访问 | 设为首页 | 加入收藏 | 网站地图

当前位置:电脑中国 > 数据库 > MSSQL >

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

2017-01-03 21:49|来源:未知 |作者:dnzg |点击:

最近遇到一个存储过程在某些特殊的情况下,效率极其低效,
  至于底下到什么程度我现在都没有一个确切的数据,因为预期很快就可以查询出来结果的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会私自更改表之前的连接方式,从而导致笛卡尔积?
  执行计划的选择是一个复杂的计算过程。执行计划的生成是跟索引,统计信息,表中的数据分布,系统资源等等多种因素一并计算出来的,

(责任编辑:dnzg)