## 介紹 MySQL 提供了一個**EXPALIN**命令，可以用于對**SELECT 語句**的執行計劃進行分析，并詳細的輸出分析結果，供開發人員進行針對性的優化。 我們想要查詢一條sql有沒有用上索引，有沒有全表查詢，這些都可以通過explain這個命令來查看。 通過explain命令，我們可以深入了解到MySQL的基于開銷的優化器，還可以獲得很多被優化器考慮到的訪問策略的細節以及運行sql語句時哪種策略預計會被優化器采用。 explain的使用十分簡單，通過在查詢語句前面加一個explain關鍵字即可。  ## 參數說明 explain 命令一共返回12列信息，分別是： ~~~text id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra ~~~ 以下是本文的案例表： ~~~mysql # 用戶表 CREATE TABLE user( id int primary key auto_increment, nickname varchar(100), name varchar(100), age int, sex tinyint(1), dep int, addr int, description varchar(100) )ENGINE INNODB; # 部門表 CREATE TABLE dep( id int primary key auto_increment, name varchar(100) )ENGINE INNODB; # 地址表 CREATE TABLE addr( id int primary key auto_increment, address varchar(100) )ENGINE INNODB; # 普通索引 ALTER TABLE user ADD INDEX idx_dep(dep); # 唯一索引 ALTER TABLE user ADD unique INDEX uniq_name(name); # 復合索引 ALTER TABLE user ADD INDEX c_nickname_age_sex(nickname,age,sex); # 全文索引 ALTER TABLE user ADD FULLTEXT f_description(description); # 普通索引 ALTER TABLE dep ADD INDEX idx_name(name); ~~~ ## id 列 * 每個select語句都會自動分配的一個唯一標識符 * 表示查詢中，操作表的順序，有三種情況 * id相同，執行順序從上到下 * id不同，如果是子查詢，id號會自增，id越大，**優先級越高** * id相同的不相同的同時存在 * id列為null表示為結果集，不需要使用這個語句來查詢 ## select\_type 列（很重要） 查詢類型，主要用于區別**普通查詢、聯合查詢（union、union all）、子查詢等復雜查詢。** **simple** 表示不需要union操作或者不包含子查詢的簡單select查詢。有連接查詢時，外層的查詢為simple，且只有一個。  **primary** 一個需要使用union的操作或者含有子查詢的select，位于最外層的單位查詢的select\_type即為primary。且只有一個  **subquery** 除了from子句中包含的子查詢外，其它地方出現的子查詢都可能時subquery  **dependent subquery** 子查詢的結果受到外層的影響：  **union、union result** union 連接的多表查詢，第一個查詢是primary，后面的是union, 結果集是 union result  **dependent union** 和union一樣，出現在union或者union all中，但是這個查詢要受到外部查詢的影響  **derived** 在from子句后面的子查詢，也叫派生表,**注意，在MySql5.6 對于此查詢沒有優化，所以查詢類型是derived.在mysql 5.7 使用了 Merge Derived table 優化，查詢類型變為SIMPLE。通過控制參數: optimizer\_switch='derived=on|off' 決定開始還是優化。默認開啟。**  mysql 5.7  mysql 5.6 ## table 列 * 顯示的查詢表名，如果查詢使用了別名，那么這里顯示的就是別名 * 如果不涉及對數據表的操作，那么這里就是null * 如果顯示為尖括號括起來的就表示這是一個臨時表，N就是執行計劃的id，表示結果來自這個查詢 * 如果顯示為尖括號括起來的也表示一個臨時表，表示來自union查詢id為n、m的結果集 ## partitions 列 分區信息 ## type 列 重要 * 依次從好到差： ~~~mysql system、const、eq_ref、ref、full_text、ref_or_null、unique_subquery、 index_subquery、range、index_merge、index、all ~~~ 除了 All 以外，其它的類型都可以用到索引，除了index\_merge可以使用多個索引之外，其它的類型最多只能使用到一個索引。 注意！！最少也應該要使用索引到range級別！ **system** 表中只有一行數據或者是空表。  **const** 使用唯一**索引或者主鍵**，返回記錄一定是一條的等值where條件時，通常type是const。  **eq\_ref** 連接字段為主鍵或者唯一索引，此類型通常出現于多表的join查詢，表示對于前表的每一個結果，都對應后表的唯一一條結果。并且查詢的比較是=操作，查詢效率比較高。  上面未使用覆蓋索引，下面使用覆蓋索引，減少回表 **ref** ref有三種情況： 1. 非主鍵或者唯一鍵的等值查詢 2. join連接字段是非主鍵或者唯一鍵 3. 最左前綴索引匹配  三個查詢分別對應上面三種情況 **fulltext** 全文檢索索引。  并不是優先使用全文索引 **ref\_or*\_*null** 和ref類似，增加了null值判斷 **unique\_subquery、 index\_subquery** 都是子查詢，前者返回唯一值，后者返回可能有重復。 **range 重要** 索引范圍掃描，常用于 ><,is null,between,in,like等  **index\_merge(索引合并)** 表示查詢使用了兩個或者以上的索引數量，常見于and或者or查詢匹配上了多個不同索引的字段 **index(輔助索引)** 減少回表次數,因為要查詢的索引都在一顆索引樹上  **all 全表掃描** ## possible\_keys 列 此次查詢中，可能選用的索引 ## key 列 查詢實際使用的索引，select\_type為index\_merge時，key列可能有多個索引，其它時候這里只會有一個 ## key\_len 列 * 用于處理查詢的索引長度，如果是單列索引，那么整個索引長度都會計算進去，如果是多列索引，那么查詢不一定能使用到所有的列，具體使用了多少個列的索引，這里就會計算進去，沒有使用到的索引，這里不會計算進去。 * 留意一下這個長度，計算一下就知道這個索引使用了多少列 * 另外，key\_len 只計算 where 條件使用到索引長度，而排序和分組就算用到了索引也不會計算key\_len ## ref * 如果是使用的常數等值查詢，這里會顯示const * 如果是連接查詢，被驅動表的執行計劃這里會顯示驅動表的關聯字段 * 如果是條件使用了表達式或者函數，或者條件列發生了內部隱式轉換，這里可能會顯示func ## rows 執行計劃估算的掃描行數，不是精確值（innodb不是精確值，myisam是精確值，主要是因為innodb使用了mvcc）。 ## extra 這個列包含很多不適合在其它列顯示的重要信息，有很多種，常用的有： * **using temporary** * 表示使用了臨時表存儲中間結果 * MySQL在對**order by和group by**時使用臨時表 * 臨時表可以是內存臨時表和磁盤臨時表，執行計劃中看不出來，需要查看status變量：used\_tmp\_table、used\_tmp\_disk\_table才可以看出來  * no table used 不帶from字句的查詢或者from dual查詢（explain select 1;） **使用 not in() 形式的子查詢查詢或者not exists運算符的連接查詢，這種叫做反鏈接** **即：**一般連接先查詢內表再查詢外表，反鏈接就是先查詢外表再查詢內表 * **using filesort** * 排序時無法使用到所以就會出現這個，常見于order by和group by * 說明MySQL會使用一個外部的索引進行排序，而不是按照索引順序進行讀取 * MySQL中無法利用索引完成的排序就叫“文件排序”  * **using index 查詢時候不需要回表** * 表示相應的select查詢中使用到了**覆蓋索引(Covering index)**，避免訪問表的數據行 * 如果同時出現了using where，說明索引被用來執行查詢鍵值 * 如果沒有using where，表示讀取數據而不是執行查找操作  * **using where** * 表示存儲引擎返回的記錄并不都是符合條件的，需要在server層進行篩選過濾，性能很低  * **using index condition** * 索引下推，不需要再在server層進行過濾,5.6.x開始支持 * **first match** * 5.6.x 開始出現的優化子查詢的新特性之一，常見于where字句含有in()類型的子查詢，如果內表數據量過大，可能出現 * **loosescan** * 5.6.x 開始出現的優化子查詢的新特性之一，常見于where字句含有in()類型的子查詢，如果內表返回有重復值，可能出現 ## filtered 列 5.7之后的版本默認就有這個字段，不需要使用explain extended了。這個字段表示存儲引擎返回的數據在server層過濾后，剩下多少滿足查詢的記錄數量的比例，注意是百分比，不是具體記錄數。 轉載：https://zhuanlan.zhihu.com/p/149807046