前言

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

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

EXPLAIN命令簡介

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

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

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

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

EXPLAIN SELECT * FROM t1

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

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

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

ID字段

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

ID字段大概可以出現3中不同的情況：

1、ID字段數值都相同，如下圖：

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

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

2、ID字段數值按遞增順序出現，如下圖：

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

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

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

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

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

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

此種情況對應的SQL，如下：

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

select_type字段

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

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

table字段

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

type字段

type字段是比較重要的，平常我們進行優(yōu)化時主要關注的也是這個字段，這個字段表示的是我們在查詢時訪問表的方式，大概分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作為關聯條件
• const、system: 當MySQL對查詢某部分進行優(yōu)化，并轉換為一個常量時，使用這些類型訪問。如將主鍵置于where列表中，MySQL就能將該查詢轉換為一個常量，system是const類型的特例，當查詢的表只有一行的情況下，使用system
• NULL: MySQL在優(yōu)化過程中分解語句，執(zhí)行時甚至不用訪問表或索引，例如從一個索引列里選取最小值可以通過單獨索引查找完成。

possible_keys 和 key字段

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

key_len字段

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

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

ref字段

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

rows字段

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

Extra字段

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

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

總結

雖然EXPLAIN很簡單，但是對于優(yōu)化SQL還是很有幫助的，當然如果數據量特別大的時候，我們需要另外想別的方法進行優(yōu)化。