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'; 在5.6.10版本裡面,是可以直接對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 這個QEP 顯示沒有使用任何索引(也就是全表掃描)並且處理了大量的行來滿足查詢。對同樣一條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 PARTITIONS 語法中才會出現) possible_keys key key_len ref rows filtered(這一列只有在EXPLAINED EXTENDED 語法中才會出現) Extra 這些列展示了SELECT 語句對每一個表的QEP。一個表可能和一個物理模式表或者在SQL 執行時生成的內部臨時表(例如從子查詢或者合並操作會產生內部臨時表)相關聯。 2.1 key key 列指出優化器選擇使用的索引。一般來說SQL 查詢中的每個表都僅使用一個索引。也存在索引合並的少數例外情況，如給定表上用到了兩個或者更多索引。 下面是QEP 中key 列的示例： key: item_id key: NULL key: first, last SHOW CREATE TABLE <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: <derivedN> table: <unionN,M> 表中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); 5. UNION 這是UNION 語句其中的一個SQL 元素。 6. UNION RESULT 這是一系列定義在UNION 語句中的表的返回結果。當select_type 為這個值時，經常可以看到table 的值是<unionN,M>， 這說明匹配的id 行是這個集合的一部分。下面的SQL產生了一個UNION和UNION RESULT select-type： mysql> EXPLAIN SELECT p.* FROM parent p WHERE p.val LIKE 'a%' -> UNION -> SELECT p.* FROM parent p WHERE p.id > 5; 2.7 partitions partitions 列代表給定表所使用的分區。這一列只會在EXPLAIN PARTITIONS 語句中出現。 2.8 Extra Extra 列提供了有關不同種類的MySQL 優化器路徑的一系列 額外信息。Extra 列可以包含多個值，可以有很多不同的取值，並 且這些值還在隨著MySQL 新版本的發布而進一步增加。下面給 出常用值的列表。你可以從下面的地址找到更全面的值的列表： http://dev.mysql.com/doc/refman/5.5/en/explain-output.html。 1. Using where 這個值表示查詢使用了where 語句來處理結果——例如執行 全表掃描。如果也用到了索引，那麼行的限制條件是通過獲取必 要的數據之後處理讀緩沖區來實現的。 2. Using temporary 這個值表示使用了內部臨時(基於內存的)表。一個查詢可能 用到多個臨時表。有很多原因都會導致MySQL 在執行查詢期間 創建臨時表。兩個常見的原因是在來自不同表的列上使用了 DISTINCT，或者使用了不同的ORDER BY 和GROUP BY 列。 可以強制指定一個臨時表使用基於磁盤的MyISAM 存儲引 擎。這樣做的原因主要有兩個： 內部臨時表占用的空間超過min(tmp_table_size，max_ heap_table_size)系統變量的限制 使用了TEXT/BLOB 列 3. Using filesort 這是ORDER 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 id 列是在QEP 中展示的表的連續引用。 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。 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)
