MySQL 效能優化：用 EXPLAIN 找出 SQL 低效根因

EXPLAIN 核心欄位解讀

type 存取類型效能階梯（由好到壞）

1. system / const：常數表、主鍵/唯一鍵等值查詢（最快）
2. eq_ref：聯接時用主鍵/唯一鍵（每行匹配 1 行）
3. ref：聯接用普通索引（每行匹配多行）
4. ref_or_null：同 ref，額外處理 NULL
5. index_merge：索引合併優化
6. unique_subquery / index_subquery：IN 子查詢優化
7. range：範圍查詢（BETWEEN、>、<、IN、LIKE 'prefix%'）
8. index：全索引掃描（覆蓋索引時較快）
9. ALL：全表掃描（最慢，需建立索引）

實戰案例：從 ALL 到 ref

優化前

EXPLAIN SELECT SUM(`profit`) FROM `sales` a, `company` b
WHERE a.`company_id` = b.`id` AND a.`year` = 2006\G
-- sales 表：type=ALL, rows=12, key=NULL

建立索引

CREATE INDEX `idx_sales_year` ON `sales`(`year`);

優化後

EXPLAIN SELECT SUM(`profit`) FROM `sales` a, `company` b
WHERE a.`company_id` = b.`id` AND a.`year` = 2006\G
-- sales 表：type=ref, key=idx_sales_year, rows=3

掃描行數從 12 降至 3，效能提升顯著。

Extra 關鍵值含義

• Using index：覆蓋索引，僅讀取索引樹，不回表（極佳）
• Using where：Server 層過濾（索引未完全覆蓋條件）
• Using temporary：建立臨時表（GROUP BY、DISTINCT、ORDER BY 不同索引）
• Using filesort：檔案排序，無索引支援 ORDER BY（需優化）
• Using join buffer：使用 Join Buffer（Block Nested Loop）
• Impossible WHERE：WHERE 條件永假（常數折疊）

進階優化清單

1. 避免 SELECT *：只查所需欄位，利於覆蓋索引。
2. 優化 ORDER BY / GROUP BY：利用索引順序避免 filesort。
3. 聯表查詢驅動表選擇：小表驅動大表，被驅動表有索引。
4. 子查詢轉 JOIN：MySQL 5.6+ 對衍生表優化，但 JOIN 仍更穩定。
5. 分頁優化：LIMIT 100000, 20 → 覆蓋索引子查詢 + 回表。
6. 定期 ANALYZE TABLE：更新統計資訊，讓優化器決策更準確。

工具推薦

• pt-visual-explain：將 EXPLAIN 輸出為樹狀圖
• EXPLAIN ANALYZE (MySQL 8.0.18+)：實際執行計畫與耗時
• sys.schema_table_statistics：表級統計

結語

EXPLAIN 是 SQL 調優的「聽診器」。熟練解讀 type、key、rows、Extra，配合索引設計、查詢重寫、統計維護，能系統性解決 90% 以上的慢查詢問題。