MySQL LEFT JOIN 進階優化：主表判定、索引策略與執行計劃深度解析

一、LEFT JOIN 主表不一定是左表

經驗法則：「LEFT JOIN 左表為主表」**並不總是成立**。MySQL 優化器會根據 WHERE 條件、索引情況、統計信息動態選擇驅動表（主表）。

二、實戰案例：被 WHERE 條件反向驅動

表結構

CREATE TABLE categories (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(15) NOT NULL,
  created DATETIME NOT NULL
);

CREATE TABLE posts (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  category_id INT UNSIGNED NOT NULL,
  title VARCHAR(100) NOT NULL,
  content VARCHAR(200) NOT NULL,
  created DATETIME NOT NULL,
  KEY idx_category_id (category_id)
);

查詢 SQL

EXPLAIN SELECT * 
FROM posts 
LEFT JOIN categories ON posts.category_id = categories.id 
WHERE categories.id = 1 
ORDER BY posts.created DESC;

EXPLAIN 結果分析

ALTER TABLE posts ADD INDEX idx_cat_created (category_id, created);

重新 EXPLAIN，Using filesort 消失。原因：主表 categories 通過 category_id 關聯 posts 時，聯合索引已按 created 排序，直接順序讀取。

3.2 將 ORDER BY 改為主表字段（若業務允許）

ORDER BY categories.created DESC  -- 避免 filesort

四、主觀控制驅動表的原則

1. WHERE 條件盡量使用預期主表的字段，且該字段有高選擇性索引
2. 確定主表後，ORDER BY 僅使用主表字段
3. 必要時用 STRAIGHT_JOIN 強制連接順序（慎用，阻礙優化器）
4. 用 USE INDEX(idx_name) / IGNORE INDEX(idx_name) 引導索引選擇

五、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

SQL：

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

結果：

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，且 a.user_id 定義為 NOT NULL

結果：3 | daodao

七、語法糖與提示

八、總結

1. LEFT JOIN 驅動表由優化器根據 WHERE/索引/統計信息動態決定，非固定左表
2. WHERE 條件決定驅動表選擇，**有索引的等值條件最有利**
3. ORDER BY 被驅動表字段導致 filesort，**聯合索引 (驅動鍵, 排序鍵) 可消除**
4. 理解「左表行 × 右表匹配行 = 結果行」的笛卡爾積生成邏輯
5. 善用 EXPLAIN 觀察驅動表順序與 Extra 信息，是調優入門必修課