MySQL 的?EXPLAIN?命令。這是一個分析和優化 SQL 查詢性能不可或缺的強大工具。它展示了 MySQL 如何執行一條 SQL 語句，包括如何使用索引、表連接順序、估計的行數等關鍵信息。

1. 如何使用 EXPLAIN

在你要分析的?SELECT?語句前加上?EXPLAIN?或?EXPLAIN FORMAT=JSON（獲取更詳細的 JSON 格式信息）即可。

EXPLAIN SELECT * FROM users WHERE age > 30;

或者用于分析連接查詢：

EXPLAIN SELECT u.name, o.order_id 
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.country = 'China';

2. EXPLAIN 輸出列詳解

執行?EXPLAIN?后，會返回一個包含多列的結果集。每一列都描述了執行計劃的一個特定方面。以下是這些列的詳細解釋，按重要性排序：

列名	描述
id	查詢中每個?SELECT?子句的唯一標識符。
select_type	SELECT?語句的類型（簡單、子查詢、聯合等）。
table	正在訪問的表名。
partitions	匹配的分區。如果表未分區，則為?NULL。
type?(非常重要)	連接類型?或?訪問類型。這是衡量查詢效率的關鍵指標，從最優到最差排列。
possible_keys	MySQL 可能選擇用來查找該表的索引。
key	MySQL?實際決定使用的索引。如果為?NULL，則未使用索引。
key_len	所使用的索引的長度（字節數）。用于判斷是否充分利用了索引（例如，復合索引使用了最左前綴的多少部分）。
ref	顯示索引的哪一列被用來與?key?列指定的索引進行比較，以從表中選擇行。
rows?(非常重要)	MySQL?估計為了找到所需的行而必須檢查的行數。這是一個估計值，通常越小越好。
filtered	表示存儲引擎返回的數據在服務器層過濾后，剩余的行數占估計總行數的百分比。理想是 100%。
Extra?(非常重要)	包含 MySQL 解決查詢的額外信息。這里經常會出現需要重點關注的內容，如是否使用了臨時表、文件排序等。

---

3. 關鍵列深度解析

1. type (訪問類型)

這是最重要的列之一。結果值從好到壞依次是：

• system: 表只有一行（系統表）。這是?const?類型的特例。

• const: 通過索引一次就能找到，用于比較?主鍵?或?唯一索引?的等值查詢。速度極快。

EXPLAIN SELECT * FROM users WHERE id = 1; -- id 是主鍵

• eq_ref: 在連接查詢中，對于來自前表的每一行，從當前表中讀取唯一的一行。通常出現在使用?主鍵?或?唯一索引?的聯表查詢中。

-- 假設 orders.user_id 是 users.id 的外鍵，并且有索引
EXPLAIN SELECT * FROM users 
INNER JOIN orders ON users.id = orders.user_id;

• ref: 使用非唯一性索引進行等值查找，或者使用索引的最左前綴規則進行查找。可能會返回多行。

EXPLAIN SELECT * FROM users WHERE age = 30; -- age 字段有一個普通索引

• range: 使用索引檢索給定范圍的行，關鍵操作符是?BETWEEN,?>,?<,?IN?等。

EXPLAIN SELECT * FROM users WHERE age > 20 AND age < 30;

• index:?全索引掃描。只遍歷索引樹來獲取數據，通常比?ALL?快，因為索引文件通常比數據文件小。

-- 假設 (age) 是一個索引
EXPLAIN SELECT age FROM users; -- 只需掃描索引，無需回表

• ALL:?全表掃描。性能最差，意味著MySQL必須從頭到尾掃描整個表來找到匹配的行。如果數據量大，需要優化（如添加索引）。

目標：在查詢優化中，我們至少要讓?type?達到?range?級別，最好能達到?ref?或以上。

2. Extra (額外信息)

此列包含大量重要信息，常見值及其含義：

• Using index: 表示查詢使用了?覆蓋索引(Covering Index)，即所有需要的數據都可以從索引中獲取，無需回表讀取數據行。性能極佳。

• Using where: 表示存儲引擎返回行后，MySQL 服務器層還需要再進行過濾（WHERE 子句中的條件不能完全用索引來過濾）。

• Using temporary: 表示 MySQL 需要創建一個臨時表來存儲結果以處理查詢。常見于?GROUP BY?和?ORDER BY?子句。通常需要優化。

• Using filesort: 表示 MySQL 無法使用索引來完成排序，需要額外的排序操作。ORDER BY?、?GROUP BY?可能會引發此問題。在數據量大時性能很差，需要優化。

• Using join buffer (Block Nested Loop): 表示連接查詢時，被驅動表沒有使用索引，需要用到連接緩沖區。應考慮為被驅動表的連接字段添加索引。

• Impossible WHERE:?WHERE?子句的條件始終為 false，無法獲取任何行。

3. rows

MySQL 根據統計信息估算的需要讀取的行數。這個值乘以?filtered?百分比，可以估算出將要和下一張表連接的行數。這個值對于找出性能瓶頸非常有用，值越小越好。

4. key

實際使用的索引。如果為?NULL，則說明沒有使用索引，需要檢查?possible_keys?為什么沒有被選用，或者考慮創建合適的索引。

---

4. 實戰分析示例

假設我們有兩張表：

users 表

• id?(INT, PRIMARY KEY)

• name?(VARCHAR(100))

• age?(INT)

• country?(VARCHAR(100))

• 索引：?idx_age_country?(age,?country)

orders 表

• order_id?(INT, PRIMARY KEY)

• user_id?(INT)

• amount?(DECIMAL)

• 索引：?idx_user_id?(user_id)

查詢：?查找年齡在 25 到 35 歲之間、來自‘China’的用戶的所有訂單金額。

EXPLAIN 
SELECT o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.age BETWEEN 25 AND 35
AND u.country = 'China';

可能的 EXPLAIN 輸出分析：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	u	range	idx_age_country	idx_age_country	208	NULL	100	10.00	Using where
1	SIMPLE	o	ref	idx_user_id	idx_user_id	5	test.u.id	1	100.00	NULL

逐行解讀：

1. 第一行 (users 表?u)：

   • type: range: 很好！使用了索引范圍掃描來查找年齡在 25-35 之間的用戶。

   • key: idx_age_country: 實際使用了我們創建的復合索引。

   • key_len: 208: 索引使用的長度，可以推斷出?age?(INT 為 4 字節) 和?country?(VARCHAR(100), 假設 utf8mb4 字符集，最壞情況 100*4 + 長度前綴) 部分都被用到了。

   • rows: 100: MySQL 估計大約要掃描 100 行?users?表記錄。

   • Extra: Using where: 因為?country='China'?是索引的第二部分，它在索引范圍內進行查找（BETWEEN）后，可能還需要用這個條件進一步過濾數據。如果索引是?(country, age)，效率可能會更高。

2. 第二行 (orders 表?o)：

   • type: ref: 很好！對于從?u?表找到的每一個?id，通過非唯一索引?idx_user_id?在?o?表中快速查找匹配的行。

   • key: idx_user_id: 實際使用了連接字段上的索引。

   • ref: test.u.id: 使用的是?u.id?的值來查找?o?表。

   • rows: 1: 對于每一個?u.id，MySQL 估計在?o?表中只找到 1 行記錄（這是一個很好的估計，假設一個用戶只有一個訂單）。

結論：這個查詢的執行計劃相當高效。兩張表都有效地使用了索引 (range?和?ref)。沒有出現?Using temporary?或?Using filesort?等危險信號。

5. 總結與最佳實踐

1. 關注核心列：優先查看?type,?key,?rows,?Extra?列。

2. 索引是王道：目標是讓?type?達到?range?級別以上，避免出現?ALL（全表掃描）。

3. 警惕壞信號：在?Extra?列中，出現?Using temporary?和?Using filesort?通常是需要優化的信號，尤其是在大表查詢中。

4. 覆蓋索引：努力讓?Extra?列出現?Using index，這能極大提升性能。

5. 聯表查詢：確保連接條件（ON?子句）和被驅動表（第二張表）的?WHERE?子句上有索引。EXPLAIN?結果中，第一張表是驅動表。

6. 不要迷信估計：rows?列是基于統計信息的估計值，有時可能不準確。可以用?ANALYZE TABLE table_name;?來更新統計信息。

7. 使用 JSON 格式：對于復雜查詢，使用?EXPLAIN FORMAT=JSON?可以獲取更詳盡的分析信息，包括成本估算。