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