MySQL数据库：explain执行计划详解
一、執行計劃：
執行計劃是SQL語句經過查詢分析器后得到的 抽象語法樹 和 相關表的統計信息 作出的一個查詢方案，這個方案是由查詢優化器自動分析產生的。由于是動態數據采樣統計分析出來的結果，所以可能會存在分析錯誤的情況，也就是存在執行計劃并不是最優的情況。
通過explain關鍵字知道MySQL是如何執行SQL查詢語句的，分析select 語句的性能瓶頸，從而改進我們的查詢，explain的結果如下：
下面是有關各列的詳細介紹，重要的有id、type、key、key_len、rows、extra：
?
1、id：
id列的編號就是select 的序列號，可以理解為SQL執行順序的標識，有幾個select 就有幾個id。
- （1）id值不同：id值越大優先級越高，越先被執行；
- （2）id值相同：從上往下依次執行；
- （3）id列為null：表示這是一個結果集，不需要使用它來進行查詢。
?
2、select_type：
查詢的類型，主要用于區分普通查詢、聯合查詢、子查詢等復雜的查詢；
（1）simple：表示查詢中不包括union操作或者子查詢，位于最外層的查詢的select_type即為simple，且只有一個；
explain select * from t3 where id=3952602;
（2）primary：需要union操作或者含有子查詢的select，位于最外層的查詢的select_type即為primary，且只有一個；
explain select * from (select * from t3 where id=3952602) a ;
（3）derived：from列表中出現的子查詢，也叫做衍生表；mysql或者遞歸執行這些子查詢，把結果放在臨時表里。
explain select * from (select * from t3 where id=3952602) a ;
（4）subquery：除了from子句中包含的子查詢外，其他地方出現的子查詢都可能是subquery。
explain select * from t3 where id = (select id from t3 whereid=3952602 ) ；
（5）union：若第二個select出現在union之后，則被標記為union；若union包含在from子句的子查詢中，外層select將被標記為derived。
explain select * from t3 where id=3952602 union all select * from t3；
（6）union result：從union表獲取結果的select ，因為它不需要參與查詢，所以id字段為null。
explain select * from t3 where id=3952602 union all select * from t3;
（7）dependent union：與union一樣，出現在union 或union all語句中，但是這個查詢要受到外部查詢的影響；
（8）dependent subquery：與dependent union類似，子查詢中的第一個SELECT，這個subquery的查詢要受到外部表查詢的影響。
?
3、table：
表示 explain 的一行正在訪問哪個表。
- （1）如果查詢使用了別名，那么這里顯示的是別名;
- （2）如果不涉及對數據表的操作，那么這顯示為null;
- （3）如果顯示為尖括號括起來的?<derived N>?就表示這個是臨時表，后邊的N就是執行計劃中的id，表示結果來自于這個查詢產生;
- （4）如果是尖括號括起來的?<union M,N>，與<derived N>類似，也是一個臨時表，表示這個結果來自于union查詢的id為M,N的結果集。
?
4、type：
訪問類型，即MySQL決定如何查找表中的行。依次從好到差：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL，除了all之外，其他的 type類型 都可以使用到索引，除了 index_merge 之外，其他的type只可以用到一個索引。一般要求type為ref，范圍查找需要達到 range。
（1）system：表中只有一條元組匹配（等于系統表），這是 const 類型的特例，平時不會出現，可以忽略不計。?
（2）const：通過索引一次就找到了，表示使用主鍵索引或者唯一索引。
（3）eq_ref：主鍵或者唯一索引中的所有字段被用于連接使用，只會返回一行匹配的數據。簡單的select查詢語句不會出現這種情況。
（4）ref：普通索引掃描，可能返回多個符合查詢條件的行。
（5）fulltext：全文索引檢索，全文索引的優先級很高，若全文索引和普通索引同時存在時，mysql不管代價，優先選擇使用全文索引。
（6）ref_or_null：與ref方法類似，只是增加了null值的比較。
（7）index_merge：表示查詢使用了兩個以上的索引，索引合并的優化方法，最后取交集或者并集，常見and ，or的條件使用了不同的索引。
（8）unique_subquery：用于where中的in形式子查詢，子查詢返回不重復值唯一值；
（9）index_subquery：用于in形式子查詢使用到了輔助索引或者in常數列表，子查詢可能返回重復值，可以使用索引將子查詢去重。
（9）range：索引范圍掃描，常見于使用>,<,between ,in ,like等運算符的查詢中。
（10）index：索引全表掃描，把索引樹從頭到尾掃描一遍；
（11）all：遍歷全表以找到匹配的行（Index與ALL雖然都是讀全表，但index是從索引中讀取，而ALL是從硬盤讀取）
（12）NULL:?MySQL在優化過程中分解語句，執行時甚至不用訪問表或索引。
?
5、possible_keys：
顯示查詢可能使用到的索引。
?
6、key：
實際使用哪個索引來優化對該表的訪問；select_type為index_merge時，這里可能出現兩個以上的索引，其他的select_type這里只會出現一個。
?
7、key_len：
實際上用于優化查詢的索引長度，即索引中使用的字節數。通過這個值，可以計算出一個多列索引里實際使用了索引的哪寫字段。key_len的計算規則：
| 字段類型 | 索引長度 |
| tinyint | 1字節 |
| smallint | 2字節 |
| int | 4字節 |
| bigint | 8字節 |
| date | 3字節 |
| timestamp | 4字節 |
| datetime | 8字節 |
| char(n) | GBK編碼：2n字節 utf8編碼：3n字節 utf8mb4編碼：4n字節 |
| varchar(n) | GBK編碼：（2n+2）字節 utf8編碼：（3n+2）字節 utf8mb4編碼：（4n+2）字節 |
?注意：
（1）如果字段允許為 NULL，需要額外1字節記錄是否為 NULL?
（2）索引的最大長度為767字節，當字符串過長時，mysql會做類似左前綴索引的處理，將前半部分的字符提取出來做索引。
（3）key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據表定義計算而得，不是通過表內檢索出的。
（4）key_len只計算where條件用到的索引長度，而排序和分組就算用到了索引，也不會計算到key_len中。
?
8、ref：
顯示哪個字段或者常量與key一起被使用。
（1）如果是使用的常量等值查詢，這里會顯示const。
（2）如果是連接查詢，被驅動表的執行計劃這里會顯示驅動表的關聯字段。
（3）如果是條件使用了表達式或者函數，或者條件列發生了內部隱式轉換，這里可能顯示為func。
?
9、rows：
表示MySQL根據表統計信息及索引選用情況，大致估算此處查詢需要讀取的行數，不是精確值。
?
10、extra：
這一列展示一些額外信息，同樣十分重要。這個列可以顯示的信息非常多，有幾十種，常用的有：
| 類型 | 說明 |
| using index | 使用覆蓋索引 |
| using index condition | 查詢的列未被索引覆蓋，where篩選條件是索引的前導列 |
| using where | 查詢的列未被索引覆蓋，where篩選條件非索引的前導列 |
| using index;using where | 查詢的列被索引覆蓋，where篩選條件非索引的前導列 |
| NULL | （既沒有using index，也沒有using where; using index，也沒有using where） 查詢的列未被索引覆蓋，并且where篩選條件是索引的前導列。意味著用到了索引，但是部分字段未被索引覆蓋，須通過“回表”來獲取查詢所需的字段 |
| using temporary | 用臨時表保存中間結果，常用于GROUP BY 和 ORDER BY操作中，通常是因為group by的列上沒有索引。也有可能是因為同時有group by和order by，但group by和order by的列又不一樣，一般看到它說明查詢需要優化了 |
| using filesort | MySQL有兩種方式對查詢結果進行排序，一種是使用索引，另一種是filesort（基于快排實現的外部排序，性能比較差），當數據量很大時，這將是一個CPU密集型的過程，所以可以通過建立合適的索引來優化排序的性能 |
| using join buffer | 使用了連接緩存： （2）Batched Key Access：連接算法是批量索引連接 |
| Not exists | MYSQL優化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標準的行， 就不再搜索了 |
| impossible where | where子句的值總是false |
| distinct | 優化distinct操作，在找到第一匹配的元組后即停止找同樣值的動作 |
| select tables optimized away | 在沒有GROUP BY子句的情況下，基于索引優化MIN/MAX操作，或者對于MyISAM存儲引擎優化COUNT(*)操作，不必等到執行階段再進行計算，查詢執行計劃生成的階段即完成優化 |
?
11、filtered：
使用explain extended時會出現這個列，5.7之后的版本默認就有這個字段，不需要使用explain extended了。這個字段表示存儲引擎返回的數據在server層過濾后，剩下多少滿足查詢的記錄數量的比例，注意是百分比，不是具體記錄數。
?
二、MySQL執行計劃的局限性：
（1）EXPLAIN不會告訴你關于觸發器、存儲過程的信息或用戶自定義函數對查詢的影響情況；
（2）EXPLAIN不考慮各種Cache；
（3）EXPLAIN不能顯示MySQL在執行查詢時所作的優化工作；
（4）部分統計信息是估算的，并非精確值；
（5）EXPALIN只能解釋SELECT操作，其他操作要重寫為SELECT后查看。
?
三、案例分析：
執行順序：
第一：（id = 4）：【select id, name from t2】：select_type 為union，說明id=4的select是union里面的第二個select。
第二：（id = 3）：【select id, name from t1 where address = ‘11’】：因為是在from語句中包含的子查詢所以被標記為DERIVED（衍生），where address = ‘11’ 通過復合索引idx_name_email_address就能檢索到，所以type為index。
第三：（id = 2）：【select id from t3】：因為是在select中包含的子查詢所以被標記為SUBQUERY。
第四：（id = 1）：【select d1.name, … d2 from … d1】：select_type為PRIMARY表示該查詢為最外層查詢，table列被標記為 “derived3”表示查詢結果來自于一個衍生表（id = 3 的select結果）。
第五：（id = NULL）：【 … union … 】：代表從union的臨時表中讀取行的階段，table列的 “union 1, 4”表示用id=1 和 id=4 的select結果進行union操作。
?
總結
以上是生活随笔為你收集整理的MySQL数据库：explain执行计划详解的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Java虚拟机：性能监控与故障处理工具
- 下一篇： MySQL数据库：使用show prof