MySQL查詢分析器EXPLAIN或DESC用法

MySQL查詢分析器EXPLAIN或DESC用法

一、簡單例子：

MySQL可以通過EXPLAIN或DESC來檢視并分析SQL語句的執行情況

mysql> desc one_and_two_kn_accumulate_stars;
或者
mysql> explain one_and_two_kn_accumulate_stars;
+---------------------------+--------------+------+-----+-------------------+-----------------------------+
| Field                     | Type         | Null | Key | Default           | Extra                       |
+---------------------------+--------------+------+-----+-------------------+-----------------------------+
| kn_id                     | int(11)      | NO   | PRI | 0                 |                             |
| current_state_date        | date         | NO   | PRI | NULL              |                             |
| grade_group_id            | int(2)       | NO   | MUL | NULL              |                             |
| subject_id                | int(2)       | NO   | MUL | NULL              |                             |
| kn_name                   | varchar(255) | NO   |     |                   |                             |
| parent_kn_id              | int(11)      | NO   | MUL | 0                 |                             |
| knowledge_level           | tinyint(1)   | NO   |     | NULL              |                             |
| knowledge_accumulate_star | smallint(5)  | NO   |     | NULL              |                             |
| ctime                     | timestamp    | YES  |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| knowledge_id              | varchar(32)  | NO   | MUL |                   |                             |
| parent_knowledge_id       | varchar(32)  | NO   | MUL |                   |                             |
| sort_index                | int(2)       | NO   |     | NULL              |                             |
+---------------------------+--------------+------+-----+-------------------+-----------------------------+
12 rows in set (0.00 sec)
mysql> desc one_and_two_kn_accumulate_stars \G;
           

由這個例子可以看出： Field:字段表示的是列名 　Type:字段表示的是列的資料類型 Null :字段表示這個列是否能取空值 Key :在mysql中key 和index 是一樣的意思，這個Key列可能會看到有如下的值：PRI(主鍵)、MUL(普通 的b-tree索引)、UNI(唯一索引) Default: 列的預設值 Extra :其它資訊

二、具體使用格式

explain或desc顯示了mysql如何使用索引來處理select語句以及連接配接表。可以幫助選擇更好的索引和寫出更優化的查詢語句。 explain 資料表 或 desc 資料表 顯示資料表各字段含義 explain sql 或desc sql 顯示sql執行效率 使用方法，在select語句前加上explain就可以了，如：

mysql> desc select * from  one_and_two_kn_accumulate_stars where kn_id=1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: one_and_two_kn_accumulate_stars
   partitions: NULL
         type: ref
possible_keys: PRIMARY,kn_id_idx
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
           

2.1、explain列的解釋

table：查詢的資料表

type：這是重要的列，顯示連接配接使用了何種類型。從最好到最差的連接配接類型為const、eq_reg、ref、range、index和all。

possible_keys：顯示可能應用在這張表中的索引。如果為空，沒有可能的索引。可以為相關的域從where語句中選擇一個合适的語句

key： 實際使用的索引。如果為null，則沒有使用索引。很少的情況下，mysql會選擇優化不足的索引。這種情況下，可以在select語句中使用use index（indexname）來強制使用一個索引或者用ignore index（indexname）來強制mysql忽略索引

key_len：使用的索引的長度。在不損失精确性的情況下，長度越短越好

ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數

rows：mysql認為必須檢查的用來傳回請求資料的行數，mysql完成此請求掃描的行數。

extra：關于mysql如何解析查詢的額外資訊。将在表4.3中讨論，但這裡可以看到的壞的例子是using temporary和using filesort，意思mysql根本不能使用索引，結果是檢索會很慢。

2.2、type中各個值解釋

1）all：全表掃描，MySQL将周遊全表以找到比對的行

2）index：index與ALL差別為index類型隻周遊索引樹

3）range：索引範圍掃描，對索引掃描開始于某一點，傳回比對值域的行。顯而易見索引範圍掃描是帶有between或者where子句裡帶有<, >查詢。當mysql使用索引去查找一系列值時，例如IN()和OR清單，也會顯示range（範圍掃描）,當然性能上是有差異的。

4）ref,使用非唯一索引掃描或者唯一索引的字首掃描，傳回比對某個單獨值的記錄行。

explain select * from t1 where name=

'yayun'

;

5）eq_ref，類似ref，差別就在使用的索引是唯一索引，對于每個索引鍵值，表中隻有一條記錄比對，簡單來說，就是多表連接配接中使用primary key或者 unique key作為關聯條件。

explain select t1.name from t1, t2 where t1.id=t2.id;

6）const，system，當MySQL對查詢某部分進行優化，并轉換為一個常量時，使用這些類型通路。如将主鍵置于where清單中，MySQL就能将該查詢轉換為一個常量。system是const類型的特例，當查詢的表隻有一行的情況下，使用system。

三、mysql explain執行計劃詳解

  1）、id列數字越大越先執行，如果說數字一樣大，那麼就從上往下依次執行，id列為null的就表是這是一個結果集，不需要使用它來進行查詢。   2）、select_type列常見的有：  

A：simple：表示不需要union操作或者不包含子查詢的簡單select查詢。有連接配接查詢時，外層的查詢為simple，且隻有一個

B：primary：一個需要union操作或者含有子查詢的select，位于最外層的機關查詢的select_type即為primary。且隻有一個

C：union：union連接配接的兩個select查詢，第一個查詢是dervied派生表，除了第一個表外，第二個以後的表select_type都是union

D：dependent union：與union一樣，出現在union 或union all語句中，但是這個查詢要受到外部查詢的影響

E：union result：包含union的結果集，在union和union all語句中,因為它不需要參與查詢，是以id字段為null

F：subquery：除了from字句中包含的子查詢外，其他地方出現的子查詢都可能是subquery

G：dependent subquery：與dependent union類似，表示這個subquery的查詢要受到外部表查詢的影響

H：derived：from字句中出現的子查詢，也叫做派生表，其他資料庫中可能叫做内聯視圖或嵌套select

3）、table          顯示的查詢表名，如果查詢使用了别名，那麼這裡顯示的是别名，如果不涉及對資料表的操作，那麼這顯示為null，如果顯示為尖括号括起來的<derived N>就表示這個是臨時表，後邊的N就是執行計劃中的id，表示結果來自于這個查詢産生。如果是尖括号括起來的<union M,N>，與<derived N>類似，也是一個臨時表，表示這個結果來自于union查詢的id為M,N的結果集。   4）、type   依次從好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type隻可以用到一個索引

A：system：表中隻有一行資料或者是空表，且隻能用于myisam和memory表。如果是Innodb引擎表，type列在這個情況通常都是all或者index

B：const：使用唯一索引或者主鍵，傳回記錄一定是1行記錄的等值where條件時，通常type是const。其他資料庫也叫做唯一索引掃描

C：eq_ref：出現在要連接配接過個表的查詢計劃中，驅動表隻傳回一行資料，且這行資料是第二個表的主鍵或者唯一索引，且必須為not null，唯一索引和主鍵是多列時，隻有所有的列都用作比較時才會出現eq_ref

D：ref：不像eq_ref那樣要求連接配接順序，也沒有主鍵和唯一索引的要求，隻要使用相等條件檢索時就可能出現，常見與輔助索引的等值查找。或者多列主鍵、唯一索引中，使用第一個列之外的列作為等值查找也會出現，總之，傳回資料不唯一的等值查找就可能出現。

E：fulltext：全文索引檢索，要注意，全文索引的優先級很高，若全文索引和普通索引同時存在時，mysql不管代價，優先選擇使用全文索引

F：ref_or_null：與ref方法類似，隻是增加了null值的比較。實際用的不多。

G：unique_subquery：用于where中的in形式子查詢，子查詢傳回不重複值唯一值

H：index_subquery：用于in形式子查詢使用到了輔助索引或者in常數清單，子查詢可能傳回重複值，可以使用索引将子查詢去重。

I：range：索引範圍掃描，常見于使用>,<,is null,between ,in ,like等運算符的查詢中。

J：index_merge：表示查詢使用了兩個以上的索引，最後取交集或者并集，常見and ，or的條件使用了不同的索引，官方排序這個在ref_or_null之後，但是實際上由于要讀取所個索引，性能可能大部分時間都不如range

K：index：索引全表掃描，把索引從頭到尾掃一遍，常見于使用索引列就可以處理不需要讀取資料檔案的查詢、可以使用索引排序或者分組的查詢。

L：all：這個就是全表掃描資料檔案，然後再在server層進行過濾傳回符合要求的記錄。

5）、possible_keys 查詢可能使用到的索引都會在這裡列出來   6）、key 查詢真正使用到的索引，select_type為index_merge時，這裡可能出現兩個以上的索引，其他的select_type這裡隻會出現一個。   7）、key_len 用于處理查詢的索引長度，如果是單列索引，那就整個索引長度算進去，如果是多列索引，那麼查詢不一定都能使用到所有的列，具體使用到了多少個列的索引，這裡就會計算進去，沒有使用到的列，這裡不會計算進去。留意下這個列的值，算一下你的多列索引總長度就知道有沒有使用到所有的列了。要注意，mysql的ICP特性使用到的索引不會計入其中。另外，key_len隻計算where條件用到的索引長度，而排序和分組就算用到了索引，也不會計算到key_len中。   8）、ref 如果是使用的常數等值查詢，這裡會顯示const，如果是連接配接查詢，被驅動表的執行計劃這裡會顯示驅動表的關聯字段，如果是條件使用了表達式或者函數，或者條件列發生了内部隐式轉換，這裡可能顯示為func   9）、rows 這裡是執行計劃中估算的掃描行數，不是精确值   10）、extra 這個列可以顯示的資訊非常多，有幾十種，常用的有

A：distinct：在select部分使用了distinc關鍵字

B：no tables used：不帶from字句的查詢或者From dual查詢

C：使用not in()形式子查詢或not exists運算符的連接配接查詢，這種叫做反連接配接。即，一般連接配接查詢是先查詢内表，再查詢外表，反連接配接就是先查詢外表，再查詢内表。

D：using filesort：排序時無法使用到索引時，就會出現這個。常見于order by和group by語句中

E：using index：查詢時不需要回表查詢，直接通過索引就可以擷取查詢的資料。

F：using join buffer（block nested loop），using join buffer（batched key accss）：5.6.x之後的版本優化關聯查詢的BNL，BKA特性。主要是減少内表的循環數量以及比較順序地掃描查詢。

G：using sort_union，using_union，using intersect，using sort_intersection：

using intersect：表示使用and的各個索引的條件時，該資訊表示是從處理結果擷取交集

using union：表示使用or連接配接各個使用索引的條件時，該資訊表示從處理結果擷取并集

using sort_union和using sort_intersection：與前面兩個對應的類似，隻是他們是出現在用and和or查詢資訊量大時，先查詢主鍵，然後進行排序合并後，才能讀取記錄并傳回。

H：using temporary：表示使用了臨時表存儲中間結果。臨時表可以是記憶體臨時表和磁盤臨時表，執行計劃中看不出來，需要檢視status變量，used_tmp_table，used_tmp_disk_table才能看出來。

I：using where：表示存儲引擎傳回的記錄并不是所有的都滿足查詢條件，需要在server層進行過濾。查詢條件中分為限制條件和檢查條件，5.6之前，存儲引擎隻能根據限制條件掃描資料并傳回，然後server層根據檢查條件進行過濾再傳回真正符合查詢的資料。5.6.x之後支援ICP特性，可以把檢查條件也下推到存儲引擎層，不符合檢查條件和限制條件的資料，直接不讀取，這樣就大大減少了存儲引擎掃描的記錄數量。extra列顯示using index condition

J：firstmatch(tb_name)：5.6.x開始引入的優化子查詢的新特性之一，常見于where字句含有in()類型的子查詢。如果内表的資料量比較大，就可能出現這個

K：loosescan(m..n)：5.6.x之後引入的優化子查詢的新特性之一，在in()類型的子查詢中，子查詢傳回的可能有重複記錄時，就可能出現這個

除了這些之外，還有很多查詢資料字典庫，執行計劃過程中就發現不可能存在結果的一些提示資訊   11）、filtered 使用explain extended時會出現這個列，5.7之後的版本預設就有這個字段，不需要使用explain extended了。這個字段表示存儲引擎傳回的資料在server層過濾後，剩下多少滿足查詢的記錄數量的比例，注意是百分比，不是具體記錄數。

參考網址: https://www.cnblogs.com/xiaoboluo768/p/5400990.html https://www.cnblogs.com/usa007lhy/p/6444216.html