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