SQL Server最佳化器特性-隱式謂詞

標籤：des   style   blog   color   io   os   ar   for   資料   

我們都知道,一條SQL語句提交給最佳化器會產生相應的執行計畫然後執行輸出結果,但他的執行計畫是如何產生的呢?這可能是關係型資料庫最複雜的部分了.這裡我為大家介紹一個有關SQL Server最佳化器的特性-隱式謂詞,並簡單介紹在此特性下如何根據情境控制最佳化器的行為.

   在這裡我通過一個簡單的執行個體來給大家說明下.

code

CREATE TABLE T1 (A INT, B INT)CREATE TABLE T2 (A INT, B INT)set showplan_text onSELECT *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 @@VERSIONSELECT *FROM T1 inner hash JOIN T2 ON T1.A = T2.AWHERE 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.AWHERE T1.A >-1 and t1.A<=0

 

關於效能

可以看出在參與JOIN操作之前,最佳化器為我們過濾掉一部分資料,使得Join的消耗減輕,這是件好事兒,但凡事都有兩個方面,為過濾掉的這部分資料他不是免費的,有時候可能會加重負擔.

這裡介紹一個trace flag 2324,他可以使最佳化器不採取隱式謂詞行為,在特殊的情境下可以讓我們的執行計畫發揮的更好.

這裡我通過一個簡單的執行個體說明下.

code

select * from aaa inner join bbb on aaa.ProductID=bbb.ProductIDwhere aaa.ProductID>1000 and aaa.ProductID<1500goselect * from aaa inner join bbb on aaa.ProductID=bbb.ProductIDwhere aaa.ProductID>1000 and aaa.ProductID<1500option(querytraceon 2324)----禁用隱性謂詞

可以看到由於由於隱式謂詞在特殊的情境中(如資料分布比較傾斜.執行個體中ProductID=1001站了bbb表中的80%資料)過濾後的join反而不如整體資料參加join來得更快.1-4

 

                                                                      圖1-4

此處執行個體只為簡單說明作用,實際生產中可能因為統計資訊問題使得最佳化器採用了不合理的運算子(如對bbb中採用seek,則消耗巨大,而統計資訊自身又不易更新)使得執行計畫不合理影響整體效能.感興趣的朋友可以自行測試.

注:TF2324隻對不等值謂詞起作用.等值謂詞如果想規避隱性謂詞,參考挖掘機的例子:)

結語:凡事都具有兩面性,隱式謂詞在絕大多數情境中是個很好的策略,微軟也在一步步完善.但在最佳化器無法合理處理時,就需要我們人為介入.

 

SQL Server最佳化器特性-隱式謂詞