MySQL 多表 JOIN 查詢核心優化實戰：從執行計劃到索引策略

業務背景與數據模型

某中學考試成績統計系統，四張核心表：

• class (班級表)：7 行 (101~303)
• student (學生表)：~300 行
• course (課程表)：~20 行
• score (成績表)：~1000 行

目標：演示從「能跑通」到「高性能」的 JOIN 優化全過程。

JOIN 類型速查

MySQL JOIN 執行原理：Nested Loop Join (NLJ)

MySQL 僅支持 **Nested Loop Join** (嵌套循環連接)：**驅動表** (外層循環) 逐行讀取，攜帶關聯條件去 **被驅動表** (內層) 查找匹配行，合併結果。若有第三張表，以前兩表結果集為新驅動表繼續循環。

優化核心：縮小驅動表結果集 + 被驅動表關聯列加索引 = 將內層查找從全表掃描 (ALL) 降為索引查找 (ref/eq_ref)。

實戰案例 1：查詢各班班長姓名

SQL 寫法對比

+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | cl    | ALL  | NULL          | NULL | NULL    | NULL |    7 |                                |
|  1 | SIMPLE      | st    | ALL  | NULL          | NULL | NULL    | NULL |  301 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+

• 驅動表 class (7 行) 全表掃描可接受。
• 被驅動表 student (301 行) 也全表掃描，Using join buffer 表明使用了 Join Buffer (Block Nested Loop)，但仍需 301 次比較。

加索引後

+----+-------------+-------+------+-----------------+-----------------+---------+---------------------+------+-------+
| id | select_type | table | type | possible_keys   | key             | key_len | ref                 | rows | Extra |
+----+-------------+-------+------+-----------------+-----------------+---------+---------------------+------+-------+
|  1 | SIMPLE      | cl    | ALL  | NULL            | NULL            | NULL    | NULL                |    7 |       |
|  1 | SIMPLE      | st    | ref  | idx_school_id   | idx_school_id   | 4       | school.cl.master_id |    1 |       |
+----+-------------+-------+------+-----------------+-----------------+---------+---------------------+------+-------+

• type 從 ALL → ref，rows 從 301 → 1，性能提升 **300 倍**。
• Extra 消失 Using join buffer，無需臨時緩衝區。

實戰案例 2：三表 JOIN 查詢學生成績單

+----+-------------+-------+--------+---------------------------------------+--------------------+---------+---------------------+------+-------+
| id | select_type | table | type   | possible_keys                         | key                | key_len | ref                 | rows | Extra |
+----+-------------+-------+--------+---------------------------------------+--------------------+---------+---------------------+------+-------+
|  1 | SIMPLE      | st    | ref    | idx_school_id                         | idx_school_id      | 4       | const               |    1 |       |
|  1 | SIMPLE      | sc    | ref    | idx_school_id_sc,idx_course_id_sc     | idx_school_id_sc   | 4       | const               |    3 |       |
|  1 | SIMPLE      | co    | eq_ref | PRIMARY                               | PRIMARY            | 4       | school.sc.course_id |    1 |       |
+----+-------------+-------+--------+---------------------------------------+--------------------+---------+---------------------+------+-------+

• 驅動表 student 通過主鍵/唯一索引定位 1 行 (ref + const)。
• 被驅動表 score 通過 idx_school_id_sc 定位 3 行 (ref)。
• 被驅動表 course 通過主鍵 eq_ref 定位 1 行 (最優)。
• 全鏈路無全表掃描，rows 列均為極小值。

優化總結：三板斧

1. 小表驅動大表：WHERE 條件過濾後行數最少的表作為驅動表 (優化器通常能自動選擇，必要時用 STRAIGHT_JOIN 強制)。
2. 被驅動表關聯列必須建索引：ON a.id = b.id → b.id 加索引。複合索引遵循最左前綴原則。
3. 只取需要的列：避免 SELECT *，減少網絡傳輸、內存佔用、臨時表大小；覆蓋索引 (Using index) 可避免回表。

進階優化技巧

• Derived Table / CTE 物化：複雜子查詢先物化為臨時表再 JOIN，避免重複計算 (MySQL 8.0+ CTE 優化器更智能)。
• Join Buffer 調優：join_buffer_size (默認 256K) 過小導致磁盤臨時表，適當調大 (如 2M) 但勿過大 (佔用連接內存 = size × max_connections)。
• Hash Join (MySQL 8.0.18+)：優化器在等值連接、無索引可用時自動選擇 Hash Join，可通過 optimizer_switch='hash_join=on' 控制。
• EXPLAIN ANALYZE (8.0.18+)：查看實際執行時間、循環次數、返回行數，比靜態 EXPLAIN 更直觀。

總結

MySQL JOIN 優化本質是 **「索引消除內層全表掃描」**。掌握 EXPLAIN 讀懂 type、rows、Extra 三大核心列，配合「小表驅動大表、關聯列建索引、列裁剪」三板斧，即可解決 90% 的多表查詢性能問題。複雜場景再引入 CTE、Hash Join、物化視圖等進階手段。