我平時(shí)在做數(shù)據(jù)庫(kù)教學(xué)嘛。經(jīng)常會(huì)有學(xué)員跑來(lái)問(wèn)一個(gè)問(wèn)題。他們寫(xiě)的明明是 LEFT JOIN。但是執(zhí)行計(jì)劃里跑出來(lái)的卻是 Hash Join。而且呢，查出來(lái)的數(shù)據(jù)比他們想的少了很多。

剛開(kāi)始有人問(wèn)我的時(shí)候，我也看不太懂那些密密麻麻的執(zhí)行計(jì)劃節(jié)點(diǎn)。但是后來(lái)看得多了，去深入查了一下。我發(fā)現(xiàn)這里面的邏輯其實(shí)挺有意思的。也就是說(shuō)，優(yōu)化器覺(jué)得它很聰明，那它到底聰明在什么地方呢？

你敲下回車(chē)到出結(jié)果，這中間數(shù)據(jù)庫(kù)內(nèi)核要跑很多流程。外連接消除就是其中一個(gè)環(huán)節(jié)。它跑得挺快的，但是也很容易讓人搞錯(cuò)。今天這篇文章我就來(lái)聊聊這個(gè)事。從你看到的現(xiàn)象說(shuō)起，接著講里面的機(jī)制，最后說(shuō)說(shuō) KES 具體是怎么做的。

一、優(yōu)化器到底在干嘛：從“你說(shuō)的”變成“最優(yōu)的跑法”

我們先建立一個(gè)基本的認(rèn)識(shí)。SQL 其實(shí)是一種聲明式的語(yǔ)言。你只是告訴數(shù)據(jù)庫(kù)“我要什么東西”。你并沒(méi)有告訴它“怎么去拿這個(gè)東西”。那么優(yōu)化器要干嘛呢？它的活兒就是去找一條代價(jià)最低的路徑。前提是，語(yǔ)義得是等價(jià)的。

這個(gè)“語(yǔ)義等價(jià)”非常關(guān)鍵。只要最后出來(lái)的結(jié)果集一模一樣，優(yōu)化器就有權(quán)去改你的查詢語(yǔ)句。外連接消除，其實(shí)就是一種改寫(xiě)。

我們看 KES（金倉(cāng)數(shù)據(jù)庫(kù)）的優(yōu)化器。它內(nèi)部其實(shí)分了兩個(gè)階段來(lái)處理：

• 邏輯優(yōu)化階段：這個(gè)階段就是做等價(jià)變換的。目標(biāo)很簡(jiǎn)單，把 SQL 換個(gè)寫(xiě)法，意思不變但是更好跑。這里面的動(dòng)作包括謂詞下推、子查詢展開(kāi)，還有常量折疊。外連接消除也在這里面。這一步靠的是等價(jià)規(guī)則。它要保證變換前后的結(jié)果集完全對(duì)得上。
• 物理優(yōu)化階段：到了這一步，它就拿著前面改好的邏輯計(jì)劃，結(jié)合表里的統(tǒng)計(jì)信息和代價(jià)模型，去挑一條最省資源的路。比如說(shuō)是用 Hash Join 還是 Nested Loop?；蛘哒f(shuō)走不走索引。這些都由它來(lái)定。

那么外連接消除屬于哪一步呢？它屬于邏輯優(yōu)化階段。它在算代價(jià)之前就發(fā)生了。優(yōu)化器先通過(guò)分析證明“這玩意兒可以消除”。然后再交給物理優(yōu)化階段去選最優(yōu)路徑。

這兩步分工是很明確的。邏輯優(yōu)化管的是“意思對(duì)不對(duì)”。物理優(yōu)化管的是“跑得快不快”。外連接消除之所以歸在邏輯優(yōu)化里，就是因?yàn)樗且环N等價(jià)改寫(xiě)。它不是為了調(diào)優(yōu)而調(diào)優(yōu)。改完之后結(jié)果必須跟原來(lái)一樣，這是大前提。

二、外連接消除怎么觸發(fā)：一個(gè)很死板的邏輯推斷

外連接消除要能成立，得滿足一個(gè)很死板的前提條件。什么條件呢？就是WHERE 子句里面，存在針對(duì) Nullable-Side 的 Null-Rejecting 條件。

什么是 Nullable-Side？

這個(gè)詞聽(tīng)起來(lái)挺唬人。其實(shí)很簡(jiǎn)單。對(duì)于 A LEFT JOIN B ON ... 來(lái)說(shuō)，B 這一側(cè)就是 Nullable-Side。因?yàn)?A 表里的數(shù)據(jù)如果在 B 表找不到匹配，那 B 表的列全都會(huì)被填成 NULL。

如果是 A RIGHT JOIN B ON ...，那 A 側(cè)就是 Nullable-Side。邏輯是對(duì)稱(chēng)的。

如果是 A FULL JOIN B ON ...，那兩邊都可能是產(chǎn)生 NULL 的一側(cè)。這種情況消除起來(lái)就更復(fù)雜了。

什么是 Null-Rejecting？

怎么去判斷是不是 Null-Rejecting 呢？方法就是：你把 Nullable-Side 的列全當(dāng)成 NULL。如果整個(gè)條件算出來(lái)的結(jié)果是 False 或者 Unknown。那它就是一個(gè) Null-Rejecting 條件。

我們拿個(gè)最常見(jiàn)的例子來(lái)看：

SELECT * FROM orders o
LEFT JOIN order_detail d ON o.order_id = d.order_id
WHERE d.amount > 0;

LEFT JOIN 跑完之后。那些 orders 里面沒(méi)有對(duì)應(yīng) order_detail 的記錄，它的 d.amount 就是 NULL。接著就到了 WHERE 這一步。WHERE d.amount > 0。你拿 NULL 去跟 0 比大小。結(jié)果是 Unknown。那這行數(shù)據(jù)就被過(guò)濾掉了。

這就是觸發(fā)點(diǎn)。如果“外連接加上過(guò)濾掉所有 NULL 行”跟“內(nèi)連接加上同樣的過(guò)濾”，跑出來(lái)的東西一模一樣。優(yōu)化器就會(huì)直接把 LEFT JOIN 改成 INNER JOIN。

下面這些常見(jiàn)條件，我給大家列了一下判定結(jié)果：

大家注意看最后兩行。如果你寫(xiě)了 OR b.status IS NULL。整個(gè) OR 表達(dá)式在遇到 NULL 的時(shí)候會(huì)返回 True。那么 NULL 行就被留下來(lái)了。這個(gè)時(shí)候外連接是不能被消除的。平時(shí)寫(xiě)業(yè)務(wù)代碼，如果你要處理“允許為空”的情況，往往就是用這個(gè)技巧。

三、優(yōu)化器是怎么做決定的：一步步還原 KES 的處理過(guò)程

我們拿 KES 來(lái)舉例。外連接消除是在邏輯計(jì)劃優(yōu)化階段發(fā)生的。大概有這么幾個(gè)步驟：

第一步：找出外連接節(jié)點(diǎn)，看看哪邊會(huì)產(chǎn)生 NULL

優(yōu)化器會(huì)去遍歷那棵邏輯計(jì)劃樹(shù)。把所有的 Outer Join 節(jié)點(diǎn)找出來(lái)。然后確定哪一邊是 Nullable-Side。

就像前面說(shuō)的，如果是 A LEFT JOIN B ON ...。那 B 側(cè)輸出列在 A 沒(méi)匹配上的時(shí)候，就都是 NULL。

第二步：把 WHERE 里的條件拎出來(lái)分析

優(yōu)化器會(huì)去掃 WHERE 子句。把那些引用了 Nullable-Side 列（也就是 B 表的列）的條件挑出來(lái)。然后逐個(gè)去做 Null-Rejecting 分析。

KES 在這一步做得很細(xì)。它不是簡(jiǎn)單看一眼“WHERE 里有沒(méi)有右表的列”就完事了。它會(huì)對(duì)那種用 AND/OR 拼起來(lái)的復(fù)合條件做完整的布爾推導(dǎo)。我們看個(gè)例子：

WHERE b.status = 'A' AND (b.amount > 0 OR b.amount IS NULL)

面對(duì)這種復(fù)合條件，優(yōu)化器會(huì)這么干：

1. 先看 b.status = 'A'。它是 Null-Rejecting 的。因?yàn)?NULL = ‘A’ 算出來(lái)是 Unknown。
2. 接著看 b.amount > 0 OR b.amount IS NULL。如果 b 是 NULL。那就是 NULL > 0 OR NULL IS NULL。算出來(lái)是 Unknown OR True，最后是 True。所以這部分不是 Null-Rejecting。
3. 然后把兩部分用 AND 連起來(lái)看。Unknown AND True，結(jié)果是 Unknown。所以整體來(lái)看，它還是 Null-Rejecting 的。

既然整體是 Null-Rejecting，那外連接就可以被消除了。

第三步：判斷等不等價(jià)

優(yōu)化器要確認(rèn)一件事。那些滿足 Null-Rejecting 的條件，是不是能把外連接產(chǎn)生的所有 NULL 行都給干掉。如果確實(shí)全都能過(guò)濾掉。那就說(shuō)明“外連接加 WHERE 過(guò)濾”跟“內(nèi)連接加 WHERE 過(guò)濾”結(jié)果是一樣的。消除的條件就成立了。

第四步：動(dòng)手改寫(xiě)計(jì)劃

證明完之后，優(yōu)化器就會(huì)把 Left Join 節(jié)點(diǎn)直接換成 Inner Join 節(jié)點(diǎn)。同時(shí)，它還會(huì)把原來(lái)應(yīng)該在 JOIN 后面才做的 WHERE 過(guò)濾給下推下去。改完之后的邏輯計(jì)劃，才會(huì)交到 CBO 那邊去，由代價(jià)模型挑一個(gè)跑起來(lái)最快的物理算法。

四、哪些情況是不會(huì)被消除的

場(chǎng)景一：用了 IS NULL 條件——其實(shí)就是想找那些空行

-- 查找沒(méi)有對(duì)應(yīng)訂單詳情的主訂單
SELECT o.order_id, o.customer
FROM orders o
LEFT JOIN order_detail d ON o.order_id = d.order_id
WHERE d.order_id IS NULL;

這是一種很常見(jiàn)的寫(xiě)法。目的就是反著找，專(zhuān)門(mén)找左表里那些在右表沒(méi)匹配上的數(shù)據(jù)。d.order_id IS NULL 這個(gè)條件，恰恰就是靠外連接產(chǎn)生的 NULL 才起作用的。你如果把這個(gè)外連接給消掉了，那這些數(shù)據(jù)你永遠(yuǎn)也找不出來(lái)了。

KES 能認(rèn)出這種情況。它會(huì)保留外連接。這說(shuō)明它的優(yōu)化器判定得很準(zhǔn)。

場(chǎng)景二：OR 條件里面帶了 IS NULL

WHERE b.status = 'A' OR b.status IS NULL

這種寫(xiě)法的意思是啥呢？就是說(shuō)右表里 status 是 ‘A’ 的數(shù)據(jù)我要。右表壓根沒(méi)記錄（也就是 NULL）的數(shù)據(jù)我也要。因?yàn)橛?IS NULL 這個(gè)分支在兜底，所以外連接是不能消除的。

場(chǎng)景三：用 CASE WHEN 把 NULL 單獨(dú)拎出來(lái)處理了

WHERE CASE WHEN b.status IS NULL THEN 'default' ELSE b.status END = 'default'

里面有 IS NULL 的分支。輸入是 NULL 的時(shí)候它不會(huì)返回 Unknown。所以外連接同樣不能消除。

場(chǎng)景四：用 COALESCE / NVL 把 NULL 替換掉了

-- COALESCE 將 NULL 替換為 0，0 > -1 為 True，NULL 行被保留
WHERE COALESCE(b.amount, 0) > -1

用了 COALESCE 或者 NVL 把 NULL 換成了一個(gè)具體的數(shù)。然后再去比大小。這就有可能讓 NULL 行剛好滿足條件給漏過(guò)去。這就會(huì)阻止外連接消除。當(dāng)然，到底能不能漏過(guò)去，還得看你替換成了什么值，以及后面的比較條件是怎么寫(xiě)的。

五、KES 優(yōu)化器做外連接消除的時(shí)候，有啥不一樣

① 復(fù)合條件它會(huì)完整地推導(dǎo)一遍

前面其實(shí)提到了。KES 不會(huì)只看一眼 WHERE 里有沒(méi)有右表的列就下結(jié)論。它會(huì)把整個(gè) WHERE 子句拿來(lái)做完整的布爾分析。哪怕是 AND 和 OR 扭在一起，它也會(huì)一步步推導(dǎo)。這就保證了它判定得很準(zhǔn)。該消除的它消除，不該消除的它絕對(duì)不會(huì)亂動(dòng)。

② 能完整兼容 Oracle 的(+)語(yǔ)法

KES 是支持 Oracle 以前那種 (+) 外連接寫(xiě)法的。而且在語(yǔ)義處理上，它跟 Oracle 保持了一致：

• 過(guò)濾條件沒(méi)帶 (+) 的 → 這就跟寫(xiě)在 WHERE 里一樣，有可能會(huì)觸發(fā)外連接消除
• 過(guò)濾條件帶上了 (+) 的 → 這就等同于放到了 ON 子句里，外連接不會(huì)被消除

-- 可能觸發(fā)消除（b.status 無(wú) (+)）
WHERE a.id = b.id(+) AND b.status = 'A'

-- 不會(huì)觸發(fā)消除（b.status 帶 (+)，等同于 ON 條件）
WHERE a.id = b.id(+) AND b.status(+) = 'A'

這點(diǎn)在把 Oracle 遷移到 KES 的時(shí)候特別重要。以前老代碼里 (+) 怎么用的人都有，挺亂的。KES 兼容了這點(diǎn)，你就不用去大批量改代碼了。但也正因?yàn)檫@樣，你得仔細(xì)去查一查，那個(gè) (+) 到底加沒(méi)加對(duì)，是不是符合你們現(xiàn)在的業(yè)務(wù)意思。

③ 執(zhí)行計(jì)劃看得見(jiàn)摸得著

你直接跑個(gè) EXPLAIN 或者 EXPLAIN ANALYZE。就能看出來(lái) KES 到底有沒(méi)有消除外連接：

EXPLAIN
SELECT a.id, b.name
FROM a LEFT JOIN b ON a.id = b.id
WHERE b.status = 'active';

如果執(zhí)行計(jì)劃里出來(lái)的是不帶 “Left” 前綴的 Hash Join 或者 Nested Loop。那就說(shuō)明已經(jīng)消除了。如果出來(lái)的是 Left Hash Join 或者 Left Nested Loop。那就說(shuō)明外連接還在。這樣排查起來(lái)就非常直接了。

五、外連接消除跟謂詞下推攪在一起的情況：很容易看漏

外連接消除不是自己一個(gè)人在跑。它會(huì)跟優(yōu)化器的其他規(guī)則攪和在一起。有時(shí)候就會(huì)產(chǎn)生一些讓開(kāi)發(fā)人員看不懂的結(jié)果。這里面最常見(jiàn)的就是外連接消除加上謂詞下推。

謂詞下推是個(gè)啥意思

謂詞下推說(shuō)白了就是：把 WHERE 里面的過(guò)濾條件，盡量往數(shù)據(jù)源頭那邊挪。早點(diǎn)過(guò)濾掉沒(méi)用的數(shù)據(jù)，中間產(chǎn)生的過(guò)程數(shù)據(jù)就少了。

-- 原始寫(xiě)法：外層查詢加過(guò)濾
SELECT * FROM (
    SELECT o.order_id, o.customer, d.amount
    FROM orders o
    LEFT JOIN order_detail d ON o.order_id = d.order_id
) sub
WHERE sub.amount > 1000;

優(yōu)化器拿到這段代碼，它是這么處理的：

1. 它看出來(lái) sub.amount > 1000 其實(shí)是在過(guò)濾里面那個(gè)子查詢里的 d.amount。
2. 這個(gè) amount 是從 LEFT JOIN 右邊來(lái)的，屬于 Nullable-Side。
3. 接著分析 amount > 1000。NULL 大于 1000 算出來(lái)是 Unknown。所以這是個(gè) Null-Rejecting 條件。
4. 它判定外連接可以消除，就直接改寫(xiě)成內(nèi)連接了。
5. 同時(shí)它順手把這個(gè)過(guò)濾條件給推到了子查詢里面去。

最后實(shí)際跑的等價(jià) SQL 變成了這樣：

SELECT o.order_id, o.customer, d.amount
FROM orders o
INNER JOIN order_detail d ON o.order_id = d.order_id
WHERE d.amount > 1000;

你看，消除和下推這兩步同時(shí)發(fā)生了。出來(lái)的結(jié)果集是很精確的。

這種復(fù)合場(chǎng)景里面的坑

危險(xiǎn)的地方在哪呢？就是當(dāng)外面有好幾個(gè)條件去引用子查詢的時(shí)候，復(fù)合變換可能就會(huì)搞出意外來(lái)。

SELECT *
FROM (
    SELECT a.id, a.name, b.detail, b.category
    FROM main_table a
    LEFT JOIN detail_table b ON a.id = b.ref_id
) sub
WHERE sub.category = 'VIP'
   OR sub.detail IS NULL;   -- 這里顯式捕獲了 NULL 行

遇到這條 SQL，優(yōu)化器得把整個(gè) WHERE 條件拿來(lái)做布爾分析：

• 先看 sub.category = 'VIP'。它是 Null-Rejecting 的。因?yàn)?NULL = ‘VIP’ 是 Unknown。
• 再看 sub.detail IS NULL。它不是 Null-Rejecting 的。因?yàn)?NULL IS NULL 是 True，NULL 行能過(guò)。
• 兩個(gè)用 OR 連起來(lái)。Unknown OR True，結(jié)果是 True。所以整體不是 Null-Rejecting 的。

因此，外連接就不會(huì)被消除。因?yàn)橛?IS NULL 在里面“保護(hù)”了外連接的語(yǔ)義。KES 的推導(dǎo)邏輯能把這種復(fù)合情況看得很清楚。它不會(huì)因?yàn)?OR 前半截是 Null-Rejecting，就腦子一熱把外連接給消了。

這其實(shí)就能看出來(lái)一個(gè)優(yōu)化器到底是做得精細(xì)還是做得粗糙。有些簡(jiǎn)單的優(yōu)化器，它可能就去掃一眼 WHERE 里有沒(méi)有右表的字段。它不做完整的布爾分析。一碰到 OR 的場(chǎng)景它就可能會(huì)把外連接錯(cuò)誤地消掉。KES 這塊是做了完整推導(dǎo)的，語(yǔ)義上很安全。

五-B、遷移的時(shí)候執(zhí)行計(jì)劃變了：舊庫(kù)沒(méi)問(wèn)題不代表你寫(xiě)對(duì)了

把 Oracle 遷移到 KES 的時(shí)候，有一個(gè)情況反復(fù)出現(xiàn)。同一條 SQL，在 Oracle 上面跑了三年一點(diǎn)事沒(méi)有。一遷到 KES，結(jié)果集突然少了幾十行。這真的是數(shù)據(jù)庫(kù)本身的差異導(dǎo)致的嗎？

答案往往是否定的。不是新庫(kù)做錯(cuò)了，而是老庫(kù)的優(yōu)化器當(dāng)時(shí)“碰巧”沒(méi)去充分優(yōu)化它。

舊庫(kù)當(dāng)時(shí)為啥能跑通

① 統(tǒng)計(jì)信息不準(zhǔn)確，走了條保守的路

優(yōu)化器要算計(jì)劃，得看統(tǒng)計(jì)信息。比如有多少行、數(shù)據(jù)分布怎么樣。如果舊庫(kù)的統(tǒng)計(jì)信息很久沒(méi)更新了。優(yōu)化器算 JOIN 代價(jià)就算得不準(zhǔn)。它可能就會(huì)選一條很保守的路。比如用 Nested Loop 去掃右表的時(shí)候，恰好沒(méi)有把右側(cè)結(jié)果給物化出來(lái)。這就導(dǎo)致消除的邏輯根本沒(méi)被觸發(fā)。

② 老版本的優(yōu)化器能力確實(shí)有限

有些低版本的數(shù)據(jù)庫(kù)，它的優(yōu)化器沒(méi)那么聰明。遇到簡(jiǎn)單的等值條件，它可能還會(huì)做做外連接消除。但是一碰到那種 AND/OR 組合起來(lái)的復(fù)合條件，它就分析不準(zhǔn)了。所以它“碰巧”沒(méi)把本該消除的外連接給消掉。你那錯(cuò)誤的寫(xiě)法反而跑出了“正確”的結(jié)果。

③ 那會(huì)兒數(shù)據(jù)量小，看不出來(lái)

表里數(shù)據(jù)少的時(shí)候，不管走哪條路，快慢都差不多。優(yōu)化器覺(jué)得沒(méi)必要去做復(fù)雜的等價(jià)變換。但是等數(shù)據(jù)量變大了，或者換到了新庫(kù)，它覺(jué)得值得去優(yōu)化了，一優(yōu)化，問(wèn)題就露出來(lái)了。

我們?cè)撛趺慈ダ斫膺@件事

一條 SQL 在舊庫(kù)上跑出了“正確”的結(jié)果。其實(shí)只有兩種可能：

• 語(yǔ)義確實(shí)是對(duì)的：SQL 本身的邏輯沒(méi)毛病。不管放在哪個(gè)庫(kù)、哪個(gè)版本，結(jié)果都應(yīng)該是一樣的。
• 純粹是僥幸：SQL 寫(xiě)法本身有邏輯問(wèn)題。只是舊庫(kù)那個(gè)版本、那個(gè)優(yōu)化器狀態(tài)、那份數(shù)據(jù)，恰好沒(méi)把問(wèn)題觸發(fā)出來(lái)。

要分清這兩種情況，你不能去糾結(jié)“它在哪個(gè)庫(kù)上跑通了”。你得去審 SQL 的語(yǔ)義本身。也就是把 SQL 翻譯成大白話，看看你寫(xiě)的邏輯是不是真的就是你想要的邏輯。

拿外連接來(lái)說(shuō)：

如果你的業(yè)務(wù)意思是“左表的數(shù)據(jù)全都要，右表沒(méi)匹配上的就顯示 NULL”。那你的 WHERE 里面就絕對(duì)不能寫(xiě)針對(duì)右表字段的過(guò)濾條件。除非你寫(xiě)的是 IS NULL 這種專(zhuān)門(mén)依賴 NULL 存在的邏輯。

這條原則跟用什么數(shù)據(jù)庫(kù)沒(méi)關(guān)系。它就是 SQL 語(yǔ)義的基本規(guī)矩。

六、寫(xiě)代碼的人得記住的兩條原則

① ON 和 WHERE 不是一個(gè)地方，意思差別很大

這是外連接里面最容易搞混的地方。我覺(jué)得值得反復(fù)說(shuō)一說(shuō)：

• ON 子句：它管的是“連接規(guī)則”。也就是哪些行能湊在一塊兒，哪些行湊不到一塊兒。就算右表沒(méi)有行能滿足 ON 的條件，左表的行還是會(huì)留下來(lái)，只不過(guò)輸出 NULL 罷了。
• WHERE 子句：它管的是“最后的結(jié)果篩選”。連接動(dòng)作全做完了，它再對(duì)最終結(jié)果動(dòng)刀子。它才不管你這行數(shù)據(jù)是真實(shí)匹配出來(lái)的，還是外連接硬生生造出來(lái)的 NULL。

-- ? 放在 WHERE 里，會(huì)觸發(fā)消除，等同于 INNER JOIN
SELECT * FROM a LEFT JOIN b ON a.id = b.id WHERE b.type = 'X';

-- ? 放在 ON 里，外連接語(yǔ)義得到保留
SELECT * FROM a LEFT JOIN b ON a.id = b.id AND b.type = 'X';

-- 兩者結(jié)果集的差異：
-- WHERE 版本：只返回 b.type = 'X' 的匹配行
-- ON 版本：返回所有 a 的行，b.type = 'X' 的顯示詳情，其余顯示 NULL

② 別拿“舊庫(kù)碰巧沒(méi)出事”來(lái)證明你的 SQL 寫(xiě)對(duì)了

測(cè)試跑通了，不等于語(yǔ)義就是對(duì)的。舊庫(kù)可能在那個(gè)特定的數(shù)據(jù)量下，或者特定的統(tǒng)計(jì)信息下，沒(méi)有觸發(fā)消除。這僅僅是“恰好沒(méi)出問(wèn)題”。并不代表你的寫(xiě)法就是對(duì)的。等遷移到新庫(kù)，優(yōu)化器做得更徹底了，這問(wèn)題自然就冒出來(lái)了。

七、從外連接消除看 SQL 性能優(yōu)化的整體思路

去搞懂外連接消除，不光是為了不踩坑。它其實(shí)給你開(kāi)了一扇窗，讓你能看到優(yōu)化器是怎么干活的。

優(yōu)化器想要達(dá)到的兩個(gè)目標(biāo)

優(yōu)化器在生成執(zhí)行計(jì)劃的時(shí)候，心里有兩個(gè)目標(biāo)：

第一層：保證意思不能錯(cuò)

不管它怎么去變換，最后的結(jié)果集必須是一模一樣的。外連接消除只有在一個(gè)前提下才會(huì)觸發(fā)。那就是能在數(shù)學(xué)上證明“LEFT JOIN 加上 WHERE 過(guò)濾”跟“INNER JOIN 加上 WHERE 過(guò)濾”結(jié)果是一樣的。這個(gè)證明的過(guò)程就是我們說(shuō)的 Null-Rejecting 分析。如果 WHERE 條件能把外連接產(chǎn)生的 NULL 全過(guò)濾掉，那結(jié)果集就等價(jià)了。

第二層：找一條最快的路

在保證意思沒(méi)變的前提下，它才會(huì)去根據(jù)代價(jià)模型（CBO）挑一條跑得最快的路。通常來(lái)說(shuō)，INNER JOIN 比 LEFT JOIN 有更多可以優(yōu)化的地方。它能用更多的 Join 算法，也能更隨便地去調(diào)換 Join 的順序。所以把外連接消掉，往往能帶來(lái)很明顯的性能提升。

開(kāi)發(fā)的人能從這里面學(xué)到啥

外連接消除其實(shí)告訴了我們一件事：你寫(xiě) SQL 時(shí)候的思考方式，應(yīng)該盡量去貼合優(yōu)化器的分析方式。

優(yōu)化器是怎么看的呢？它看你的條件放在了哪里，是 ON 還是 WHERE。它看你的條件遇到 NULL 輸入的時(shí)候會(huì)怎么樣，是 Null-Rejecting 還是 Null-Passing。它還要看變換完之后結(jié)果一不一致。

如果你平時(shí)寫(xiě)完 SQL，也能習(xí)慣性地想一想：“我寫(xiě)在 WHERE 里的這個(gè)條件，要是碰上右表的 NULL 行，會(huì)把它們?cè)趺刺幚恚?rdquo;如果你有這個(gè)習(xí)慣，那你寫(xiě)的時(shí)候就能避開(kāi)大部分外連接的坑。根本不用等到看執(zhí)行計(jì)劃的時(shí)候才發(fā)現(xiàn)問(wèn)題。

外連接消除其實(shí)就是白撿的性能提升

從性能的角度來(lái)說(shuō)。如果你的 SQL 寫(xiě)法本身就滿足了外連接消除的條件（也就是說(shuō) WHERE 里面恰好有過(guò)濾右表非 NULL-Passing 數(shù)據(jù)的條件）。那優(yōu)化器就會(huì)自動(dòng)去走更高效的路徑。你不用自己動(dòng)手去改 SQL，也不用去加 Hint。優(yōu)化器全給你搞定了。

但是有個(gè)大前提：你的 SQL 語(yǔ)義必須是對(duì)的。如果業(yè)務(wù)上就是需要外連接的語(yǔ)義（也就是沒(méi)匹配的左表行必須留著），那你就老老實(shí)實(shí)把過(guò)濾條件寫(xiě)在 ON 里面。語(yǔ)義正確永遠(yuǎn)是性能優(yōu)化的前提。你不能為了“讓它觸發(fā)消除”去硬改 SQL 的意思。

八、簡(jiǎn)單總結(jié)一下

說(shuō)白了，外連接消除就是優(yōu)化器在“結(jié)果得一樣”這個(gè)框框里做的合法改寫(xiě)。只要 WHERE 里面出現(xiàn)了針對(duì) Nullable-Side 的 Null-Rejecting 條件，優(yōu)化器就會(huì)把外連接給干掉。

KES 在做這件事的時(shí)候，布爾推導(dǎo)做得很完整。Oracle 的 (+) 老寫(xiě)法它也兼容得很好。而且執(zhí)行計(jì)劃看得清清楚楚。去理解這個(gè)機(jī)制，不光能讓你寫(xiě)出來(lái)的 SQL 意思更明確。碰到數(shù)據(jù)庫(kù)遷移、查慢 SQL、看執(zhí)行計(jì)劃的時(shí)候，這也是個(gè)繞不開(kāi)的基礎(chǔ)知識(shí)。

優(yōu)化器做的每一個(gè)決定，底下都是有邏輯的。搞明白“為什么這么做”，比死記硬背“應(yīng)該怎么寫(xiě)”要有用得多。

到此這篇關(guān)于SQL外連接消除是怎么回事？從KES看優(yōu)化器如何改寫(xiě)你的查詢的文章就介紹到這了,更多相關(guān)數(shù)據(jù)庫(kù)優(yōu)化器外連接消除原理內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論