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

查了一圈，最后定位到一句看起來很普通的查詢，問題出在 LEFT JOIN 上。這篇就把這個坑掰開揉碎講一下——它怎么產生的、在 KES 里怎么親手驗證，以及上線前怎么把它攔住。

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

報表背后的 SQL 長這樣：

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';

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

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

當時開發(fā)第一反應是，KES 是不是有 bug。

這里先把結論撂下：真不是 KES 的問題。這條 SQL 你原封不動扔到 MySQL、Oracle 里，一樣少這幾行。根子在 SQL 自己的語義上，只不過遷移那陣子做了回歸比對，才把這個一直潛伏的 bug 給照出來。

下面慢慢拆。

二、LEFT JOIN 到底保的是什么

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

其實只對了一半。

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

可以這么想：LEFT JOIN 就像食堂打飯，你來了我就給你配菜，沒菜可配的也給你個空盤子；WHERE 呢，是門口的保安，不管你盤子里有沒有菜，不達標就不放進去。

麻煩就在這——當 WHERE 里冒出來一個專門沖著右表（也就是 orders，會被填 NULL 的那一側）去的條件時，那些靠 LEFT JOIN 勉強留下、右表是 NULL 的行，一算 NULL = 'PAID' 得到的是"未知"，自然就被保安擋外頭了。

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

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

三、真兇：右表條件觸發(fā)了"外連接消除"

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

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

于是它順手改寫了一下：

-- 你寫的，看著像外連接
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)化器眼里的等價形式，其實就是內連接
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';

重點在這：這步改寫不動結果，一行不多一行不少。優(yōu)化器沒改你的語義，只是把一個掛著外連接名頭、其實早沒作用的寫法還原成本來面目，順便讓執(zhí)行計劃跑得快點。

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

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

四、動手驗證

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

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

留意一下 3 號客戶"王五"，他名下一筆訂單都沒有，就是待會兒要消失的那位。

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');
-- 王五沒訂單

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

寫法 A，純 LEFT JOIN，不去過濾右表，客戶全在：

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。

寫法 B，把 status='PAID' 挪到 WHERE 里，這就是翻車的那個寫法：

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

王五沒了，張三那條未支付的也跟著沒了。

寫法 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)

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

4.3 用 EXPLAIN 逮現(xiàn)行

寫法 B 到底是不是被改成了內連接？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，沒有 Left。

再對比寫法 A（不寫 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。

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

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

要是覺得看節(jié)點名還不夠直觀，那就上 ANALYZE，看實際跑出來的行數(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 一比，丟沒丟心里就有數(shù)了。

五、報表里最容易翻車的地方：LEFT JOIN 配 COUNT

做報表的同學對這種寫法肯定不陌生：LEFT JOIN 接一個 COUNT。需求通常長這樣——統(tǒng)計每個客戶有幾筆已支付訂單，沒買過的也顯示個 0。

條件放 ON 的時候，是正常的：

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ù)的是右表非空的行，王五沒匹配上，自然算 0，沒問題。

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

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

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

六、遷移到 KES 還要注意的幾個坑

WHERE 和 ON 這事是標準 SQL 的通病，換哪家?guī)於家粯印５珡?MySQL 或者 PostgreSQL 搬到 KES，還有幾處差異會把這問題放大，讓人覺得 LEFT JOIN 更容易丟數(shù)據(jù)，得單拎出來說說。

MySQL 大小寫不敏感，KES 默認敏感

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

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

KES 默認是大小寫敏感的，'paid' = 'PAID' 直接就不成立。右表匹配不上，補個 NULL，再被 WHERE 一擋，整行又沒了。修起來有幾招：

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

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

隱式類型轉換，KES 比 MySQL 較真

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

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

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

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

空串不等于 NULL

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

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

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

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

七、修法：條件別放錯地方

口訣就一句：想過濾右表、又想保住左表的，條件放 ON；真打算從結果里刪掉整行的，才放 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;

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

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è)務上希望"右表是空也算滿足條件"，那就得顯式把 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;

八、上線前的自查清單

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

先把所有 LEFT JOIN 過一遍，看 WHERE 里有沒有引用右表的列；有的話，確認是不是真打算因為這個條件丟掉左表的行。核心那幾條報表 SQL，順手拿 EXPLAIN 跑一下，只要計劃里 LEFT JOIN 變成了不帶 Left 的內連接，就重點復核。

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

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

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

SQL 多到一條條看不過來的話，先讓腳本把嫌疑大的挑出來，再人工細看：

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

寫在最后

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

這不是 KES 的鍋，標準 SQL 就這么定義的，MySQL、PostgreSQL、Oracle 都一個樣，只是遷移時的回歸測試把它抖了出來。

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

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

遷移遇到問題，別上來就懷疑數(shù)據(jù)庫，先 EXPLAIN 看一眼——多數(shù)時候，優(yōu)化器比咱們的直覺要誠實。

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

最新評論