我們都知道,一條SQL語句提交給優(yōu)化器會產(chǎn)生相應(yīng)的執(zhí)行計劃然后執(zhí)行輸出結(jié)果,但他的執(zhí)行計劃是如何產(chǎn)生的呢?這可能是關(guān)系型數(shù)據(jù)庫最復(fù)雜的部分了.這里我為大家介紹一個有關(guān)SQL Server優(yōu)化器的特性-隱式謂詞,并簡單介紹在此特性下如何根據(jù)場景控制優(yōu)化器的行為.
?? 在這里我通過一個簡單的實例來給大家說明下.
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
可以看出我的語句執(zhí)行計劃中優(yōu)化器為我添加了T2.A=0這個謂詞.如圖1-1
??????????????????????????????????????????????????????????????? 圖1-1
優(yōu)化器根據(jù)語義邏輯判斷,在不改變結(jié)果集的前提下認為提前在T2表中過濾出T2.A=0的結(jié)果集再參與下面運算可以提升效率,這樣在未征得我們同意的情況下他就這樣做了:)
這就是 隱式謂詞
既然這是SQL Server優(yōu)化器的默認行為,那在我們自己要控制優(yōu)化器行為的時候就少不了與其默認行為沖突.這里還是通過簡單的實例說明.
code
?
select
@@VERSION
SELECT
*
FROM
T1
inner
hash
JOIN
T2
ON
T1.A
=
T2.A
WHERE
T1.A
=
0
可以看到,我為sql加了個hash join的hint結(jié)果就出現(xiàn)了錯誤.如圖1-2
?
??????????????????????????????????????????????????????? 圖1-2
原因: 默認的情況下優(yōu)化器為我們加上了T2.A=0,t1,t2采用了相同的過濾條件,這時T1.A=T2.A自身的意義就不存在了,而hash join自身又需要等值鏈接(equijoin),此時報錯就出現(xiàn)了.
延伸: 其實在此種情況下select中檢索湊到一起就可以了,拋錯誤讓開發(fā)人員郁悶.微軟注意到了這點,SQL2012中上述查詢就沒有問題了.如圖1-3
?
??????????????????????????????????????? 圖1-3
問題來了(不是挖掘機哪家強),如果我用了hint這種情況又該怎么辦呢?就我們剛才的語句分析,hash join需要等值鏈接,可以根據(jù)表的定義規(guī)避他的隱式謂詞特性造成的這個問題.
Where 條件中換成不等值不就可以了:)
Code
SELECT
*
FROM
T1
inner
hash
JOIN
T2
ON
T1.A
=
T2.A
WHERE
T1.A
>-
1
and
t1.A
<=
0
?
關(guān)于性能
可以看出在參與JOIN操作之前,優(yōu)化器為我們過濾掉一部分?jǐn)?shù)據(jù),使得Join的消耗減輕,這是件好事兒,但凡事都有兩個方面,為過濾掉的這部分?jǐn)?shù)據(jù)他不是免費的,有時候可能會加重負擔(dān).
這里介紹一個 trace flag 2324 ,他可以使優(yōu)化器不采取隱式謂詞行為,在特殊的場景下可以讓我們的執(zhí)行計劃發(fā)揮的更好.
這里我通過一個簡單的實例說明下.
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
)
--
--禁用隱性謂詞
可以看到由于由于隱式謂詞在特殊的場景中(如數(shù)據(jù)分布比較傾斜.實例中ProductID=1001站了bbb表中的80%數(shù)據(jù))過濾后的join反而不如整體數(shù)據(jù)參加join來得更快.如圖1-4
?
????????????????????????????????????????????????????????????????????? 圖1-4
此處實例只為簡單說明作用,實際生產(chǎn)中可能因為統(tǒng)計信息問題使得優(yōu)化器采用了不合理的運算符(如對bbb中采用seek,則消耗巨大,而統(tǒng)計信息自身又不易更新)使得執(zhí)行計劃不合理影響整體性能.感興趣的朋友可以自行測試.
注:TF2324只對不等值謂詞起作用.等值謂詞如果想規(guī)避隱性謂詞,參考挖掘機的例子:)
結(jié)語: 凡事都具有兩面性,隱式謂詞在絕大多數(shù)場景中是個很好的策略,微軟也在一步步完善.但在優(yōu)化器無法合理處理時,就需要我們?nèi)藶榻槿?
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
