EXPLAIN 執行計劃完全指南：從輸出解讀到索引優化實戰
輸出字段完整解讀 (MySQL 5.6+)
| 字段 | 含義 | 關鍵關注點 |
|---|
| id | SELECT 標識符，遞增數字 | 相同 id 從上到下執行；不同 id 數字大先執行 |
| select_type | 查詢類型 | SIMPLE/PRIMARY/UNION/SUBQUERY/DERIVED/DEPENDENT... |
| table | 訪問的表 (含衍生表/別名) | <derived2> 表示衍生表 |
| partitions | 匹配的分區 (5.6+) | 分區裁剪驗證 |
| type | 訪問類型 (核心！) | system > const > eq_ref > ref > range > index > ALL |
| possible_keys | 可能用到的索引 | 僅參考，不代表實際使用 |
| key | 實際使用的索引 | NULL = 全表掃描 |
| key_len | 索引鍵長度 (字節) | 判斷組合索引用了幾列 |
| ref | 與 key 配合的列/常量 | const/func/列名 |
| rows | 預估掃描行數 | 越小越好，乘積反映總掃描量 |
| filtered | 條件過濾比例 (5.6+) | 100 = 無過濾，越低說明索引選擇性差 |
| Extra | 額外信息 (關鍵！) | Using index/Where/Filesort/Temporary/Impossible WHERE |
type 類型優劣完整順序 (最好 → 最差)
- system：表僅 1 行 (系統表)
- const：主鍵/唯一索引等值查詢，最多 1 行，優化器當常量處理
- eq_ref：主鍵/唯一索引 JOIN，每張表最多 1 行匹配
- ref：非唯一索引等值查詢/JOIN，可能多行
- ref_or_null：ref + NULL 值補充
- unique_subquery：IN 子查詢主鍵優化
- index_subquery：IN 子查詢非唯一索引優化
- range：索引範圍掃描 (<, >, >=, BETWEEN, IN)
- index：全索引掃描 (不讀數據文件)
- ALL：全表掃描 (最差，需優化)
Extra 關鍵值含義
| 值 | 含義 | 優化方向 |
|---|
| Using index | 覆蓋索引 (僅讀索引) | ✅ 優秀，無需回表 |
| Using where | Server 層過濾 | 正常，但若 type=ALL 則需加索引 |
| Using index condition (5.6+) | ICP 索引條件下推 | ✅ 優秀，減少回表 |
| Using temporary | 創建臨時表 | ❌ 優化 GROUP BY/ORDER BY |
| Using filesort | 外部排序 | ❌ 加索引消除排序 |
| Using join buffer | Join Buffer 內存連接 | ❌ 小表驅動大表/加索引 |
| Impossible WHERE | WHERE 條件永假 | ✅ 優化器直接返回空 |
| Select tables optimized away | 聚合函數優化 (MIN/MAX/COUNT) | ✅ 極優 |
實戰優化案例：四表關聯從 450億 掃描降至 3872 行
初始狀態
EXPLAIN SELECT ... FROM tt, et, et et_1, do
WHERE tt.submittime IS NULL
AND tt.actualpc = et.employid
AND tt.assignedpc = et_1.employid
AND tt.clientid = do.custnmbr;
-- 所有表 type=ALL，rows 乘積 74*2135*74*3872 = 452億
-- Extra: range checked for each record
優化步驟
- 修正字段長度不一致：
CHAR(10) vs CHAR(15) 導致索引失效
ALTER TABLE tt MODIFY actualpc VARCHAR(15),
MODIFY assignedpc VARCHAR(15),
MODIFY clientid VARCHAR(15);
- 執行 ANALYZE TABLE 更新統計信息，優化器獲得真實分布
最終執行計劃
+-------+--------+----------------+---------+--------+------+-------+--------------------------+
| table | type | possible_keys | key | key_len| ref | rows | Extra |
+-------+--------+----------------+---------+--------+------+-------+--------------------------+
| tt | ALL | assignedpc... | NULL | NULL | NULL | 3872 | Using where |
| et | eq_ref | PRIMARY | PRIMARY | 15 | tt.actualpc | 1 | |
| et_1 | eq_ref | PRIMARY | PRIMARY | 15 | tt.assignedpc| 1 | |
| do | eq_ref | PRIMARY | PRIMARY | 15 | tt.clientid | 1 | |
+-------+--------+----------------+---------+--------+------+-------+--------------------------+
掃描行數：3872 × 1 × 1 × 1 = 3872，性能提升 1160萬倍！
進階技巧
EXPLAIN FORMAT=JSON (5.6+) 獲取更豐富信息 (成本估算、ICP細節)EXPLAIN ANALYZE (8.0.18+) 顯示實際執行時間/行數 vs 預估- 配合
SHOW INDEX / SHOW TABLE STATUS 驗證索引定義
- 使用
STRAIGHT_JOIN 強制連接順序 (僅作調試)
