前陣子有個(gè)團(tuán)隊(duì)把訂單系統(tǒng)點(diǎn)擊了解將MySQL遷移到金倉(cāng)KingbaseES后LEFT JOIN意外丟失數(shù)據(jù)的真實(shí)案例,這篇文章詳細(xì)拆解外連接消除的根因、如何用EXPLAIN定位問(wèn)題,并提供SQL寫(xiě)法與遷移自查清單,幫你提前攔截90%的報(bào)表數(shù)據(jù)丟失從 MySQL 搬到金倉(cāng) KingbaseES（下面統(tǒng)一叫 KES），結(jié)構(gòu)轉(zhuǎn)完了，SQL 也改完了，回歸一路過(guò)。結(jié)果上線第二天，財(cái)務(wù)找過(guò)來(lái)說(shuō)對(duì)賬報(bào)表少了好幾個(gè)客戶。

查了一圈，最后定位到一句看起來(lái)很普通的查詢，問(wèn)題出在 LEFT JOIN 上。這篇就把這個(gè)坑掰開(kāi)揉碎講一下——它怎么產(chǎn)生的、在 KES 里怎么親手驗(yàn)證，以及上線前怎么把它攔住。

一、先看翻車現(xiàn)場(chǎng)

報(bào)表背后的 SQL 長(zhǎng)這樣：

SELECT c.cust_name, o.order_no, o.amount
FROM   customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id
WHERE  o.status = 'PAID';

需求其實(shí)挺好懂：把所有客戶都列出來(lái)，每人帶上自己已支付（PAID）的訂單；有些人可能壓根沒(méi)下過(guò)單，或者只下過(guò)未支付的，那這種人客戶信息也得留著，訂單那幾列空著就行。

SQL 里寫(xiě)得明明白白是 LEFT JOIN，按道理左表一行都漏不掉?？梢慌?mdash;—好家伙，沒(méi)訂單的、只有未支付訂單的客戶，全沒(méi)了。

當(dāng)時(shí)開(kāi)發(fā)第一反應(yīng)是，KES 是不是有 bug。

這里先把結(jié)論撂下：真不是 KES 的問(wèn)題。這條 SQL 你原封不動(dòng)扔到 MySQL、Oracle 里，一樣少這幾行。根子在 SQL 自己的語(yǔ)義上，只不過(guò)遷移那陣子做了回歸比對(duì)，才把這個(gè)一直潛伏的 bug 給照出來(lái)。

下面慢慢拆。

二、LEFT JOIN 到底保的是什么

很多人有個(gè)下意識(shí)的認(rèn)知，覺(jué)得只要 SQL 里寫(xiě)了 LEFT JOIN，左表的行就穩(wěn)了。

其實(shí)只對(duì)了一半。

LEFT JOIN 那句"左表全保留"的承諾，只認(rèn)它自己 ON 后面那個(gè)條件。WHERE 不歸它管——WHERE 是等連接做完之后，再對(duì)結(jié)果做的一次篩選，它分不清什么外連接內(nèi)連接。

可以這么想：LEFT JOIN 就像食堂打飯，你來(lái)了我就給你配菜，沒(méi)菜可配的也給你個(gè)空盤(pán)子；WHERE 呢，是門(mén)口的保安，不管你盤(pán)子里有沒(méi)有菜，不達(dá)標(biāo)就不放進(jìn)去。

麻煩就在這——當(dāng) WHERE 里冒出來(lái)一個(gè)專門(mén)沖著右表（也就是 orders，會(huì)被填 NULL 的那一側(cè)）去的條件時(shí)，那些靠 LEFT JOIN 勉強(qiáng)留下、右表是 NULL 的行，一算 NULL = 'PAID' 得到的是"未知"，自然就被保安擋外頭了。

FROM customers                  -- 先把左表拿來(lái)
LEFT JOIN orders ON ...         -- 連一下：左表全留，右表沒(méi)匹配的補(bǔ) NULL
WHERE o.status = 'PAID'         -- 再篩：右表是 NULL 的行，條件算出來(lái) UNKNOWN，被過(guò)濾

數(shù)據(jù)就丟在最后這一步。

三、真兇：右表?xiàng)l件觸發(fā)了"外連接消除"

這現(xiàn)象有個(gè)名字，叫外連接消除（Outer Join Elimination），通俗講就是外連接被優(yōu)化器偷偷改寫(xiě)成了內(nèi)連接。

道理其實(shí)挺樸素的。只要 WHERE 里出現(xiàn)一個(gè)針對(duì)右表、并且天生排斥空值的條件——比如 o.status = 'PAID'、o.amount > 0、o.order_id IS NOT NULL——優(yōu)化器就琢磨：這一側(cè)反正不可能有 NULL，有的話早被 WHERE 干掉了，那這 LEFT JOIN 跟 INNER JOIN 還有啥區(qū)別？

于是它順手改寫(xiě)了一下：

-- 你寫(xiě)的，看著像外連接
SELECT c.cust_name, o.order_no
FROM   customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id
WHERE  o.status = 'PAID';
-- 優(yōu)化器眼里的等價(jià)形式，其實(shí)就是內(nèi)連接
SELECT c.cust_name, o.order_no
FROM   customers c
INNER JOIN orders o ON c.cust_id = o.cust_id
WHERE  o.status = 'PAID';

重點(diǎn)在這：這步改寫(xiě)不動(dòng)結(jié)果，一行不多一行不少。優(yōu)化器沒(méi)改你的語(yǔ)義，只是把一個(gè)掛著外連接名頭、其實(shí)早沒(méi)作用的寫(xiě)法還原成本來(lái)面目，順便讓執(zhí)行計(jì)劃跑得快點(diǎn)。

所以真相就一句——這幾行數(shù)據(jù)本來(lái)就該丟，不是 KES 給弄沒(méi)的。優(yōu)化器只不過(guò)比你坦白，直接告訴你這 LEFT JOIN 壓根沒(méi)起作用。

想通這個(gè)，你也就理解了，為啥同一條 SQL 在老庫(kù) MySQL 里也少數(shù)據(jù)，只是那會(huì)兒數(shù)據(jù)少、又沒(méi)人挨個(gè)對(duì)，就一直沒(méi)被發(fā)現(xiàn)。

四、動(dòng)手驗(yàn)證

講道理不如動(dòng)手。我們?cè)?KES 里建張小表，讓數(shù)據(jù)丟一回給你看，再用 EXPLAIN 把優(yōu)化器這步操作逮住。

4.1 先備一桌數(shù)據(jù)

留意一下 3 號(hào)客戶"王五"，他名下一筆訂單都沒(méi)有，就是待會(huì)兒要消失的那位。

CREATE TABLE customers (
    cust_id   INT PRIMARY KEY,
    cust_name VARCHAR(50)
);
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    cust_id  INT,
    amount   NUMERIC(10,2),
    status   VARCHAR(10)
);
INSERT INTO customers VALUES (1,'張三'), (2,'李四'), (3,'王五');
INSERT INTO orders VALUES
    (101, 1, 100.00, 'PAID'),
    (102, 1,  50.00, 'UNPAID'),
    (103, 2, 200.00, 'PAID');
-- 王五沒(méi)訂單

4.2 同一份數(shù)據(jù)，三種寫(xiě)法

寫(xiě)法 A，純 LEFT JOIN，不去過(guò)濾右表，客戶全在：

SELECT c.cust_name, o.order_no, o.status
FROM   customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id;

 cust_name | order_no | status
-----------+----------+--------
 張三      | 101      | PAID
 張三      | 102      | UNPAID
 李四      | 103      | PAID
 王五      | (null)   | (null)

王五保住了，訂單那列給他填 NULL。

寫(xiě)法 B，把 status='PAID' 挪到 WHERE 里，這就是翻車的那個(gè)寫(xiě)法：

SELECT c.cust_name, o.order_no, o.status
FROM   customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id
WHERE  o.status = 'PAID';

 cust_name | order_no | status
-----------+----------+--------
 張三      | 101      | PAID
 李四      | 103      | PAID

王五沒(méi)了，張三那條未支付的也跟著沒(méi)了。

寫(xiě)法 C，條件放回 ON：

SELECT c.cust_name, o.order_no, o.status
FROM   customers c
LEFT JOIN orders o
       ON c.cust_id = o.cust_id
      AND o.status = 'PAID';

 cust_name | order_no | status
-----------+----------+--------
 張三      | 101      | PAID
 李四      | 103      | PAID
 王五      | (null)   | (null)

王五回來(lái)了。這才是"列出所有客戶、帶上已支付訂單"該有的樣子。

4.3 用 EXPLAIN 逮現(xiàn)行

寫(xiě)法 B 到底是不是被改成了內(nèi)連接？EXPLAIN 一跑就知道。

EXPLAIN
SELECT c.cust_name, o.order_no
FROM   customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id
WHERE  o.status = 'PAID';

                    QUERY PLAN
--------------------------------------------------------------
 Hash Join
   Hash Cond: (c.cust_id = o.cust_id)
   ->  Seq Scan on customers c
   ->  Hash
         ->  Seq Scan on orders o
               Filter: (status = 'PAID'::text)

看第一行，是 Hash Join，沒(méi)有 Left。

再對(duì)比寫(xiě)法 A（不寫(xiě) WHERE，外連接還活著）：

EXPLAIN
SELECT c.cust_name, o.order_no
FROM   customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id;

                    QUERY PLAN
--------------------------------------------------------------
 Hash Left Join
   Hash Cond: (c.cust_id = o.cust_id)
   ->  Seq Scan on customers c
   ->  Hash
         ->  Seq Scan on orders o

這回是 Hash Left Join，帶著 Left。

信號(hào)其實(shí)挺明顯：只要發(fā)現(xiàn)"我明明寫(xiě)的 LEFT JOIN，計(jì)劃里卻是個(gè)沒(méi) Left 的內(nèi)連接"，基本就能斷定——哪個(gè)沖著右表的 WHERE 條件，把外連接給消除掉了。嵌套循環(huán)和歸并連接同理，Nested Loop Left Join 會(huì)變成 Nested Loop，Merge Left Join 會(huì)變成 Merge Join。

4.4 再補(bǔ)一錘：看真實(shí)行數(shù)

要是覺(jué)得看節(jié)點(diǎn)名還不夠直觀，那就上 ANALYZE，看實(shí)際跑出來(lái)的行數(shù)：

EXPLAIN ANALYZE
SELECT c.cust_name, o.order_no
FROM   customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id
WHERE  o.status = 'PAID';

                          QUERY PLAN
--------------------------------------------------------------
 Hash Join ( ... ) (actual ... rows=2 ...)
   Hash Cond: (c.cust_id = o.cust_id)
   ->  Seq Scan on customers c (actual rows=3 ...)
   ->  Hash
         ->  Seq Scan on orders o (actual rows=3 ...)
               Filter: (status = 'PAID'::text)
               Rows Removed by Filter: 1

左表明明掃出 3 行，最后連接只吐了 2 行，少掉那一行就是王五。actual rows 一比，丟沒(méi)丟心里就有數(shù)了。

五、報(bào)表里最容易翻車的地方：LEFT JOIN 配 COUNT

做報(bào)表的同學(xué)對(duì)這種寫(xiě)法肯定不陌生：LEFT JOIN 接一個(gè) COUNT。需求通常長(zhǎng)這樣——統(tǒng)計(jì)每個(gè)客戶有幾筆已支付訂單，沒(méi)買過(guò)的也顯示個(gè) 0。

條件放 ON 的時(shí)候，是正常的：

SELECT c.cust_name, COUNT(o.order_id) AS paid_cnt
FROM   customers c
LEFT JOIN orders o
       ON c.cust_id = o.cust_id
      AND o.status = 'PAID'
GROUP BY c.cust_name
ORDER BY c.cust_name;

 cust_name | paid_cnt
-----------+----------
 張三      | 1
 李四      | 1
 王五      | 0

COUNT 數(shù)的是右表非空的行，王五沒(méi)匹配上，自然算 0，沒(méi)問(wèn)題。

可一旦又把 status='PAID' 順手塞回 WHERE，王五就又消失了，這回連統(tǒng)計(jì)成 0 的資格都沒(méi)了：

SELECT c.cust_name, COUNT(o.order_id) AS paid_cnt
FROM   customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id
WHERE  o.status = 'PAID'
GROUP BY c.cust_name;

 cust_name | paid_cnt
-----------+----------
 張三      | 1
 李四      | 1

這里有個(gè)細(xì)節(jié)，遷移之后要是發(fā)現(xiàn)統(tǒng)計(jì)數(shù)字對(duì)不上，先別急著翻數(shù)據(jù)——先看看 COUNT(*) 和 COUNT(右表某列) 有沒(méi)有用混。前者數(shù)所有行，后者只數(shù)右表非空的那部分，口徑完全不一樣。

六、遷移到 KES 還要注意的幾個(gè)坑

WHERE 和 ON 這事是標(biāo)準(zhǔn) SQL 的通病，換哪家?guī)於家粯?。但?MySQL 或者 PostgreSQL 搬到 KES，還有幾處差異會(huì)把這問(wèn)題放大，讓人覺(jué)得 LEFT JOIN 更容易丟數(shù)據(jù)，得單拎出來(lái)說(shuō)說(shuō)。

MySQL 大小寫(xiě)不敏感，KES 默認(rèn)敏感

這個(gè)踩的人最多。MySQL 的字符串列默認(rèn)走大小寫(xiě)不敏感的排序規(guī)則（像 utf8mb4_general_ci 這種），下面這條能匹配上 ‘PAID’：

WHERE o.status = 'paid'      -- MySQL 里能命中 'PAID'

KES 默認(rèn)是大小寫(xiě)敏感的，'paid' = 'PAID' 直接就不成立。右表匹配不上，補(bǔ)個(gè) NULL，再被 WHERE 一擋，整行又沒(méi)了。修起來(lái)有幾招：

-- 轉(zhuǎn)小寫(xiě)再比
WHERE lower(o.status) = 'paid'
-- 或者用不區(qū)分大小寫(xiě)的匹配
WHERE o.status ILIKE 'paid'

更省心的辦法是在遷移那陣就把這類枚舉值統(tǒng)一成大寫(xiě)或小寫(xiě)，從根上斷了歧義。

隱式類型轉(zhuǎn)換，KES 比 MySQL 較真

MySQL 在連接條件、WHERE 里對(duì)跨類型比較特別寬容，一個(gè) INT 列跟字符串 ‘123’ 也能比對(duì)上：

-- MySQL：a.id 是 INT，b.code 是 VARCHAR '123'，照樣匹配
FROM a JOIN b ON a.id = b.code

KES 在這上面就較真多了，字符型和數(shù)值型混著用，輕的匹配率下降，重的直接報(bào)錯(cuò)，表現(xiàn)出來(lái)還是"右表匹配不上、數(shù)據(jù)變少"。穩(wěn)妥起見(jiàn)，顯式把類型對(duì)齊：

FROM a JOIN b ON a.id = b.code::int

空串不等于 NULL

有些從 MySQL 遷過(guò)來(lái)的數(shù)據(jù)，"沒(méi)填"的地方存的是空字符串，不是 NULL。你要是寫(xiě) WHERE o.remark IS NULL，在 KES 里對(duì)空串是不命中的——空串它不是 NULL。排查的時(shí)候得把空串也帶上：

WHERE o.remark IS NULL OR o.remark = ''

順帶提一下從 Oracle 來(lái)的 (+)

要是源頭是 Oracle，KES 是兼容 (+) 外連接寫(xiě)法的。但這符號(hào)特別容易寫(xiě)錯(cuò)，多條件的時(shí)候每個(gè)條件都得加 (+)，還不能跟 OR、IN 搭一起，搞不好就又變成"本想外連接、結(jié)果成了內(nèi)連接"。遷移的時(shí)候建議直接全改成 LEFT JOIN ... ON (...)，干凈，也好維護(hù)。

七、修法：條件別放錯(cuò)地方

口訣就一句：想過(guò)濾右表、又想保住左表的，條件放 ON；真打算從結(jié)果里刪掉整行的，才放 WHERE。

條件放 ON 是最常用的：

SELECT c.cust_name, o.order_no
FROM   customers c
LEFT JOIN orders o
       ON c.cust_id = o.cust_id
      AND o.status = 'PAID'
      AND o.amount >= 100;

右表的篩選邏輯要是比較復(fù)雜，就先在子查詢里篩干凈再連，可讀性好很多：

SELECT c.cust_name, t.order_no
FROM   customers c
LEFT JOIN (
    SELECT cust_id, order_no
    FROM   orders
    WHERE  status = 'PAID' AND amount >= 100
) t ON c.cust_id = t.cust_id;

還有一種情況，業(yè)務(wù)上希望"右表是空也算滿足條件"，那就得顯式把 NULL 處理一下：

SELECT c.cust_name, o.order_no
FROM   customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id
WHERE  o.status = 'PAID' OR o.status IS NULL;

八、上線前的自查清單

遷移的回歸流程里，把下面這些事安排上，后面能省掉大量排查時(shí)間。

先把所有 LEFT JOIN 過(guò)一遍，看 WHERE 里有沒(méi)有引用右表的列；有的話，確認(rèn)是不是真打算因?yàn)檫@個(gè)條件丟掉左表的行。核心那幾條報(bào)表 SQL，順手拿 EXPLAIN 跑一下，只要計(jì)劃里 LEFT JOIN 變成了不帶 Left 的內(nèi)連接，就重點(diǎn)復(fù)核。

別只盯著"跑不報(bào)錯(cuò)"——同一份數(shù)據(jù)，遷移前后對(duì)核心 SQL 做結(jié)果比對(duì)，行數(shù)和抽樣內(nèi)容都得對(duì)上。這塊最容易被忽略，但也最能提前把問(wèn)題兜住。

數(shù)據(jù)層面的幾個(gè)點(diǎn)也別落下：MySQL 那邊大小寫(xiě)不敏感的列，在 KES 這邊給個(gè)明確的大小寫(xiě)策略；JOIN 和 WHERE 里做比較的兩邊，類型顯式對(duì)齊，別讓隱式轉(zhuǎn)換偷偷改命中率；空串和 NULL 要摸一遍，確認(rèn) IS NULL 不會(huì)漏掉空串?dāng)?shù)據(jù)。

聚合那塊單獨(dú)提一下，COUNT(*) 和 COUNT(右表列) 別用混，前者數(shù)所有行，后者只數(shù)右表非空的部分。要是源頭是 Oracle，(+) 統(tǒng)一改成標(biāo)準(zhǔn) LEFT JOIN。

SQL 多到一條條看不過(guò)來(lái)的話，先讓腳本把嫌疑大的挑出來(lái)，再人工細(xì)看：

# 掃一遍代碼，把帶 LEFT JOIN 的語(yǔ)句都列出來(lái)
grep -rniE "left[[:space:]]+(outer[[:space:]]+)?join" \
    src/ --include="*.sql" --include="*.xml" --include="*.java"
# MyBatis 的 XML 里最愛(ài)藏這種 SQL，單獨(dú)盯一下
grep -rniE "left[[:space:]]+(outer[[:space:]]+)?join" \
    src/main/resources/mapper/ --include="*.xml"

寫(xiě)在最后

說(shuō)到底，LEFT JOIN 丟數(shù)據(jù)這事，根子是過(guò)濾條件放錯(cuò)了地方——寫(xiě)在了 WHERE 里，又恰好作用在會(huì)被填 NULL 的那一側(cè)，于是 KES 做了外連接消除，把外連接改成了內(nèi)連接，左表沒(méi)匹配上的行就跟著沒(méi)了。

這不是 KES 的鍋，標(biāo)準(zhǔn) SQL 就這么定義的，MySQL、PostgreSQL、Oracle 都一個(gè)樣，只是遷移時(shí)的回歸測(cè)試把它抖了出來(lái)。

排查的時(shí)候，EXPLAIN 是最好用的家伙：連接節(jié)點(diǎn)從 Hash Left Join 變成 Hash Join，就是外連接被消除的信號(hào)。

真正的坑，除了 WHERE 和 ON，主要集中在大小寫(xiě)敏感、隱式類型轉(zhuǎn)換、空串和 NULL、還有 (+) 這幾樣上，按前面那份清單逐個(gè)過(guò)一遍，基本就穩(wěn)了。

遷移遇到問(wèn)題，別上來(lái)就懷疑數(shù)據(jù)庫(kù)，先 EXPLAIN 看一眼——多數(shù)時(shí)候，優(yōu)化器比咱們的直覺(jué)要誠(chéng)實(shí)。

到此這篇關(guān)于MySQL/PostgreSQL 遷移金倉(cāng) KES：LEFT JOIN 丟數(shù)據(jù)排查與避坑指南的文章就介紹到這了,更多相關(guān)mysql/pl 遷移kes left join丟數(shù)據(jù)排查內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論