前言

如何寫出效率高的SQL語句，提到這必然離不開Explain執(zhí)行計劃的分析，至于什么是執(zhí)行計劃，如何寫出高效率的SQL，本篇文章將會一一介紹。

執(zhí)行計劃

執(zhí)行計劃是數(shù)據(jù)庫根據(jù) SQL 語句和相關表的統(tǒng)計信息作出的一個查詢方案，這個方案是由查詢優(yōu)化器自動分析產(chǎn)生的。

使用explain關鍵字可以模擬優(yōu)化器執(zhí)行 SQL 查詢語句，從而知道 MySQL 是如何處理你的 SQL 語句的，分析你的 select 語句或是表結構的性能瓶頸，讓我們知道 select 效率低下的原因，從而改進我們的查詢。

explain 的結果如下：

下面是有關各列的詳細介紹，重要的有id、type、key、rows、extra。

id

id 列的編號就是 select 的序列號，也可以理解為 SQL 執(zhí)行順序的標識，有幾個 select 就有幾個 id。 id 值不同：如果是只查詢，id 的序號會遞增，id 值越大優(yōu)先級越高，越先被執(zhí)行； id 值相同：從上往下依次執(zhí)行； id 列為 null：表示這是一個結果集，不需要使用它來進行查詢。

select_type

查詢的類型，主要用于區(qū)分普通查詢、聯(lián)合查詢、子查詢等復雜的查詢；

simple：表示查詢中不包括 union 操作或者子查詢，位于最外層的查詢的 select_type 即為 simple，且只有一個；

explain select * from t3 where id=3952602;

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

explain select * from (select * from t3 where id=3952602) a ;

derived：from 列表中出現(xiàn)的子查詢，也叫做衍生表；mysql 或者遞歸執(zhí)行這些子查詢，把結果放在臨時表里。

explain select * from (select * from t3 where id=3952602) a ;

subquery：除了 from 子句中包含的子查詢外，其他地方出現(xiàn)的子查詢都可能是 subquery。

explain select * from t3 where id = (select id from t3 whereid=3952602 ) ;

union：若第二個 select 出現(xiàn)在 union 之后，則被標記為 union；若 union 包含在 from 子句的子查詢中，外層 select 將被標記為 derived。

explain select * from t3 where id=3952602 union all select * from t3;

union result：從 union 表獲取結果的 select ，因為它不需要參與查詢，所以 id 字段為 null。

explain select * from t3 where id=3952602 union all select * from t3;

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

dependent subquery：與 dependent union 類似，子查詢中的第一個 SELECT，這個 subquery 的查詢要受到外部表查詢的影響。

table

表示 explain 的一行正在訪問哪個表。

如果查詢使用了別名，那么這里顯示的是別名; 如果不涉及對數(shù)據(jù)表的操作，那么這顯示為 null; 如果顯示為尖括號括起來的就表示這個是臨時表，后邊的 N 就是執(zhí)行計劃中的 id，表示結果來自于這個查詢產(chǎn)生; 如果是尖括號括起來的<union M,N>，與類似，也是一個臨時表，表示這個結果來自于 union 查詢的 id 為 M,N 的結果集。

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 只可以用到一個索引。一般來說，得保證查詢至少達到 range 級別，最好能達到 ref。

system：表中只有一行數(shù)據(jù)（等于系統(tǒng)表），這是 const 類型的特例，平時不會出現(xiàn)，可以忽略不計。 const：使用唯一索引或者主鍵，表示通過索引一次就找到了，const 用于比較 primary key 或者 unique 索引。因為只需匹配一行數(shù)據(jù)，所有很快。如果將主鍵置于 where 列表中，mysql 就能將該查詢轉換為一個 const。 eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一行數(shù)據(jù)與之匹配。常見于主鍵或唯一索引掃描。 ref：非唯一性索引掃描，返回匹配某個單獨值的所有行。本質(zhì)也是一種索引。 fulltext：全文索引檢索，全文索引的優(yōu)先級很高，若全文索引和普通索引同時存在時，mysql 不管代價，優(yōu)先選擇使用全文索引。 ref_or_null：與 ref 方法類似，只是增加了 null 值的比較。 index_merge：表示查詢使用了兩個以上的索引，索引合并的優(yōu)化方法，最后取交集或者并集，常見 and ，or 的條件使用了不同的索引。 unique_subquery：用于 where 中的 in 形式子查詢，子查詢返回不重復值唯一值； index_subquery：用于 in 形式子查詢使用到了輔助索引或者 in 常數(shù)列表，子查詢可能返回重復值，可以使用索引將子查詢?nèi)ブ亍? range：索引范圍掃描，常見于使用>,<,between ,in ,like等運算符的查詢中。 index：索引全表掃描，把索引樹從頭到尾掃一遍； all：遍歷全表以找到匹配的行（Index 與 ALL 雖然都是讀全表，但 index 是從索引中讀取，而 ALL 是從硬盤讀?。? NULL: MySQL 在優(yōu)化過程中分解語句，執(zhí)行時甚至不用訪問表或索引。

possible_keys

顯示查詢可能使用到的索引。

key

顯示查詢實際使用哪個索引來優(yōu)化對該表的訪問；

select_type 為 index_merge 時，這里可能出現(xiàn)兩個以上的索引，其他的 select_type 這里只會出現(xiàn)一個。

key_len

用于處理查詢的索引長度，表示索引中使用的字節(jié)數(shù)。通過這個值，可以得出一個多列索引里實際使用了哪一部分。 注：key_len 顯示的值為索引字段的最大可能長度，并非實際使用長度，即 key_len 是根據(jù)表定義計算而得，不是通過表內(nèi)檢索出的。另外，key_len 只計算 where 條件用到的索引長度，而排序和分組就算用到了索引，也不會計算到 key_len 中。

ref

顯示哪個字段或者常數(shù)與 key 一起被使用。

如果是使用的常數(shù)等值查詢，這里會顯示 const。 如果是連接查詢，被驅(qū)動表的執(zhí)行計劃這里會顯示驅(qū)動表的關聯(lián)字段。 如果是條件使用了表達式或者函數(shù)，或者條件列發(fā)生了內(nèi)部隱式轉換，這里可能顯示為 func。

rows

表示 MySQL 根據(jù)表統(tǒng)計信息及索引選用情況，大致估算的找到所需的目標記錄所需要讀取的行數(shù)，不是精確值。

extra

不適合在其他列中顯示但十分重要的額外信息。

這個列可以顯示的信息非常多，有幾十種，常用的有：

類型 說明 Using filesort MySQL 有兩種方式可以生成有序的結果，通過排序操作或者使用索引，當 Extra 中出現(xiàn)了 Using filesort 說明 MySQL 使用了后者，但注意雖然叫 filesort 但并不是說明就是用了文件來進行排序，只要可能排序都是在內(nèi)存里完成的。大部分情況下利用索引排序更快，所以一般這時也要考慮優(yōu)化查詢了。使用文件完成排序操作，這是可能是 ordery by，group by 語句的結果，這可能是一個 CPU 密集型的過程，可以通過選擇合適的索引來改進性能，用索引來為查詢結果排序。 Using temporary 用臨時表保存中間結果，常用于 GROUP BY 和 ORDER BY 操作中，一般看到它說明查詢需要優(yōu)化了，就算避免不了臨時表的使用也要盡量避免硬盤臨時表的使用。 Not exists MYSQL 優(yōu)化了 LEFT JOIN，一旦它找到了匹配 LEFT JOIN 標準的行， 就不再搜索了。 Using index 說明查詢是覆蓋了索引的，不需要讀取數(shù)據(jù)文件，從索引樹（索引文件）中即可獲得信息。如果同時出現(xiàn) using where，表明索引被用來執(zhí)行索引鍵值的查找，沒有 using where，表明索引用來讀取數(shù)據(jù)而非執(zhí)行查找動作。這是 MySQL 服務層完成的，但無需再回表查詢記錄。 Using index condition 這是 MySQL 5.6 出來的新特性，叫做“索引條件推送”。簡單說一點就是 MySQL 原來在索引上是不能執(zhí)行如 like 這樣的操作的，但是現(xiàn)在可以了，這樣減少了不必要的 IO 操作，但是只能用在二級索引上。 Using where 使用了 WHERE 從句來限制哪些行將與下一張表匹配或者是返回給用戶。注意：Extra 列出現(xiàn) Using where 表示 MySQL 服務器將存儲引擎返回服務層以后再應用 WHERE 條件過濾。 Using join buffer 使用了連接緩存：Block Nested Loop，連接算法是塊嵌套循環(huán)連接;Batched Key Access，連接算法是批量索引連接 impossible where where 子句的值總是 false，不能用來獲取任何元組 select tables optimized away 在沒有 GROUP BY 子句的情況下，基于索引優(yōu)化 MIN/MAX 操作，或者對于 MyISAM 存儲引擎優(yōu)化 COUNT(*)操作，不必等到執(zhí)行階段再進行計算，查詢執(zhí)行計劃生成的階段即完成優(yōu)化。 distinct 優(yōu)化 distinct 操作，在找到第一匹配的元組后即停止找同樣值的動作

filtered

使用 explain extended 時會出現(xiàn)這個列，5.7 之后的版本默認就有這個字段，不需要使用 explain extended 了。 這個字段表示存儲引擎返回的數(shù)據(jù)在 server 層過濾后，剩下多少滿足查詢的記錄數(shù)量的比例，注意是百分比，不是具體記錄數(shù)。

關于 MySQL 執(zhí)行計劃的局限性

EXPLAIN 不會告訴你關于觸發(fā)器、存儲過程的信息或用戶自定義函數(shù)對查詢的影響情況； EXPLAIN 不考慮各種 Cache； EXPLAIN 不能顯示 MySQL 在執(zhí)行查詢時所作的優(yōu)化工作； 部分統(tǒng)計信息是估算的，并非精確值； EXPALIN 只能解釋 SELECT 操作，其他操作要重寫為 SELECT 后查看。

查詢計劃案例分析

執(zhí)行順序

（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 表示該查詢?yōu)樽钔鈱硬樵?，table 列被標記為 “derived3”表示查詢結果來自于一個衍生表（id = 3 的 select 結果）。 （id = NULL）：【 … union … 】：代表從 union 的臨時表中讀取行的階段，table 列的 “union 1, 4”表示用 id=1 和 id=4 的 select 結果進行 union 操作。

總結

到此這篇關于Mysql深入探索之Explain執(zhí)行計劃的文章就介紹到這了,更多相關Mysql Explain執(zhí)行計劃內(nèi)容請搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持好吧啦網(wǎng)！