MySQL EXPLAIN詳解：揭開SQL執行計劃的神秘面紗詳情 - sql,MySQL,字段,MySQL,數據庫修己xj 博客

在日常的數據庫開發和性能優化中，我們經常會遇到這樣的問題：“為什麼這條SQL查詢這麼慢？”MySQL的EXPLAIN命令就是解決這類問題的瑞士軍刀。它能夠展示MySQL如何執行查詢，讓我們深入瞭解查詢的執行計劃，從而找到性能瓶頸並進行優化。

什麼是EXPLAIN？

EXPLAIN是MySQL提供的一個關鍵字，用於分析SELECT語句的執行計劃。通過它，我們可以瞭解MySQL是如何處理SQL語句的，包括：

表的讀取順序
數據讀取操作的類型
哪些索引可能被使用
哪些索引實際被使用
表之間的引用關係
每張表有多少行被優化器查詢

基本用法

-- 最基本的用法
EXPLAIN SELECT * FROM users WHERE id = 1;

-- 也可以用於連接查詢
EXPLAIN SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.email = 'example@email.com';

-- 使用EXPLAIN FORMAT=JSON獲取更詳細的信息
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 20;

EXPLAIN輸出字段詳解

讓我們深入瞭解EXPLAIN輸出的每個字段：

1. id - 查詢標識符

SELECT語句的執行順序
id相同，執行順序從上到下
id不同，值越大優先級越高，越先執行
id為NULL表示是結果集，如UNION查詢

2. select_type - 查詢類型

類型	説明
SIMPLE	簡單SELECT，不包含子查詢或UNION
PRIMARY	查詢中若包含子查詢，最外層被標記為PRIMARY
SUBQUERY	在SELECT或WHERE列表中包含了子查詢
DERIVED	FROM列表中的子查詢，臨時表
UNION	UNION中的第二個或後面的SELECT語句
UNION RESULT	UNION的結果

3. table - 訪問的表

顯示這一步訪問的是哪張表，有時可能是：

<derivedN> 派生表
<unionM,N> UNION結果

4. partitions - 匹配的分區

如果表是分區表，顯示匹配的分區信息。

5. type - 訪問類型（非常重要！）

這是判斷查詢是否高效的關鍵指標，從最優到最差排序：

最優級別：

system：表只有一行記錄（等於系統表）
const：通過索引一次就找到，用於primary key或unique key比較
eq_ref：唯一索引掃描，對於每個索引鍵，表中只有一條記錄匹配

良好級別：

ref：非唯一索引掃描，返回匹配某個單獨值的所有行
range：只檢索給定範圍的行，使用索引選擇行
index：Full Index Scan，遍歷整個索引樹

需要優化：

ALL：Full Table Scan，全表掃描，性能最差

6. possible_keys - 可能使用的索引

查詢中可能用到的索引，但不一定實際使用。

7. key - 實際使用的索引

查詢中實際使用的索引。如果為NULL，則沒有使用索引。

8. key_len - 使用的索引長度

表示索引中使用的字節數，可通過該列計算查詢中使用的索引長度。

9. ref - 索引的比較值

顯示索引的哪一列被使用了，可能是一個常數(const)。

10. rows - 預估需要讀取的行數

根據表統計信息及索引選用情況，大致估算出找到所需的記錄需要讀取的行數。

11. filtered - 過濾百分比

表示存儲引擎返回的數據在server層過濾後，剩下多少滿足查詢的記錄數比例。

12. Extra - 額外信息（重點關注！）

包含不適合在其他列顯示的額外信息：

好的情況：

Using index：使用覆蓋索引，無需回表
Using where：在存儲引擎檢索後，Server層再進行過濾
Using index condition：使用索引條件下推優化

需要關注的情況：

Using temporary：使用臨時表，常見於排序和分組查詢
Using filesort：使用文件排序，無法利用索引完成的排序
Using join buffer：使用連接緩存，關聯字段沒有索引

實戰分析：通過案例理解EXPLAIN

案例1：簡單查詢分析

-- 創建測試表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    age INT,
    created_at DATETIME,
    INDEX idx_age (age),
    INDEX idx_name_age (name, age)
);

-- 分析不同查詢
EXPLAIN SELECT * FROM users WHERE id = 1;
-- type: const, key: PRIMARY

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- type: const, key: email唯一索引

EXPLAIN SELECT * FROM users WHERE age > 20;
-- type: range, key: idx_age

案例2：聯合索引分析

-- 最佳實踐：最左前綴原則
EXPLAIN SELECT * FROM users WHERE name = 'John';
-- 使用索引 idx_name_age

EXPLAIN SELECT * FROM users WHERE age = 25;
-- 可能不會使用 idx_name_age，因為不符合最左前綴原則

EXPLAIN SELECT * FROM users WHERE name = 'John' AND age = 25;
-- 使用聯合索引 idx_name_age

案例3：連接查詢分析

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10,2),
    order_date DATE,
    INDEX idx_user_id (user_id),
    INDEX idx_order_date (order_date)
);

EXPLAIN 
SELECT u.name, SUM(o.amount) as total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age > 18 AND o.order_date >= '2024-01-01'
GROUP BY u.id
ORDER BY total DESC;

高級技巧：EXPLAIN的擴展用法

1. EXPLAIN ANALYZE (MySQL 8.0.18+)

-- 顯示實際執行時間和成本
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;

2. EXPLAIN FORMAT=JSON

-- 獲取詳細的JSON格式執行計劃
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 25;

3. 分析UPDATE/DELETE語句

-- 先使用EXPLAIN分析SELECT，再執行UPDATE
EXPLAIN SELECT * FROM users WHERE age > 60;
UPDATE users SET status = 'retired' WHERE age > 60;

常見性能問題及優化建議

問題1：全表掃描（type=ALL）

症狀： 查詢沒有使用索引 解決方案：

添加合適的索引
檢查WHERE條件是否可以使用索引

問題2：文件排序（Extra=Using filesort）

症狀： ORDER BY無法使用索引排序 解決方案：

為ORDER BY字段添加索引
使用覆蓋索引

問題3：臨時表（Extra=Using temporary）

症狀： GROUP BY或DISTINCT需要臨時表 解決方案：

為GROUP BY字段添加索引
減少查詢返回的列

問題4：索引選擇性差

症狀： 雖然使用了索引，但rows值很高 解決方案：

使用選擇性更高的索引
考慮組合索引

最佳實踐總結

定期分析慢查詢

-- 開啓慢查詢日誌
SET GLOBAL slow_query_log = ON;

理解業務查詢模式
- 為高頻查詢字段建立索引
- 考慮組合索引的最左前綴原則
避免索引失效的常見陷阱
- 不要在索引列上進行計算或函數操作
- 注意LIKE查詢的通配符位置
- 小心類型轉換導致索引失效

監控索引使用情況

-- 查看索引使用統計
SELECT * FROM sys.schema_index_statistics;

使用覆蓋索引優化查詢
- 只查詢索引包含的字段
- 減少回表操作

結語

掌握EXPLAIN是MySQL性能優化的基本功。通過深入理解執行計劃的每個細節，我們不僅能解決當前的性能問題，還能在設計階段避免潛在的性能陷阱。記住，最好的優化是在問題發生之前就避免它。

持續學習，不斷實踐，你會發現EXPLAIN不僅是調試工具，更是理解MySQL查詢優化器工作原理的窗口。祝你在數據庫性能優化的道路上越走越遠！

修己xj 博客

修己xj 博客

博客 / 詳情