MySQL 多表 JOIN 索引設計核心原理：從 EXPLAIN 看懂優化器決策

核心觀念：MySQL JOIN 執行邏輯 = 單次掃描 + 多次查找

MySQL 處理 JOIN 時，不會一次把所有表讀進記憶體再比對，而是採用 Nested Loop Join（巢狀迴圈連接）：

1. 從第一張表（驅動表）讀一行
2. 拿這行的關聯欄位值，去第二張表索引中查找匹配行
3. 再拿第二表結果去第三表查找...
4. 所有表都找到匹配後，輸出結果列
5. 回溯上一層表，找下一個匹配行，繼續

關鍵結論：被查找的表（非驅動表）必須在關聯欄位上有索引，否則每次查找都是全表掃描，效能呈指數級惡化。

EXPLAIN 解讀實戰：三表 JOIN 範例

SELECT * FROM `tblA`, `tblB`, `tblC`
WHERE `tblA`.`col1` = `tblB`.`col1`
  AND `tblA`.`col2` = `tblC`.`col1`;

初始狀態：無任何索引

+-------+------+---------------+------+---------+------+------+-------------+
| table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+-------+------+---------------+------+---------+------+------+-------------+
| tblA  | ALL  | NULL          | NULL | NULL    | NULL | 1000 |             |
| tblB  | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using where |
| tblC  | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using where |
+-------+------+---------------+------+---------+------+------+-------------+

• type=ALL：全表掃描
• rows=1000：預估掃描 1000 行
• 三表 JOIN 結果集 = 1000 × 1000 × 1000 = 10 億行（災難）

步驟 1：給 tblB.col1 加索引（因為 tblA.col1 = tblB.col1）

ALTER TABLE `tblB` ADD INDEX `idx_col1` (`col1`);

+-------+------+---------------+----------+---------+-----------+------+-------------+
| table | type | possible_keys | key      | key_len | ref       | rows | Extra       |
+-------+------+---------------+----------+---------+-----------+------+-------------+
| tblA  | ALL  | NULL          | NULL     | NULL    | NULL      | 1000 |             |
| tblB  | ref  | idx_col1      | idx_col1 | 5       | tblA.col1 | 1    | Using where |
| tblC  | ALL  | NULL          | NULL     | NULL    | NULL      | 1000 | Using where |
+-------+------+---------------+----------+---------+-----------+------+-------------+

• tblB.type=ref：使用索引查找，ref=tblA.col1 表示用 tblA 的值去查 idx_col1
• rows=1：預估每次只查 1 行，效能提升 1000 倍

步驟 2：給 tblC.col1 加索引（因為 tblA.col2 = tblC.col1）

ALTER TABLE `tblC` ADD INDEX `idx_col1` (`col1`);

+-------+------+---------------+----------+---------+-----------+------+-------------+
| table | type | possible_keys | key      | key_len | ref       | rows | Extra       |
+-------+------+---------------+----------+---------+-----------+------+-------------+
| tblA  | ALL  | NULL          | NULL     | NULL    | NULL      | 1000 |             |
| tblB  | ref  | idx_col1      | idx_col1 | 5       | tblA.col1 | 1    | Using where |
| tblC  | ref  | idx_col1      | idx_col1 | 5       | tblA.col2 | 1    | Using where |
+-------+------+---------------+----------+---------+-----------+------+-------------+

• tblC 同樣變為 ref，ref=tblA.col2
• 最終結果集 = 1000 × 1 × 1 = 1000 行，完成優化

複雜實戰：四表 JOIN + 9 條 WHERE + GROUP BY

SELECT COUNT(tblB.a_id) AS correct, tblA.type, tblA.se_type
FROM tblA, tblB, tblC, tblD
WHERE tblA.ex_id = tblC.ex_id
  AND tblC.st_ex_id = tblB.st_ex_id
  AND tblB.q_num = tblA.q_num
  AND tblB.se_num = tblA.se_num
  AND tblB.ans = tblA.ans
  AND tblD.ex_id = tblA.ex_id
  AND tblD.exp <> tblB.se_num
  AND tblB.ans = tblA.ans
  AND tblA.ex_id = 1001
  AND tblC.r_id = 542
GROUP BY tblA.type, tblA.se_type;

優化步驟推演

1. 看 EXPLAIN 初始狀態：結果集 9400 萬行，tblA/tblB 全表掃描
2. 分析 tblA-tblB 關聯條件：三個等值條件 q_num, se_num, ans
3. 在 tblB 建立複合索引：INDEX idx_se_q (se_num, q_num)（選擇最常用的兩欄）
4. 結果集降至 69 萬行：tblB 變 ref，但 tblA 仍 ALL
5. 發現 tblA 有過濾條件：tblA.ex_id = 1001
6. 給 tblA.ex_id 加索引：INDEX idx_ex_id (ex_id)
7. 最終結果集 641 行：tblA 變 ref，rows=1，完成優化

索引設計黃金法則總結

1. 只看 EXPLAIN 輸出順序中的兩兩關係：不管多複雜，優化器按列表順序兩兩處理
2. 前一表的值已知，後一表需用索引查找：被查找表的關聯欄位必須建索引
3. 優先索引「選擇性高」的欄位：唯一值越多，索引效果越好
4. 複合索引遵循最左前綴：INDEX (a, b, c) 支援 a、a,b、a,b,c 查找
5. 驅動表（第一張表）若有 WHERE 過濾，也要加索引：讓驅動表也走 ref 而非 ALL
6. 避免過度索引：寫入效能、空間、優化器選擇困惑，折中選擇最高頻查詢模式

進階：JOIN 順序控制與 Straight Join

• MySQL 優化器通常能自動選擇最佳驅動表（小結果集優先）
• 若優化器選錯，可用 STRAIGHT_JOIN 強制順序：SELECT * FROM tblA STRAIGHT_JOIN tblB ON ...
• 或用 USE INDEX/IGNORE INDEX 引導優化器

總結

MySQL JOIN 索引優化的本質是：理解 Nested Loop 機制，針對 EXPLAIN 中每一步「被查找的表」在關聯欄位建索引。掌握「兩兩分析法」，再複雜的多表 JOIN 也能拆解為簡單步驟，系統性地消除全表掃描，將百萬行結果集壓縮至百行級。