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

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

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

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

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

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

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

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

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

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

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

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

什么是 Nullable-Side？

這個詞聽起來挺唬人。其實很簡單。對于 A LEFT JOIN B ON ... 來說，B 這一側(cè)就是 Nullable-Side。因為 A 表里的數(shù)據(jù)如果在 B 表找不到匹配，那 B 表的列全都會被填成 NULL。

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

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

什么是 Null-Rejecting？

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

我們拿個最常見的例子來看：

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

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

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

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

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

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

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

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

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

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

第二步：把 WHERE 里的條件拎出來分析

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

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

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

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

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

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

第三步：判斷等不等價

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

第四步：動手改寫計劃

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

四、哪些情況是不會被消除的

場景一：用了 IS NULL 條件——其實就是想找那些空行

-- 查找沒有對應(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;

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

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

場景二：OR 條件里面帶了 IS NULL

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

這種寫法的意思是啥呢？就是說右表里 status 是 ‘A’ 的數(shù)據(jù)我要。右表壓根沒記錄（也就是 NULL）的數(shù)據(jù)我也要。因為有 IS NULL 這個分支在兜底，所以外連接是不能消除的。

場景三：用 CASE WHEN 把 NULL 單獨拎出來處理了

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

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

場景四：用 COALESCE / NVL 把 NULL 替換掉了

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

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

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

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

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

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

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

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

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

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

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

③ 執(zhí)行計劃看得見摸得著

你直接跑個 EXPLAIN 或者 EXPLAIN ANALYZE。就能看出來 KES 到底有沒有消除外連接：

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

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

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

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

謂詞下推是個啥意思

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

-- 原始寫法：外層查詢加過濾
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. 它看出來 sub.amount > 1000 其實是在過濾里面那個子查詢里的 d.amount。
2. 這個 amount 是從 LEFT JOIN 右邊來的，屬于 Nullable-Side。
3. 接著分析 amount > 1000。NULL 大于 1000 算出來是 Unknown。所以這是個 Null-Rejecting 條件。
4. 它判定外連接可以消除，就直接改寫成內(nèi)連接了。
5. 同時它順手把這個過濾條件給推到了子查詢里面去。

最后實際跑的等價 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;

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

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

危險的地方在哪呢？就是當外面有好幾個條件去引用子查詢的時候，復(fù)合變換可能就會搞出意外來。

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)化器得把整個 WHERE 條件拿來做布爾分析：

• 先看 sub.category = 'VIP'。它是 Null-Rejecting 的。因為 NULL = ‘VIP’ 是 Unknown。
• 再看 sub.detail IS NULL。它不是 Null-Rejecting 的。因為 NULL IS NULL 是 True，NULL 行能過。
• 兩個用 OR 連起來。Unknown OR True，結(jié)果是 True。所以整體不是 Null-Rejecting 的。

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

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

五-B、遷移的時候執(zhí)行計劃變了：舊庫沒問題不代表你寫對了

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

答案往往是否定的。不是新庫做錯了，而是老庫的優(yōu)化器當時“碰巧”沒去充分優(yōu)化它。

舊庫當時為啥能跑通

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

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

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

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

③ 那會兒數(shù)據(jù)量小，看不出來

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

我們該怎么去理解這件事

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

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

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

拿外連接來說：

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

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

六、寫代碼的人得記住的兩條原則

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

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

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

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

-- ? 放在 ON 里，外連接語義得到保留
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

② 別拿“舊庫碰巧沒出事”來證明你的 SQL 寫對了

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

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

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

優(yōu)化器想要達到的兩個目標

優(yōu)化器在生成執(zhí)行計劃的時候，心里有兩個目標：

第一層：保證意思不能錯

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

第二層：找一條最快的路

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

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

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

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

如果你平時寫完 SQL，也能習(xí)慣性地想一想：“我寫在 WHERE 里的這個條件，要是碰上右表的 NULL 行，會把它們怎么處理？”如果你有這個習(xí)慣，那你寫的時候就能避開大部分外連接的坑。根本不用等到看執(zhí)行計劃的時候才發(fā)現(xiàn)問題。

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

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

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

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

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

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

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

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

最新評論