前言

• 如何寫出效率高的SQL語(yǔ)句，提到這必然離不開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ù)雜的查詢；

    explain select * from t3 where id=3952602;

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

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

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

• subquery：除了 from 子句中包含的子查詢外，其他地方出現(xiàn)的子查詢都可能是 subquery。
• derived：from 列表中出現(xiàn)的子查詢，也叫做衍生表；mysql 或者遞歸執(zhí)行這些子查詢，把結(jié)果放在臨時(shí)表里。
• primary：需要 union 操作或者含有子查詢的 select，位于最外層的查詢的 select_type 即為 primary，且只有一個(gè)；
• simple：表示查詢中不包括 union 操作或者子查詢，位于最外層的查詢的 select_type 即為 simple，且只有一個(gè)；

• 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;

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

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

• 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ù)與之匹配。常見于主鍵或唯一索引掃描。

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)化方法，最后取交集或者并集，常見 and ，or 的條件使用了不同的索引。

8. unique_subquery：用于 where 中的 in 形式子查詢，子查詢返回不重復(fù)值唯一值；

9. index_subquery：用于 in 形式子查詢使用到了輔助索引或者 in 常數(shù)列表，子查詢可能返回重復(fù)值，可以使用索引將子查詢?nèi)ブ亍?/p>

10. range：索引范圍掃描，常見于使用>,<,between ,in ,like等運(yùn)算符的查詢中。

11. index：索引全表掃描，把索引樹從頭到尾掃一遍；

12. all：遍歷全表以找到匹配的行（Index 與 ALL 雖然都是讀全表，但 index 是從索引中讀取，而 ALL 是從硬盤讀取）

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 一起被使用。

1. 如果是使用的常數(shù)等值查詢，這里會(huì)顯示 const。

2. 如果是連接查詢，被驅(qū)動(dòng)表的執(zhí)行計(jì)劃這里會(huì)顯示驅(qū)動(dòng)表的關(guān)聯(lián)字段。

3. 如果是條件使用了表達(dá)式或者函數(shù)，或者條件列發(fā)生了內(nèi)部隱式轉(zhuǎn)換，這里可能顯示為 func。

rows

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

extra

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

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

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ì)劃的局限性

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

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

執(zhí)行順序

1. （id = 4）：【select id, name from t2】：select_type 為 union，說(shuō)明 id=4 的 select 是 union 里面的第二個(gè) select。

2. （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。

3. （id = 2）：【select id from t3】：因?yàn)槭窃?select 中包含的子查詢所以被標(biāo)記為 SUBQUERY。

4. （id = 1）：【select d1.name, … d2 from … d1】：select_type 為 PRIMARY 表示該查詢?yōu)樽钔鈱硬樵?，table 列被標(biāo)記為 “derived3”表示查詢結(jié)果來(lái)自于一個(gè)衍生表（id = 3 的 select 結(jié)果）。

5. （id = NULL）：【 … union … 】：代表從 union 的臨時(shí)表中讀取行的階段，table 列的 “union 1, 4”表示用 id=1 和 id=4 的 select 結(jié)果進(jìn)行 union 操作。