MySQL EXPLAIN 指令詳解學習

MySQL EXPLAIN 指令詳解

MySQL的EXPLAIN指令用於SQL語句的查詢執行計畫(QEP)。這條指令的輸出結果能夠讓我們了解MySQL 最佳化器是如何執行
SQL 語句的。這條命令並沒有提供任何調整建議，但它能夠提供重要的資訊來幫助你做出調優決策。

1 語法
MySQL 的EXPLAIN 語法可以運行在SELECT 語句或特定表上。如果作用在表上，那麼此指令等同於DESC 表指令。 UPDATE
和DELETE 指令也需要進行效能改進，當這些指令不是直接在表格的主碼上執行時，為了確保最最佳化的索引使用率，需要把它們改
寫成SELECT 語句(以便對它們執行EXPLAIN 指令) 。請看下面的範例：

UPDATE table1
SET col1 = X, col2 = Y
WHERE id1 = 9
AND dt >= '2010-01-01';

這個UPDATE語句可以被重寫成為下面這樣的SELECT語句：

SELECT col1, col2
FROM table1
WHERE id1 = 9
AND dt >= '2010-01-01';

。對dml語句進行explain分析操作的.

MySQL 優化器是基於開銷來工作的，它並不提供任何的QEP的位置。這表示QEP 是在每個SQL 語句執行的時候動態地計

算出來的。在MySQL 預存程序中的SQL 語句也是在每次執行時計算QEP 的。預存程序快取僅解析查詢樹。

2 各列詳解

MySQL EXPLAIN指令能夠為SQL語句中的每個表產生以下資訊：

mysql> EXPLAIN SELECT * FROM inventory WHERE item_id = 16102176\G;
  ********************* 1. row ***********************
  id: 1
  select_type: SIMPLE
  table: inventory
  type: ALL
  possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
  rows: 787338
  Extra: Using where

EP 無掃描

EP）並且處理了大量的行來滿足查詢。對同樣一條SELECT 語句，一個最佳化的QEP 如下所示：

  ********************* 1. row ***********************
  id: 1
  select_type: SIMPLE
  table: inventory
  type: ref
  possible_keys: item_id
  key: item_id
  key_len: 4
  ref: const
  rows: 1
  Extra:

在這個QEP 中，我們看到使用了一個索引，並且估計只有一行資料會被取得。

QEP 中每個行的所有列表如下所示：

 id select_type
 table partitions(這一列只有在EXPLAIN PARTIibleS 語法中才會出現key IN PARTIibleTIONS 語法
 key_len
 ref
 rows
 filtered(這一列只有在EXPLAINED EXTENDED 語法中才會出現)
 Extra

這些欄位展示了SELECT 語句對每個表格的QEP。一個表可能和一個實體模式表或在SQL 執行時產生的內部臨時表(例如從子查詢或合併操作會產生內部臨時表)相關聯。
可以參考MySQL Reference Manual 以獲得更多資訊：http://www.php.cn/。

2.1 key

 key 欄位指出最佳化器選擇使用的索引。一般來說SQL 查詢中的每個表都只使用一個索引。也存在索引合併的少數例外情況，如給定表上用到了兩個或更多索引。

 下方是QEP 中key 欄位的範例：
 key: item_id key: NULL key: first, last
 SHOW CREATE TABLE

指令是最簡單的檢視表格和索引列細節的方式。和key 欄位相關的欄位也包括possible_keys、rows 以及key_len。


2.2 ROWS

 rows 欄位提供了試圖分析所有存在於累積結果集中的行數目的MySQL 最佳化器估計值。 QEP 很容易描述這個很困難的統計量。  查詢中總的讀取操作數量是基於合併之前行的每一行的rows 值的連續累積而得出的。這是一種巢狀行演算法。

  以連接兩張表格的QEP 為例。透過id=1 這個條件找到的第一行的rows 值為1，等於對第一個表格做了一次讀取操作。第二行是 透過id=2 找到的，rows 的值為5。這等於有5 次讀取操作符合目前1 的累積量。參考兩個表，讀取操作的總數目是6。在另一個QEP
 中，第一rows 的值是5，第二rows 的值是1。這等於第一個表格有5 次讀取操作，對5個累積量中每個都有一個讀取操作。因此兩個表
 總的讀取操作的次數是10(5+5)次。
 
 最好的估計值是1，一般來說這種情況發生在當尋找的行在表中可以透過主鍵或唯一鍵找到的時候。
 在下面的QEP 中，外面的巢狀循環可以透過id=1 來找到，其估計的物理行數是1。第二個循環處理了10行。
 

 ********************* 1. row ***********************
 id: 1
 select_type: SIMPLE
 table: p
 type: const
 possible_keys: PRIMARY
 key: PRIMARY
 key_len: 4
 ref: const
 rows: 1
 Extra:
 ********************* 2. row ***********************
 id: 1
 select_type: SIMPLE
 table: c
 type: ref
 possible_keys: parent_id
 key: parent_id
 key_len: 4
 ref: const
 rows: 10
 Extra:

 可以使用SHOW STATUS 指令來檢視實際的行操作。這個指令可以提供最佳的確認物理行操作的方式。請看下面的範例：

 mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | Handler_read_first    | 0     |
  | Handler_read_key      | 0     | 
  | Handler_read_last     | 0     |
  | Handler_read_next     | 0     |
  | Handler_read_prev     | 0     |
  | Handler_read_rnd      | 0     |
  | Handler_read_rnd_next | 11    |
  +-----------------------+-------+
  7 rows in set (0.00 sec)

  
 在下一個QEP 中，透過id=1 找到的外層嵌套循環估計有160行。第二個循環估計有1 行。

 ********************* 1. row ***********************
  id: 1
  select_type: SIMPLE
  table: p
  type: ALL
  possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
  rows: 160
  Extra:
 ********************* 2. row ***********************
  id: 1
  select type: SIMPLE
  table: c
  type: ref
  possible_keys: PRIMARY,parent_id
  key: parent_id
  key_len: 4
  ref: test.p.parent_id
  rows: 1
  Extra: Using where

 
 透過SHOW STATUS 指令可以查看實際的行操作，該指令顯示物理讀取操作數量大幅增加。請看下面的範例：

 mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
 +--------------------------------------+---------+
 | Variable_name | Value |
 +--------------------------------------+---------+
 | Handler_read_first | 1 |
 | Handler_read_key | 164 |
 | Handler_read_last | 0 |
 | Handler_read_next | 107 |
 | Handler_read_prev | 0 |
 | Handler_read_rnd | 0 |
 | Handler_read_rnd_next | 161 |
 +--------------------------------------+---------+
 相关的QEP 列还包括key列。

 
 2.3 possible_keys
 possible_keys 列指出优化器为查询选定的索引。
 一个会列出大量可能的索引(例如多于3 个)的QEP 意味着备选索引数量太多了，同时也可能提示存在一个无效的单列索引。
 可以用第2 章详细介绍过的SHOW INDEXES 命令来检查索引是否有效且是否具有合适的基数。
 为查询确定QEP 的速度也会影响到查询的性能。如果发现有大量的可能的索引，则意味着这些索引没有被使用到。
 相关的QEP 列还包括key 列。
 
 2.4 key_len
 key_len 列定义了用于SQL 语句的连接条件的键的长度。此列值对于确认索引的有效性以及多列索引中用到的列的数目很重要。
 此列的一些示例值如下所示：
 
 此列的一些示例值如下所示：
 key_len: 4 // INT NOT NULL
 key_len: 5 // INT NULL
 key_len: 30 // CHAR(30) NOT NULL
 key_len: 32 // VARCHAR(30) NOT NULL
 key_len: 92 // VARCHAR(30) NULL CHARSET=utf8
 
 从这些示例中可以看出，是否可以为空、可变长度的列以及key_len 列的值只和用在连接和WHERE 条件中的索引的列
 有关。索引中的其他列会在ORDER BY 或者GROUP BY 语句中被用到。下面这个来自于著名的开源博客软件WordPress 的表展示了
 如何以最佳方式使用带有定义好的表索引的SQL 语句：

 CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_status` varchar(20) NOT NULL DEFAULT 'publish' ,
  `post_type` varchar(20) NOT NULL DEFAULT 'post',
  PRIMARY KEY (`ID`),
  KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`)
 ) DEFAULT CHARSET=utf8
 
  CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_status` varchar(20) NOT NULL DEFAULT 'publish' ,
  `post_type` varchar(20) NOT NULL DEFAULT 'post',
  PRIMARY KEY (`ID`),
  KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`)
 ) DEFAULT CHARSET=utf8

 这个表的索引包括post_type、post_status、post_date 以及ID列。下面是一个演示索引列用法的SQL 查询：
 EXPLAIN SELECT ID, post_title FROM wp_posts WHERE post_type='post' AND post_date > '2010-06-01';
 
 这个查询的QEP 返回的key_len 是62。这说明只有post_type列上的索引用到了(因为(20×3)+2=62)。尽管查询在WHERE 语句
 中使用了post_type 和post_date 列，但只有post_type 部分被用到了。其他索引没有被使用的原因是MySQL 只能使用定义索引的
 最左边部分。为了更好地利用这个索引，可以修改这个查询来调整索引的列。请看下面的示例：

 mysql> EXPLAIN SELECT ID, post_title
 -> FROM wp_posts
 -> WHERE post_type='post'
 -> AND post_status='publish'
 -> AND post_date > '2010-06-01';

 
 在SELECT查询的添加一个post_status 列的限制条件后，QEP显示key_len 的值为132，这意味着post_type、post_status、post_date
 三列(62+62+8，(20×3)+2，(20×3)+2，8)都被用到了。此外，这个索引的主码列ID 的定义是使用MyISAM 存储索
 引的遗留痕迹。当使用InnoDB 存储引擎时，在非主码索引中包含主码列是多余的，这可以从key_len 的用法看出来。
 相关的QEP 列还包括带有Using index 值的Extra 列。
 
 2.5 table
 table 列是EXPLAIN 命令输出结果中的一个单独行的唯一标识符。这个值可能是表名、表的别名或者一个为查询产生临时表
 的标识符，如派生表、子查询或集合。下面是QEP 中table 列的一些示例：
 table: item
 table: 6db98b0ebf978836f3b742968b1383c4
 table: 3d79052a4b39814111818a40e6c7009c
 表中N 和M 的值参考了另一个符合id 列值的table 行。相关的QEP 列还有select_type

 2.6 select_type
 select_type 列提供了各种表示table 列引用的使用方式的类型。最常见的值包括SIMPLE、PRIMARY、DERIVED 和UNION。其他可能
 的值还有UNION RESULT、DEPENDENT SUBQUERY、DEPENDENT UNION、UNCACHEABLE UNION 以及UNCACHEABLE QUERY。

 1. SIMPLE
 对于不包含子查询和其他复杂语法的简单查询，这是一个常 见的类型。

 2. PRIMARY
 这是为更复杂的查询而创建的首要表(也就是最外层的表)。这个类型通常可以在DERIVED 和UNION 类型混合使用时见到。
 
 3. DERIVED
 当一个表不是一个物理表时，那么就被叫做DERIVED。下面的SQL 语句给出了一个QEP 中DERIVED select-type 类型的
 示例：
 mysql> EXPLAIN SELECT MAX(id)
 -> FROM (SELECT id FROM users WHERE first = 'west') c;

 4. DEPENDENT SUBQUERY
 這個select-type 值是為使用子查詢而定義的。下面的SQL語句提供了這個值：
 mysql> EXPLAIN SELECT p.*
 -> FROM parent p
 -> WHERE p.id NOT IN (SELECT c.parent_id FROM child c);
這是UNION 語句其中的一個SQL 元素。
 6. UNION RESULT 這是一系列定義在UNION 陳述式的表格所回傳的結果。當select_type 為這個值時，常常可以看到table 的值是， 這表示符合的id 行是這個集合的一部分。下面的SQL產生了一個UNION和UNION RESULT select-type：
 mysql> EXPLAIN SELECT p.* FROM parent p WHERE p.val
 LIKE 'a%'
 -> UNION
p-> SELECT p. WROM parent
p. WROM. p.id > 5;


 2.7  partitions

  partitions 欄位代表給定表所使用的分區。這一列只會在EXPLAIN  PARTITIONS 語句中出現。

 2.8 Extra

 Extra 欄位提供了一系列不同種類的MySQL 最佳化器路徑的 額外資訊。 Extra 欄位可以包含多個值，可以有許多不同的取值，並且 且這些值仍在隨著MySQL 新版本的發布而進一步增加。下面給
 出常用值的列表。你可以從下面的地址找到更全面的值的列表：
 http://www.php.cn/。

 
1. Using where

 這個值表示查詢使用了where 語句來處理結果－例如執行 全表掃描。如果也用到了索引，那麼行的限制條件是透過取得必 要的資料之後處理讀取緩衝區來實現的。


 2. Using temporary

 這個值表示使用了內部臨時(基於內存的)表。一個查詢可能 用到多個臨時表。有很多原因都會導致MySQL 在執行查詢期間 建立臨時表。兩個常見的原因是在來自不同表格的欄位上使用了
 DISTINCT，或使用了不同的ORDER BY 和GROUP BY 欄位。
 想了解更多內容可以上http://www.php.cn/
 of_query_execution_and_use_of_temp_tables。
 可以強制指定一個臨時表使用基於磁碟的MyISAM 儲存引
 擎。這樣做的原因主要有二：
  內部臨時表佔用的空間超過min(tmp_table_size，max_
 heap_table_size)系統變數的限制
  使用了TEXT/BLOB 列
BY 語句的結果。這可能是CPU 密集型的過程。
 可以透過選擇合適的索引來改善效能，並用索引來為查詢結果排序。詳細過程請參考第4 章。

 

4. Using index 這個值重點強調了只需要使用索引就可以滿足查詢表的要求，不需要直接存取表資料。請參考第5 章的詳細範例來理解這
 個值。

 5. Using join buffer 這個值強調了在取得連接條件時沒有使用索引，並且需要連接緩衝區來儲存中間結果。
 如果出現了這個值，那麼應該注意，根據查詢的具體情況可能需要添加索引來改進效能。

 6. Impossible where 這個值強調了where 語句會導致沒有符合條件的行。請看下面的範例：
 mysql> EXPLAIN SELECT * FROM user WHERE 1=2;

 7. Select tables optimized away 這個值意味著只透過使用索引函數，最佳化器可能只從聚合結果中傳回。請看下面的範例：

 8. Distinct

 這個值意味著MySQL 在找到第一個符合的行之後就會停止搜尋其他行。

 9. Index merges

 當MySQL 決定要在一個給定的表上使用超過一個索引的時候，就會出現以下格式中的一個，詳細說明使用的索引以及合併的類型。   Using sort_union(...)
  Using union(...)

  Using intersect(...)

 2.9 id

.

 2.10 ref

 ref 欄位可以用來識別那些用來進行索引比較的欄位或常數。

 2.11 filtered
 filtered 列给出了一个百分比的值，这个百分比值和rows 列的值一起使用，可以估计出那些将要和QEP 中的前一个表进行连
 接的行的数目。前一个表就是指id 列的值比当前表的id 小的表。这一列只有在EXPLAIN EXTENDED 语句中才会出现。

 2.12 type
 type 列代表QEP 中指定的表使用的连接方式。下面是最常用的几种连接方式：
  const 当这个表最多只有一行匹配的行时出现system 这是const 的特例，当表只有一个row 时会出现
  eq_ref 这个值表示有一行是为了每个之前确定的表而读取的
  ref 这个值表示所有具有匹配的索引值的行都被用到
  range 这个值表示所有符合一个给定范围值的索引行都被用到
  ALL 这个值表示需要一次全表扫描其他类型的值还有fulltext 、ref_or_null 、index_merge 、unique_subquery、index_subquery 以及index。
  想了解更多信息可以访问http://www.php.cn/。
 

 3 解释EXPLAIN 输出结果
 理解你的应用程序(包括技术和实现可能性)和优化SQL 语句同等重要。下面给出一个从父子关系中获取孤立的父辈记录的商
 业需求的例子。这个查询可以用三种不同的方式构造。尽管会产生相同的结果，但QEP 会显示三种不同的路径。

 mysql> EXPLAIN SELECT p.*
 -> FROM parent p
 -> WHERE p.id NOT IN (SELECT c.parent_id FROM child
 c)\G
 ********************* 1. row ***********************
 id: 1
 select type: PRIMARY
 table: p
 type: ALL
 possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 160
 Extra: Using where
 ********************* 2. row ***********************
 id: 2
 select_type: DEPENDENT SUBQUERY
 table: c
 type: index_subquery
 possible_keys: parent_id
 key: parent_id
 key_len: 4
 ref: func
 rows: 1
 Extra: Using index
 2 rows in set (0.00 sec)

 EXPLAIN SELECT p.* FROM parent p LEFT JOIN child c ON p.id = c.parent_id WHERE c.child_id IS NULL\G
 ********************* 1. row ***********************
 id: 1
 select_type: SIMPLE
 table: p
 type: ALL
 possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 160
 Extra:
 ********************* 2. row ***********************
 id: 1
 select_type: SIMPLE
 table: c
 type: ref
 possible_keys: parent_id
 key: parent_id
 key_len: 4
 ref: test.p.id
 rows: 1
 Extra: Using where; Using index; Not exists
 2 rows in set (0.00 sec)

 以上就是MySQL EXPLAIN 命令详解学习的内容，更多相关内容请关注PHP中文网（www.php.cn）！