MySQL LEFT JOIN 進階：主表判定、索引優化與查詢效能分析

在 MySQL 中，LEFT JOIN 的「主表」並非固定為左表，而是由優化器根據查詢條件與索引情況動態決定。誤判主表會導致索引失效、產生 Using filesort，嚴重拖慢查詢。本文透過實例深入剖析主表判定邏輯、索引優化策略，並結合資料分佈特性解析 LEFT JOIN 查詢效能。

一、什麼是 LEFT JOIN 的「主表」？

主表是指 MySQL 在連接查詢中作為驅動表（先讀取）的那張表。雖然經驗上左表常為主表，但優化器會根據 WHERE 條件與索引選擇最優驅動表。

實例驗證

建立測試表：

CREATE TABLE categories (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(15), created DATETIME);
CREATE TABLE posts (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, category_id INT UNSIGNED, title VARCHAR(100), content VARCHAR(200), created DATETIME, KEY category_id (category_id));

執行查詢：

EXPLAIN SELECT * FROM posts LEFT JOIN categories ON posts.category_id = categories.id WHERE categories.id = '既有ID' ORDER BY posts.created DESC;

EXPLAIN 結果第一行顯示 categories 為主表（使用 PRIMARY 索引），第二行為 posts（使用 category_id 索引）。原因：WHERE 條件針對 categories.id（主鍵索引），優化器判斷以 categories 為主表可快速縮小結果集。

二、Using filesort 產生原因與解決

主表為 categories，但 ORDER BY 使用從表 posts.created，導致 Using filesort。解決方案：

1. 優先使用主表欄位排序；
2. 若業務必須用從表欄位排序，建立聯合索引：ALTER TABLE posts ADD INDEX (category_id, created);

加上索引後，主表 categories 透過 category_id 連接從表時，可直接利用索引順序取得排序結果，消除 filesort。

三、寫 SQL 時的主表控制原則

• 若希望左表為主表，WHERE 條件盡量使用左表欄位；
• 確定主表後，ORDER BY 最好只用主表欄位；
• 必要時用 STRAIGHT_JOIN 強制連接順序，但需配合 EXPLAIN 驗證。

四、LEFT JOIN 查詢效能分析：資料分佈視角

測試資料

user 表：           user_action 表：
id | name           user_id | action
1  | libk           1       | jump
2  | zyfon          1       | kick
3  | daodao         1       | jump
                   2       | run
                   4       | swim

查詢與結果

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

id | name   | action
1  | libk   | jump      ← ①
1  | libk   | kick      ← ②
1  | libk   | jump      ← ③
2  | zyfon  | run       ← ④
3  | daodao | NULL      ← ⑤

核心結論

1. user_action 中 user_id=4 的行不出現（右表無對應左表行被捨棄）。
2. user 中 id=3 無對應右表行，但仍出現，右表欄位為 NULL。
3. 左表每行讀取後，匹配所有符合 ON 條件的右表行（含重複），形成 N 行結果；無匹配則補 NULL。

五、反向應用：找出左表有、右表無的資料

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

注意： 判斷欄位必須定義為 NOT NULL，否則無法區分「無匹配」與「欄位值為 NULL」；使用 IS NULL 而非 = NULL。

六、實用 Tips

1. ON a.c1 = b.c1 等同 USING(c1)。
2. INNER JOIN 與逗號 ， 語義相同。
3. 使用 USE INDEX / IGNORE INDEX 提示優化器選擇/忽略特定索引。
4. 多表連接範例：

   SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id LEFT JOIN table3 ON table2.id = table3.id;
   SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3;

七、總結

掌握 LEFT JOIN 主表判定邏輯、善用聯合索引消除 filesort、理解資料分佈對結果集的影響，是寫出高效連接查詢的關鍵。實戰中請配合 EXPLAIN 持續驗證與調優。