前言

如何寫出效率高的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 語句或是表結(jié)構(gòu)的性能瓶頸，讓我們知道 select 效率低下的原因，從而改進我們的查詢。

explain 的結(jié)果如下：

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

id

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

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í)行這些子查詢，把結(jié)果放在臨時表里。

 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 表獲取結(jié)果的 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，表示結(jié)果來自于這個查詢產(chǎn)生;
• 如果是尖括號括起來的<union M,N>，與類似，也是一個臨時表，表示這個結(jié)果來自于 union 查詢的 id 為 M,N 的結(jié)果集。

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。

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

rows

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

extra

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

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

filtered

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

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

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

查詢計劃案例分析

執(zhí)行順序

1. （id = 4）：【select id, name from t2】：select_type 為 union，說明 id=4 的 select 是 union 里面的第二個 select。
2. （id = 3）：【select id, name from t1 where address = ‘11'】：因為是在 from 語句中包含的子查詢所以被標記為 DERIVED（衍生），where address = ‘11' 通過復合索引 idx_name_email_address 就能檢索到，所以 type 為 index。
3. （id = 2）：【select id from t3】：因為是在 select 中包含的子查詢所以被標記為 SUBQUERY。
4. （id = 1）：【select d1.name, … d2 from … d1】：select_type 為 PRIMARY 表示該查詢?yōu)樽钔鈱硬樵?，table 列被標記為 “derived3”表示查詢結(jié)果來自于一個衍生表（id = 3 的 select 結(jié)果）。
5. （id = NULL）：【 … union … 】：代表從 union 的臨時表中讀取行的階段，table 列的 “union 1, 4”表示用 id=1 和 id=4 的 select 結(jié)果進行 union 操作。

總結(jié)

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

最新評論