在日常工作中，我們會有時會開慢查詢?nèi)ビ涗浺恍﹫?zhí)行時間比較久的SQL語句，找出這些SQL語句并不意味著完事了，些時我們常常用到explain這個命令來查看一個這些SQL語句的執(zhí)行計劃，查看該SQL語句有沒有使用上了索引，有沒有做全表掃描，這都可以通過explain命令來查看。所以我們深入了解MySQL的基于開銷的優(yōu)化器，還可以獲得很多可能被優(yōu)化器考慮到的訪問策略的細(xì)節(jié)，以及當(dāng)運(yùn)行SQL語句時哪種策略預(yù)計會被優(yōu)化器采用。

-- 實際SQL，查找用戶名為Jefabc的員工
select * from emp where name = 'Jefabc';
-- 查看SQL是否使用索引，前面加上explain即可
explain select * from emp where name = 'Jefabc';

expain出來的信息有10列，分別是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

概要描述：
id:選擇標(biāo)識符
select_type:表示查詢的類型。
table:輸出結(jié)果集的表
partitions:匹配的分區(qū)
type:表示表的連接類型
possible_keys:表示查詢時，可能使用的索引
key:表示實際使用的索引
key_len:索引字段的長度
ref:列與索引的比較
rows:掃描出的行數(shù)(估算的行數(shù))
filtered:按表條件過濾的行百分比
Extra:執(zhí)行情況的描述和說明

下面對這些字段出現(xiàn)的可能進(jìn)行解釋：

一、 id

SELECT識別符。這是SELECT的查詢序列號

我的理解是SQL執(zhí)行的順序的標(biāo)識，SQL從大到小的執(zhí)行

1. id相同時，執(zhí)行順序由上至下

2. 如果是子查詢，id的序號會遞增，id值越大優(yōu)先級越高，越先被執(zhí)行

3. id如果相同，可以認(rèn)為是一組，從上往下順序執(zhí)行；在所有組中，id值越大，優(yōu)先級越高，越先執(zhí)行

-- 查看在研發(fā)部并且名字以Jef開頭的員工，經(jīng)典查詢
explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like 'Jef%' and d.name = '研發(fā)部';

二、select_type

示查詢中每個select子句的類型

(1) SIMPLE(簡單SELECT，不使用UNION或子查詢等)

(2) PRIMARY(子查詢中最外層查詢，查詢中若包含任何復(fù)雜的子部分，最外層的select被標(biāo)記為PRIMARY)

(3) UNION(UNION中的第二個或后面的SELECT語句)

(4) DEPENDENT UNION(UNION中的第二個或后面的SELECT語句，取決于外面的查詢)

(5) UNION RESULT(UNION的結(jié)果，union語句中第二個select開始后面所有select)

(6) SUBQUERY(子查詢中的第一個SELECT，結(jié)果不依賴于外部查詢)

(7) DEPENDENT SUBQUERY(子查詢中的第一個SELECT，依賴于外部查詢)

(8) DERIVED(派生表的SELECT, FROM子句的子查詢)

(9) UNCACHEABLE SUBQUERY(一個子查詢的結(jié)果不能被緩存，必須重新評估外鏈接的第一行)

三、table

顯示這一步所訪問數(shù)據(jù)庫中表名稱（顯示這一行的數(shù)據(jù)是關(guān)于哪張表的），有時不是真實的表名字，可能是簡稱，例如上面的e，d，也可能是第幾步執(zhí)行的結(jié)果的簡稱

四、type

對表訪問方式，表示MySQL在表中找到所需行的方式，又稱“訪問類型”。

常用的類型有： ALL、index、range、 ref、eq_ref、const、system、NULL（從左到右，性能從差到好）

ALL：Full Table Scan， MySQL將遍歷全表以找到匹配的行

index: Full Index Scan，index與ALL區(qū)別為index類型只遍歷索引樹

range:只檢索給定范圍的行，使用一個索引來選擇行

ref: 表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值

eq_ref: 類似ref，區(qū)別就在使用的索引是唯一索引，對于每個索引鍵值，表中只有一條記錄匹配，簡單來說，就是多表連接中使用primary key或者 unique key作為關(guān)聯(lián)條件

const、system: 當(dāng)MySQL對查詢某部分進(jìn)行優(yōu)化，并轉(zhuǎn)換為一個常量時，使用這些類型訪問。如將主鍵置于where列表中，MySQL就能將該查詢轉(zhuǎn)換為一個常量，system是const類型的特例，當(dāng)查詢的表只有一行的情況下，使用system

NULL: MySQL在優(yōu)化過程中分解語句，執(zhí)行時甚至不用訪問表或索引，例如從一個索引列里選取最小值可以通過單獨(dú)索引查找完成。

五、possible_keys

指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用（該查詢可以利用的索引，如果沒有任何索引顯示 null）

該列完全獨(dú)立于EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實際上不能按生成的表次序使用。
如果該列是NULL，則沒有相關(guān)的索引。在這種情況下，可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢性能。如果是這樣，創(chuàng)造一個適當(dāng)?shù)乃饕⑶以俅斡肊XPLAIN檢查查詢

六、Key

key列顯示MySQL實際決定使用的鍵（索引），必然包含在possible_keys中

如果沒有選擇索引，鍵是NULL。要想強(qiáng)制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

七、key_len

表示索引中使用的字節(jié)數(shù)，可通過該列計算查詢中使用的索引的長度（key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據(jù)表定義計算而得，不是通過表內(nèi)檢索出的）

不損失精確性的情況下，長度越短越好

八、ref

列與索引的比較，表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值

九、rows

 估算出結(jié)果集行數(shù)，表示MySQL根據(jù)表統(tǒng)計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數(shù)

十、Extra

該列包含MySQL解決查詢的詳細(xì)信息,有以下幾種情況：

Using where:不用讀取表中所有信息，僅通過索引就可以獲取所需數(shù)據(jù)，這發(fā)生在對表的全部的請求列都是同一個索引的部分的時候，表示mysql服務(wù)器將在存儲引擎檢索行后再進(jìn)行過濾

Using temporary：表示MySQL需要使用臨時表來存儲結(jié)果集，常見于排序和分組查詢，常見 group by ; order by

Using filesort：當(dāng)Query中包含 order by 操作，而且無法利用索引完成的排序操作稱為“文件排序”

-- 測試Extra的filesort
explain select * from emp order by name;

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

Impossible where：這個值強(qiáng)調(diào)了where語句會導(dǎo)致沒有符合條件的行（通過收集統(tǒng)計信息不可能存在結(jié)果）。

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

No tables used：Query語句中使用from dual 或不含任何from子句

-- explain select now() from dual;

總結(jié)：

• EXPLAIN不會告訴你關(guān)于觸發(fā)器、存儲過程的信息或用戶自定義函數(shù)對查詢的影響情況
• EXPLAIN不考慮各種Cache
• EXPLAIN不能顯示MySQL在執(zhí)行查詢時所作的優(yōu)化工作
• 部分統(tǒng)計信息是估算的，并非精確值
• EXPALIN只能解釋SELECT操作，其他操作要重寫為SELECT后查看執(zhí)行計劃。

通過收集統(tǒng)計信息不可能存在結(jié)果

作者：杰克思勒
出處：http://www.cnblogs.com/tufujie/

以上就是本文的全部內(nèi)容，希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。