前言
如何寫(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
查詢的類(lèi)型，主要用于區(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 類(lèi)似，子查詢中的第一個(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>，與類(lèi)似，也是一個(gè)臨時(shí)表，表示這個(gè)結(jié)果來(lái)自于 union 查詢的 id 為 M,N 的結(jié)果集。
type
訪問(wèn)類(lèi)型，即 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 類(lèi)型的特例，平時(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 方法類(lèi)似，只是增加了 null 值的比較。
- index_merge：表示查詢使用了兩個(gè)以上的索引，索引合并的優(yōu)化方法，最后取交集或者并集，常見(jiàn) and ，or 的條件使用了不同的索引。
- unique_subquery：用于 where 中的 in 形式子查詢，子查詢返回不重復(fù)值唯一值；
- index_subquery：用于 in 形式子查詢使用到了輔助索引或者 in 常數(shù)列表，子查詢可能返回重復(fù)值，可以使用索引將子查詢?nèi)ブ亍?/li>
- 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è)列可以顯示的信息非常多，有幾十種，常用的有：
| 類(lèi)型 |
說(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)樽钔鈱硬樵?，table 列被標(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)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- 詳解 MySQL 執(zhí)行計(jì)劃
- MySQL執(zhí)行計(jì)劃的深入分析
- mysql執(zhí)行計(jì)劃id為空（UNION關(guān)鍵字）詳解
- MySQL中通過(guò)EXPLAIN如何分析SQL的執(zhí)行計(jì)劃詳解
- MySQL中執(zhí)行計(jì)劃explain命令示例詳解
- MySql中如何使用 explain 查詢 SQL 的執(zhí)行計(jì)劃
- mysql執(zhí)行計(jì)劃介紹
- MYSQL explain 執(zhí)行計(jì)劃
- 帶你學(xué)習(xí)MySQL執(zhí)行計(jì)劃
