Mysql深入探索之Explain執(zhí)行計(jì)劃詳析

前言

如何寫出效率高的SQL語(yǔ)句，提到這必然離不開(kāi)Explain執(zhí)行計(jì)劃的分析，至于什么是執(zhí)行計(jì)劃，如何寫出高效率的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。

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