一、前言

平時(shí)做國(guó)產(chǎn)庫(kù)遷移，或是線上SQL調(diào)優(yōu)的時(shí)候，不少開(kāi)發(fā)同事都會(huì)碰到一種很難看懂的異?，F(xiàn)象。我們明明寫(xiě)了LEFT JOIN，本意是把左表里所有數(shù)據(jù)都查出來(lái)，最后返回的記錄卻少了一大截。

拿執(zhí)行計(jì)劃去核對(duì)的時(shí)候就能發(fā)現(xiàn)，原來(lái)的左外連接，被優(yōu)化器悄悄改成了INNER JOIN。這個(gè)現(xiàn)象，行業(yè)里一般叫做外連接消除。

KES內(nèi)核自帶和Oracle對(duì)齊的等價(jià)改寫(xiě)邏輯，要是不清楚這套底層轉(zhuǎn)換規(guī)則，線上業(yè)務(wù)很容易出現(xiàn)數(shù)據(jù)統(tǒng)計(jì)出錯(cuò)的問(wèn)題。

下面我準(zhǔn)備了可以直接在KES里跑的測(cè)試SQL，搭配執(zhí)行計(jì)劃對(duì)比，把觸發(fā)條件、背后邏輯、規(guī)避辦法全部講清楚，適配國(guó)產(chǎn)化遷移、日常SQL優(yōu)化這類(lèi)場(chǎng)景。

先搭好兩張測(cè)試表用來(lái)復(fù)現(xiàn)問(wèn)題，直接復(fù)制執(zhí)行就行：

-- 創(chuàng)建左表t1
CREATE TABLE t1 (
    id1 INT PRIMARY KEY,
    name1 VARCHAR(20)
);
-- 創(chuàng)建右表t2
CREATE TABLE t2 (
    id2 INT PRIMARY KEY,
    name2 VARCHAR(20)
);
-- 插入測(cè)試數(shù)據(jù)
INSERT INTO t1 VALUES (1, '張三'),(2, '李四'),(3, '王五'),(4, '趙六');
INSERT INTO t2 VALUES (1, 'cc'),(2, 'dd');
-- 查看原始數(shù)據(jù)
SELECT * FROM t1;
SELECT * FROM t2;

原始數(shù)據(jù)展示
t1表里面一共四條記錄：

二、踩坑案例：錯(cuò)誤寫(xiě)法觸發(fā)外連接消除

1. 有問(wèn)題的SQL寫(xiě)法，過(guò)濾條件放在WHERE子句

SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2
WHERE t2.name2 = 'cc';

大家心里預(yù)期的查詢結(jié)果應(yīng)該是四條，id1等于1的行帶出cc，剩下三條t2字段全部為空：

我們用EXPLAIN ANALYZE看執(zhí)行計(jì)劃，就能確認(rèn)優(yōu)化器做了轉(zhuǎn)換：

EXPLAIN ANALYZE
SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2
WHERE t2.name2 = 'cc';

打印出來(lái)的計(jì)劃關(guān)鍵片段如下：

Hash Join (Inner Join)
  Hash Cond: (t1.id1 = t2.id2)
  -> Seq Scan on t1
  -> Hash
       -> Seq Scan on t2
            Filter: (name2 = 'cc'::character varying)

這里能清楚看到，算子變成了內(nèi)連接Hash Join，也就是前面說(shuō)的外連接消除。那些右表匹配為空的數(shù)據(jù)，直接被過(guò)濾丟掉了。

三、底層原理：WHERE寫(xiě)右表?xiàng)l件會(huì)觸發(fā)消除的原因

1 SQL語(yǔ)句實(shí)際執(zhí)行的先后順序

標(biāo)準(zhǔn)SQL的執(zhí)行順序是先執(zhí)行FROM和JOIN關(guān)聯(lián)，之后才會(huì)走WHERE過(guò)濾。

• 第一步執(zhí)行LEFT JOIN之后，t1里面id3、id4這兩行，在t2這邊沒(méi)有匹配項(xiàng)，t2所有字段都會(huì)填充N(xiāo)ULL；
• 第二步走到WHERE t2.name2 = 'cc’這一段，數(shù)據(jù)庫(kù)判斷NULL和任意常量對(duì)比，結(jié)果都不成立，這兩行就直接被舍棄；
• 整條語(yǔ)句最終的執(zhí)行效果，和先做內(nèi)連接再過(guò)濾沒(méi)有區(qū)別。

2 優(yōu)化器的等價(jià)轉(zhuǎn)換邏輯

KES優(yōu)化器會(huì)自動(dòng)做等價(jià)判斷，這里的邏輯很直白：

• WHERE條件里面，如果是針對(duì)右表的等值、范圍過(guò)濾，所有NULL行最后都會(huì)被篩掉。
• 這種場(chǎng)景下LEFT JOIN和INNER JOIN輸出的數(shù)據(jù)完全一致。
• 內(nèi)連接的計(jì)算開(kāi)銷(xiāo)會(huì)更低，不用額外保留空行，優(yōu)化器就會(huì)自動(dòng)替換連接類(lèi)型，也就是外連接消除。

四、不會(huì)觸發(fā)外連接消除的特殊場(chǎng)景

只有WHERE條件寫(xiě)IS NULL、IS NOT NULL這類(lèi)判斷的時(shí)候，優(yōu)化器不會(huì)改動(dòng)LEFT JOIN。

舉一段查詢左表無(wú)匹配記錄的示例SQL：

SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2
WHERE t2.name2 IS NULL;

這條語(yǔ)句執(zhí)行出來(lái)的結(jié)果：

對(duì)應(yīng)的執(zhí)行計(jì)劃也能看到保留了左連接算子：

Hash Left Join
  Hash Cond: (t1.id1 = t2.id2)
  -> Seq Scan on t1
  -> Hash
       -> Seq Scan on t2
Filter: (t2.name2 IS NULL)

原因也很好理解，IS NULL本身就是用來(lái)抓取關(guān)聯(lián)后空行的邏輯，要是轉(zhuǎn)成內(nèi)連接，這部分?jǐn)?shù)據(jù)直接就沒(méi)了，優(yōu)化器不會(huì)做這種轉(zhuǎn)換。

五、KES里三種標(biāo)準(zhǔn)解決辦法，規(guī)避數(shù)據(jù)丟失

方案1 把右表過(guò)濾條件挪到ON后面（優(yōu)先推薦）

整體思路調(diào)整為先過(guò)濾右表數(shù)據(jù)，再執(zhí)行左關(guān)聯(lián)，左表所有記錄都會(huì)保留，不會(huì)觸發(fā)消除：

SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 AND t2.name2 = 'cc';

執(zhí)行出來(lái)的結(jié)果符合業(yè)務(wù)預(yù)期，四條數(shù)據(jù)全部存在：

執(zhí)行計(jì)劃也能看到Left Join算子保留完整：

Hash Left Join
  Hash Cond: (t1.id1 = t2.id2)
  -> Seq Scan on t1
  -> Hash
       -> Seq Scan on t2
            Filter: (name2 = 'cc'::character varying)

2 Oracle兼容(+)語(yǔ)法場(chǎng)景專用寫(xiě)法

KES支持Oracle老式(+)左連接語(yǔ)法，這里也容易踩同類(lèi)坑。
錯(cuò)誤寫(xiě)法，過(guò)濾條件不帶(+)，會(huì)觸發(fā)外連接消除：

SELECT * FROM t1, t2
WHERE t1.id1 = t2.id2(+) AND t2.name2 = 'cc';

正確寫(xiě)法，右表字段同步加上(+)，過(guò)濾邏輯下沉到關(guān)聯(lián)階段：

SELECT * FROM t1, t2
WHERE t1.id1 = t2.id2(+) AND t2.name2(+) = 'cc';

3 過(guò)濾條件放在左表WHERE不受影響

如果WHERE里面過(guò)濾的是左表字段，不會(huì)觸發(fā)外連接消除，只會(huì)提前篩左表原始數(shù)據(jù)：

-- 只篩選t1里姓名等于張三的數(shù)據(jù)，左關(guān)聯(lián)特性不會(huì)變
SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2
WHERE t1.name1 = '張三';

六、線上SQL排查、核對(duì)小技巧

平時(shí)調(diào)優(yōu)、遷移排查碰到LEFT JOIN行數(shù)不對(duì)，可以按下面步驟定位問(wèn)題：

• 1 先看執(zhí)行計(jì)劃里的連接算子名稱
  正常保留左連接： Hash Left Join / Nested Loop Left Join
  出現(xiàn)消除： 只寫(xiě)Hash Join、Nested Loop，不帶Left標(biāo)識(shí)
• 2 SQL書(shū)寫(xiě)規(guī)范簡(jiǎn)單整理
  右表的等值、區(qū)間、模糊匹配過(guò)濾，統(tǒng)一寫(xiě)到ON子句；
  右表判斷空、非空，WHERE里面寫(xiě)沒(méi)問(wèn)題；
  左表的過(guò)濾條件，WHERE或者ON里寫(xiě)都可以。
• 3 遷移階段優(yōu)先保證數(shù)據(jù)準(zhǔn)確
  從Oracle往KES遷移的時(shí)候，不要單純依賴優(yōu)化器自動(dòng)改寫(xiě)，數(shù)據(jù)正確比查詢速度更重要。

七、全文總結(jié)

• 1 觸發(fā)外連接消除的核心原因：WHERE子句對(duì)LEFT JOIN的右表做非空類(lèi)等值、范圍過(guò)濾，NULL行會(huì)被全部過(guò)濾，優(yōu)化器自動(dòng)換成內(nèi)連接。
• 2 唯一不會(huì)觸發(fā)轉(zhuǎn)換的情況：WHERE條件使用IS NULL / IS NOT NULL判斷右表字段。
• 3 最穩(wěn)妥的修復(fù)方式：把右表的過(guò)濾條件移動(dòng)到JOIN后面的ON條件中。
• 4 使用Oracle(+)兼容語(yǔ)法的時(shí)候，右表過(guò)濾字段也要同步帶上(+)標(biāo)識(shí)。
• 5 線上排查異常數(shù)據(jù)，可以直接拿EXPLAIN ANALYZE查看連接算子，快速確認(rèn)是否發(fā)生外連接消除。

到此這篇關(guān)于KES數(shù)據(jù)庫(kù)優(yōu)化踩坑指南：外連接消除引發(fā)的LEFT JOIN數(shù)據(jù)丟失問(wèn)題深度剖析的文章就介紹到這了,更多相關(guān)KES數(shù)據(jù)庫(kù)實(shí)戰(zhàn)避坑內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論