MySQL 中的 EXPLAIN 命令

季风时节發表於2024-06-06
MySqlAI

在 MySQL 中，EXPLAIN 命令用於顯示 MySQL 最佳化器如何執行 SQL 查詢的詳細資訊。它是一個強大的工具，用於分析查詢的執行計劃，以幫助最佳化和除錯查詢效能。

使用方法

要使用 EXPLAIN 命令，只需在要分析的 SQL 查詢前加上 EXPLAIN 關鍵字。例如：

EXPLAIN SELECT * FROM users WHERE age > 30;

輸出說明

EXPLAIN 命令的輸出是一個表格，其中每一行表示查詢中的一個步驟。以下是 EXPLAIN 輸出的常見列及其說明：

  1. id: 查詢中每個選擇表的識別符號。id 值相同的行表示這些行是按順序執行的；id 不同的行表示這些行是巢狀子查詢或聯合查詢。
  2. select_type: 查詢型別，可以是 SIMPLE（簡單查詢，不包括子查詢或聯合查詢），PRIMARY（主查詢），UNION（聯合中的第二個或隨後的查詢），DEPENDENT UNION（依賴於外部查詢的聯合中的第二個或隨後的查詢），SUBQUERY（子查詢），DEPENDENT SUBQUERY（依賴於外部查詢的子查詢）等。
  3. table: 正在訪問的表的名稱。
  4. partitions: 查詢中所使用的分割槽資訊。
  5. type: 聯接型別，表示MySQL如何查詢表中的行，範圍從ALL（全表掃描）到const（對主鍵或唯一索引的常量查詢）。常見的型別有：
    • ALL: 全表掃描。
    • index: 全索引掃描。
    • range: 索引範圍掃描。
    • ref: 非唯一索引掃描。
    • eq_ref: 唯一索引掃描。
    • const: 常量。
    • system: 系統表。
  6. possible_keys: 查詢中可能使用的索引。
  7. key: 實際使用的索引。
  8. key_len: 使用的索引的長度。
  9. ref: 使用的索引的哪一列或常數。
  10. rows: MySQL 估計要讀取的行數。
  11. filtered: 估計表的行數被查詢條件過濾的百分比。
  12. Extra: 附加資訊，如 Using where（使用了WHERE子句），Using index（使用了索引覆蓋），Using temporary（使用了臨時表），Using filesort（使用了檔案排序）等。

示例

下面是一個示例查詢及其 EXPLAIN 輸出：

EXPLAIN SELECT * FROM users WHERE age > 30;

可能的輸出如下：

id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE users ALL NULL NULL NULL NULL 1000 50.00 Using where

分析輸出

  • id: 1（這是一個簡單查詢）
  • select_type: SIMPLE（簡單查詢，沒有子查詢）
  • table: users（正在查詢的表）
  • type: ALL（全表掃描，這表明查詢效能可能不佳）
  • possible_keys: NULL（沒有可能使用的索引）
  • key: NULL（沒有實際使用的索引）
  • key_len: NULL（沒有索引長度）
  • ref: NULL（沒有引用）
  • rows: 1000（估計掃描 1000 行）
  • filtered: 50.00（估計過濾掉 50% 的行）
  • Extra: Using where（使用了WHERE子句）

結論

EXPLAIN 是最佳化查詢的關鍵工具。透過分析 EXPLAIN 的輸出，你可以識別效能瓶頸，並採取相應措施，如新增索引、最佳化查詢條件或重新設計表結構等，從而提升查詢效能。

補充：

在 MySQL 中，當使用 EXPLAIN 命令分析查詢執行計劃時，有時會看到 Select tables optimized away 出現在 Extra 列中。這個資訊表明 MySQL 最佳化器已經確定可以透過某些最佳化手段避免實際掃描表，從而直接返回結果。這通常發生在特定型別的聚合查詢中，例如使用 COUNT()、SUM()、MIN()、MAX() 等函式，並且查詢的結果可以在最佳化階段直接計算出來。

示例情況

一個典型的例子是當你在一個表上執行 COUNT(*) 查詢且沒有 WHERE 子句時，如果 MySQL 知道這個表的行數，它可以直接返回結果，而無需實際掃描表。

EXPLAIN SELECT COUNT(*) FROM users;

示例輸出

+----+-------------+-------+------+---------------+------+---------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra                    |
+----+-------------+-------+------+---------------+------+---------+------+----------+--------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL     | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+----------+--------------------------+

解釋

  • id: 1（查詢識別符號）
  • select_type: SIMPLE（簡單查詢，沒有子查詢）
  • table: NULL（沒有實際訪問的表）
  • type: NULL（沒有訪問型別，因為沒有掃描表）
  • possible_keys: NULL（沒有可能使用的索引）
  • key: NULL（沒有使用的索引）
  • key_len: NULL（沒有索引長度）
  • ref: NULL（沒有引用）
  • rows: NULL（沒有估計的行數）
  • Extra: Select tables optimized away（選擇的表已被最佳化掉）

發生這種情況的條件

  1. 聚合函式：當查詢使用聚合函式且沒有 GROUP BY 子句，並且 MySQL 可以從後設資料中直接計算結果。例如 COUNT(*)。
  2. 常量表示式：當查詢包含常量表示式，且 MySQL 能在最佳化階段計算出結果，而不需要訪問任何表。
  3. 索引覆蓋：在某些情況下，MySQL 可以利用索引覆蓋特性直接從索引中獲取聚合結果，而無需掃描實際的資料行。

最佳化的益處

  • 效能提升：避免了全表掃描或大範圍掃描，大大減少了 I/O 操作，提高了查詢效能。
  • 資源節約：減少了 CPU 和記憶體的使用，因為最佳化器直接返回結果而不是逐行掃描和計算。

實際應用

假設我們有一個名為 users 的表，包含一些使用者資料。以下是幾個可能觸發 Select tables optimized away 的查詢：

-- 計算使用者總數
EXPLAIN SELECT COUNT(*) FROM users;

-- 計算使用者的總年齡（假設表中有age列，且不為空）
EXPLAIN SELECT SUM(age) FROM users;

在這些例子中，如果 MySQL 可以直接從表的後設資料或索引中計算出結果，它就會在 Extra 列中顯示 Select tables optimized away。

總結

Select tables optimized away 是一個最佳化器資訊，表明 MySQL 已經成功地避免了不必要的表掃描，直接計算並返回了查詢結果。這種最佳化有助於顯著提升查詢效能，特別是在處理大量資料時。透過了解這一資訊，資料庫管理員和開發人員可以更好地理解和調優查詢，提高資料庫的整體效能。

相關文章