搞多表查詢很多人一開始是懵的單表 CURD 寫得好好的一到連表就抓瞎要么數(shù)據(jù)對不上要么慢得像蝸牛甚至一條 SQL 能把數(shù)據(jù)庫拖垮。MySQL 里的多表查詢說白了就是“把多張表的數(shù)據(jù)按關(guān)系拼起來”但這里面的門道不少從 JOIN 的類型選擇到連接條件的寫法再到關(guān)聯(lián)字段的索引設(shè)計每一步都可能把你的查詢從“能用”變成“好用”。這篇文章我從頭到尾梳理一遍多表查詢的完整知識體系包括各種 JOIN 的適用場景、一個從零開始的小型實戰(zhàn)、性能優(yōu)化里最容易踩的坑以及一堆實際排查問題的經(jīng)驗。不管你剛?cè)腴T SQL還是寫了幾年代碼想系統(tǒng)補補短板這篇都值得花幾分鐘看完。1. 為什么一定要用多表查詢數(shù)據(jù)拆散與關(guān)系重建1.1 數(shù)據(jù)為什么要拆到多張表你先想一個現(xiàn)實場景做一個電商系統(tǒng)的用戶訂單功能。如果按“方便”的思路來一張表塞下用戶昵稱、手機號、商品名稱、商品單價、訂單數(shù)量、下單時間、收貨地址確實查詢很爽但問題接踵而至。同一用戶買了十件商品用戶昵稱和手機號就得重復存十遍商品價格調(diào)整了歷史訂單里的價格跟著改動還是不改動地址變了往期訂單的收貨信息全亂套了而且這些字段間還存在更新異常、插入異常、刪除異常。所以才要把數(shù)據(jù)拆開。用戶信息放一張表商品放一張表訂單放一張表訂單明細再放一張表。每張表只負責“一類事實”通過外鍵字段把關(guān)系串聯(lián)起來。這就是規(guī)范化的基本思路。但拆完之后你看數(shù)據(jù)的視角就碎了——想看“某用戶最近買了什么”得同時拿用戶表、訂單表、訂單明細表、商品表。這時候多表查詢就是把這些碎塊重新拼接成完整信息的唯一手段。MySQL 的數(shù)據(jù)組織方式也決定了它天然適合多表查詢。關(guān)系型數(shù)據(jù)庫的核心數(shù)學模型就是“關(guān)系”一張表是一個關(guān)系兩張表之間的外鍵關(guān)聯(lián)也是一個關(guān)系。SQL 作為關(guān)系數(shù)據(jù)庫的語言JOIN 就是關(guān)系代數(shù)里的連接運算的落地實現(xiàn)。所以別把多表查詢當成一個“高級功能”它是關(guān)系型數(shù)據(jù)庫的看家本事繞不開。1.2 連接條件缺失時的“數(shù)據(jù)爆炸”笛卡爾積多表查詢最容易被忽視、后果最嚴重的問題就是忘記寫連接條件。比如SELECT u.user_name, o.order_no FROM t_user u, t_order o;這種老式寫法用逗號分隔兩張表如果寫漏了 WHERE 里的關(guān)聯(lián)條件MySQL 會把 t_user 的每一行都和 t_order 的每一行配對一次。用戶表 1000 行訂單表 2000 行結(jié)果集直接變成 200 萬行。這就是笛卡爾積。很多剛?cè)腴T的同學第一次寫多表查詢就栽在這里結(jié)果刷出來一大堆莫名其妙的數(shù)據(jù)以為是自己查詢邏輯寫錯了查了半天發(fā)現(xiàn)問題出在跨表配對?，F(xiàn)代的 JOIN 寫法其實也是這樣。只要 ON 子句里的條件寫在但不符合實際關(guān)聯(lián)邏輯或者干脆漏掉 ON 條件一樣會產(chǎn)生笛卡爾積。比如FROM t_user LEFT JOIN t_order后面什么都不跟MySQL 也會把兩張表全交叉一遍。笛卡爾積在特定場景下是有用的比如你要生成測試數(shù)據(jù)、做交叉組合枚舉。但業(yè)務(wù)查詢里一旦出現(xiàn)全量交叉要么是 SQL 寫錯了要么就是過濾條件沒加全。排查的時候有個經(jīng)驗如果結(jié)果集行數(shù)突然等于兩張表行數(shù)的乘積十有八九是連接條件丟失。1.3 連接的底層邏輯拿一張表的每一行去另一張表匹配拋開各種 JOIN 的語法差異多表查詢要做的事其實非常樸素拿左表的一行數(shù)據(jù)根據(jù)連接條件去右表找對應的行找到就拼成新行沒找到就看 JOIN 類型決定留還是丟。這個匹配過程如果沒有任何優(yōu)化開銷就是左表行數(shù)乘以右表行數(shù)。MySQL 的優(yōu)化器會盡力規(guī)避這種全表匹配比如用索引快速定位右表中符合條件的行或者把驅(qū)動表換成數(shù)據(jù)量更小的那張。理解“拿一行去匹配另一張表”這個模型很多問題就解釋得通了。比如你給關(guān)聯(lián)字段建了索引JOIN 會快很多因為匹配時可以直接走索引查找而不是逐行掃描。再比如連接條件里的字段類型不一致MySQL 需要對其中一列做隱式轉(zhuǎn)換一旦轉(zhuǎn)換發(fā)生在索引列上索引就失效了匹配退化成全表掃描。這些事情稍后詳細講但底層模型先行后續(xù)內(nèi)容都建立在這個匹配模型上。2. JOIN 家族的完整解析從語法到選型2.1 INNER JOIN只要大家都有的那部分INNER JOIN 也叫內(nèi)連接是用的最多的 JOIN 類型表達的是“交集”。它返回的是左表和右表中連接條件能同時命中的那些行。連接條件不成立的左表數(shù)據(jù)、右表數(shù)據(jù)統(tǒng)統(tǒng)不會出現(xiàn)在結(jié)果里。SELECT u.user_name, o.order_no FROM t_user u INNER JOIN t_order o ON u.user_id o.user_id;這個查詢的意思只返回那些確實下過訂單的用戶及其對應的訂單記錄。沒下過單的用戶雖然用戶表里有他的資料但在訂單表里找不到匹配行所以不會出現(xiàn)在結(jié)果里。INNER JOIN 有一個容易被忽略的特點如果右表存在多條匹配記錄左表的那一行會被復制多份。比如一個用戶下了三筆訂單INNER JOIN 之后結(jié)果里這個用戶會對應三行每行是不同的訂單。這個特性在做聚合統(tǒng)計時尤其要小心。你要統(tǒng)計“下單用戶數(shù)”如果直接COUNT(u.user_id)一個用戶有三筆訂單就會被數(shù)成三次。必須配合DISTINCT或者先子查詢?nèi)ブ卦訇P(guān)聯(lián)統(tǒng)計。什么時候優(yōu)先用 INNER JOIN答案是你只關(guān)心兩邊都滿足條件的記錄其他數(shù)據(jù)一概不需要。比如查有效訂單的用戶信息、查有庫存且上架的商品明細、查有對應班級信息的學生名單。內(nèi)連接不會給你意外的 NULL數(shù)據(jù)相對干凈也是三種 JOIN 里性能通常最容易優(yōu)化的。2.2 LEFT JOIN 與 RIGHT JOIN主從關(guān)系的兩個鏡像LEFT JOIN 的邏輯是左表的每一行都保留下來右表根據(jù)連接條件去匹配匹配到了就把右表字段填上匹配不到就把右表字段置成 NULL。RIGHT JOIN 完全反過來以右表為基準保留全部行左表匹配不上就補 NULL。看一個典型例子SELECT u.user_name, o.order_no FROM t_user u LEFT JOIN t_order o ON u.user_id o.user_id;這個查詢會返回所有用戶包括沒下過單的用戶。沒下過單的用戶o.order_no是 NULL。這個結(jié)果和 INNER JOIN 的差異正好暴露了業(yè)務(wù)含義的區(qū)別一個查“所有用戶及其訂單狀態(tài)不管有沒有單”一個查“真的下過單的用戶”。RIGHT JOIN 的應用場景相對少因為大多數(shù)情況下你能通過調(diào)換表的書寫順序把 RIGHT JOIN 改寫成等價的 LEFT JOIN。但有一些復雜場景比如嵌套子查詢里已經(jīng)固定了表的別名含義或者你希望保留右側(cè)副表的數(shù)據(jù)RIGHT JOIN 會更直觀。我個人建議日常查詢統(tǒng)一用 LEFT JOIN保持思維一致性少一個方向就少一層認知負擔。這里有個極其關(guān)鍵的坑LEFT JOIN 的 WHERE 條件要分清楚寫在 ON 里和寫在 WHERE 里完全不一樣。ON 里的右表條件只影響匹配過程即使條件不滿足左表行依然會被保留WHERE 里的右表條件是在 JOIN 完成之后過濾結(jié)果一旦過濾條件拒絕了 NULL左表中原本沒匹配上的行就會被剔除相當于把 LEFT JOIN 悄悄變成了 INNER JOIN。后面單獨展開。2.3 CROSS JOIN 與自連接兩個特別但實用的變種CROSS JOIN 就是笛卡爾積的顯式寫法比如SELECT * FROM t_product p CROSS JOIN t_activity a;商品表和活動表之間的全量組合可以用來批量生成活動推廣組合記錄。這種 JOIN 沒有連接條件結(jié)果行數(shù)是兩張表行數(shù)的乘積使用前必須有意識地控制數(shù)據(jù)量否則秒級 SQL 能給你跑出幾分鐘的大事故。自連接是指表和自身做 JOIN。表面上看是“自己連自己”但處理的是行與行之間有關(guān)系的數(shù)據(jù)。典型場景是員工表和上級的關(guān)系或者商品分類表里的父子層級。SELECT e.emp_name AS employee, m.emp_name AS manager FROM t_employee e LEFT JOIN t_employee m ON e.manager_id m.emp_id;如果不用自連接你需要把同一張表當成兩張獨立的邏輯表來對待分別取別名。MySQL 沒有專門處理樹形結(jié)構(gòu)的關(guān)鍵字8.0 有遞歸 CTE但那是另一套思路而自連接是理解結(jié)構(gòu)關(guān)系最直白的方案。第一次寫自連接容易犯糊涂核心要點就是給同一張表起兩個不同的別名把實例當作兩個獨立對象看待然后按普通 JOIN 的規(guī)則操作。2.4 JOIN 類型橫向?qū)Ρ纫粡埍砜炊Z義差異把四種 JOIN 的語義擺在同一張表里新手對照選型會清晰很多JOIN 類型核心語義左表無匹配時右表無匹配時典型業(yè)務(wù)場景INNER JOIN兩邊交集該行不返回該行不返回查詢有效訂單、已配對記錄LEFT JOIN左表全部保留保留左表行右表補 NULL—查詢所有用戶的下單情況RIGHT JOIN右表全部保留—保留右表行左表補 NULL以右表為主視角的報表CROSS JOIN全量交叉組合——生成組合數(shù)據(jù)、批量匹配自連接本質(zhì)上不是新的 JOIN 類型它是 JOIN 的一種用法用 INNER 或 LEFT 都可以。選型時最重要的判斷依據(jù)是業(yè)務(wù)上你要不要把某張表的“全量數(shù)據(jù)”作為結(jié)果集的主干。需要就走 LEFT/RIGHT不需要就 INNER要生成組合就 CROSS。3. 小型實戰(zhàn)從零搭建一個多表查詢模型3.1 建表和準備數(shù)據(jù)把關(guān)系先理順光講語法不練手等于白看這一節(jié)我們直接搭一個迷你訂單系統(tǒng)把多表查詢的核心套路走一遍。假設(shè)有四張表用戶表、商品表、訂單表、訂單明細表。關(guān)系是這樣一個用戶可以有多個訂單一個訂單屬于一個用戶一個訂單包含多個商品通過訂單明細表來記錄一個商品出現(xiàn)在多個訂單里也是通過訂單明細表來體現(xiàn)。這種用戶和商品之間通過訂單和明細間接產(chǎn)生的多對多關(guān)系正好是多表查詢最能體現(xiàn)價值的地方。CREATE TABLE t_user ( user_id INT PRIMARY KEY AUTO_INCREMENT, user_name VARCHAR(50), phone VARCHAR(20) ); CREATE TABLE t_product ( product_id INT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(100), price DECIMAL(10,2) ); CREATE TABLE t_order ( order_id INT PRIMARY KEY AUTO_INCREMENT, user_id INT, order_no VARCHAR(32), order_time DATETIME ); CREATE TABLE t_order_detail ( detail_id INT PRIMARY KEY AUTO_INCREMENT, order_id INT, product_id INT, quantity INT, amount DECIMAL(10,2) );然后插入幾條簡單數(shù)據(jù)三個用戶里有兩個下過單總共兩筆訂單第一筆訂單買了兩樣商品第二筆買了一樣。故意讓第三個用戶沒有訂單這樣后續(xù)對比 INNER JOIN 和 LEFT JOIN 的結(jié)果差異時非常直觀。商品表放四種商品其中一種沒有銷量。3.2 從訂單視角出發(fā)JOIN 多張表拼出完整信息第一個業(yè)務(wù)需求管理后臺要展示訂單列表顯示訂單號、下單用戶、商品名稱、數(shù)量、金額、下單時間。這個需求看起來簡單但如果不 JOIN你拿到的是分散在四張表里的零散數(shù)據(jù)。正確寫法SELECT o.order_no, u.user_name, p.product_name, d.quantity, d.amount, o.order_time FROM t_order o INNER JOIN t_user u ON o.user_id u.user_id INNER JOIN t_order_detail d ON o.order_id d.order_id INNER JOIN t_product p ON d.product_id p.product_id;這里每一層 JOIN 都是必要的訂單要關(guān)聯(lián)用戶拿用戶名訂單明細要關(guān)聯(lián)訂單拿商品明細商品要關(guān)聯(lián)明細拿商品名。留意一個現(xiàn)象第一筆訂單買了兩個商品所以訂單號、用戶信息、下單時間會在結(jié)果里出現(xiàn)兩次分別對應兩行明細。這是多表 JOIN 的正常表現(xiàn)——拼接時右表有多個匹配記錄左表行就會復制。從這個例子里能悟出一個多表 JOIN 的原則先明確結(jié)果需要的字段來自哪些表再決定連接路徑最后用 WHERE 補過濾條件。思路對了SQL 就是照抄路徑。很多初學者一上來就寫 JOIN結(jié)果是表拼得飛起但字段邏輯全亂了。從訂單視角查詢還有一個常見變體如果只想看訂單維度不展開明細可以用子查詢或者聚合把明細先匯總。比如SELECT o.order_no, u.user_name, o.order_time, SUM(d.amount) AS total_amount FROM t_order o INNER JOIN t_user u ON o.user_id u.user_id INNER JOIN t_order_detail d ON o.order_id d.order_id GROUP BY o.order_no, u.user_name, o.order_time;要說明的是不要把非聚合字段隨意塞進 GROUP BY 里。在 MySQL 的 ONLY_FULL_GROUP_BY 模式下select 列表里出現(xiàn)沒有被聚合函數(shù)包裹且不屬于 GROUP BY 的列直接報錯。GROUP BY 的原則性寫法是先確定分組粒度這里是訂單用戶時間再把粒度字段原樣 select 出來其他字段一律聚合處理。3.3 從用戶視角出發(fā)保留全部用戶的 LEFT JOIN第二個業(yè)務(wù)需求要一張報表統(tǒng)計每個用戶一共下了多少單沒下過單的用戶也要出現(xiàn)在列表里訂單數(shù)顯示 0。這就是 LEFT JOIN 的典型場景。以用戶表為主表關(guān)聯(lián)訂單表再對訂單做計數(shù)SELECT u.user_id, u.user_name, COUNT(o.order_id) AS order_count FROM t_user u LEFT JOIN t_order o ON u.user_id o.user_id GROUP BY u.user_id, u.user_name;注意 COUNT 這里必須用COUNT(o.order_id)而不能用COUNT(*)。LEFT JOIN 之后沒下過單的用戶對應訂單字段全是 NULL但行還在。COUNT(*)數(shù)的是“結(jié)果集有多少行”沒下過單的用戶那一行也會被數(shù)成 1。COUNT(o.order_id)數(shù)的是“這個字段非 NULL 的數(shù)量”沒下過單的用戶這個字段是 NULL所以正確計數(shù)為 0。這是多表查詢里一個特別經(jīng)典的坑面試題也愛出但工作中一急真的會寫錯。如果還想看到“沒下過單的用戶”只取一次而不是分成多行LEFT JOIN 后加個 GROUP BY 或者改成子查詢都可以。本質(zhì)上統(tǒng)計需求決定了 JOIN 的形態(tài)按行展示就 JOIN 展開按維度匯總就 GROUP BY 收攏。3.4 從商品視角出發(fā)找出“沒人買”的商品第三個業(yè)務(wù)需求運營想知道哪些商品從來沒有出現(xiàn)在任何訂單明細里以便調(diào)整商品狀態(tài)。思路是用商品表做左表LEFT JOIN 訂單明細表然后篩選出明細表字段為 NULL 的行SELECT p.product_id, p.product_name, d.detail_id FROM t_product p LEFT JOIN t_order_detail d ON p.product_id d.product_id WHERE d.detail_id IS NULL;這個 SQL 完美體現(xiàn)了 LEFT JOIN 加 NULL 過濾的組合威力。連接條件保證了商品表每一行都保留沒被任何訂單引用到的商品在 JOIN 結(jié)果里它的d.detail_id是 NULL。然后 WHERE 里只保留 NULL 行就得到了從來沒有被購買的商品的列表。這個模式可以引申出很多變體查沒有發(fā)過文章的作者、查沒有分配項目的員工、查沒有任何子分類的頂級分類。全流程都是“主表 LEFT JOIN 子表 子表主鍵 IS NULL”。把這個模式記住比臨時從零想更快也更不容易寫錯。4. 多表查詢的性能優(yōu)化從“能跑”到“跑得快”4.1 關(guān)聯(lián)字段索引JOIN 快慢的分水嶺多表查詢最影響性能的通常是 JOIN 關(guān)聯(lián)時做的“匹配”。MySQL 要拿左邊表的每一行去右邊表里找滿足連接條件的行。如果右表的關(guān)聯(lián)字段上沒有索引MySQL 只能全表掃一遍逐行比對左表 1000 行右表 100 萬行那就是 1000 次全表掃描想想都嚇人。一旦在右表關(guān)聯(lián)字段上建了索引MySQL 可以在索引里快速定位到符合條件的行時間從線性退化到對數(shù)級。經(jīng)驗規(guī)則是被驅(qū)動表的關(guān)聯(lián)字段一定要有索引。查出哪張表是被驅(qū)動表用 EXPLAIN 就能看到。哪些字段應該加索引經(jīng)常出現(xiàn)在 JOIN ON 子句里的字段、WHERE 過濾字段、ORDER BY 排序字段。在我的訂單例子里t_order.user_id、t_order_detail.order_id、t_order_detail.product_id就是典型的索引候選。組合索引也有講究如果查詢經(jīng)常以user_id和order_time為條件建(user_id, order_time)這樣的聯(lián)合索引比兩個單列索引更高效。4.2 驅(qū)動表選擇小表驅(qū)動大表多表 JOIN 時MySQL 會選擇一個表作為驅(qū)動表先讀取它的數(shù)據(jù)再用這些數(shù)據(jù)去匹配另一張表。驅(qū)動表的每一行都要去被驅(qū)動表里找一次匹配。因此被驅(qū)動表的掃描次數(shù)近似等于驅(qū)動表的行數(shù)。這就是“小表驅(qū)動大表”原則的來源如果驅(qū)動表只有 10 行被驅(qū)動表有 100 萬行一共只需執(zhí)行 10 次索引匹配反過來驅(qū)動表 100 萬行被驅(qū)動表 10 行就是 100 萬次匹配。雖然優(yōu)化器會自動選擇較優(yōu)的順序但優(yōu)化器不是萬能的統(tǒng)計信息缺失、條件復雜時也會選錯。這時候你可以手動干預比如改變 FROM 子句中的表順序、調(diào)整 JOIN 順序甚至用STRAIGHT_JOIN強制指定連接順序。什么樣的表算“小表”不是說行數(shù)少就一定小而是經(jīng)過 WHERE 過濾后真正參與 JOIN 的行數(shù)少。一個 100 萬行的表WHERE 過濾到只剩 100 行它在這個查詢里就是小表。所以寫 SQL 時盡量先把過濾條件下足讓每個環(huán)節(jié)進入 JOIN 的數(shù)據(jù)量都先瘦身。4.3 EXPLAIN 命令讓優(yōu)化器把執(zhí)行計劃交代清楚任何多表查詢慢的問題第一步不是猜而是看執(zhí)行計劃EXPLAIN SELECT u.user_name, o.order_no FROM t_user u LEFT JOIN t_order o ON u.user_id o.user_id WHERE u.phone 13800000000;結(jié)果里的 type 列是關(guān)鍵如果出現(xiàn)ALL說明是全表掃描出現(xiàn)range說明在索引范圍掃描ref或eq_ref說明精確使用了普通索引或唯一索引這是 JOIN 里最理想的狀態(tài)。rows估算的是掃描行數(shù)如果比預期大很多說明優(yōu)化器認為需要檢查的行太多了。還有個細節(jié)Extra列如果出現(xiàn)Using join buffer說明被驅(qū)動表沒有有效索引MySQL 不得不在內(nèi)存里用 join buffer 來做連接這種查詢在大數(shù)據(jù)量下會非常痛苦。復制出來 SQL 檢查一下給被驅(qū)動表的關(guān)聯(lián)字段補上索引這個提示通常就會消失。執(zhí)行計劃不是一成不變的數(shù)據(jù)量變化、統(tǒng)計信息的更新、參數(shù)調(diào)整都可能導致計劃變化。線上 SQL 如果突然變慢第一件事就是重看 EXPLAIN跟歷史執(zhí)行計劃對比很多性能問題都能從計劃變化里找到線索。4.4 連接條件里的“隱形殺手”函數(shù)與隱式類型轉(zhuǎn)換很多人知道 WHERE 里對字段用函數(shù)會導致索引失效但在 JOIN 的 ON 條件里犯同樣的錯卻不容易察覺。比如SELECT * FROM t_order o INNER JOIN t_user u ON DATE(o.order_time) DATE(u.create_time);這種寫法MySQL 沒法直接使用索引定位o.order_time因為每條記錄都要先被DATE()函數(shù)處理一遍才能去匹配。函數(shù)處理之后索引的順序被打亂優(yōu)化器只能放棄索引。改成o.order_time 2024-01-01 AND o.order_time 2024-01-02這樣的范圍條件索引就能正常用了。隱式類型轉(zhuǎn)換更隱蔽。假設(shè)t_user.user_id是 VARCHAR 類型存了字符串t_order.user_id是 INT你寫ON t_user.user_id t_order.user_idMySQL 會把字符串轉(zhuǎn)換成數(shù)字去比轉(zhuǎn)換過程中索引可能失效還可能產(chǎn)生奇怪的匹配結(jié)果。經(jīng)驗之談關(guān)聯(lián)字段在設(shè)計時就該統(tǒng)一類型否則一邊要改數(shù)據(jù)一邊要改 SQL兩頭受罪。另一個容易被忽視的問題ON 條件里盡量不要寫不必要的表達式運算。每行都要算一次量大了就是整體性能下降。比如你有a.price * b.quantity這種計算放在 WHERE 里過濾就比預先把金額字段存下來再關(guān)聯(lián)要慢得多?？臻g換時間在數(shù)據(jù)表設(shè)計里一點也不丟人。5. 高頻踩坑實錄多表查詢問題排查與避坑指南5.1 結(jié)果集多了很多行最常見的原因是 JOIN 右邊存在一對多的匹配關(guān)系。比如用戶表 JOIN 訂單表一個用戶有 5 筆訂單結(jié)果里用戶信息就重復 5 次。很多人沒意識到這一點直接拿結(jié)果渲染頁面發(fā)現(xiàn)列表出現(xiàn)大量“重復”記錄。排查方法很簡單檢查 JOIN 的兩個表的關(guān)聯(lián)字段是否唯一如果右表關(guān)聯(lián)字段不唯一結(jié)果集膨脹是必然的。另一種原因是 ON 條件寫寬了。比如關(guān)聯(lián)訂單明細時把商品表的連接條件寫成了d.product_id p.category_id類型對不上但能匹配上部分數(shù)據(jù)就會產(chǎn)生垃圾行。這種問題要結(jié)合業(yè)務(wù)數(shù)據(jù)反推用 DISTINCT 看看關(guān)鍵字段取值是否符合預期。5.2 結(jié)果集少了行LEFT JOIN 被 WHERE 悄悄改造成 INNER JOIN這個坑幾乎每個寫 SQL 的人遲早會遇到。舉個例子SELECT u.user_name, o.order_no FROM t_user u LEFT JOIN t_order o ON u.user_id o.user_id WHERE o.order_time 2024-01-01;表面上看用了 LEFT JOIN意圖是“所有用戶及其 2024 年后的訂單”。但實際執(zhí)行后那些沒有匹配到訂單的用戶o.order_time是 NULLWHERE 條件NULL 2024-01-01不成立這些用戶直接被過濾掉了。結(jié)果集等價于“只有 2024 年后下過單的用戶”LEFT JOIN 白寫了。正確寫法是把時間過濾條件挪到 ON 里SELECT u.user_name, o.order_no FROM t_user u LEFT JOIN t_order o ON u.user_id o.user_id AND o.order_time 2024-01-01;這樣過濾發(fā)生在匹配階段沒匹配上的用戶依然保留只是訂單字段為 NULL。判斷經(jīng)驗如果你想保留左表的全部數(shù)據(jù)右表的過濾條件一律放在 ON 里如果你確實只要滿足條件的匹配數(shù)據(jù)過濾條件放 WHERE 里也沒問題但要想清楚語義。5.3 NULL 值參與比較的“消失的數(shù)據(jù)”多表 JOIN 之后沒有匹配記錄的右表字段是 NULL。你可能會想把這些 NULL 值用 0或者 0過濾掉結(jié)果發(fā)現(xiàn)有些有值的數(shù)據(jù)也不見了。原因是 SQL 的三值邏輯NULL 不等于任何值也不等于 NULL任何與 NULL 的算術(shù)或比較運算結(jié)果都是 NULL。o.order_no N/A永遠不可能把 NULL 匹配上。想要把 NULL 找出來用IS NULL想要把 NULL 替換成默認值用IFNULL(o.order_no, N/A)。寫入的時候也要小心避免往關(guān)聯(lián)字段寫入 NULL否則 JOIN 時那一行會直接丟失匹配資格。這是我見過的一個隱蔽問題某系統(tǒng)的訂單表 user_id 因為歷史數(shù)據(jù)問題有空值LEFT JOIN 用戶表后那條訂單的 user_id 就顯示 NULL排查了半天才定位到源頭。5.4 多表 JOIN 時的子查詢與預聚合策略業(yè)務(wù)復雜時一個查詢可能要 JOIN 五六張表每一層都套一個子查詢。寫到后面 SQL 能看懂都不容易性能更加不可控。我的經(jīng)驗是能先聚合的先聚合能先過濾的先過濾讓每一層 JOIN 的輸入盡可能小。舉例要查每個訂單的總金額和商品數(shù)量如果一個訂單有 100 個明細直接 JOIN 明細表后 GROUP BY 訂單中間產(chǎn)生的臨時結(jié)果會膨脹 100 倍。換成先對明細表做聚合SELECT order_id, SUM(amount) AS total_amount, SUM(quantity) AS total_quantity FROM t_order_detail GROUP BY order_id;再和訂單表、用戶表 JOIN最終數(shù)據(jù)集小很多執(zhí)行也快不少。這個思路適用于絕大多數(shù)報表型查詢寧可多寫一層子查詢也不要讓中間結(jié)果冗余膨脹。多表 JOIN 的順序也有講究。盡量把過濾條件最嚴的表放在 JOIN 鏈的起始位置讓它作為驅(qū)動表先瘦身把數(shù)據(jù)量小、關(guān)聯(lián)字段有索引的表放在被驅(qū)動位減少匹配次數(shù)。雖然優(yōu)化器會做自動優(yōu)化但在復雜查詢里手動干預連接順序執(zhí)行效率往往能看出明顯差距。我個人在實際項目里的體會是多表查詢從語法到熟練真正分水嶺不在會寫幾種 JOIN而在于能預判這個 SQL 在真實數(shù)據(jù)量下的表現(xiàn)以及能看懂執(zhí)行計劃回推哪張表該加索引。寫 SQL 之前先畫一遍表關(guān)系圖把查詢的主干表想清楚寫出來的代碼自然穩(wěn)。手上的項目如果數(shù)據(jù)量已經(jīng)上來了強烈建議專程花時間把最常用的幾條多表查詢逐個 EXPLAIN 一遍你會發(fā)現(xiàn)優(yōu)化空間往往比想象中大很多。最后分享一個我自己的習慣凡是有 JOIN 的查詢必須看一眼執(zhí)行計劃里有沒有全表掃描沒有全表掃描才允許上生產(chǎn)環(huán)境。這個習慣幫我避掉了很多次隱藏的慢查詢事故。