MySQL EXPLAIN EXTENDED 深度解析：查詢優化器的隱藏視角

為什麼需要 EXPLAIN EXTENDED？

標準的 EXPLAIN 已能顯示查詢執行計畫，但 EXPLAIN EXTENDED 更進一步——它能揭示 MySQL 查詢優化器內部如何重寫你的 SQL。配合 SHOW WARNINGS，我們能看到優化後的實際查詢語句，這對於深度 SQL 調優至關重要。

基本用法

EXPLAIN EXTENDED SELECT * FROM account\G;
SHOW WARNINGS\G;

實戰案例解析

案例一：SELECT * 被展開為具體欄位

-- 原始查詢
EXPLAIN EXTENDED SELECT * FROM account\G;

-- SHOW WARNINGS 輸出
Message: select `dbunit`.`account`.`id` AS `id`,`dbunit`.`account`.`name` AS `name` from `dbunit`.`account`

優化器將 SELECT * 明確化為具體欄位，這在生產環境中建議開發者直接寫明欄位，減少網路傳輸與解析開銷。

案例二：條件簡化的限制

EXPLAIN EXTENDED SELECT * FROM sbtest WHERE id>5 AND id>6 AND c="a" AND pad=c

優化器無法將 id>5 AND id>6 合併為 id>6，這揭示了優化器在條件推導上的邊界。

案例三：子查詢轉換為 EXISTS

EXPLAIN EXTENDED SELECT * FROM t WHERE a IN (SELECT b FROM i);
SHOW WARNINGS\G;

-- 輸出片段
Message: select `test`.`t`.`a` AS `a`... from `test`.`t` where 
<in_optimizer>(`test`.`t`.`a`, <exists>(select 1 AS `Not_used` from `test`.`i` where (<cache>(`test`.`t`.`a`) = `test`.`i`.`b`)))

看似不相關的子查詢被標記為 DEPENDENT SUBQUERY，優化器實際將其轉換為 EXISTS 相關子查詢並引入快取機制，這解釋了為什麼看似獨立的子查詢會產生相關性。

案例四：JOIN 順序與常數傳播

EXPLAIN EXTENDED SELECT t1.id,t2.pad FROM sbtest t1, sbtest t2 WHERE t1.id=5 AND t2.k=t1.k;
SHOW WARNINGS\G;

-- 輸出
Message: SELECT `test`.`t1`.`id` AS `id`,`test`.`t2`.`pad` AS `pad` FROM `test`.`sbtest` `t1` JOIN `test`.`sbtest` `t2` WHERE ((`test`.`t2`.`k` = `test`.`t1`.`k`) AND (`test`.`t1`.`id` = 5))

EXPLAIN 顯示 t1 使用 const 類型（主鍵查找），但優化後的 SQL 中並未明確體現這一優化細節，說明 SHOW WARNINGS 輸出的優化 SQL 可能不是最終執行計畫的完整反映。

核心洞察與限制

• 優化可視化：能看到優化器「能做什麼」與「不能做什麼」
• 非最終計畫：輸出的 SQL 可能只是中間重寫結果，非最終執行計畫
• 輔助分析：結合標準 EXPLAIN 的 type、key、rows 等欄位綜合判斷
• 版本差異：不同 MySQL 版本優化器行為可能不同，需實測驗證

最佳實踐建議

1. 先用標準 EXPLAIN 觀察執行計畫關鍵指標
2. 再用 EXPLAIN EXTENDED + SHOW WARNINGS 理解優化器重寫邏輯
3. 針對複雜查詢（子查詢、多表 JOIN、條件推導）重點分析
4. 驗證索引建議是否被採納，條件是否被正確下推

掌握 EXPLAIN EXTENDED，就像擁有了窺探優化器「大腦」的 X 光眼，是資深 DBA 與後端工程師進行深度 SQL 調優的必備技能。