- 相關(guān)推薦
MySQL中EXPLAIN命令用法詳解
MySQL的EXPLAIN命令可以用于SQL語(yǔ)句的查詢(xún)執行計劃。這些命令的輸出能讓我們知道MySQL 優(yōu)化器是怎樣執行SQL 語(yǔ)句的。這命令并沒(méi)有提供任何調整建議，但它能夠提供重要的信息幫助你做出調優(yōu)決策。而下面讓我們來(lái)詳細的了解下其用法吧。
1. 語(yǔ)法
MySQL 的EXPLAIN 語(yǔ)法可以運行在SELECT 語(yǔ)句或者特定表上。如果作用在表上，那么此命令等同于DESC 表命令。UPDATE和DELETE 命令也需要進(jìn)行性能改進(jìn)，當這些命令不是直接在表的主碼上運行時(shí)，為了確保最優(yōu)化的索引使用率，需要把它們改寫(xiě)成SELECT 語(yǔ)句(以便對它們執行EXPLAIN 命令)。請看下面的示例：
UPDATE table1
SET col1 = X, col2 = Y
WHERE id1 = 9
AND dt >= '2010-01-01';
這個(gè)UPDATE語(yǔ)句可以被重寫(xiě)成為下面這樣的SELECT語(yǔ)句：
SELECT col1, col2
FROM table1
WHERE id1 = 9
AND dt >= '2010-01-01';
在5.6.10版本里面,是可以直接對dml語(yǔ)句進(jìn)行explain分析操作的.
MySQL 優(yōu)化器是基于開(kāi)銷(xiāo)來(lái)工作的，它并不提供任何的QEP的位置。這意味著(zhù)QEP 是在每條SQL 語(yǔ)句執行的時(shí)候動(dòng)態(tài)地計
算出來(lái)的。在MySQL 存儲過(guò)程中的SQL 語(yǔ)句也是在每次執行時(shí)計算QEP 的。存儲過(guò)程緩存僅僅解析查詢(xún)樹(shù)。
2. 各列詳解
MySQL EXPLAIN命令能夠為SQL語(yǔ)句中的每個(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 顯示沒(méi)有使用任何索引(也就是全表掃描)并且處理了大量的行來(lái)滿(mǎn)足查詢(xún)。對同樣一條SELECT 語(yǔ)句，一個(gè)優(yōu)化過(guò)的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è)索引，且估計只有一行數據將被獲取。
QEP 中每個(gè)行的所有列表如下所示：
id
select_type
table
partitions(這一列只有在EXPLAIN PARTITIONS 語(yǔ)法中才會(huì )出現)
possible_keys
key
key_len
ref
rows
filtered(這一列只有在EXPLAINED EXTENDED 語(yǔ)法中才會(huì )出現)
Extra
這些列展示了SELECT 語(yǔ)句對每一個(gè)表的QEP。一個(gè)表可能和一個(gè)物理模式表或者在SQL 執行時(shí)生成的內部臨時(shí)表(例如從子查詢(xún)或者合并操作會(huì )產(chǎn)生內部臨時(shí)表)相關(guān)聯(lián)。
可以參考MySQL Reference Manual 獲得更多信息：http://dev.mysql.com/doc/refman/5.5/en/explain-output.html。
2.1 key
key 列指出優(yōu)化器選擇使用的索引。一般來(lái)說(shuō)SQL 查詢(xún)中的每個(gè)表都僅使用一個(gè)索引。也存在索引合并的少數例外情況，如給定表上用到了兩個(gè)或者更多索引。
下面是QEP 中key 列的示例：
key: item_id
key: NULL
key: first, last
SHOW CREATE TABLE
命令是最簡(jiǎn)單的查看表和索引列細節的方式。和key 列相關(guān)的列還包括possible_keys、rows 以及key_len。
2.2 ROWS
rows 列提供了試圖分析所有存在于累計結果集中的行數目的MySQL 優(yōu)化器估計值。QEP 很容易描述這個(gè)很困難的統計量。
查詢(xún)中總的讀操作數量是基于合并之前行的每一行的rows 值的連續積累而得出的。這是一種嵌套行算法。
以連接兩個(gè)表的QEP 為例。通過(guò)id=1 這個(gè)條件找到的第一行的rows 值為1，這等于對第一個(gè)表做了一次讀操作。第二行是
通過(guò)id=2 找到的，rows 的值為5。這等于有5 次讀操作符合當前1 的積累量。參考兩個(gè)表，讀操作的總數目是6。在另一個(gè)QEP
中，第一rows 的值是5，第二rows 的值是1。這等于第一個(gè)表有5 次讀操作，對5個(gè)積累量中每個(gè)都有一個(gè)讀操作。因此兩個(gè)表
總的讀操作的次數是10(5+5)次。
最好的估計值是1，一般來(lái)說(shuō)這種情況發(fā)生在當尋找的行在表中可以通過(guò)主鍵或者唯一鍵找到的時(shí)候。
在下面的QEP 中，外面的嵌套循環(huán)可以通過(guò)id=1 來(lái)找到，其估計的物理行數是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 命令來(lái)查看實(shí)際的行操作。這個(gè)命令可以提供最佳的確認物理行操作的方式。請看下面的示例：
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 中，通過(guò)id=1 找到的外層嵌套循環(huán)估計有160行。第二個(gè)循環(huán)估計有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
通過(guò)SHOW STATUS 命令可以查看實(shí)際的行操作，該命令表明物理讀操作數量大幅增加。請看下面的示例：
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)化器為查詢(xún)選定的索引。
一個(gè)會(huì )列出大量可能的索引(例如多于3 個(gè))的QEP 意味著(zhù)備選索引數量太多了，同時(shí)也可能提示存在一個(gè)無(wú)效的單列索引。
可以用第2 章詳細介紹過(guò)的SHOW INDEXES 命令來(lái)檢查索引是否有效且是否具有合適的基數。
為查詢(xún)確定QEP 的速度也會(huì )影響到查詢(xún)的性能。如果發(fā)現有大量的可能的索引，則意味著(zhù)這些索引沒(méi)有被使用到。
相關(guān)的QEP 列還包括key 列。
2.4 key_len
key_len 列定義了用于SQL 語(yǔ)句的連接條件的鍵的長(cháng)度。此列值對于確認索引的有效性以及多列索引中用到的列的數目很重要。
此列的一些示例值如下所示：
此列的一些示例值如下所示：
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
從這些示例中可以看出，是否可以為空、可變長(cháng)度的列以及key_len 列的值只和用在連接和WHERE 條件中的索引的列有關(guān)。索引中的其他列會(huì )在ORDER BY或者GROUP BY 語(yǔ)句中被用到。下面這個(gè)來(lái)自于著(zhù)名的開(kāi)源博客軟件WordPress 的表展示了如何以最佳方式使用帶有定義好的表索引的SQL 語(yǔ)句：
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` var20) NOT NULL DEFAULT 'publish' ,
`post_type` var20) 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` var20) NOT NULL DEFAULT 'publish' ,
`post_type` var20) 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 查詢(xún)：
EXPLAIN SELECT ID, post_title FROM wp_posts WHERE post_type=’post’ AND post_date > ‘2010-06-01’;
這個(gè)查詢(xún)的QEP 返回的key_len 是62。這說(shuō)明只有post_type列上的索引用到了(因為(20×3)+2=62)。盡管查詢(xún)在WHERE 語(yǔ)句中使用了post_type 和post_date 列，但只有post_type 部分被用到了。其他索引沒(méi)有被使用的原因是MySQL 只能使用定義索引的最左邊部分。為了更好地利用這個(gè)索引，可以修改這個(gè)查詢(xún)來(lái)調整索引的列。請看下面的示例：
mysql> EXPLAIN SELECT ID, post_title
-> FROM wp_posts
-> WHERE post_type='post'
-> AND post_status='publish'
-> AND post_date > '2010-06-01';
在SELECT查詢(xún)的添加一個(gè)post_status 列的限制條件后，QEP顯示key_len 的值為132，這意味著(zhù)post_type、post_status、post_date三列(62+62+8，(20×3)+2，(20×3)+2，8)都被用到了。此外，這個(gè)索引的主碼列ID 的定義是使用MyISAM 存儲索引的遺留痕跡。當使用InnoDB 存儲引擎時(shí)，在非主碼索引中包含主碼列是多余的，這可以從key_len 的用法看出來(lái)。
相關(guān)的QEP 列還包括帶有Using index 值的Extra 列。
2.5 table
table 列是EXPLAIN 命令輸出結果中的一個(gè)單獨行的唯一標識符。這個(gè)值可能是表名、表的別名或者一個(gè)為查詢(xún)產(chǎn)生臨時(shí)表的標識符，如派生表、子查詢(xún)或集合。下面是QEP 中table 列的一些示例：
table: item
table:
table:
表中N 和M 的值參考了另一個(gè)符合id 列值的table 行。相關(guān)的QEP 列還有select_type
2.6 select_type
select_type 列提供了各種表示table 列引用的使用方式的類(lèi)型。最常見(jiàn)的值包括SIMPLE、PRIMARY、DERIVED 和UNION。其他可能的值還有UNION RESULT、DEPENDENT SUBQUERY、DEPENDENT UNION、UNCACHEABLE UNION 以及UNCACHEABLE QUERY。
1). SIMPLE
對于不包含子查詢(xún)和其他復雜語(yǔ)法的簡(jiǎn)單查詢(xún)，這是一個(gè)常 見(jiàn)的類(lèi)型。
2). PRIMARY
這是為更復雜的查詢(xún)而創(chuàng )建的首要表(也就是最外層的表)。這個(gè)類(lèi)型通�？梢栽贒ERIVED 和UNION 類(lèi)型混合使用時(shí)見(jiàn)到。
3). DERIVED
當一個(gè)表不是一個(gè)物理表時(shí)，那么就被叫做DERIVED。下面的SQL 語(yǔ)句給出了一個(gè)QEP 中DERIVED select-type 類(lèi)型的
示例：
mysql> EXPLAIN SELECT MAX(id)
-> FROM (SELECT id FROM users WHERE first = ‘west’) c;
4). DEPENDENT SUBQUERY
這個(gè)select-type 值是為使用子查詢(xún)而定義的。下面的SQL語(yǔ)句提供了這個(gè)值：
mysql> EXPLAIN SELECT p.*
-> FROM parent p
-> WHERE p.id NOT IN (SELECT c.parent_id FROM child c);
5). UNION
這是UNION 語(yǔ)句其中的一個(gè)SQL 元素。
6). UNION RESULT
這是一系列定義在UNION 語(yǔ)句中的表的返回結果。當select_type 為這個(gè)值時(shí)，經(jīng)�？梢钥吹絫able 的值是
mysql> EXPLAIN SELECT p.* FROM parent p WHERE p.val
LIKE ‘a(chǎn)%’
-> UNION
-> SELECT p.* FROM parent p WHERE p.id > 5;
2.7 partitions
partitions 列代表給定表所使用的分區。這一列只會(huì )在EXPLAIN
PARTITIONS 語(yǔ)句中出現。
2.8 Extra
Extra 列提供了有關(guān)不同種類(lèi)的MySQL 優(yōu)化器路徑的一系列額外信息。Extra 列可以包含多個(gè)值，可以有很多不同的取值，并且這些值還在隨著(zhù)MySQL 新版本的發(fā)布而進(jìn)一步增加。下面給出常用值的列表。你可以從下面的地址找到更全面的值的列表：http://dev.mysql.com/doc/refman/5.5/en/explain-output.html。
1). Using where
這個(gè)值表示查詢(xún)使用了where 語(yǔ)句來(lái)處理結果——例如執行全表掃描。如果也用到了索引，那么行的限制條件是通過(guò)獲取必要的數據之后處理讀緩沖區來(lái)實(shí)現的。
2). Using temporary
這個(gè)值表示使用了內部臨時(shí)(基于內存的)表。一個(gè)查詢(xún)可能用到多個(gè)臨時(shí)表。有很多原因都會(huì )導致MySQL 在執行查詢(xún)期間創(chuàng )建臨時(shí)表。兩個(gè)常見(jiàn)的原因是在來(lái)自不同表的列上使用了DISTINCT，或者使用了不同的ORDER BY 和GROUP BY 列。想了解更多內容可以訪(fǎng)問(wèn)http://forge.mysql.com/wiki/Overview_of_query_execution_and_use_of_temp_tables�？梢詮娭浦付ㄒ粋�(gè)臨時(shí)表使用基于磁盤(pán)的MyISAM 存儲引擎。
這樣做的原因主要有兩個(gè)：
內部臨時(shí)表占用的空間超過(guò)min(tmp_table_size，max_heap_table_size)系統變量的限制
使用了TEXT/BLOB 列
3). Using filesort
這是ORDER BY 語(yǔ)句的結果。這可能是一個(gè)CPU 密集型的過(guò)程�？梢酝ㄟ^(guò)選擇合適的索引來(lái)改進(jìn)性能，用索引來(lái)為查詢(xún)結果排序。詳細過(guò)程請參考第4章。
4). Using index
這個(gè)值重點(diǎn)強調了只需要使用索引就可以滿(mǎn)足查詢(xún)表的要求，不需要直接訪(fǎng)問(wèn)表數據。請參考第5 章的詳細示例來(lái)理解這個(gè)值。
5). Using join buffer
這個(gè)值強調了在獲取連接條件時(shí)沒(méi)有使用索引，并且需要連接緩沖區來(lái)存儲中間結果。如果出現了這個(gè)值，那應該注意，根據查詢(xún)的具體情況可能需要添加索引來(lái)改進(jìn)性能。
6). Impossible where
這個(gè)值強調了where 語(yǔ)句會(huì )導致沒(méi)有符合條件的行。請看下面的示例：mysql> EXPLAIN SELECT * FROM user WHERE 1=2;
7). Select tables optimized away
這個(gè)值意味著(zhù)僅通過(guò)使用索引，優(yōu)化器可能僅從聚合函數結果中返回一行。
8). Distinct
這個(gè)值意味著(zhù)MySQL 在找到第一個(gè)匹配的行之后就會(huì )停止搜索其他行。
9). Index merges
當MySQL 決定要在一個(gè)給定的表上使用超過(guò)一個(gè)索引的時(shí)候，就會(huì )出現以下格式中的一個(gè)，詳細說(shuō)明使用的索引以及合并的類(lèi)型。
Using sort_union(…)
Using union(…)
Using intersect(…)
2.9 id
id 列是在QEP 中展示的表的連續引用。
2.10 ref
ref 列可以被用來(lái)標識那些用來(lái)進(jìn)行索引比較的列或者常量。
2.11 filtered
filtered 列給出了一個(gè)百分比的值，這個(gè)百分比值和rows 列的值一起使用，可以估計出那些將要和QEP 中的前一個(gè)表進(jìn)行連接的行的數目。前一個(gè)表就是指id 列的值比當前表的id 小的表。這一列只有在EXPLAIN EXTENDED 語(yǔ)句中才會(huì )出現。
2.12 type
type 列代表QEP 中指定的表使用的連接方式。下面是最常用的幾種連接方式：
const 當這個(gè)表最多只有一行匹配的行時(shí)出現system 這是const 的特例，當表只有一個(gè)row 時(shí)會(huì )出現
eq_ref 這個(gè)值表示有一行是為了每個(gè)之前確定的表而讀取的
ref 這個(gè)值表示所有具有匹配的索引值的行都被用到
range 這個(gè)值表示所有符合一個(gè)給定范圍值的索引行都被用到
ALL 這個(gè)值表示需要一次全表掃描其他類(lèi)型的值還有fulltext 、ref_or_null 、index_merge 、unique_subquery、index_subquery 以及index。
想了解更多信息可以訪(fǎng)問(wèn)http://dev.mysql.com/doc/refman/5.5/en/explain-output.html。
3. 解釋EXPLAIN 輸出結果
理解你的應用程序(包括技術(shù)和實(shí)現可能性)和優(yōu)化SQL 語(yǔ)句同等重要。下面給出一個(gè)從父子關(guān)系中獲取孤立的父輩記錄的商業(yè)需求的例子。這個(gè)查詢(xún)可以用三種不同的方式構造。盡管會(huì )產(chǎn)生相同的結果，但QEP 會(huì )顯示三種不同的路徑。
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 NULLG
********************* 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命令用法詳解】相關(guān)文章：
Linux系統中sed命令在文件某行前后添加內容的用法06-26
初中定語(yǔ)從句用法詳解07-09
關(guān)于Linux中更改轉移mysql數據庫目錄的步驟06-27
「MySQL」經(jīng)典面試題07-11
Mysql常用技巧總結07-11
mysql安裝筆記教程04-24
雅思聽(tīng)力考試中的替換原則詳解07-04
生活中的抗皺小竅門(mén)詳解07-03