我們都知道,一條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只對不等值謂詞起作用.等值謂詞如果想規避隱性謂詞,參考挖掘機的例子:)
結語: 凡事都具有兩面性,隱式謂詞在絕大多數場景中是個很好的策略,微軟也在一步步完善.但在優化器無法合理處理時,就需要我們人為介入.
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
