前言

在數據庫性能調優中，SQL 查詢的執行效率是影響系統整體性能的關鍵因素之一。MySQL 提供了強大的工具——EXPLAIN 語句，幫助開發者和數據庫管理員深入分析查詢的執行計劃，從而發現潛在的性能瓶頸并進行針對性優化。

EXPLAIN 語句能夠模擬 MySQL 優化器的執行過程，返回查詢的詳細執行計劃，包括表的訪問順序、索引的使用情況、連接類型、掃描行數等關鍵信息。通過理解 EXPLAIN 的輸出，開發者可以快速定位低效查詢的問題所在，例如全表掃描、缺少索引、臨時表或文件排序等，并采取相應的優化措施。

本文將詳細介紹 EXPLAIN 的基本用法、輸出字段的含義，并通過實際案例演示如何利用 EXPLAIN 分析和優化 SQL 查詢。

?

?

一、關于EXPLAIN語句

1.1 簡介

EXPLAIN 是 MySQL 提供的用于分析 SQL 查詢執行計劃的工具。它通過在 SELECT 語句前添加 EXPLAIN 關鍵字，使 MySQL 返回查詢的執行計劃，而不是實際執行查詢。執行計劃描述了 MySQL 如何訪問表、如何使用索引以及如何連接表等信息。

EXPLAIN 的主要作用包括：

• 分析查詢性能：識別慢查詢的根源，例如全表掃描或索引未命中。
• 驗證索引有效性：確認是否正確使用了索引，或者是否需要添加新的索引。
• 優化查詢結構：調整查詢語句或表結構以提高執行效率。

?

1.2 語法

EXPLAIN 的基本語法如下：

EXPLAIN [EXTENDED] [FORMAT = {TRADITIONAL | JSON}] SELECT ...;  

• EXTENDED：擴展輸出，顯示更多信息（如優化后的查詢語句）。
• FORMAT = JSON：以 JSON 格式返回結果，便于解析和調試。

示例：

在select前加explain關鍵字，MySQL會返回該查詢的執行計劃而不是執行這條SQL

mysql> explain select * from student where id=1;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | student | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
1 row in set

?

?

?

二、Explain列的含義

EXPLAIN 的輸出結果包含多個字段，每個字段提供了不同的信息。以下是關鍵字段的詳細說明：

2.1 概覽

以下是 MySQL ?EXPLAIN?? 輸出列的詳細說明，包括每列的定義、示例值、優化目標及判斷標準，幫助你深入理解查詢執行計劃：

列名	作用描述	優化目標（好的輸出特征）
id	查詢的執行順序標識符	子查詢或復雜查詢時，id 值高的先執行
select_type	查詢類型（簡單查詢、子查詢、UNION 等）	?SIMPLE? 或 PRIMARY? 表示簡單或主查詢
table	當前操作的表名（包括臨時表或派生表）	表名清晰，避免過多 <derived>? 或 <union>?
partitions	匹配的分區（若表有分區）	分區裁剪合理，避免全分區掃描
type	數據訪問方式（關鍵性能指標）	?const?、eq_ref?、ref?、range? 優于 ALL?
possible_keys	可能用到的索引	包含實際使用的索引
key	實際使用的索引	明確顯示有效索引名，非 NULL?
key_len	索引使用的字節數	與索引字段長度匹配，避免未完全使用索引
ref	與索引比較的列或常量	?const? 或關聯字段，避免 NULL?
rows	預估掃描的行數（估算值）	數值越小越好
filtered	查詢條件過濾后剩余行的百分比	百分比高（接近 100%）
Extra	額外執行信息（關鍵優化提示）	出現 Using index?，避免 Using filesort?

?

2.2 每列詳細說明及優化建議

1. id

• 含義：查詢的標識符，表示查詢中 SELECT? 子句的執行順序。
  查詢的序列號，標識執行順序。相同 id? 按從上到下執行；不同 id? 時，值大的先執行（如子查詢）。

• 示例：

  EXPLAIN SELECT * FROM (SELECT * FROM t1) AS t_derived JOIN t2 ON t1.id = t2.id;
  

  • id=1?：派生表 t_derived?（子查詢）。
  • id=1?：主查詢 t2?。

• 優化目標：
  避免多層嵌套子查詢（id? 過多），減少復雜查詢。

• 單一查詢（無子查詢或 UNION?）時，id? 為 1?。

• 復雜查詢中，id? 的層級清晰，避免嵌套過深。

?

2. select_type

• 含義：查詢的類型，描述查詢的復雜度。

• |常見值及優化建議：|||

  值	說明	理想情況
  SIMPLE	簡單查詢，不包含子查詢或 UNION?。	最佳，避免復雜嵌套。
  PRIMARY	最外層查詢。	正常，需關注其依賴的子查詢。
  SUBQUERY	子查詢中的第一個 SELECT?。	盡量避免，可考慮改寫為連接查詢。
  DEPENDENT SUBQUERY	子查詢依賴外部查詢結果。	高風險，可能導致性能下降。
  UNION	?UNION? 中的第二個或后續查詢。	正常，需注意 UNION? 結果集。
  UNION RESULT	?UNION? 的結果集。	正常，需檢查是否需要額外處理。
  DERIVED	派生表（FROM? 子句中的子查詢）。	需檢查派生表的性能。
  MATERIALIZED	物化子查詢（MySQL 8.0+）。	正常，但需確認物化效果。

?

3. table

• 含義：當前查詢涉及的表名。

• 理想輸出：

  • 表名明確，避免派生表（如 <derivedN>?）或臨時表（如 <union1,2>?）。
  • 若出現派生表，需檢查子查詢是否可優化為連接查詢。

?

4. partitions

• 含義：查詢涉及的分區（如果表是分區表）。

  表示查詢涉及的分區情況。當表是分區表時，這個列會顯示匹配的分區。例如，一個表按照日期字段進行分區，查詢中指定了日期范圍，那么 partitions 列就會顯示涉及到的分區編號或者分區名稱。

• 理想輸出：

  • 分區表中僅掃描相關分區（如 p1?），而非全表掃描。

  • 若為 NULL?，表示表未分區或未使用分區。

    如果表是分區表，希望 partitions 列顯示的分區范圍盡量小。這樣可以減少查詢需要掃描的數據量，提高查詢效率。例如，如果一個分區表有 100 個分區，而查詢只涉及到其中的 1 - 2 個分區，這就是比較理想的輸出。

?

5. type

• 含義：連接類型（訪問方法），反映 MySQL 如何查找表中的行。

• 性能排序（從優到劣） ：

  1. system：表僅一行（系統表），是 const? 的特例。
  2. const：通過主鍵或唯一索引等值查詢，最多匹配一行。
  3. eq_ref：使用主鍵或唯一索引進行等值連接（如 JOIN?）。
  4. ref：使用非唯一索引進行等值查詢。
  5. range：索引范圍查詢（如 BETWEEN?、>?、<?）。
  6. index：全索引掃描（比全表掃描快）。
  7. ALL：全表掃描（最差）。

• 優化建議：

  • 目標是達到 const?、eq_ref? 或 ref?。
  • 避免 ALL?，需添加索引或優化查詢條件。

?

6. possible_keys

• 含義：可能使用的索引（候選索引）。

• 理想輸出：

  • 顯示多個候選索引（說明索引設計合理）。

  • 若為 NULL?，表示無可用索引，需添加索引。

    列出與查詢條件相關的索引。

?

7. key

• 含義：實際使用的索引。如果 key? 為 NULL?，表示沒有使用索引，可能是全表掃描。

• 理想輸出：

  • 明確顯示使用的索引（如 idx_name?）。
  • 若為 NULL?，表示未使用索引，需檢查 possible_keys? 并優化索引。
  • 顯示與 possible_keys? 中相同的索引，說明 MySQL 選擇了合適的索引。

?

8. key_len

• 含義：使用的索引長度（字節數）。

• 理想輸出：

  • 值越小越好（表示使用的索引列越少或數據類型更緊湊）。
  • 例如，VARCHAR(100)? 使用 utf8mb4? 編碼時，最大占用 400? 字節。

?

9. ref

• 含義：顯示索引的哪一列被使用，以及與之比較的值（常量或列名）。

  • 顯示哪些列或常量被用于查找索引列上的值。常見值包括：

  const?：使用常量值。

  表的列名：使用其他表的列進行比較。

• func?：使用函數結果。

• 理想輸出：

  • 顯示具體的列名或常量（如 const?），表明索引有效。
  • 若為 func? 或 NULL?，可能表示索引未正確使用。
  • 顯示具體的列名或常量，表明索引被有效利用。

?

10. rows（估計掃描行數）

• 含義：MySQL 估計需要掃描的行數。

• 理想輸出：

  • 值越小越好（表示過濾條件越精確）。例如，如果一個查詢估算只需要檢查 10 行就可以得到結果，這比估算檢查 10000 行要好得多。這表明查詢能夠快速定位到所需的數據行。
  • 若值過大（如 100000?），需優化索引或查詢條件。

?

11. filtered

• 含義：表示查詢條件過濾的行百分比（MySQL 5.7+）。該值表示查詢掃描的行中有多少被篩選掉，值的范圍是 0 到 100。

  • 表示在存儲引擎返回的行中，經過 MySQL 服務器層過濾后，實際滿足查詢條件的行的比例。它是基于表統計信息和索引統計信息的一個估算值。

• 理想輸出：

  • 值越高越好（如 100%? 表示無過濾條件）。
  • 若值較低（如 10%?），說明查詢條件未充分利用索引。
  • filtered 的值應該盡可能高。例如，如果 filtered 的值是 90%，意味著存儲引擎返回的行中有 90% 的行滿足查詢條件，這比 filtered 值為 10% 的情況要好，因為減少了不必要的數據處理。

?

12. Extra

• 含義：額外信息，提供查詢執行的附加說明，幫助診斷查詢執行的細節
• |常見值及優化建議：|||

  值	說明	優化建議
  Using index	使用覆蓋索引（查詢列全部命中索引）。	無需回表，性能最佳。
  Using where	使用 WHERE? 條件過濾數據。	正常，但需檢查過濾條件效率。
  Using temporary	需要創建臨時表（如 ORDER BY? 和 GROUP BY? 一起使用）。	避免，優化查詢或添加索引。
  Using filesort	需要額外排序操作（如 ORDER BY? 未使用索引）。	避免，優化排序字段索引。
  Distinct	優化了 DISTINCT? 查詢。	正常，無需額外優化。
  Range checked for each record	未找到合適索引，需逐行檢查。	添加合適索引。

?

• 良好輸出：希望出現像 “Using index” 這樣的提示，這表明查詢效率較高。盡量避免出現 “Using temporary” 和 “Using filesort”，因為它們表示需要額外的資源開銷來處理查詢，如臨時表和文件排序，這可能會降低查詢性能。

?

?

三、優化建議及示例

3.1 優化建議

1. 關注 ?type?? 列：確保查詢達到 const?、eq_ref? 或 ref? 級別，避免 ALL?。
2. 優化 ?Extra?? 列：避免 Using filesort? 和 Using temporary?。
3. 分析 ?key?? 和 ?possible_keys??：確認是否使用了預期的索引。
4. 減少掃描行數：通過索引或優化查詢條件降低 rows? 值。
5. 檢查 ?filtered??：確保過濾條件有效，提高查詢效率。

?

3.2 優化示例

全表掃描

場景：全表掃描（type=ALL?）

• 問題 SQL：

  EXPLAIN SELECT * FROM users WHERE phone = '123456789';
  

• 輸出：type=ALL?，key=NULL?。

• 優化：為 phone? 字段添加索引：

  ALTER TABLE users ADD INDEX idx_phone(phone);
  

• 優化后輸出：type=ref?，key=idx_phone?，rows=1?。

?

?

多表連接優化（Using join buffer）

問題描述

多表連接時出現 Using join buffer?，性能低下。

原SQL

SELECT u.name, o.order_no 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.age > 25 AND o.status = 'completed';

EXPLAIN 分析

EXPLAIN SELECT u.name, o.order_no FROM users u JOIN orders o ON u.id = o.user_id WHERE u.age > 25 AND o.status = 'completed';

輸出結果：

id | select_type | table | type | possible_keys | key        | rows | Extra
---|-------------|-------|------|---------------|------------|------|-------------------
1  | SIMPLE      | u     | ALL  | idx_age       | NULL       | 1000 | Using where
1  | SIMPLE      | o     | ref  | idx_user_id   | idx_user_id| 500  | Using where; Using join buffer (Block Nested Loop)

問題診斷

• users 表 type=ALL：未使用索引，全表掃描。
• orders 表 Using join buffer：連接時未使用索引，性能差。

?

優化方案

1. 為 ?users.age?? 創建索引：

   ALTER TABLE users ADD INDEX idx_age (age);
   

2. 為 ?orders.user_id?? 和 ?orders.status?? 創建索引：

   ALTER TABLE orders ADD INDEX idx_user_id_status (user_id, status);
   

3. 調整查詢：

   SELECT u.name, o.order_no 
   FROM users u 
   JOIN orders o ON u.id = o.user_id 
   WHERE u.age > 25 AND o.status = 'completed';
   

4. 驗證優化效果：

   EXPLAIN SELECT u.name, o.order_no FROM users u JOIN orders o ON u.id = o.user_id WHERE u.age > 25 AND o.status = 'completed';
   

   優化后輸出：

   id | select_type | table | type | possible_keys         | key                  | rows | Extra
   ---|-------------|-------|------|-----------------------|----------------------|------|---------
   1  | SIMPLE      | u     | range| idx_age               | idx_age              | 500  | Using where
   1  | SIMPLE      | o     | ref  | idx_user_id_status    | idx_user_id_status   | 200  | Using where
   

效果

• users 表 type=range：使用索引范圍掃描。
• orders 表 Using join buffer 消失：連接直接通過索引完成。

?

結束語

EXPLAIN?語句是MySQL查詢優化的核心工具，如同數據庫工程師的"聽診器"。通過本文的詳細解析，相信您已經掌握了各輸出列的精髓。但需要強調的是，真正的優化功力需要在實踐中不斷積累。建議每次執行重要查詢時養成查看執行計劃的習慣，結合業務場景靈活運用索引策略、查詢重寫等手段。記住：優秀的數據庫性能不是偶然，而是源于對每個執行細節的精心雕琢。

?

?