MySQL執(zhí)行計劃命令EXPLAIN詳解

前言

我們在寫服務(wù)端代碼時，經(jīng)常會與SQL語句打交道，最初寫SQL時主要注重的是能不能正常執(zhí)行SQL，然后查詢出的數(shù)據(jù)是否正常。如果這兩方面沒有問題，基本不會考慮SQL執(zhí)行速度的問題，畢竟這個時候數(shù)據(jù)庫中數(shù)據(jù)量還是很少的，怎么寫都執(zhí)行都很快。但是隨著數(shù)據(jù)量的增加，會發(fā)現(xiàn)系統(tǒng)查詢速度越來越慢，這時就需要對系統(tǒng)進行優(yōu)化了。

最初優(yōu)化的方向就是是否存在慢查詢，如果存在我們應(yīng)該怎么去優(yōu)化這些SQL語句，今天我們就介紹一下日常中經(jīng)常使用的EXPLAIN命令。

EXPLAIN命令簡介

EXPLAIN命令可以模擬優(yōu)化器執(zhí)行SQL查詢語句，從而知道MySQL是如何處理你的SQL語句的。分析你的查詢語句或是表結(jié)構(gòu)的性能瓶頸。

通過EXPLAIN我們可以分析出一下幾個結(jié)果：

表的讀取順序；
數(shù)據(jù)讀取操作的操作類型；
哪些索引可以使用；
哪些索引被實際使用；
表之間的引用；
每張表有多少行被優(yōu)化器查詢

EXPLAIN的使用方式：EXPLAIN + SQL語句，例如：

EXPLAIN SELECT * FROM t1

執(zhí)行結(jié)果包含的信息，如下圖：

執(zhí)行計劃返回有10個字段，分別是：id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra，下面我們就具體分析執(zhí)行結(jié)果中返回的每個字段的具體含義。

EXPLAIN執(zhí)行計劃字段含義詳解

ID字段

ID字段是SELECT查詢的序列號，表示查詢中執(zhí)行select子句或操作表的順序；

ID字段大概可以出現(xiàn)3中不同的情況：

1、ID字段數(shù)值都相同，如下圖：

此時所有的ID字段數(shù)值都相同，SQL語句執(zhí)行順序由上到下，順序執(zhí)行。SQL中不存在子查詢等，如下SQL：

SELECT * FROM a , b WHERE a.id = b.id

2、ID字段數(shù)值按遞增順序出現(xiàn)，如下圖：

此時ID字段的數(shù)值是遞增出現(xiàn)的，ID數(shù)值越大優(yōu)先級就越高，SQL語句就優(yōu)先執(zhí)行，對應(yīng)的SQL語句如下：

SELECT * FROM a WHERE a.id = (SELECT id FROM b WHERE b.id = (SELECT id FROM c where c.sub_id = 1))

子查詢級別越深，ID的數(shù)值就越大，也就優(yōu)先執(zhí)行。

3、ID字段中相同數(shù)值和不相同數(shù)值同時存在，如下圖：

此時ID數(shù)值相同的算作一組，級別相同按順序執(zhí)行，ID數(shù)值越高的級別越高，越先執(zhí)行。

注意：衍生出來的表定義為DERIVED，有多個時會在“DERIVED”后加入一個數(shù)字標識區(qū)分。

此種情況對應(yīng)的SQL，如下：

SELECT *
FROM (
	SELECT a.id,a.sub FROM a
) b 
INNER JOIN c ON c.id = b.id

select_type字段

select_type字段主要用來區(qū)分查詢的類型，如普通查詢、聯(lián)合查詢、子查詢等類型，具體可以分為以下幾種類型：

SIMPLE 簡單的select查詢，查詢中不包含子查詢或者UNION；
PRIMARY 查詢中若包含任何復(fù)雜的子部分，最外層查詢則被標記為PRIMARY；
SUBQUERY 在SELECT或WHERE列表中包含了子查詢；
DERIVED 在FROM列表中包含的子查詢被標記為DERIVED（衍生），MySQL會遞歸執(zhí)行這些子查詢，把結(jié)果放在臨時表中；
UNION 若第二個SELECT出現(xiàn)在UNION之后，則被標記為UNION：若UNION包含在FROM子句的子查詢中，外層SELECT將被標記為：DERIVED；
UNION RESULT 從UNION表獲取結(jié)果的SELECT

table字段

table字段指的是當(dāng)前執(zhí)行的表

type字段

type字段是比較重要的，平常我們進行優(yōu)化時主要關(guān)注的也是這個字段，這個字段表示的是我們在查詢時訪問表的方式，大概分ALL、index、range、 ref、eq_ref、const、system、NULL（從左到右，性能從差到好。一般來說，得保證查詢至少達到range級別，最好能達到ref）。

幾種類型簡介：

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對查詢某部分進行優(yōu)化，并轉(zhuǎn)換為一個常量時，使用這些類型訪問。如將主鍵置于where列表中，MySQL就能將該查詢轉(zhuǎn)換為一個常量，system是const類型的特例，當(dāng)查詢的表只有一行的情況下，使用system
NULL: MySQL在優(yōu)化過程中分解語句，執(zhí)行時甚至不用訪問表或索引，例如從一個索引列里選取最小值可以通過單獨索引查找完成。

possible_keys 和 key字段

possible_keys：顯示可能應(yīng)用在這張表中的索引，一個或多個。查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢實際使用。
key：實際使用的索引，如果為NULL，則沒有使用索引。（可能原因包括沒有建立索引或索引失效）；查詢中若使用了覆蓋索引（select 后要查詢的字段剛好和創(chuàng)建的索引字段完全相同），則該索引僅出現(xiàn)在key列表中

key_len字段

表示索引中使用的字節(jié)數(shù)，可通過該列計算查詢中使用的索引的長度，在不損失精確性的情況下，長度越短越好。key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據(jù)表定義計算而得，不是通過表內(nèi)檢索出的。

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

ref字段

顯示索引的那一列被使用了，如果可能的話，最好是一個常數(shù)。哪些列或常量被用于查找索引列上的值。

rows字段

根據(jù)表統(tǒng)計信息及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數(shù)，也就是說，用的越少越好。

Extra字段

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

Using where:不用讀取表中所有信息，僅通過索引就可以獲取所需數(shù)據(jù)，這發(fā)生在對表的全部的請求列都是同一個索引的部分的時候，表示mysql服務(wù)器將在存儲引擎檢索行后再進行過濾
Using temporary：表示MySQL需要使用臨時表來存儲結(jié)果集，常見于排序和分組查詢，常見 group by ; order by
Using filesort：當(dāng)Query中包含 order by 操作，而且無法利用索引完成的排序操作稱為“文件排序”
Using join buffer：改值強調(diào)了在獲取連接條件時沒有使用索引，并且需要連接緩沖區(qū)來存儲中間結(jié)果。如果出現(xiàn)了這個值，那應(yīng)該注意，根據(jù)查詢的具體情況可能需要添加索引來改進能。
Impossible where：這個值強調(diào)了where語句會導(dǎo)致沒有符合條件的行（通過收集統(tǒng)計信息不可能存在結(jié)果）。
Select tables optimized away：這個值意味著僅通過使用索引，優(yōu)化器可能僅從聚合函數(shù)結(jié)果中返回一行
No tables used：Query語句中使用from dual 或不含任何from子句