MySQL LEFT JOIN 執行機制與效能優化深度解析

LEFT JOIN 語義與結果集推導

以 user（左表）與 user_action（右表）為例：

SELECT u.id, u.name, a.action
FROM user u
LEFT JOIN user_action a ON u.id = a.user_id;

執行邏輯：逐行讀取左表，在右表尋找所有滿足 ON 條件的行進行笛卡兒積組合；若右表無匹配，輸出 NULL 行。因此：

• 左表獨有行（如 id=3）保留，右表欄位填 NULL。
• 右表獨有行（如 user_id=4）直接捨棄。
• 一對多關係產生重複左表行（id=1 出現三次）。

反向應用：找出「左有右無」差集

SELECT u.id, u.name
FROM user u
LEFT JOIN user_action a ON u.id = a.user_id
WHERE a.user_id IS NULL;  -- 必須用 IS NULL，且判斷欄位定義為 NOT NULL

此技巧常用於資料品質稽核、增量同步比對。

MySQL 內部優化規則（官方文件 7.2.9 節）

1. 先根據左表及其依賴表設定右表。
2. LEFT JOIN 條件僅用於決定如何從右表搜尋，WHERE 條件不參與右表存取路徑選擇。
3. 所有標準 JOIN 優化（Index Nested-Loop、Block Nested-Loop 等）均適用。
4. 若 WHERE 條件導致 NULL 產生的行必定為 FALSE（如 WHERE t2.not_null_col = 5），MySQL 會自動將 LEFT JOIN 轉為 INNER JOIN，進而獲得更佳連線順序。

實戰優化建議

• ON 條件欄位雙方務必建立索引，且資料類型、字符集、長度完全一致。
• 驅動表（左表）結果集越小越好，必要時用子查詢先過濾：LEFT JOIN (SELECT ... WHERE ...) a。
• 避免在 ON 條件使用函數或隱式轉型，導致索引失效。
• 大表關聯考慮 STRAIGHT_JOIN 強制連線順序，或拆分為應用層多次查詢組裝。
• 使用 EXPLAIN 觀察 type、rows、Extra（Using where; Using join buffer...）判斷執行計畫優劣。