mysql- explain 分析 -06
MySQL EXPLAIN 詳解：SQL性能分析與優(yōu)化指南
EXPLAIN是MySQL中用于分析SQL查詢性能的關鍵工具，它能夠顯示MySQL如何執(zhí)行查詢，包括使用的索引、表連接順序等重要信息。下面我將全面介紹EXPLAIN的使用方法、各字段含義及分析方法。
一、EXPLAIN基本用法
1. 基本語法
EXPLAIN SELECT * FROM users WHERE id = 100; -- 或更詳細的格式（MySQL 8.0+） EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 100;
查詢出的字段信息下面有詳細解釋
2. 分析UPDATE/DELETE/INSERT語句
EXPLAIN UPDATE users SET status = 'active' WHERE age > 18; EXPLAIN DELETE FROM users WHERE last_login < '2023-01-01';
3. 分析連接查詢
EXPLAIN SELECT u.*, o.order_date FROM users u JOIN orders o ON u.id = o.user_id WHERE u.city = 'Beijing';
二、EXPLAIN輸出字段詳解
以下是EXPLAIN輸出的主要列及其含義：
| 列名 | 說明 | 重要程度 | 優(yōu)化建議 | 典型場景 | 取值示例 |
|---|---|---|---|---|---|
| id |
SELECT查詢的序列號， id相同，執(zhí)行順序從上到下， id不同，從大到小執(zhí)行， id為null最后執(zhí)行 |
★★ | 關注復雜查詢中id的分布，子查詢或UNION可能導致多id |
嵌套子查詢、UNION查
|
1 (簡單查詢) 1,2 (子查詢) |
| select_type |
查詢類型，分別如下這些： SIMPLE ：簡單SELECT查詢(不含子查詢或UNION) PRIMARY ：最外層查詢 SUBQUERY ：子查詢中的第一個SELECT DERIVED ：派生表(FROM子句中的子查詢) UNION： UNION中的第二個或后續(xù)查詢 UNION RESULT： UNION的結果 |
★★★ | 避免DEPENDENT SUBQUERY（依賴外層查詢的子查詢，性能差） | SIMPLE: 簡單查詢 DERIVED: FROM子句中的子查詢 |
SIMPLE, PRIMARY, SUBQUERY, DERIVED |
| table | 訪問的表名或別名 | ★★ | 表名較長時建議使用別名 | 多表連接時顯示表的訪問順序 | users, u (別名), <derived2> (派生表) |
| partitions | 匹配的分區(qū) | ★ | 分區(qū)表優(yōu)化時關注 | 分區(qū)表查詢 | p0, p1 (具體分區(qū)名) |
| type | 訪問類型（關鍵指標） 1. system：表只有一行記錄(系統(tǒng)表) 2. const：通過主鍵或唯一索引一次就找到(eg: EXPLAIN SELECT * FROM users WHERE id = 1;
) 3. eq_ref：唯一索引關聯(lián)查詢 (eg: EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
) 4. ref：非唯一索引查找(eg: EXPLAIN SELECT * FROM users WHERE age = 25; -- age有普通索引
) 5. range：索引范圍掃描(eg: EXPLAIN SELECT * FROM users WHERE id > 100;
) 6. index：全索引掃描(比全表掃描好)(eg: EXPLAIN SELECT id FROM users; -- id是主鍵
) 7. ALL：全表掃描(需優(yōu)化) |
★★★★★ | 至少達到range級別，優(yōu)先優(yōu)化ALL和index | const: 主鍵查詢 ref: 非唯一索引查詢 ALL: 全表掃描 |
訪問類型，性能從好到壞排序: system > const > eq_ref > ref > range > index > ALL |
| possible_keys | 可能使用的索引 | ★★★ | 檢查列出的索引是否合理 | 顯示優(yōu)化器考慮的索引選項 | idx_name, PRIMARY |
| key | 實際使用的索引 | ★★★★★ | 若為NULL則未用索引，需創(chuàng)建 | 顯示實際選擇的索引 | idx_age (使用age索引) |
| key_len | 使用的索引字節(jié)數(shù) | ★★★ | 與字段定義長度對比，判斷是否充分利用索引 | varchar(100) utf8mb4索引，前30字符: 30*4+2=122 | 4 (int) 122 (varchar部分使用) |
| ref | 與索引比較的列或常量 | ★★ | 若為func可能使用了函數(shù)導致索引失效 | 顯示索引匹配方式 | const (常量) db1.users.id (列引用) |
| rows | 預估檢查行數(shù) | ★★★★ | 數(shù)值越大性能越差，結合filtered評估 | 全表掃描時等于表總行數(shù) | 1 (精確匹配) 1000 (范圍掃描) |
| filtered | 存儲引擎層過濾后剩余數(shù)據(jù)的百分比 | ★★★ | 大于20%可能需優(yōu)化 | 連接查詢中前表filtered影響后表掃描次數(shù) | 10.00 (過濾掉90%) |
| Extra | 額外執(zhí)行信息（關鍵提示） | ★★★★★ | 重點優(yōu)化Using filesort/temporary | Using index: 覆蓋索引 Using where: 服務層過濾 |
Using index, Using where, Using filesort |
三、EXPLAIN分析方法
1. 性能分析步驟
-
查看type列：確認訪問類型，避免ALL全表掃描
-
檢查key列：確認是否使用了合適的索引
-
分析rows列：預估行數(shù)是否合理
-
研究Extra列：查看額外執(zhí)行信息
-
評估key_len：確認索引使用是否充分
2. 實際案例分析
案例1：未使用索引
EXPLAIN SELECT * FROM users WHERE phone = '13800138000';
-
如果type=ALL且key=NULL，說明是全表掃描
-
解決方案：為phone字段添加索引
案例2：索引使用不充分
EXPLAIN SELECT * FROM users WHERE name LIKE '%張%';
-
type=range但rows值很大
-
解決方案：避免前導通配符，考慮全文索引
案例3：多表連接優(yōu)化
EXPLAIN SELECT u.name, o.order_no
FROM users u JOIN orders o ON u.id = o.user_id
WHERE u.city = 'Shanghai';
-
檢查每張表的type和key
-
確保連接字段有索引
浙公網(wǎng)安備 33010602011771號