您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL EXPLAIN 深度解析：執行計劃核心指標、優化器決策邏輯與慢查詢優化實戰

編輯：MySQL 資料庫相關知識

EXPLAIN 基礎用法


-- 基本語法
EXPLAIN SELECT * FROM tbl WHERE col = 1;
EXPLAIN EXTENDED SELECT ...;  -- 5.6+ 顯示優化後重寫 SQL (SHOW WARNINGS 查看)
EXPLAIN FORMAT=JSON SELECT ...;  -- 5.6+ 結構化輸出，含成本信息
EXPLAIN ANALYZE SELECT ...;  -- 8.0.18+ 實際執行統計 (耗時、行數、循環次數)

-- 表結構查看
EXPLAIN tbl_name;  -- 等同 DESC tbl_name / SHOW COLUMNS FROM tbl_name

輸出列核心指標深度解讀

列名	含義	優化關注點	優秀基準
id	SELECT 查詢序列號	同 id 從上往下執行；不同 id 值大先執行 (子查詢/衍生表)	—
select_type	查詢類型	SIMPLE/PRIMARY/SUBQUERY/DERIVED/UNION/UNION RESULT	SIMPLE 最優，避免 DEPENDENT SUBQUERY
table	訪問的表 (別名/衍生表名)	關聯查詢順序 = 輸出順序	—
partitions	匹配的分區 (分區表)	分區裁剪是否生效	僅目標分區
type	訪問類型 (核心!)	system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL	至少 range，最好 ref/eq_ref/const
possible_keys	理論可用索引	為空 = 無索引可用，需建索引	—
key	實際選擇索引	NULL = 全表掃描	非 NULL
key_len	索引鍵長度 (字節)	推斷聯合索引使用了幾列：`列長度總和 + NULL標誌(1字節/可空列) + 變長列長度前綴(2字節)`	—
ref	與 key 配合的比較值	const/列名/函數/參數	—
rows	預估掃描行數 (統計信息)	越小越好，InnoDB 為抽樣估算，可能偏差大	接近實際返回行數
filtered	按表條件過濾後剩餘比例 % (5.7+)	`rows * filtered/100` ≈ 實際參與聯接行數	越高越好 (100% 為全匹配)
Extra	額外信息 (關鍵!)	見下文詳解	Using index 好；Using where 正常；Using filesort/temporary 需優化

Type 訪問類型完整層級圖譜


system (表僅 1 行，系統表) 
  > const (主鍵/唯一索引等值查詢，最多 1 行) 
  > eq_ref (唯一索引/主鍵聯接，每張表 1 行) 
  > ref (非唯一索引等值查詢/聯接，多行) 
  > fulltext (全文索引) 
  > ref_or_null (ref + NULL 值) 
  > index_merge (索引合併：Intersection/Union/Sort-Union) 
  > unique_subquery (IN 子查詢主鍵) 
  > index_subquery (IN 子查詢非唯一索引) 
  > range (索引範圍掃描：>, <, >=, <=, IN, BETWEEN, LIKE 'prefix%') 
  > index (全索引掃描，無 WHERE/ORDER BY 條件) 
  > ALL (全表掃描，最差)

Extra 信息解碼手冊

Extra 值	含義	性能影響	優化方向
Using index	覆蓋索引：僅讀索引樹滿足查詢，無回表	🟢 極佳	設計覆蓋索引
Using where	Server 層過濾 WHERE 條件 (存儲引擎未完全過濾)	🟡 正常	下推條件到引擎 (ICP)
Using index condition	ICP (Index Condition Pushdown, 5.6+)：存儲引擎層過濾	🟢 優	確保索引列順序支持
Using filesort	文件排序：無索引支持 ORDER BY，需額外排序階段	🔴 重 (大數據排序極慢)	加索引消除排序、Limit 優化、增大 sort_buffer_size
Using temporary	臨時表：GROUP BY/DISTINCT/UNION/ORDER BY 異列需物化	🔴 重 (內存/磁盤臨時表開銷大)	優化 SQL 結構、增大 tmp_table_size、避免 SELECT *
Using join buffer (Block Nested Loop)	塊嵌套循環聯接：被驅動表無索引，需緩存驅動表結果	🔴 重	給被驅動表加索引、調整聯接順序 (STRAIGHT_JOIN)
Using join buffer (Batched Key Access)	BKA (5.6+)：批量鍵訪問，優於 BNL	🟡 中	確保被驅動表有索引、開啟 optimizer_switch='batched_key_access=on'
Impossible WHERE	WHERE 條件恒假 (如 `id=1 AND id=2`)，優化器直接返回空	🟢 極快	檢查業務邏輯
Select tables optimized away	聚合查詢無 GROUP BY，優化器直接從索引統計獲取結果 (如 `COUNT(*)` MyISAM)	🟢 極快	—
Distinct	發現第一匹配行後停止掃描	🟢 好	—

實戰優化案例

案例 1：Using filesort 消除


-- 原 SQL
SELECT * FROM orders WHERE user_id=100 ORDER BY created_at DESC LIMIT 10;
-- EXPLAIN: type=ref, key=idx_user_id, Extra=Using where; Using filesort

-- 優化：聯合索引 (user_id, created_at DESC)
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at DESC);
-- EXPLAIN: type=ref, key=idx_user_created, Extra=Using where (無 filesort)

案例 2：Using temporary 消除


-- 原 SQL
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id ORDER BY COUNT(*) DESC;
-- EXPLAIN: Using temporary; Using filesort

-- 優化：覆蓋索引 + 應用層排序 / 物化視圖
ALTER TABLE orders ADD INDEX idx_user_id (user_id);  -- 僅消除臨時表，排序仍需 filesort
-- 或：定時任務刷新總結表
CREATE TABLE daily_user_order_cnt (user_id BIGINT PRIMARY KEY, cnt INT, updated_at DATETIME);

案例 3：Index Merge 降級為 Range


-- 原 SQL
SELECT * FROM products WHERE category_id=5 OR brand_id=10;
-- EXPLAIN: type=index_merge, key=idx_cat,idx_brand, Extra=Using union(idx_cat,idx_brand); Using where

-- 優化：UNION ALL 替代 OR (避免臨時表去重)
SELECT * FROM products WHERE category_id=5
UNION ALL
SELECT * FROM products WHERE brand_id=10 AND category_id!=5;
-- 兩條 SQL 分別走 ref，應用層合併

JSON 格式輸出優勢 (5.6+)


EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id=100;

輸出包含：query_block、table、cost_info (read_cost, eval_cost, prefix_cost, data_read_per_join)、used_columns、attached_condition 等結構化信息，便於程序化解析、成本模型分析、自動化審核。

ANALYZE 實際執行統計 (8.0.18+)


EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id=100;

輸出關鍵實測值：actual_time (首行/所有行微秒)、actual_rows、actual_loops、r_loops (並行掃描)、r_total_time。預估 vs 實際 差異直接暴露統計信息失準、優化器誤判。

避坑指南

EXPLAIN 不執行 DML： 安全用於生產，但長時間運行的 EXPLAIN ANALYZE 會實際執行
統計信息準確性： ANALYZE TABLE 更新 (InnoDB 抽樣 8 頁)，大表偏差可達 50%+
Partition Pruning 驗證： partitions 列顯示 p0,p3 即裁剪生效
Derived Table (衍生表) 物化： 5.6+ 默認物化，select_type=DERIVED，關注 materialized 標記
Optimizer Trace： SET optimizer_trace='enabled=on'; 查看優化器決策全過程

總結

EXPLAIN 是 SQL 優化的「CT 掃描」。核心讀懂三要素：type (訪問路徑)、key/key_len/ref (索引使用細節)、Extra (額外開銷)。優化閉環：慢查詢發現 → EXPLAIN 診斷 → 索引/改寫/架構調整 → EXPLAIN 驗證 → 上線觀測。進階利器：FORMAT=JSON 程序化分析、ANALYZE 實測校準、Optimizer Trace 決策白盒。記住：**索引是手段，業務滿足是目的；過度索引同樣有害，定期清理未用索引。**