MySQL EXPLAIN 深度解析：從輸出字段到實戰索引優化

一、EXPLAIN 基礎語法

EXPLAIN SELECT ... FROM ... WHERE ...;
-- 或
EXPLAIN EXTENDED SELECT ...;  -- 後接 SHOW WARNINGS 看優化器重寫

可視化工具：mk-visual-explain 生成樹形執行計劃。

二、核心輸出字段詳細解讀

type 類型由優到劣詳解

1. system/const：表僅 1 行或主鍵/唯一鍵精確匹配，極快。
2. eq_ref：唯一/主鍵索引全部列參與 JOIN，每前表行匹配 1 行。
3. ref：非唯一索引或最左前綴匹配，每前表行匹配多行。
4. ref_or_null：同 ref，額外處理 IS NULL。
5. index_merge：索引合併優化（多索引 UNION/INTERSECTION）。
6. unique_subquery / index_subquery：IN 子查詢優化替換為索引查找。
7. range：索引範圍掃描（>, >=, <, <=, BETWEEN, IN）。
8. index：全索引掃描（比 ALL 快，因索引文件較小）。
9. ALL：全表掃描，**性能最差，需優化**。

Extra 關鍵值

• Using index：覆蓋索引，只讀索引不回表，**極佳**。
• Using where：WHERE 過濾，配合 ALL/index 需警惕。
• Using filesort：需額外排序，**通常需優化**（建索引消除）。
• Using temporary：創建臨時表，**通常需優化**（調整 GROUP BY/ORDER BY 列順序）。
• Range checked for each record：無理想索引，極慢。

三、實戰案例：消除 Using filesort

場景

SELECT author_id FROM article
WHERE category_id = 1 AND comments > 1
ORDER BY views DESC LIMIT 1;

表結構：id PK, author_id, category_id, views, comments, title, content，初始僅有主鍵。

步驟 1：全表掃描 + filesort

EXPLAIN ... 
-- type: ALL, key: NULL, Extra: Using where; Using filesort

步驟 2：建組合索引 (category_id, comments, views)

ALTER TABLE article ADD INDEX x (category_id, comments, views);
-- type: range, key: x, Extra: Using where; Using filesort

仍有 filesort。原因：comments > 1 是範圍條件，B+ 樹組合索引中範圍列後的列（views）無法用於排序。

步驟 3：調整索引順序 (category_id, views)

ALTER TABLE article ADD INDEX y (category_id, views);
-- 優化器可能仍選舊索引 x，需強制
EXPLAIN SELECT ... FORCE INDEX (y) ...;
-- type: ref, key: y, Extra: Using where  ✅ filesort 消失

或刪除索引 x 讓優化器自動選 y。

四、組合索引設計黃金法則

1. 最左前綴原則：查詢必須從索引最左列開始匹配。
2. 範圍列放最後：組合索引中**只能有一個範圍列**，且必須置於末尾，其後列失效。
3. 覆蓋索引：SELECT 列全部包含在索引中，避免回表。
4. ORDER BY 列順序 = 索引列順序：且方向一致（全 ASC 或全 DESC），才能避免 filesort。

五、進階優化技巧

• EXPLAIN EXTENDED + SHOW WARNINGS 查看優化器重寫後的 SQL。
• 關注 rows × filtered/100 估算實際參與 JOIN 的行數。
• 大表 JOIN 時，驅動表（外層循環）應為小結果集，被驅動表需有索引支持 ref/eq_ref。
• 子查詢優化：優先改寫為 JOIN 或利用 unique_subquery/index_subquery。

六、總結

EXPLAIN 是 SQL 調優的「聽診器」。核心目標：讓 type 至少達到 range，最好 ref/eq_ref/const；Extra 無 filesort/temporary。結合組合索引設計原則，大多數慢查詢可通過調整索引順序、增加覆蓋列、消除範圍列後的排序列來解決。