MySQL 多表關聯查詢核心優化：從 EXPLAIN 看索引驅動策略

業務背景與數據模型

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

• class：班級表 (id, class_name, master_id, is_key)
• student：學生表 (id, school_id, name, class_name...)
• course：課程表 (id, course_name, grade, class_name...)
• score：成績表 (id, course_id, school_id, score)

連接類型選型實戰

場景：查詢各班級班長姓名

新增班級 303 暫無班長，WHERE/INNER JOIN 將丟失該行：

-- WHERE / INNER JOIN 結果 (6 行，缺 303)
SELECT cl.class_name, st.name 
FROM class cl, student st WHERE cl.master_id = st.school_id;

-- LEFT JOIN 結果 (7 行，含 303 NULL)
SELECT cl.class_name, st.name 
FROM class cl LEFT JOIN student st ON cl.master_id = st.school_id;

核心原則：業務需要「主表全量保留」時，必須使用 LEFT/RIGHT JOIN；WHERE 子句等價於 INNER JOIN，會過濾掉無匹配行。

EXPLAIN 實戰優化演示

優化前：全表掃描

EXPLAIN SELECT cl.class_name, st.name 
FROM class cl LEFT JOIN student st ON cl.master_id = st.school_id;

+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 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 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

問題：被驅動表 student 類型為 ALL (全表掃描)，rows=301。

優化後：被驅動表加索引

ALTER TABLE student ADD INDEX idx_school_id (school_id);

+----+-------------+-------+------+-----------------+-----------------+---------+---------------------+------+-------+
| 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 (單行精確定位)
• Extra：消失 Using join buffer，無需臨時表

MySQL Nested Loop Join 原理

MySQL 僅支援 Nested Loop Join：

1. 驅動表 (外層循環) 產生結果集
2. 遍歷結果集，每行作為條件查詢被驅動表 (內層循環)
3. 合併結果，若有第三張表，重複上述過程

優化核心：被驅動表 JOIN 條件列必須有索引，將內層循環從全表掃描降為索引查找 O(log n)。

複雜查詢優化實戰

場景：根據學號查詢學生成績單 (三表關聯)

EXPLAIN SELECT st.name, co.course_name, sc.score
FROM student st
JOIN score sc ON sc.school_id = st.school_id
JOIN course co ON co.id = sc.course_id
WHERE st.school_id = 100005;

+----+-------------+-------+--------+---------------------------------------+--------------------+---------+---------------------+------+-------+
| 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 通過 WHERE school_id=const 直接定位 1 行
• score 表複合索引支援 school_id 精確查找
• course 走主鍵 eq_ref，單行精確匹配
• 全鏈路無全表掃描，總掃描行數極小

優化總結清單

1. 語義優先：業務需要外連接時堅決用 LEFT/RIGHT JOIN，別用 WHERE 代替
2. 索引是核心：被驅動表 JOIN 鍵、WHERE 條件列、ORDER BY/GROUP BY 列建立索引
3. 小表驅動大表：通過 WHERE 條件縮小驅動表結果集
4. 避免 SELECT *：只取需要列，減少 IO 與網絡傳輸
5. 定期 ANALYZE TABLE：更新統計信息，保證優化器決策準確

附錄：完整建表與索引 SQL

CREATE DATABASE school;
USE school;

CREATE TABLE student (
    id INT NOT NULL AUTO_INCREMENT,
    school_id INT(11) NOT NULL,
    name VARCHAR(30) NOT NULL,
    sex INT NOT NULL,
    age INT NOT NULL,
    class_name INT NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE class (
    id INT NOT NULL AUTO_INCREMENT,
    class_name INT NOT NULL,
    master_id INT,
    is_key INT NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE course (
    id INT NOT NULL AUTO_INCREMENT,
    course_name VARCHAR(10) NOT NULL,
    grade INT NOT NULL,
    president_id INT,
    is_neces INT NOT NULL,
    credit INT NOT NULL,
    class_name INT,
    PRIMARY KEY (id)
);

CREATE TABLE score (
    id INT NOT NULL AUTO_INCREMENT,
    course_id INT NOT NULL,
    school_id INT NOT NULL,
    score INT,
    PRIMARY KEY (id)
);

-- 核心索引
ALTER TABLE student ADD INDEX idx_school_id (school_id);
ALTER TABLE score ADD INDEX idx_school_id_sc (school_id);
ALTER TABLE score ADD INDEX idx_course_id_sc (course_id);
ALTER TABLE class ADD INDEX idx_master_id (master_id);