萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL LEFT JOIN 多表關聯查詢語法詳解與效能優化實戰

LEFT JOIN 是多表關聯查詢的核心工具,保留左表所有行並關聯右表匹配數據。本文系統講解語法結構、執行邏輯、與 INNER JOIN 的區別、多表鏈式 JOIN 寫法、效能優化關鍵點與常見陷阱。

基本語法結構


SELECT 選取列表
FROM 左表 A
LEFT JOIN 右表 B ON A.關聯欄位 = B.關聯欄位
[LEFT JOIN 右表 C ON ...]
[WHERE 過濾條件]
[ORDER BY 排序欄位]
[LIMIT 限制行數];

執行邏輯圖解

  1. 從**左表(驅動表)**逐行讀取。
  2. 對每行,依 ON 條件在**右表(被驅動表)**查找匹配行。
  3. 找到 → 合併輸出;**找不到 → 右表欄位填 NULL** 輸出。
  4. WHERE 過濾在 JOIN 完成**後**執行(若含右表欄位判斷,會將 LEFT JOIN 退化為 INNER JOIN)。

實戰範例:四表鏈式 LEFT JOIN


SELECT
    d.id,
    d.company_name,
    a.order_id,
    a.amount,
    b.ship_date,
    c.tracking_no
FROM
    `company` d
LEFT JOIN `order` a ON a.company_id = d.id
LEFT JOIN `shipment` b ON b.order_id = a.order_id
LEFT JOIN `tracking` c ON c.shipment_id = b.id
WHERE d.status = 1
ORDER BY d.id DESC
LIMIT 20;

關鍵點

  • **驅動表(company)在最左**,後續表依次 RIGHT JOIN 等價,但 LEFT JOIN 閱讀習慣更自然。
  • ON 條件**只寫關聯關係**,業務過濾條件放 WHERE。
  • 右表欄位在 WHERE 中判斷 IS NOT NULL 會過濾掉無匹配行,**變相變為 INNER JOIN**。

LEFT JOIN vs INNER JOIN 效能與語義對比

維度LEFT JOININNER JOIN
結果集左表全量 + 右表匹配/NULL雙方都匹配的行
驅動表選擇優化器通常選小表驅動大表,但 LEFT JOIN **強制左表為驅動表**優化器自由選擇最優驅動表
索引需求**右表關聯欄位必須有索引**雙方關聯欄位最好都有索引
適用場景主表必須保留(報表、明細關聯)純關聯查詢、雙方必須存在
效能左表大時較慢(全掃左表)通常更快(可選小表驅動)

效能優化實戰清單

  1. 被驅動表關聯欄位建索引:ALTER TABLE shipment ADD INDEX idx_order_id (order_id);
  2. 驅動表結果集縮小:WHERE 條件盡早過濾左表(如 d.status=1)。
  3. 避免 SELECT *:只取需要欄位,減少回表、網路、臨時表開銷。
  4. 大表分頁:LIMIT 100000, 20 → 延遲關聯:
    SELECT ... FROM (SELECT id FROM company WHERE status=1 ORDER BY id DESC LIMIT 100000, 20) t JOIN ...
  5. 關聯欄位類型一致:INT 關聯 VARCHAR 導致隱式轉型、索引失效。
  6. 使用 STRAIGHT_JOIN 強制順序(慎用):優化器選錯驅動表時。

常見陷阱

陷阱現象修正
WHERE 判斷右表欄位WHERE b.status = 1 過濾掉無匹配行移至 ON:LEFT JOIN b ON a.id=b.id AND b.status=1
ON 條件寫錯關聯欄位拼寫錯誤/類型不符檢查 SHOW CREATE TABLE、EXPLAIN 驗證
多表 JOIN 產生笛卡爾積缺少關聯條件或條件不夠每個 JOIN 必須有 ON,檢查行數爆炸
隱式轉型導致索引失效VARCHAR col = 123統一類型、加引號、或建函數索引(8.0+)

其他 JOIN 類型速查

  • RIGHT JOIN:右表全量,左表匹配/NULL。可改寫為 LEFT JOIN 互換表順序,**統一用 LEFT JOIN 降低認知負荷**。
  • FULL JOIN:MySQL 不支援。模擬:SELECT * FROM A LEFT JOIN B ON ... UNION SELECT * FROM A RIGHT JOIN B ON ...。
  • CROSS JOIN:笛卡爾積,無 ON 條件。慎用,僅用於生成測試數據/矩陣運算。
copyright © 萬盛學電腦網 all rights reserved