MySQL Join 算法原理深度解析：Nested Loop Join 唯一實現與執行計劃讀解

核心結論

MySQL **僅支援 Nested Loop Join (NLJ)**，無 Hash Join、Sort Merge Join。
原理：以外層驅動表結果集為循環基礎，逐行以關聯條件查詢內層表，合併結果；多表遞迴同理。

測試環境與表結構

MySQL 5.1.26
-- user_group：用戶-群組關係
CREATE TABLE user_group (
    user_id INT NOT NULL, group_id INT NOT NULL, user_type INT NOT NULL,
    gmt_create DATETIME, gmt_modified DATETIME, status VARCHAR(16),
    KEY idx_user_group_uid (user_id)
) ENGINE=MyISAM;

-- group_message：群組訊息
CREATE TABLE group_message (
    id INT PRIMARY KEY AUTO_INCREMENT,
    gmt_create DATETIME, gmt_modified DATETIME,
    group_id INT, user_id INT, author VARCHAR(32), subject VARCHAR(128),
    KEY idx_group_message_author_subject (author, subject(16)),
    KEY idx_group_message_author (author),
    KEY idx_group_message_gid_uid (group_id, user_id)
) ENGINE=MyISAM;

-- group_message_content：訊息內容
CREATE TABLE group_message_content (
    group_msg_id INT NOT NULL, content TEXT,
    KEY group_message_content_msg_id (group_msg_id)
) ENGINE=MyISAM;

查詢與執行計劃

SELECT m.subject msg_subject, c.content msg_content
FROM user_group g, group_message m, group_message_content c
WHERE g.user_id = 1
  AND m.group_id = g.group_id
  AND c.group_msg_id = m.id;

EXPLAIN 輸出解讀（關鍵欄位）

執行流程圖解

1. 驅動表 g：ref 走 idx_user_group_uid，user_id=1 過濾，預估 1 行
2. 第 2 表 m：對每行 g，用 g.group_id 走 idx_group_message_gid_uid 查詢，預估 10 行
3. 第 3 表 c：對每行 m，用 m.id 走 group_message_content_msg_id 查詢，預估 1 行
4. 總預估行數：1 × 10 × 1 = 10 行

優化啟示

• 驅動表應盡量小：WHERE 條件過濾後行數最少的表優先（此例 g 表）
• 關聯欄位必須有索引：內層表關聯欄位需建立索引（m.group_id、c.group_msg_id）
• 直連表順序：MySQL 優化器通常能自動選擇最佳驅動表，必要時用 STRAIGHT_JOIN 強制
• 避免全表掃描：type=ALL 表示無索引可用，需優先優化

版本演進補充

MySQL 8.0.18+ 引入 **Hash Join**（關閉 NLJ 時使用），但預設仍以 NLJ 為主。理解 NLJ 原理仍是調優基礎。