美圖欣賞 | 設為首頁 | 加入收藏 | 網站地圖

當前位置:電腦中國 > 數據庫 > 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)
足彩半全场是什么意思