什么是MySQL EXPLAIN 命令

MySQL的EXPLAIN命令用于SQL語句的查詢執(zhí)行計(jì)劃(QEP)。這條命令的輸出結(jié)果能夠讓我們了解MySQL 優(yōu)化器是如何執(zhí)行SQL 語句的。這條命令并沒有提供任何調(diào)整建議，但它能夠提供重要的信息幫助你做出調(diào)優(yōu)決策。

1 語法
MySQL 的EXPLAIN 語法可以運(yùn)行在SELECT 語句或者特定表上。如果作用在表上，那么此命令等同于DESC 表命令。UPDATE和DELETE 命令也需要進(jìn)行性能改進(jìn)，當(dāng)這些命令不是直接在表的主碼上運(yùn)行時(shí)，為了確保最優(yōu)化的索引使用率，需要把它們改
寫成SELECT 語句(以便對(duì)它們執(zhí)行EXPLAIN 命令)。請(qǐng)看下面的示例：

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

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

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

在5.6.10版本里面,是可以直接對(duì)dml語句進(jìn)行explain分析操作的.

MySQL 優(yōu)化器是基于開銷來工作的，它并不提供任何的QEP的位置。這意味著QEP 是在每條SQL 語句執(zhí)行的時(shí)候動(dòng)態(tài)地計(jì)算出來的。在MySQL 存儲(chǔ)過程中的SQL 語句也是在每次執(zhí)行時(shí)計(jì)算QEP 的。存儲(chǔ)過程緩存僅僅解析查詢樹。

2 各列詳解
MySQL EXPLAIN命令能夠?yàn)镾QL語句中的每個(gè)表生成以下信息：

mysql>?EXPLAIN?SELECT?*?FROM?inventory?WHERE?item_id?=?16102176G;?? ??*********************?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??

??
這個(gè)QEP 顯示沒有使用任何索引(也就是全表掃描)并且處理了大量的行來滿足查詢。對(duì)同樣一條SELECT 語句，一個(gè)優(yōu)化過的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:??

在這個(gè)QEP 中，我們看到使用了一個(gè)索引，且估計(jì)只有一行數(shù)據(jù)將被獲取。

QEP 中每個(gè)行的所有列表如下所示：
? id
? select_type
? table
? partitions(這一列只有在EXPLAIN PARTITIONS 語法中才會(huì)出現(xiàn))
? possible_keys
? key
? key_len
? ref
? rows
? filtered(這一列只有在EXPLAINED EXTENDED 語法中才會(huì)出現(xiàn))
? Extra

這些列展示了SELECT 語句對(duì)每一個(gè)表的QEP。一個(gè)表可能和一個(gè)物理模式表或者在SQL 執(zhí)行時(shí)生成的內(nèi)部臨時(shí)表(例如從子查詢或者合并操作會(huì)產(chǎn)生內(nèi)部臨時(shí)表)相關(guān)聯(lián)。

2.1 key
?key 列指出優(yōu)化器選擇使用的索引。一般來說SQL 查詢中的每個(gè)表都僅使用一個(gè)索引。也存在索引合并的少數(shù)例外情況，如給定表上用到了兩個(gè)或者更多索引。
?下面是QEP 中key 列的示例：
?key: item_id
?key: NULL
?key: first, last
?SHOW CREATE TABLE

命令是最簡(jiǎn)單的查看表和索引列細(xì)節(jié)的方式。和key 列相關(guān)的列還包括possible_keys、rows 以及key_len。

2.2 ROWS
?rows 列提供了試圖分析所有存在于累計(jì)結(jié)果集中的行數(shù)目的MySQL 優(yōu)化器估計(jì)值。QEP 很容易描述這個(gè)很困難的統(tǒng)計(jì)量。
?查詢中總的讀操作數(shù)量是基于合并之前行的每一行的rows 值的連續(xù)積累而得出的。這是一種嵌套行算法。
?
?以連接兩個(gè)表的QEP 為例。通過id=1 這個(gè)條件找到的第一行的rows 值為1，這等于對(duì)第一個(gè)表做了一次讀操作。第二行是通過id=2 找到的，rows 的值為5。這等于有5 次讀操作符合當(dāng)前1 的積累量。參考兩個(gè)表，讀操作的總數(shù)目是6。在另一個(gè)QEP
?中，第一rows 的值是5，第二rows 的值是1。這等于第一個(gè)表有5 次讀操作，對(duì)5個(gè)積累量中每個(gè)都有一個(gè)讀操作。因此兩個(gè)表總的讀操作的次數(shù)是10(5+5)次。
?
?最好的估計(jì)值是1，一般來說這種情況發(fā)生在當(dāng)尋找的行在表中可以通過主鍵或者唯一鍵找到的時(shí)候。
?在下面的QEP 中，外面的嵌套循環(huán)可以通過id=1 來找到，其估計(jì)的物理行數(shù)是1。第二個(gè)循環(huán)處理了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 命令來查看實(shí)際的行操作。這個(gè)命令可以提供最佳的確認(rèn)物理行操作的方式。請(qǐng)看下面的示例：
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)??

??
?在下一個(gè)QEP 中，通過id=1 找到的外層嵌套循環(huán)估計(jì)有160行。第二個(gè)循環(huán)估計(jì)有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 命令可以查看實(shí)際的行操作，該命令表明物理讀操作數(shù)量大幅增加。請(qǐng)看下面的示例：
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?|?? +--------------------------------------+---------+?? 相關(guān)的QEP?列還包括key列。??

?
?2.3 possible_keys
?possible_keys 列指出優(yōu)化器為查詢選定的索引。
?一個(gè)會(huì)列出大量可能的索引(例如多于3 個(gè))的QEP 意味著備選索引數(shù)量太多了，同時(shí)也可能提示存在一個(gè)無效的單列索引。
?可以用第2 章詳細(xì)介紹過的SHOW INDEXES 命令來檢查索引是否有效且是否具有合適的基數(shù)。
?為查詢確定QEP 的速度也會(huì)影響到查詢的性能。如果發(fā)現(xiàn)有大量的可能的索引，則意味著這些索引沒有被使用到。
?相關(guān)的QEP 列還包括key 列。
?
?2.4 key_len
?key_len 列定義了用于SQL 語句的連接條件的鍵的長(zhǎng)度。此列值對(duì)于確認(rèn)索引的有效性以及多列索引中用到的列的數(shù)目很重要。
?此列的一些示例值如下所示：
?
?此列的一些示例值如下所示：
?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
?
?從這些示例中可以看出，是否可以為空、可變長(zhǎng)度的列以及key_len 列的值只和用在連接和WHERE 條件中的索引的列
?有關(guān)。索引中的其他列會(huì)在ORDER BY 或者GROUP BY 語句中被用到。下面這個(gè)來自于著名的開源博客軟件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?? ?這個(gè)表的索引包括post_type、post_status、post_date 以及ID列。下面是一個(gè)演示索引列用法的SQL 查詢：
?EXPLAIN SELECT ID, post_title FROM wp_posts WHERE post_type='post' AND post_date > '2010-06-01';
?
?這個(gè)查詢的QEP 返回的key_len 是62。這說明只有post_type列上的索引用到了(因?yàn)?20×3)+2=62)。盡管查詢?cè)赪HERE 語句?中使用了post_type 和post_date 列，但只有post_type 部分被用到了。其他索引沒有被使用的原因是MySQL 只能使用定義索引的最左邊部分。為了更好地利用這個(gè)索引，可以修改這個(gè)查詢來調(diào)整索引的列。請(qǐng)看下面的示例：

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

?在SELECT查詢的添加一個(gè)post_status 列的限制條件后，QEP顯示key_len 的值為132，這意味著post_type、post_status、post_date
?三列(62+62+8，(20×3)+2，(20×3)+2，8)都被用到了。此外，這個(gè)索引的主碼列ID 的定義是使用MyISAM 存儲(chǔ)索引的遺留痕跡。當(dāng)使用InnoDB 存儲(chǔ)引擎時(shí)，在非主碼索引中包含主碼列是多余的，這可以從key_len 的用法看出來。相關(guān)的QEP 列還包括帶有Using index 值的Extra 列。
?
?2.5 table
?table 列是EXPLAIN 命令輸出結(jié)果中的一個(gè)單獨(dú)行的唯一標(biāo)識(shí)符。這個(gè)值可能是表名、表的別名或者一個(gè)為查詢產(chǎn)生臨時(shí)表的標(biāo)識(shí)符，如派生表、子查詢或集合。下面是QEP 中table 列的一些示例：
?table: item
?table: