MySQL 多表聯合查詢效能分析與優化：JOIN 類型、執行原理、優化策略全解析

一、JOIN 類型完整分類

1. 交叉連接 / 笛卡爾積

SELECT * FROM t1 CROSS JOIN t2;
SELECT * FROM t1 JOIN t2;        -- 同 CROSS JOIN
SELECT * FROM t1, t2;            -- 逗號語法等同

無條件時產生 M×N 行，極度危險，僅用於測試或極小集合。

2. 內連接

SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;
-- INNER 可省略
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id;

僅返回雙方均滿足 ON 條件的行。

3. 外連接

實例：找出無對應記錄的用戶

-- user 為左表，user_action 為右表
SELECT u.id, u.name, a.action
FROM user u
LEFT JOIN user_action a ON u.id = a.user_id
WHERE a.user_id IS NULL;  -- 僅保留左表獨有行

結果：daodao（id=3）無對應 action，action 顯示 NULL。

二、連接條件語法

• ON：通用條件，支援任意運算式
• USING(col)：雙表連接欄位同名時的簡寫
• WHERE：內連接等同於 ON；外連接中 WHERE 會過濾掉 NULL 行，轉為內連接語義

三、MySQL JOIN 優化器執行邏輯

MySQL 採用 Nested Loop Join（嵌套循環連接）：

1. 選擇一張表為 驅動表（外層循環）
2. 逐行讀取驅動表，代入條件到 被驅動表（內層循環）查找匹配行
3. 輸出滿足條件的行組合

關鍵優化原則：小結果集驅動大結果集

• 驅動表應為過濾後行數最少的表
• 被驅動表連接欄位必須有索引
• 若被驅動表無索引，MySQL 會分配 join_buffer 做 Block Nested Loop，效能仍遠遜於索引查找

四、LEFT/RIGHT JOIN 優化規則

1. 根據左表及其依賴表設定右表
2. 根據 LEFT JOIN 條件中使用的所有表（除右表外）設定左表
3. LEFT JOIN 條件僅用於決定如何從右表搜索行（WHERE 條件不參與）
4. 標準連接優化均適用，除循環依賴會報錯
5. WHERE 條件後續優化
6. 若左表行匹配 WHERE 但右表無匹配 ON，生成全 NULL 行
7. 關鍵優化： WHERE 中檢查右表 NOT NULL 欄位 IS NULL 時，匹配到第一行即停止搜尋

優化實例：強制表順序

-- 原查詢：b 在前導致全掃描
SELECT * FROM a, b LEFT JOIN c ON c.key=a.key LEFT JOIN d ON d.key=a.key WHERE b.key=d.key;

-- 優化：將 b 移至 FROM 首位
SELECT * FROM b, a LEFT JOIN c ON c.key=a.key LEFT JOIN d ON d.key=a.key WHERE b.key=d.key;

五、JOIN 優化實戰檢查清單

六、進階提示

• STRAIGHT_JOIN：強制按 FROM 順序連接，阻止優化器重排
• USE INDEX(idx) / IGNORE INDEX(idx)：提示/禁止特定索引
• 多表連接超過 61 張表會報錯（MySQL 限制）
• 衍生表（子查詢）物化後無索引，大表 JOIN 衍生表極慢，改用 CTE (MySQL 8.0+) 或臨時表

核心心法： JOIN 優化的本質是「讓優化器以最小代價找到匹配行」。永遠從 EXPLAIN 看起，確認驅動表、被驅動表、索引使用、臨時表產生四大指標，再針對性調整 SQL 或結構。