MySQL 多表聯合查詢效率分析與優化：連接類型、優化器原理與最佳實踐

連接類型分類

外連接工作原理（以 LEFT JOIN 為例）

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

結果集：

右表 user_id=4 無左表匹配被丟棄；左表 id=3 無右表匹配保留並 NULL 填充。

反向應用：找出左表有、右表無的記錄

SELECT u.id, u.name
FROM user AS u
LEFT JOIN user_action a ON u.id = a.user_id
WHERE a.user_id IS NULL;  -- 必須用 IS NULL，且判斷列定義為 NOT NULL

MySQL LEFT/RIGHT JOIN 優化器執行流程

1. 根據左表及其依賴表設置右表；
2. 根據 LEFT JOIN 條件中使用的所有表（除右表）設置左表；
3. LEFT JOIN 條件決定如何從右表搜索行（WHERE 條件不參與）；
4. 進行標準連接優化（循環依賴報錯）；
5. 應用標準 WHERE 優化；
6. 若左表行匹配 WHERE 但右表無 ON 匹配，生成全 NULL 行；
7. 若 WHERE 條件為 col IS NULL（col 為 NOT NULL），匹配到第一行即停止搜索。

優化器限制與 STRAIGHT_JOIN 強制順序

優化器自動決定連接順序。LEFT JOIN 與 STRAIGHT_JOIN 會強制表讀取順序，減少優化器排列組合開銷。

-- 低效：強制先讀 a，再左連 b，導致 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;

-- 優化：調整 FROM 順序，b 先讀
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;

WHERE 條件消除外連接優化

若 WHERE 條件對 NULL 行永假，外連接退化為內連接，優化器可自由調序：

-- 原查詢
SELECT * FROM t1 LEFT JOIN t2 ON (t1.column1 = t2.column1) WHERE t2.column2 = 5;
-- t2.column2=5 對 NULL 永假，等價於內連接
SELECT * FROM t1, t2 WHERE t2.column2 = 5 AND t1.column1 = t2.column1;

連接條件寫法最佳實踐

多表連接優化清單

1. 小表驅動大表：驅動表（外層循環）行數少，被驅動表（內層）有索引；
2. 索引覆蓋：被驅動表連接列建立索引，最好覆蓋查詢列避免回表；
3. Join Buffer：無索引時啟用 Block Nested Loop Join，調大 join_buffer_size；
4. EXPLAIN 關注點：type=ref/eq_ref、rows 預估、Extra=Using where; Using join buffer；
5. 避免 SELECT *：減少網絡傳輸與臨時表開銷；
6. 條件下推：MySQL 5.6+ 支持 ICP，確保 WHERE 條件盡早過濾。