寫(xiě) LEFT JOIN 的時(shí)候，大部分人心里的預(yù)期都很樸素：左表的數(shù)據(jù)無(wú)論如何都會(huì)保留，右表匹配不上就是 NULL。這個(gè)預(yù)期在大多數(shù)場(chǎng)景下是成立的，但只要 WHERE 子句里對(duì)右表字段加了一個(gè)過(guò)濾條件，這個(gè)預(yù)期就可能悄悄崩掉——SQL 文本里寫(xiě)的明明是 LEFT JOIN，執(zhí)行計(jì)劃里跑出來(lái)的卻是一個(gè)不折不扣的內(nèi)連接，結(jié)果集也跟著變。

這篇不復(fù)現(xiàn)業(yè)務(wù)場(chǎng)景，直接用最干凈的兩張表把這個(gè)機(jī)制的邊界條件挨個(gè)測(cè)一遍：什么條件會(huì)觸發(fā)這種改寫(xiě)、什么條件不會(huì)、條件放在不同位置結(jié)果差多少。環(huán)境還是 KES V009R001C010，業(yè)務(wù)賬號(hào)連接：

ksql -h 127.0.0.1 -p 54321 -U app_user -d app_db

搭一個(gè)最小的測(cè)試臺(tái)

兩張表，t_oje_t1 是左表，t_oje_t2 是右表：

create table app_schema.t_oje_t1 (
  id1 integer primary key,
  name1 varchar(30) not null
);
create table app_schema.t_oje_t2 (
  id2 integer primary key,
  id1 integer not null,
  name2 varchar(30) not null
);
insert into app_schema.t_oje_t1(id1, name1) values
  (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
insert into app_schema.t_oje_t2(id2, id1, name2) values
  (101, 1, 'aa'),
  (102, 2, 'bb'),
  (103, 2, 'cc'),
  (104, 3, 'cc');

數(shù)據(jù)故意設(shè)計(jì)成這樣：t1 有 4 行，id1=4 在 t2 里完全沒(méi)有匹配（模擬"右表缺失"），id1=2 在 t2 里對(duì)應(yīng)兩條記錄（bb 和 cc，模擬一對(duì)多）。后面會(huì)看到，這個(gè)"一對(duì)多"的設(shè)計(jì)埋了一個(gè)很關(guān)鍵的坑，先賣個(gè)關(guān)子。

第一步：右表?xiàng)l件放 WHERE，外連接直接沒(méi)了

explain analyze
select * from app_schema.t_oje_t1 t1
left join app_schema.t_oje_t2 t2 on t1.id1 = t2.id1
where t2.name2 = 'cc';

執(zhí)行計(jì)劃第一行是 Hash Join，注意——不是 Hash Left Join。KES 這邊只要真按外連接執(zhí)行，計(jì)劃里都會(huì)老老實(shí)實(shí)帶上 Left 這個(gè)字樣，這里沒(méi)有，說(shuō)明這條 LEFT JOIN 在優(yōu)化階段就已經(jīng)被改寫(xiě)成了普通內(nèi)連接。往下看，t_oje_t2 被 Seq Scan 掃描，帶了個(gè) Filter: (name2)::text = 'cc'::text)，Rows Removed by Filter: 2——t2 總共 4 行，過(guò)濾后只剩 2 行（id1=2 的 cc、id1=3 的 cc），這 2 行再去跟 t1 做 Hash Join，最終 actual rows=2。

道理很直白：t2.name2 = 'cc' 這個(gè)條件，對(duì) id1=1（沒(méi)有 cc 匹配）和 id1=4（t2 里壓根沒(méi)數(shù)據(jù)，字段全是 NULL）來(lái)說(shuō)，NULL = 'cc' 的結(jié)果既不是真也不是假，是"未知"，在 WHERE 里"未知"就等于被扔掉。既然外連接產(chǎn)生的這些 NULL 行反正都要被過(guò)濾掉，那"外連接 + 這個(gè)過(guò)濾"和"內(nèi)連接 + 這個(gè)過(guò)濾"結(jié)果完全一樣，優(yōu)化器一看這筆賬劃算，就直接換成開(kāi)銷更小的內(nèi)連接去跑了。這就是外連接消除。

第二步：換成 IS NULL，結(jié)果完全反過(guò)來(lái)

explain analyze
select * from app_schema.t_oje_t1 t1
left join app_schema.t_oje_t2 t2 on t1.id1 = t2.id1
where t2.name2 is null;

這次執(zhí)行計(jì)劃顯示的是 Hash Right Join，Filter: (t2.name2 IS NULL)，Rows Removed by Filter: 4，最終 actual rows=1——也就是 id1=4 那一行。

這里有個(gè)細(xì)節(jié)容易讓人愣一下：計(jì)劃里寫(xiě)的是 Right Join，不是 Left Join。這不是消除，是優(yōu)化器把驅(qū)動(dòng)表和探測(cè)表的位置換了一下（先掃 t2 建 hash 表，再拿 t1 去探測(cè)），但語(yǔ)義上 t1 LEFT JOIN t2 和這里的 t2 做 build 端、t1 做 probe 端的 Right Join 是完全等價(jià)的外連接，只是物理執(zhí)行順序反過(guò)來(lái)了，外連接的"保底"語(yǔ)義一點(diǎn)沒(méi)丟。跟第一步的 Hash Join（沒(méi)有 Left/Right 字樣，純內(nèi)連接）完全是兩碼事，不要混在一起看。

為什么這次不能消除：IS NULL 這個(gè)條件本身就是專門(mén)用來(lái)抓外連接產(chǎn)生的 NULL 行的。如果把外連接改成內(nèi)連接，t2 里沒(méi)匹配的行根本進(jìn)不了結(jié)果集，t2.name2 is null 就永遠(yuǎn)不可能為真，這已經(jīng)不是"用更快的方式得到同樣結(jié)果"，是徹底改變了查詢語(yǔ)義，所以優(yōu)化器不會(huì)碰這種條件。

判斷標(biāo)準(zhǔn)到這兒就很清楚了：能不能消除，看這個(gè) WHERE 條件對(duì)外連接產(chǎn)生的 NULL 值判定成什么——一定是假或未知（比如普通等值比較），消除是安全的；有可能判定成真（比如 IS NULL），消除就會(huì)改變結(jié)果，優(yōu)化器不會(huì)做。

第三步：條件換到左表，跟外連接消除沒(méi)關(guān)系

explain analyze
select * from app_schema.t_oje_t1 t1
left join app_schema.t_oje_t2 t2 on t1.id1 = t2.id1
where t1.name1 = 'b';

這次執(zhí)行計(jì)劃是 Nested Loop Left Join，Left 字樣老老實(shí)實(shí)地在，是真正的外連接。Join Filter: (t1.id1 = t2.id1)，t_oje_t1 先按 Filter: (name1)::text = 'b'::text 篩出 1 行（id1=2），再拿這一行去跟 t2 做外連接，最終 actual rows=2——因?yàn)?id1=2 在 t2 里有兩條匹配（bb、cc），一行左表數(shù)據(jù)關(guān)聯(lián)出兩行結(jié)果，這跟外連接消除完全不搭邊。

這一步的過(guò)濾本質(zhì)上是"先決定左表要哪些行，再拿這些行去外連接"，跟右表的 Nullable 特性沒(méi)有任何關(guān)系。很多人容易把"WHERE 里出現(xiàn)的任何條件"都當(dāng)成外連接消除的誘因，這一步就是用來(lái)打破這個(gè)誤解的對(duì)照組。

第四步：把左表?xiàng)l件挪進(jìn) ON，結(jié)果比想象中多了一行

到這一步本來(lái)想驗(yàn)證的是：左表?xiàng)l件放進(jìn) ON，會(huì)不會(huì)跟"右表?xiàng)l件放 WHERE"一樣有什么隱藏效應(yīng)。

select * from app_schema.t_oje_t1 t1
left join app_schema.t_oje_t2 t2
  on t1.id1 = t2.id1 and t1.name1 = 'b';

原本設(shè)想的結(jié)果是 4 行——t1 全量保留，只是 name1 <> 'b' 的行 t2 字段全是 NULL。跑出來(lái)一看，是 5 行，多了一行。仔細(xì)看輸出：id1=2 這一行出現(xiàn)了兩次，一次對(duì)應(yīng) id2=102/bb，一次對(duì)應(yīng) id2=103/cc；id1=1、3、4 各出現(xiàn)一次，t2 相關(guān)字段全是 NULL。

想明白這事之后覺(jué)得挺合理的：t1.name1='b' 這個(gè)條件放進(jìn) ON，只是告訴優(yōu)化器"只有 name1='b' 的左表行才允許去匹配右表"，它管的是"允不允許連"，不管"連上以后右邊能出幾行"。id1=2 滿足 name1='b'，于是它就拿著 id1=2 去 t2 里找所有 id1=2 的記錄——而 t2 里 id1=2 本來(lái)就有兩條，兩條全都會(huì)被連出來(lái)，一條也不會(huì)因?yàn)?quot;外連接只保底一行"就被合并掉。左連接的"保底"只保證左表這一行至少出現(xiàn)一次，從沒(méi)保證只出現(xiàn)一次。

這跟前面認(rèn)為的"4 行"錯(cuò)在哪兒：想當(dāng)然地把"左表 4 行"和"結(jié)果 4 行"劃了等號(hào)，卻忘了右表本來(lái)就有一對(duì)多的數(shù)據(jù)。這個(gè)坑其實(shí)挺常見(jiàn)，很多人寫(xiě)業(yè)務(wù) SQL 的時(shí)候，只要 JOIN 的右表存在一對(duì)多關(guān)系，加不加條件、條件放哪，行數(shù)都可能跟"左表有幾行"對(duì)不上，得先確認(rèn)清楚右表對(duì)應(yīng)關(guān)系，再看結(jié)果對(duì)不對(duì)得上。

第五步：右表?xiàng)l件挪進(jìn) ON，才是真正保住外連接語(yǔ)義的寫(xiě)法

explain analyze
select * from app_schema.t_oje_t1 t1
left join app_schema.t_oje_t2 t2
  on t1.id1 = t2.id1 and t2.name2 = 'cc';

這次執(zhí)行計(jì)劃是 Hash Left Join，Left 字樣穩(wěn)穩(wěn)地在，actual rows=4。跟第四步不一樣的地方在于：這次的過(guò)濾條件 t2.name2='cc' 作用在右表上，它會(huì)先把 t2 過(guò)濾成只剩滿足 name2='cc' 的行（id1=2 的 cc、id1=3 的 cc，id1=2 的 bb 被擋在外面），過(guò)濾完的 t2 每個(gè) id1 最多只剩一條，再拿去跟 t1 做外連接，t1 的 4 行每行正好對(duì)應(yīng) 1 行結(jié)果，id1=1、id1=4 的 t2 字段是 NULL，id1=2、id1=3 有值。

對(duì)比第一步就很清楚了：同樣是想找"關(guān)聯(lián)到 name2=‘cc’ 的記錄"，條件放 WHERE 會(huì)把 t1 里沒(méi)匹配上的行連帶殺掉（外連接被消除），條件放 ON 才能既過(guò)濾右表又保住左表全量。這是這篇最該記住的一條規(guī)則：對(duì)右表的過(guò)濾，只要目的是保留外連接語(yǔ)義，就該放 ON，不要放 WHERE。而對(duì)左表的過(guò)濾（第四步），放 ON 和放 WHERE 效果是不一樣的——放 WHERE 會(huì)先篩左表再連接，放 ON 只決定"篩出來(lái)的左表行允不允許連"，右表該出幾行還是出幾行，兩者不能混著記。

第六步：Oracle(+)寫(xiě)法，同樣的規(guī)則換個(gè)皮

KES 兼容 Oracle 風(fēng)格的 (+) 外連接寫(xiě)法，實(shí)測(cè)看它是不是遵循同一套規(guī)則：

explain analyze
select * from app_schema.t_oje_t1 t1, app_schema.t_oje_t2 t2
where t1.id1 = t2.id1(+)
  and t2.name2 = 'cc';

執(zhí)行計(jì)劃是 Hash Join，沒(méi)有 Left/Right 字樣，actual rows=2，跟第一步的 WHERE 寫(xiě)法結(jié)果一模一樣——過(guò)濾條件不帶 (+)，外連接照樣被消除。再看過(guò)濾條件也帶上 (+) 的寫(xiě)法：

explain analyze
select * from app_schema.t_oje_t1 t1, app_schema.t_oje_t2 t2
where t1.id1 = t2.id1(+)
  and t2.name2(+) = 'cc';

這次是 Hash Left Join，actual rows=4，和第五步條件下推到 ON 的結(jié)果完全一致。這說(shuō)明 (+) 寫(xiě)法底層走的是同一套判斷邏輯，(+) 只是外連接的另一種語(yǔ)法糖，不代表寫(xiě)了它外連接就一定被保留——過(guò)濾條件要不要跟著寫(xiě) (+)，效果跟"條件放 WHERE 還是放 ON"是對(duì)應(yīng)的。這一點(diǎn)對(duì)從 Oracle 遷移過(guò)來(lái)的讀者尤其要注意：老代碼里如果只在連接條件上寫(xiě)了 (+)，后面又單獨(dú)加了一個(gè)不帶 (+) 的過(guò)濾條件，一樣會(huì)被判定為可以安全消除成內(nèi)連接。

收個(gè)尾：怎么在自己的 SQL 里審計(jì)這個(gè)問(wèn)題

跑一遍下來(lái)，判斷標(biāo)準(zhǔn)可以歸成幾條：

1. 看 SQL 文本里有沒(méi)有 LEFT/RIGHT JOIN 或 (+)，這是審計(jì)的起點(diǎn)。
2. 跑一遍 EXPLAIN ANALYZE，盯住連接節(jié)點(diǎn)是不是明確帶 Left/Right 字樣。不帶的話基本可以確定被消除了。
3. 對(duì)照 WHERE 子句，看是不是對(duì)右表（Nullable 一側(cè)）字段做了等值、范圍、IN 之類的過(guò)濾，且沒(méi)用 IS NULL/IS NOT NULL——這類條件是觸發(fā)消除的典型信號(hào)。
4. 右表過(guò)濾條件想保住外連接語(yǔ)義，下推到 ON；(+) 寫(xiě)法下也是同樣道理，過(guò)濾條件要不要帶 (+)，跟對(duì)應(yīng)關(guān)系走。
5. 左表?xiàng)l件放 ON 和放 WHERE 不是一回事：放 WHERE 是先篩左表再連接，放 ON 只決定這行左表能不能參與連接，右表該出幾行還是出幾行——尤其右表存在一對(duì)多關(guān)系時(shí)，千萬(wàn)別拿左表行數(shù)直接套右表行數(shù)。

這幾條規(guī)則說(shuō)到底都是一件事：外連接消不消除，從來(lái)不取決于你寫(xiě)沒(méi)寫(xiě) LEFT，而取決于這個(gè)查詢的整體邏輯，跟內(nèi)連接放在一起算，結(jié)果是不是完全一樣。只要有一丁點(diǎn)不一樣（哪怕只是多保留一個(gè) NULL 行的可能性），優(yōu)化器就不會(huì)去消除；只要完全一樣，它就一定會(huì)去消除，圖的就是內(nèi)連接更便宜這點(diǎn)執(zhí)行開(kāi)銷。寫(xiě) SQL 的時(shí)候腦子里想的是"業(yè)務(wù)要不要保底"，數(shù)據(jù)庫(kù)執(zhí)行的時(shí)候看的是"邏輯上能不能劃等號(hào)"，這中間的落差，就是這一類坑的根源。

到此這篇關(guān)于LEFT JOIN 到底什么時(shí)候會(huì)被優(yōu)化器偷偷改寫(xiě)成 INNER JOIN的文章就介紹到這了,更多相關(guān)left join優(yōu)化替換inner join內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論