前言

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

執(zhí)行計(jì)劃

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

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

explain 的結(jié)果如下：

下面是有關(guān)各列的詳細(xì)介紹，重要的有id、type、key、rows、extra。

id

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

select_type

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

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

explain select * from t3 where id=3952602;

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

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

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

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：若第二個(gè) select 出現(xiàn)在 union 之后，則被標(biāo)記為 union；若 union 包含在 from 子句的子查詢中，外層 select 將被標(biāo)記為 derived。

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

union result：從 union 表獲取結(jié)果的 select ，因?yàn)樗恍枰獏⑴c查詢，所以 id 字段為 null。

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

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

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

table

表示 explain 的一行正在訪問(wèn)哪個(gè)表。

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

type

訪問(wèn)類型，即 MySQL 決定如何查找表中的行。

依次從好到差：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL，除了 all 之外，其他的 type 都可以使用到索引，除了 index_merge 之外，其他的 type 只可以用到一個(gè)索引。一般來(lái)說(shuō)，得保證查詢至少達(dá)到 range 級(jí)別，最好能達(dá)到 ref。

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

possible_keys

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

key

顯示查詢實(shí)際使用哪個(gè)索引來(lái)優(yōu)化對(duì)該表的訪問(wèn)；

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

key_len

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

ref

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

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

rows

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

extra

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

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

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

filtered

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

關(guān)于 MySQL 執(zhí)行計(jì)劃的局限性

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

查詢計(jì)劃案例分析

執(zhí)行順序

（id = 4）：【select id, name from t2】：select_type 為 union，說(shuō)明 id=4 的 select 是 union 里面的第二個(gè) select。 （id = 3）：【select id, name from t1 where address = ‘11’】：因?yàn)槭窃?from 語(yǔ)句中包含的子查詢所以被標(biāo)記為 DERIVED（衍生），where address = ‘11’ 通過(guò)復(fù)合索引 idx_name_email_address 就能檢索到，所以 type 為 index。 （id = 2）：【select id from t3】：因?yàn)槭窃?select 中包含的子查詢所以被標(biāo)記為 SUBQUERY。 （id = 1）：【select d1.name, … d2 from … d1】：select_type 為 PRIMARY 表示該查詢?yōu)樽钔鈱硬樵儯瑃able 列被標(biāo)記為 “derived3”表示查詢結(jié)果來(lái)自于一個(gè)衍生表（id = 3 的 select 結(jié)果）。 （id = NULL）：【 … union … 】：代表從 union 的臨時(shí)表中讀取行的階段，table 列的 “union 1, 4”表示用 id=1 和 id=4 的 select 結(jié)果進(jìn)行 union 操作。

總結(jié)

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