MySQL LEFT JOIN 查詢優化：驅動表選擇、索引失效與最佳實踐

核心觀念：誰是驅動表？

在 LEFT JOIN 中，左表不一定是驅動表。MySQL 優化器根據 WHERE 條件、索引可用性、預估掃描行數動態決定。錯誤假設左表為驅動表會導致索引規劃失效、出現 Using temporary; Using filesort。

實戰案例分析

場景：分類表 categories 與文章表 posts

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

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 `category_id` (`category_id`),
  KEY `created` (`created`),
  KEY `category_id_created` (`category_id`, `created`)
) ENGINE=InnoDB;

查詢 1：WHERE 條件在右表

EXPLAIN SELECT * 
FROM posts 
LEFT JOIN categories ON posts.category_id = categories.id 
WHERE categories.name LIKE 'foobar%' 
ORDER BY posts.created DESC;

EXPLAIN 結果：

分析： 優化器選 categories 為驅動表（因 WHERE 使用其索引 name），但 ORDER BY 用從表 posts.created，導致需建立臨時表排序，效能極差。

查詢 2：WHERE 條件為右表主鍵精確值

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

結果： 無 Using temporary/filesort。因驅動表 categories 僅 1 行（常數表），從表可直接走 category_id_created 索引有序掃描。

查詢 3：WHERE 條件在左表（預期驅動表）

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

此時 posts 成為驅動表，ORDER BY 走聚簇索引或 created 索引，無額外排序開銷。

優化鐵律

1. WHERE 條件盡量寫左表欄位，引導優化器選左表為驅動表。
2. ORDER BY 僅用驅動表欄位，避免 Using filesort。
3. 必要時用 STRAIGHT_JOIN 強制連接順序（謹慎使用，失去優化器彈性）。
4. 複合索引設計：(WHERE欄位, JOIN欄位, ORDER BY欄位) 覆蓋查詢。

總結

LEFT JOIN 效能關鍵在於「驅動表確定」與「索引覆蓋」。寫 SQL 前先 EXPLAIN，觀察 table 順序與 Extra 欄位，依據實際執行計畫調整條件分布與索引，而非憑經驗猜測左表必為驅動表。