萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL EXPLAIN 執行計劃深度解析:從 type、key、rows 到 Extra 排序優化實戰

EXPLAIN 基礎用法

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1;
EXPLAIN ANALYZE SELECT ...  -- 8.0.18+ 實際執行統計

輸出列詳細解讀

列名含義關鍵判讀
id查詢序號相同 id 從上到下執行;不同 id 大者先執行
select_type查詢類型SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION 等
table訪問的表<derived2> 表示衍生表
partitions匹配的分區分區表查看分區剪枝效果
type訪問類型(核心)system > const > eq_ref > ref > range > index > ALL
possible_keys可能用到的索引為 NULL 表示無可用索引
key實際使用的索引為 NULL 表示全表掃描
key_len索引長度(字節)越短越好;多列索引可判斷用了幾列
ref索引比較值來源const、func、NULL、列名
rows預估掃描行數越小越好;配合 type 判斷效率
filtered按條件過濾後剩餘比例%越接近 100 越好
Extra額外信息(關鍵)見下表紅綠燈

Extra 列紅綠燈速查

值級別含義行動
Using index🟢 綠覆蓋索引,僅掃描索引樹優秀,保持
Using where🟡 黃Server 層過濾(非存儲引擎)檢查能否下推
Using index condition🟢 綠ICP 索引條件下推(5.6+)優秀
Using temporary🔴 紅創建臨時表(GROUP BY/ORDER BY 不同列)優化索引/查詢
Using filesort🔴 紅文件排序(無索引支持 ORDER BY)加索引/調整 ORDER BY
Using join buffer🟡 黃Join Buffer 緩存關聯加索引避免
Impossible WHERE🟢 綠WHERE 條件永假,優化器直接返回空正常
Select tables optimized away🟢 綠聚合查詢優化掉表訪問優秀
Range checked for each record🔴 紅逐行檢查用哪個索引(極慢)重寫查詢/加索引

實戰優化案例

案例:忘記加 group_id 索引的多表關聯

EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.gid = t2.gid WHERE t1.status=1;
-- type=ALL, rows=7883, Extra=Using where; Using join buffer (Block Nested Loop)

加上索引後

ALTER TABLE t1 ADD INDEX idx_gid_status (gid, status);
ALTER TABLE t2 ADD INDEX idx_gid (gid);

EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.gid = t2.gid WHERE t1.status=1;
-- type=ref, rows=9/16, Extra=Using index condition

掃描行數從 7883 降至 9+16,**效能提升 300 倍+**。


Type 訪問類型效能階梯

system (表僅1行) 
  > const (PK/UK 唯一查找) 
  > eq_ref (JOIN 時 PK/UK 關聯) 
  > ref (非唯一索引/最左前綴) 
  > range (索引範圍掃描 >,<,IN,BETWEEN) 
  > index (全索引掃描) 
  > ALL (全表掃描)  -- 最差,必須優化

強制/忽略索引提示

SELECT * FROM t USE INDEX (idx_name) WHERE ...;   -- 強制使用
SELECT * FROM t IGNORE INDEX (idx_name) WHERE ...; -- 忽略某索引
SELECT * FROM t FORCE INDEX (idx_name) WHERE ...;  -- 強制(比 USE 更強)

⚠️ 僅作應急,根治靠統計信息準確(ANALYZE TABLE)與合理索引設計。


優化檢查清單

  1. type 是否 ≥ ref?
  2. key 是否非 NULL?
  3. rows 是否可接受(< 1000 或佔比 < 10%)?
  4. Extra 是否有 🔴 紅燈?
  5. key_len 是否符合預期(組合索引列數)?
  6. filtered 是否 > 50%?
copyright © 萬盛學電腦網 all rights reserved