MySQL JOIN 多表連接全解析：語法分類、視覺化關係、性能優化十大法則與陷阱避坑

二、樣本數據與基礎示例

-- 表 A
id | name
1  | Pirate
2  | Monkey
3  | Ninja
4  | Spaghetti

-- 表 B
id | name
1  | Rutabaga
2  | Pirate
3  | Darth Vader
4  | Ninja

INNER JOIN

SELECT * FROM A INNER JOIN B ON A.name = B.name;
-- 結果：Pirate/Pirate, Ninja/Ninja

LEFT JOIN

SELECT * FROM A LEFT JOIN B ON A.name = B.name;
-- A 全量，Monkey/Spaghetti 右側 NULL

RIGHT JOIN

SELECT * FROM A RIGHT JOIN B ON A.name = B.name;
-- B 全量，Rutabaga/Darth Vader 左側 NULL

CROSS JOIN

SELECT * FROM A CROSS JOIN B;  -- 16 行笛卡爾積

模擬 FULL JOIN

SELECT * FROM A LEFT JOIN B ON A.name = B.name
UNION
SELECT * FROM A RIGHT JOIN B ON A.name = B.name;

三、性能優化十大法則

1. 顯式 INNER JOIN vs 隱式逗號連接

SELECT * FROM a INNER JOIN b ON a.id=b.id 與 SELECT * FROM a,b WHERE a.id=b.id 執行計劃一致，但顯式語法可讀性強、易維護、避免笛卡爾積風險，強制規範使用顯式。

2. 優先用 INNER JOIN，避免 LEFT JOIN + NULL 判斷

• 業務允許時用內連接，優化器可自由調整連接順序、選擇最優驅動表。
• LEFT JOIN 固定左表為驅動表，限制優化器選擇空間。

3. ON 條件盡可能完整，減少 WHERE 過濾

原理：ON 在匹配階段過濾，WHERE 在匹配後過濾。外連接中，ON 決定保留哪些外表行，WHERE 可能將外連接隱式轉為內連接。

-- 差：WHERE 過濾
SELECT * FROM A LEFT JOIN B ON A.name=B.name LEFT JOIN C ON C.name=B.name WHERE C.status>1 AND D.status=1;

-- 優：ON 下推
SELECT * FROM A INNER JOIN B ON B.name=A.name 
  LEFT JOIN C ON C.name=B.name AND C.status>1 
  LEFT JOIN D ON D.id=C.id AND D.status=1;

4. 理解 ON vs WHERE 語義差異（外連接關鍵）

-- ON 過濾：保留左表所有行，右表不匹配置 NULL
SELECT * FROM product LEFT JOIN details ON product.id=details.id AND details.id=2;

-- WHERE 過濾：先連接再過濾，相當於內連接
SELECT * FROM product LEFT JOIN details ON product.id=details.id WHERE details.id=2;

5. 子查詢改 JOIN（NOT EXISTS → LEFT JOIN ... IS NULL）

-- 子查詢
INSERT INTO t1(a) SELECT b FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE t1.id=t2.r_id);

-- JOIN 改寫
INSERT INTO t1(a) SELECT b FROM t2 LEFT JOIN (SELECT DISTINCT id FROM t1) t1 ON t1.id=t2.r_id WHERE t1.id IS NULL;

6. 驅動表選擇：小表驅動大表

• MySQL Nested Loop Join：外層驅動表逐行探測內層被驅動表。
• 被驅動表連接列必須有索引，否則每次探測全表掃描。
• 優化器通常自動選擇小結果集為驅動表，可用 STRAIGHT_JOIN 強制順序。

7. 索引列隔離：避免函數/運算破壞索引

-- 悲劇：索引失效
WHERE account_id + 1 = 5;
WHERE TO_DAYS(NOW()) - TO_DAYS(create_time) <= 10;

-- 正確：隔離列
WHERE account_id = 4;
WHERE create_time >= DATE_SUB('2023-01-01', INTERVAL 10 DAY);

8. 聯合索引列序：等值在前，範圍在後

-- SQL: WHERE payee=72478814 AND amount>1.00
-- 正確索引：(payee, amount)  -- 等值列前，範圍列後
-- 錯誤索引：(amount, payee)  -- 範圍列導致後續列不可用

9. 覆蓋索引消除回表

SELECT a,b FROM t WHERE c=1 → 索引 (c,a,b)，Extra 出現 Using index，避免回表隨機 I/O。

10. 定期 ANALYZE/OPTIMIZE 維護統計與碎片

• ANALYZE TABLE 更新統計信息（InnoDB 採樣、非阻塞）。
• OPTIMIZE TABLE 整理碎片（鎖表，大表用 pt-online-schema-change）。

四、常見陷阱總結

1. LEFT JOIN` 後 `WHERE right.col = val` 將外連接轉為內連接。
2. ON` 條件缺少驅動列索引，導致被驅動表全掃描。
3. 隱式類型轉換（字符串比數字）導致索引失效。
4. 大表 `CROSS JOIN` 產生億級笛卡爾積癱瘓服務器。
5. 未用括號明確優先級混用 `INNER/LEFT JOIN` 導致語義錯誤。