MySQL的EXPLAIN命令顯示了mysql如何使用索引來處理select語句以及連接表?？梢詭椭x擇更好的索引和寫出更優(yōu)化的查詢語句。

一、通過expalin可以得到

1、表的讀取順序

2、表的讀取操作的操作類型

3、哪些索引可以使用

4、哪些索引被實(shí)際使用

5、表之間的引用

6、每張表有多少行被優(yōu)化器查詢

二、如何調(diào)用expalin命令

1、只需要在SELECT前面加上EXPLAIN即可，例如

explain select surname,first_name form a,b where a.id=b.id

2、在語句結(jié)尾(;之前)加上\G能夠更清晰的查看.

3、需要說的是EXPLAIN只對SELECT查詢作解釋,INSERT,UPDATE,DELETE不會(huì)

三、explain顯示的列

1、id

語句的執(zhí)行順序標(biāo)識，如果在語句中沒有子查詢或聯(lián)合,說明只有一個(gè)SELECT,于是這個(gè)列顯示為1,否則內(nèi)層的SELECT會(huì)順序編號.

2、select_type

顯示了對應(yīng)的查詢是簡單還是復(fù)雜SELECT，主要有以下幾種查詢類型

1)、simple 簡單類型

語句中沒有子查詢或union

2)、primary

最外層的select ，不是主鍵

這是為更復(fù)雜的查詢而創(chuàng)建的首要表(也就是最外層的表)。這個(gè)類型通?？梢栽贒ERIVED 和UNION 類型混合使用時(shí)見到。

3)、union

union是在select 語句中第二個(gè)select語句后面所有的select，第一個(gè)select 為primary

這是UNION 語句其中的一個(gè)SQL 元素。

4)、dependent subquery

子查詢中內(nèi)層中第一個(gè)select語句

這個(gè)select-type 值是為使用子查詢而定義的。下面的SQL語句提供了這個(gè)值： mysql> EXPLAIN SELECT p.* -> FROM parent p -> WHERE p.id NOT IN (SELECT c.parent_id FROM child c);

5)、dependent union

子查詢中union且為union中第二個(gè)select開始的后面所有select，依賴于外部的結(jié)果集。

6)、SUBQUERY

子查詢內(nèi)層查詢的第一個(gè)SELECT，結(jié)果不依賴于外部查詢結(jié)果集。

7)、devived

派生表的查詢語句

當(dāng)一個(gè)表不是一個(gè)物理表時(shí)，那么就被叫做DERIVED。下面的SQL 語句給出了一個(gè)QEP 中DERIVED select-type 類型的

示例：

mysql> EXPLAIN SELECT MAX(id)

-> FROM (SELECT id FROM users WHERE first = 'west') c;

8)、uncacheable subquery

結(jié)果集無法緩存的子查詢

9)、union result

union中合并的結(jié)果

這是一系列定義在UNION 語句中的表的返回結(jié)果。當(dāng)select_type 為這個(gè)值時(shí)，經(jīng)?？梢钥吹絫able 的值是， 這說明匹配的id 行是這個(gè)集合的一部分。下面的SQL產(chǎn)生了一個(gè)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;

**3、table **

1)、顯示對應(yīng)行正在訪問哪個(gè)表

2)、當(dāng)FROM子句中有子查詢或UNION時(shí),table列是,其中N是id列對應(yīng)的值

**4、type **

聯(lián)合查詢所使用的類型，type顯示的是訪問類型，是較為重要的一個(gè)指標(biāo)，結(jié)果值從好到壞依次是：

system > const > eq_ref > ref >fulltext > ref_or_null > index_merge > unique_subquery >index_subquery > range > index > ALL

一般來說，得保證查詢至少達(dá)到range級別，最好能達(dá)到ref。

這列很重要,顯示了連接使用了哪種類別,有無使用索引。主要包括以下幾種類型

1)、all

全表掃描，效果是最不理想的。

2)、const

const是在where條件以常量作為查詢條件，最多只會(huì)有一條記錄匹配，由于是常量，實(shí)際上只須要讀一次。

3)、eq_ref

最多只會(huì)有一條匹配結(jié)果，一般是通過主鍵或唯一鍵索引來訪問。一般會(huì)出現(xiàn)在連接查詢的語句中。

4)、fulltext

進(jìn)行全文索引檢索。

5)、index

全索引掃描。MySQL在掃描表時(shí)按索引次序進(jìn)行而不是行。

6)、index_merge

查詢中同時(shí)使用兩個(gè)(或更多)索引，然后對索引結(jié)果進(jìn)行合并(merge)，再讀取表數(shù)據(jù)。

7)、index_subquery

子查詢中的返回結(jié)果字段組合是一個(gè)索引(或索引組合)，但不是一個(gè)主鍵或唯一索引。

8)、rang

索引范圍掃描。一個(gè)有限制的索引掃描,它開始于索引里的某一點(diǎn),返回匹配這個(gè)值域的行(顯而易見的范圍掃描.即帶有BETWEEN或在WHERE子句中帶有>的查詢,當(dāng)MySQL使用索引去查找一系列值的時(shí)候,如IN()和OR列表，也為顯示的范圍掃描)

9)、ref

也叫索引查找,他返回所有匹配某單個(gè)值的行,它可能會(huì)找到多個(gè)符合條件行。

10)、ref_or_null

與ref的唯一區(qū)別就是在使用索引引用的查詢之外再增加一個(gè)空值的查詢。

11)、system

系統(tǒng)表，表中只有一行數(shù)據(jù)；

12)、unique_subquery

子查詢中的返回結(jié)果字段組合是主鍵或唯一約束。

5、possible_keys

這一列顯示了查詢可以使用哪些索引,是基于查詢訪問的列和使用的比較操作符來判斷的.

如果沒有任何索引可以使用，就會(huì)顯示成null

6、key

顯示了MySQL決定采用哪個(gè)索引來優(yōu)化對該表的訪問

7、key_len

1)、key_len列顯示mysql決定使用的鍵長度，如果鍵是null，則長度為null。

2)、顯示MySQL在索引里使用的字節(jié)數(shù).舉個(gè)例子就是在查詢中使用到了主鍵,而主鍵的數(shù)據(jù)類型為INT,則為4,SMALLINT則為2

3)、使用的索引長度，一般越短越好。

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)。索引中的其他列會(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)。盡管查詢在WHERE 語句 中使用了post_type 和post_date 列，但只有post_type 部分被用到了。其他索引沒有被使用的原因是MySQL 只能使用定義索引的 最左邊部分。為了更好地利用這個(gè)索引，可以修改這個(gè)查詢來調(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查詢的添加一個(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 存儲索 引的遺留痕跡。當(dāng)使用InnoDB 存儲引擎時(shí)，在非主碼索引中包含主碼列是多余的，這可以從key_len 的用法看出來。 相關(guān)的QEP 列還包括帶有Using index 值的Extra 列。

8、Ref

顯示了之前的表在key列記錄的索引中查詢值所用到的列或常量

。

9、rows

顯示的是MySQL為了找到所需的值而要讀取的行數(shù).

10、extra

在此顯示的是在其他列不適合顯示的額外信息

，主要可能會(huì)是以下內(nèi)容：

1)、Distinct

查找distinct 值，當(dāng)mysql找到了第一條匹配的結(jié)果時(shí)，將停止該值的查詢，轉(zhuǎn)為后面其他值查詢。

2)、Full scan on NULL key

子查詢中的一種優(yōu)化方式，主要在遇到無法通過索引訪問null值的使用。

3)、Using index

表優(yōu)化器只需要通過訪問索引就可以獲取到需要的數(shù)據(jù)

4)、Using where

表示優(yōu)化器需要通過索引回表查詢數(shù)據(jù)(需要通過訪問數(shù)據(jù)塊獲取結(jié)果集)

5)、Using temporary

意味著MySQL在對查詢結(jié)果排序時(shí)會(huì)用到一個(gè)臨時(shí)表.

這個(gè)值表示使用了內(nèi)部臨時(shí)(基于內(nèi)存的)表。一個(gè)查詢可能 用到多個(gè)臨時(shí)表。有很多原因都會(huì)導(dǎo)致MySQL 在執(zhí)行查詢期間 創(chuàng)建臨時(shí)表。兩個(gè)常見的原因是在來自不同表的列上使用了 DISTINCT，或者使用了不同的ORDER BY 和GROUP BY 列。 想了解更多內(nèi)容可以訪問http://forge.mysql.com/wiki/Overview_ of_query_execution_and_use_of_temp_tables。 可以強(qiáng)制指定一個(gè)臨時(shí)表使用基于磁盤的MyISAM 存儲引 擎。這樣做的原因主要有兩個(gè)： ? 內(nèi)部臨時(shí)表占用的空間超過min(tmp_table_size，max_ heap_table_size)系統(tǒng)變量的限制 ? 使用了TEXT/BLOB 列

6)、Using filesort

看到這個(gè)的時(shí)候，查詢就需要優(yōu)化了。意味著MySQL會(huì)對結(jié)果使用一個(gè)外部索引排序,而不是按索引次序從表里讀出來.

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

7). Using join buffer

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

這篇博客中有Using join buffer的詳細(xì)說明

https://my.oschina.net/xinxingegeya/blog/495897

8). Impossible where

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

9). Select tables optimized away

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

10). Index merges

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

? Using sort_union(...)

? Using union(...)

? Using intersect(...)

11). Using where, Using index .....

說明該查詢有部分?jǐn)?shù)據(jù)需要通過回表方式獲取有部分?jǐn)?shù)據(jù)可以直接通過索引獲取

總結(jié)

以上是生活随笔為你收集整理的MySql数据库explain用法示例_mysql explain的用法的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。