MySQL 表連接查詢優化實戰：LEFT JOIN 執行機制與優化技巧

MySQL 中的 LEFT JOIN 是多表查詢的核心操作，但其執行機制若不理解透徹，極易產生效能瓶頸。本文深入解析 LEFT JOIN 內部執行流程、優化器行為，並透過實戰案例示範如何調整表順序、善用索引與條件下推，大幅提升查詢效能。

一、LEFT JOIN 執行流程解析

對於 A LEFT JOIN B ON join_condition，MySQL 執行步驟如下：

1. 根據表 A 及其依賴表，設定表 B 的讀取順序。
2. 根據 LEFT JOIN 條件中使用的所有表（除 B 外），設定表 A 的讀取順序。
3. LEFT JOIN 的 ON 條件僅用於決定如何從表 B 搜尋行，不使用 WHERE 子句條件。
4. 套用所有標準連接優化，循環依賴時拋錯。
5. 套用標準 WHERE 優化。
6. 若 A 中有行符合 WHERE，但 B 無行符合 ON 條件，生成一行所有欄位為 NULL 的 B 行。
7. 若用 LEFT JOIN 尋找不存在的行（WHERE col_name IS NULL 且 col_name 為 NOT NULL），找到首個匹配行後即停止搜尋。

二、連接順序對效能的關鍵影響

優化器會計算表的最佳連接順序。LEFT JOIN 與 STRAIGHT_JOIN 可強制表讀取順序，減少優化器檢查的排列組合，加快優化過程。

反面教材：錯誤順序導致全表掃描

SELECT * FROM a, b LEFT JOIN c ON (c.key = a.key) LEFT JOIN d ON (d.key = a.key) WHERE b.key = d.key;

此處 LEFT JOIN 強制 MySQL 先讀取 b，導致全表掃描。

修正：調整 FROM 子句順序

SELECT * FROM b, a LEFT JOIN c ON (c.key = a.key) LEFT JOIN d ON (d.key = a.key) WHERE b.key = d.key;

將 b 移至最前，優化器可優先使用 b 的索引，避免全掃描。

三、LEFT JOIN 轉換為內連接的優化規則

若 WHERE 條件對產生的 NULL 行恆為假，MySQL 會自動將 LEFT JOIN 轉為內連接，進而獲得更靈活的連接順序。

範例

SELECT * FROM t1 LEFT JOIN t2 ON (t1.column1 = t2.column1) WHERE t2.column2 = 5;

因 t2.column2 = 5 在 t2.column1 為 NULL 時為假，可安全轉換為：

SELECT * FROM t1, t2 WHERE t2.column2 = 5 AND t1.column1 = t2.column1;

此時 MySQL 可優先讀取 t2（若其索引更佳），若需強制順序可用 STRAIGHT_JOIN。

四、實戰優化案例：多表關聯與索引設計

核心原則：先過濾，後連接；關聯欄位必須建立索引。

優化前 SQL（巢狀子查詢，難以利用索引）

SELECT M.*, SS.SensorCode, ..., bat.BerthTypeId
FROM (
    SELECT T.*, BS.ParkStatus, BS.ChangeTime, CA.CantonName, SE.SectionName
    FROM (
        SELECT A.*, B.BerthId, B.BerthCode, B.BerthAddress, B.BerthStatus, B.LineDirection, B.CantonId, B.SectionId
        FROM (
            SELECT AR.AreaId, AR.AreaCode, AR.AreaName
            FROM SYS_Area AS AR WHERE 1=1 AND AR.AreaCode = '110'
        ) A LEFT JOIN SYS_Berth AS B ON B.AreaId = A.AreaId
    ) T
    JOIN SYS_BerthStatus AS BS ON T.BerthCode = BS.BerthCode
    JOIN SYS_Canton AS CA ON T.CantonId = CA.CantonId
    JOIN SYS_Section AS SE ON T.SectionId = SE.SectionId
) M
LEFT JOIN SYS_Sensor SS ON M.BerthCode = SS.BerthCode
LEFT JOIN TRA_PricingBerth AS tp ON tp.BerthCode = M.BerthCode
LEFT JOIN TRA_PricingStrategy AS tps ON tps.PricingStrategyId = tp.PricingStrategyId
LEFT JOIN SYS_BerthAndType AS bat ON bat.BerthCode = M.BerthCode
ORDER BY BerthCode ASC;

優化建議

1. 扁平化查詢： 移除不必要的巢狀子查詢，直接多表 JOIN，讓優化器自由選擇最佳連接順序。
2. 關聯欄位建索引： 所有 ON 條件欄位（BerthCode、AreaId、PricingStrategyId 等）均需建立索引。
3. 過濾條件前置： 將 AR.AreaCode = '110' 等高選擇性條件盡早套用，縮小驅動表資料集。
4. 避免 SELECT *： 明確列出所需欄位，減少網路傳輸與記憶體佔用。
5. 考慮覆蓋索引： 若查詢欄位較固定，建立包含所有查詢欄位的聯合索引，實現「索引覆蓋」，避免回表。

五、總結

• 理解 LEFT JOIN 執行邏輯，特別是 ON 與 WHERE 的語義差異。
• 善用 EXPLAIN 分析執行計畫，關注 type、key、rows、Extra 欄位。
• 調整表順序、建立適當索引、將過濾條件下推，是提升連接查詢效能的三板斧。
• 必要時使用 STRAIGHT_JOIN 強制順序，但需謹慎測試。