品才網(wǎng)>數(shù)據(jù)庫操作系統(tǒng)>MySQL中EXPLAIN命令用法詳解

MySQL中EXPLAIN命令用法詳解

時間：2022-06-26 06:54:25 數(shù)據(jù)庫操作系統(tǒng) 我要投稿

相關(guān)推薦

MySQL中EXPLAIN命令用法詳解

MySQL的EXPLAIN命令可以用于SQL語句的查詢執(zhí)行計劃。這些命令的輸出能讓我們知道MySQL 優(yōu)化器是怎樣執(zhí)行SQL 語句的。這命令并沒有提供任何調(diào)整建議，但它能夠提供重要的信息幫助你做出調(diào)優(yōu)決策。而下面讓我們來詳細(xì)的了解下其用法吧。

1. 語法

MySQL 的EXPLAIN 語法可以運行在SELECT 語句或者特定表上。如果作用在表上，那么此命令等同于DESC 表命令。UPDATE和DELETE 命令也需要進(jìn)行性能改進(jìn)，當(dāng)這些命令不是直接在表的主碼上運行時，為了確保最優(yōu)化的索引使用率，需要把它們改寫成SELECT 語句(以便對它們執(zhí)行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語句進(jìn)行explain分析操作的.

MySQL 優(yōu)化器是基于開銷來工作的，它并不提供任何的QEP的位置。這意味著QEP 是在每條SQL 語句執(zhí)行的時候動態(tài)地計

算出來的。在MySQL 存儲過程中的SQL 語句也是在每次執(zhí)行時計算QEP 的。存儲過程緩存僅僅解析查詢樹。

2. 各列詳解

MySQL EXPLAIN命令能夠為SQL語句中的每個表生成以下信息：

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

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

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

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

 id

 select_type

 table

 partitions(這一列只有在EXPLAIN PARTITIONS 語法中才會出現(xiàn))

 possible_keys

 key

 key_len

 ref

 rows

 filtered(這一列只有在EXPLAINED EXTENDED 語法中才會出現(xiàn))

 Extra

這些列展示了SELECT 語句對每一個表的QEP。一個表可能和一個物理模式表或者在SQL 執(zhí)行時生成的內(nèi)部臨時表(例如從子查詢或者合并操作會產(chǎn)生內(nèi)部臨時表)相關(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)化器選擇使用的索引。一般來說SQL 查詢中的每個表都僅使用一個索引。也存在索引合并的少數(shù)例外情況，如給定表上用到了兩個或者更多索引。

下面是QEP 中key 列的示例：

key: item_id

key: NULL

key: first, last

SHOW CREATE TABLE

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

2.2 ROWS

rows 列提供了試圖分析所有存在于累計結(jié)果集中的行數(shù)目的MySQL 優(yōu)化器估計值。QEP 很容易描述這個很困難的統(tǒng)計量。

查詢中總的讀操作數(shù)量是基于合并之前行的每一行的rows 值的連續(xù)積累而得出的。這是一種嵌套行算法。

以連接兩個表的QEP 為例。通過id=1 這個條件找到的第一行的rows 值為1，這等于對第一個表做了一次讀操作。第二行是

通過id=2 找到的，rows 的值為5。這等于有5 次讀操作符合當(dāng)前1 的積累量。參考兩個表，讀操作的總數(shù)目是6。在另一個QEP

中，第一rows 的值是5，第二rows 的值是1。這等于第一個表有5 次讀操作，對5個積累量中每個都有一個讀操作。因此兩個表

總的讀操作的次數(shù)是10(5+5)次。

最好的估計值是1，一般來說這種情況發(fā)生在當(dāng)尋找的行在表中可以通過主鍵或者唯一鍵找到的時候。

在下面的QEP 中，外面的嵌套循環(huán)可以通過id=1 來找到，其估計的物理行數(shù)是1。第二個循環(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 命令來查看實際的行操作。這個命令可以提供最佳的確認(rèn)物理行操作的方式。請看下面的示例：

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 找到的外層嵌套循環(huán)估計有160行。第二個循環(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

通過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)化器為查詢選定的索引。

一個會列出大量可能的索引(例如多于3 個)的QEP 意味著備選索引數(shù)量太多了，同時也可能提示存在一個無效的單列索引。

可以用第2 章詳細(xì)介紹過的SHOW INDEXES 命令來檢查索引是否有效且是否具有合適的基數(shù)。

為查詢確定QEP 的速度也會影響到查詢的性能。如果發(fā)現(xiàn)有大量的可能的索引，則意味著這些索引沒有被使用到。

相關(guān)的QEP 列還包括key 列。

2.4 key_len

key_len 列定義了用于SQL 語句的連接條件的鍵的長度。此列值對于確認(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

從這些示例中可以看出，是否可以為空、可變長度的列以及key_len 列的值只和用在連接和WHERE 條件中的索引的列有關(guān)。索引中的其他列會在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` 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

這個表的索引包括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 只能使用定義索引的最左邊部分。為了更好地利用這個索引，可以修改這個查詢來調(diào)整索引的列。請看下面的示例：

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 存儲索引的遺留痕跡。當(dāng)使用InnoDB 存儲引擎時，在非主碼索引中包含主碼列是多余的，這可以從key_len 的用法看出來。

相關(guān)的QEP 列還包括帶有Using index 值的Extra 列。

2.5 table

table 列是EXPLAIN 命令輸出結(jié)果中的一個單獨行的唯一標(biāo)識符。這個值可能是表名、表的別名或者一個為查詢產(chǎn)生臨時表的標(biāo)識符，如派生表、子查詢或集合。下面是QEP 中table 列的一些示例：

table: item

table:

表中N 和M 的值參考了另一個符合id 列值的table 行。相關(guān)的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

對于不包含子查詢和其他復(fù)雜語法的簡單查詢，這是一個常見的類型。

2). PRIMARY

這是為更復(fù)雜的查詢而創(chuàng)建的首要表(也就是最外層的表)。這個類型通常可以在DERIVED 和UNION 類型混合使用時見到。

3). DERIVED

當(dāng)一個表不是一個物理表時，那么就被叫做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 語句中的表的返回結(jié)果。當(dāng)select_type 為這個值時，經(jīng)常可以看到table 的值是，這說明匹配的id 行是這個集合的一部分。下面的SQL產(chǎn)生了一個UNION和UNION RESULT select-type：

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 列代表給定表所使用的分區(qū)。這一列只會在EXPLAIN

PARTITIONS 語句中出現(xiàn)。

2.8 Extra

Extra 列提供了有關(guān)不同種類的MySQL 優(yōu)化器路徑的一系列額外信息。Extra 列可以包含多個值，可以有很多不同的取值，并且這些值還在隨著MySQL 新版本的發(fā)布而進(jìn)一步增加。下面給出常用值的列表。你可以從下面的地址找到更全面的值的列表：http://dev.mysql.com/doc/refman/5.5/en/explain-output.html。

1). Using where

這個值表示查詢使用了where 語句來處理結(jié)果——例如執(zhí)行全表掃描。如果也用到了索引，那么行的限制條件是通過獲取必要的數(shù)據(jù)之后處理讀緩沖區(qū)來實現(xiàn)的。

2). Using temporary

這個值表示使用了內(nèi)部臨時(基于內(nèi)存的)表。一個查詢可能用到多個臨時表。有很多原因都會導(dǎo)致MySQL 在執(zhí)行查詢期間創(chuàng)建臨時表。兩個常見的原因是在來自不同表的列上使用了DISTINCT，或者使用了不同的ORDER BY 和GROUP BY 列。想了解更多內(nèi)容可以訪問http://forge.mysql.com/wiki/Overview_of_query_execution_and_use_of_temp_tables�？梢詮�(qiáng)制指定一個臨時表使用基于磁盤的MyISAM 存儲引擎。

這樣做的原因主要有兩個：

 內(nèi)部臨時表占用的空間超過min(tmp_table_size，max_heap_table_size)系統(tǒng)變量的限制

 使用了TEXT/BLOB 列

3). Using filesort

這是ORDER BY 語句的結(jié)果。這可能是一個CPU 密集型的過程�？梢酝ㄟ^選擇合適的索引來改進(jìn)性能，用索引來為查詢結(jié)果排序。詳細(xì)過程請參考第4章。

4). Using index

這個值重點強(qiáng)調(diào)了只需要使用索引就可以滿足查詢表的要求，不需要直接訪問表數(shù)據(jù)。請參考第5 章的詳細(xì)示例來理解這個值。

5). Using join buffer

這個值強(qiáng)調(diào)了在獲取連接條件時沒有使用索引，并且需要連接緩沖區(qū)來存儲中間結(jié)果。如果出現(xiàn)了這個值，那應(yīng)該注意，根據(jù)查詢的具體情況可能需要添加索引來改進(jìn)性能。

6). Impossible where

這個值強(qiáng)調(diào)了where 語句會導(dǎo)致沒有符合條件的行。請看下面的示例：mysql> EXPLAIN SELECT * FROM user WHERE 1=2;

7). Select tables optimized away

這個值意味著僅通過使用索引，優(yōu)化器可能僅從聚合函數(shù)結(jié)果中返回一行。

8). Distinct

這個值意味著MySQL 在找到第一個匹配的行之后就會停止搜索其他行。

9). Index merges

當(dāng)MySQL 決定要在一個給定的表上使用超過一個索引的時候，就會出現(xiàn)以下格式中的一個，詳細(xì)說明使用的索引以及合并的類型。

 Using sort_union(…)

 Using union(…)

 Using intersect(…)

2.9 id

id 列是在QEP 中展示的表的連續(xù)引用。

2.10 ref

ref 列可以被用來標(biāo)識那些用來進(jìn)行索引比較的列或者常量。

2.11 filtered

filtered 列給出了一個百分比的值，這個百分比值和rows 列的值一起使用，可以估計出那些將要和QEP 中的前一個表進(jìn)行連接的行的數(shù)目。前一個表就是指id 列的值比當(dāng)前表的id 小的表。這一列只有在EXPLAIN EXTENDED 語句中才會出現(xiàn)。

2.12 type

type 列代表QEP 中指定的表使用的連接方式。下面是最常用的幾種連接方式：

 const 當(dāng)這個表最多只有一行匹配的行時出現(xiàn)system 這是const 的特例，當(dāng)表只有一個row 時會出現(xiàn)

 eq_ref 這個值表示有一行是為了每個之前確定的表而讀取的

 ref 這個值表示所有具有匹配的索引值的行都被用到

 range 這個值表示所有符合一個給定范圍值的索引行都被用到

 ALL 這個值表示需要一次全表掃描其他類型的值還有fulltext 、ref_or_null 、index_merge 、unique_subquery、index_subquery 以及index。

想了解更多信息可以訪問http://dev.mysql.com/doc/refman/5.5/en/explain-output.html。

3. 解釋EXPLAIN 輸出結(jié)果

理解你的應(yīng)用程序(包括技術(shù)和實現(xiàn)可能性)和優(yōu)化SQL 語句同等重要。下面給出一個從父子關(guān)系中獲取孤立的父輩記錄的商業(yè)需求的例子。這個查詢可以用三種不同的方式構(gòu)造。盡管會產(chǎn)生相同的結(jié)果，但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