MySQL應(yīng)該是我們平時用得最多的一個關(guān)系型數(shù)據(jù)庫了吧，畢竟開源免費而且功能強(qiáng)大。但是如果想知道一條SQL語句具體是怎么執(zhí)行的，總不能每次都看源碼吧，而官方就提供了一個工具——EXPLAIN語句，可以查看一條SQL的具體執(zhí)行計劃。

其實EXPLAIN我平時用得也不少，但EXPLAIN的輸出信息實在比較豐富，所以每次都是根據(jù)輸出結(jié)果然后上網(wǎng)定向查，過后就又忘了。這一次終于下定決心要把EXPLAIN的所有情況做一個總結(jié)。

本文介紹的MySQL版本

5.7

EXPLAIN的使用場景與功能

• EXPLAIN后面如果跟的是SQL語句，則只適用于DML，官方文檔直接說明：SELECT, DELETE, INSERT, REPLACE和 UPDATE 語句。也就是說你想用EXPLAIN去查看MySQL是怎么CREATE或者DROP一張表的，這是不行滴。
• EXPLAIN可以用來輸出一條SQL的具體執(zhí)行計劃，包括多個表之間的連接順序，如何連接以及索引的使用等等。
• EXPLAIN也可以用于顯示某個連接中執(zhí)行語句的執(zhí)行計劃，格式類似于EXPLAIN FOR CONNECTION connection_id。
• 針對SELECT語句，EXPLAIN可以輸出更多的執(zhí)行計劃信息，方法是在EXPLAIN后執(zhí)行SHOW WARNINGS。
• EXPLAIN可以輸出查詢語句中有關(guān)分區(qū)表的信息，分區(qū)簡單而言，就是通過一定的規(guī)則將單表做切分，然后可以以不同的形式存儲于不同的物理區(qū)域，因為跟本文關(guān)聯(lián)不是很大，所以不做重點介紹。
• EXPLAIN后面緊跟表名的話跟DESCRIBE的作用相同。

根據(jù)我自己的使用經(jīng)驗，我們平時使用EXPLAIN最多的用途就是查看某條SQL有沒有用到索引、用了哪個索引、是否有文件排序、表的關(guān)聯(lián)順序是否和預(yù)期一致等等和SQL執(zhí)行速度相關(guān)的情況，主要就是用來解決慢SQL。

EXPLIAN的輸出信息

由于情況類似，這里以SELECT語句為例，EXPLAIN會根據(jù)SELECT語句中涉及到的每個表(包括臨時表)都輸出一行信息。每一行都包含很多列，下面重點介紹每一列的含義，畢竟這最常用。

輸出列介紹

下面的表格列出了所有EXPLAIN語句輸出的列，后面會詳細(xì)說明每一列的情況。

id

這是MySQL執(zhí)行查表操作的標(biāo)識符，除了引用其他表并集結(jié)果的情況下會為null，其實一般都是有值的，而且一般是大于0的整數(shù)。id的作用就是表明了每個表之間的查詢順序，兩條規(guī)則：

1. id越大，越先被執(zhí)行
2. id相同，靠前的先執(zhí)行

select_type

下面的表格列出了select_type的所有可能值。

這里DEPENDENT SUBQUERY和UNCACHEABLE SUBQUERY是有區(qū)別的，DEPENDENT SUBQUERY針對外部查詢中的每個值重新評估(重復(fù)值評估一次)，而UNCACHEABLE SUBQUERY會針對外部的每個值都評估一次(重復(fù)值重復(fù)評估)。

• SIMPLE

• PRIMARY & UNION & UNION RESULT

• DEPENDENT UNION & UNCACHEABLE SUBQUERY

• SUBQUERY

• DEPENDENT SUBQUERY

• DERIVED

• MATERIALIZED

這是一種對子查詢的優(yōu)化，不必針對每個外層循環(huán)執(zhí)行子查詢

• UNCACHEABLE UNION

table

輸出當(dāng)前行所引用的表的名稱，也有以下其他的情況：

1. <unionm,n style="box-sizing: border-box;">：即id為M和N的查詢結(jié)果的并集</unionm,n>
2. <derivedn style="box-sizing: border-box;">：id為N的查詢結(jié)果的派生表，比如一個FROM子句中的子查詢</derivedn>
3. <subqueryn style="box-sizing: border-box;">：id為N的物化子查詢結(jié)果，可以認(rèn)為是一個內(nèi)存臨時表，用于加快查詢速度的</subqueryn>

partitions

一般為null，畢竟MySQL的分區(qū)功能比較少用，如果有值，代表當(dāng)前查詢匹配到的分區(qū)。

type

連接類型，用于表示多個表之間是怎么連接的，可以取的值需要詳細(xì)介紹，不同取值差異巨大。

• system

該表只有一行(即系統(tǒng)表)。這是const聯(lián)接類型的特例 ，一般在業(yè)務(wù)開發(fā)中不太常見。

• const

該表最多有一個匹配行，本次查詢開始的時候就已經(jīng)獲取到這個唯一值了，所以后續(xù)的所有查詢都將本次查詢到的內(nèi)容視為常量，常見的就是主鍵或者唯一索引的等值比較。

// emp_no是主鍵
explain select * from employees where emp_no = 10021;

• eq_ref

之前查詢到的結(jié)果中每一行在當(dāng)前查詢中最多匹配一行，是除了system和 const類型之外最好的聯(lián)接類型。常見的就是：主鍵或者唯一索引 = {之前查詢的某個列值}

// emp_no是employees表的主鍵
explain select * from employees, dept_manager where employees.emp_no = dept_manager.emp_no;

• ref

與eq_ref類似，但是之前查詢到的結(jié)果中每一行在當(dāng)前查詢中可能匹配到多行(索引匹配)。常見的就是：非唯一索引 = {之前查詢的某個列值}

// dept_no是dept_manager表的普通索引explain select * from departments, dept_manager where departments.dept_no = dept_manager.dept_no;

• fulltext

使用fulltext類型的索引進(jìn)行連接，這里就不做具體展開了，可以認(rèn)為和ref類似，但是索引類型是fulltext。

• ref_or_null

就是ref加上一個null值查詢，需要索引所在列允許值為null。

• index_merge

使用了索引合并的優(yōu)化，其實就是MySQL用到了多個索引，最常見的就是兩個索引=值的or連接查詢

// 這里dept_no和emp_no都有各自的索引explain select * from dept_manager where dept_no = "d009" or emp_no = 110183;

• unique_subquery

官方介紹說是唯一索引的查找條件中用到了子查詢，替代某些eq_ref的IN查詢。但是我按照官方例子試了還是eq_ref。

• index_subquery

這個就是普通索引的查找條件用了子查詢，替代某些ref的IN查詢。同樣，我也試不出來

• range

索引在某個范圍內(nèi)的查詢。

• index

與ALL其實差不多，只是MySQL掃描了索引樹，有兩種情況

1. 覆蓋索引，通過掃描索引就拿到了數(shù)據(jù)，不用回表，Extra會顯示Using index。

1. 通過掃描索引來掃描全表，Extra不會顯示Using index。

• ALL

全表掃描，最爛的一種情況，要避免。

possible_keys

可能用到的索引，注意僅僅是可能，如果這一列沒有值，那你要緊張一下了。。

key

實際使用到的索引。通常情況下是possible_keys中的某一個，但是也有特殊情況，比如MySQL根據(jù)where條件列出了所有可能用到的索引，但是實際查詢的列被另一個索引完全覆蓋(覆蓋索引)，這個時候key就有可能是這個覆蓋索引，因為雖然這個索引并不能用來過濾，但根據(jù)這個索引全部掃描一次更快(不用回表)。

某些情況下先ANALYZE TABLE table_name會影響索引的選擇，因為統(tǒng)計信息也是MySQL選擇索引的重要參考條件。

key_len

使用到的索引的長度，有時候不用使用全部索引長度就可以過濾完成，由于索引存儲格式的原因，允許為null的那些列對應(yīng)的索引會長一個單位(相比那些not null的列)。

ref

顯示哪些列或者常量被用于索引的比較。如果值是func，那么代表索引的比較條件是一個函數(shù)結(jié)果，可以通過SHOW WARNINGS查看更具體的結(jié)果。

rows

查詢需要掃描的行數(shù)，對于InnoDB來說，是個估計值。

filtered

表示按表條件過濾的表行的估計百分比，最大值為100，這表示未過濾任何行。所以該值越小表明當(dāng)前過濾條件越有效。

Extra(不知道為啥就這個字段是大寫開頭)

包含一些執(zhí)行計劃的擴(kuò)展信息，包含以下這些可選值，常見的有詳細(xì)分析。

• const row not found

查詢空表的情況，但實際我沒試出來

• Deleting all rows

MyISAM引擎快速刪除所有表格記錄時會顯示這個值

• Distinct

使用了distinct

• FirstMatch(tbl_name)

當(dāng)前面表的查詢策略使用了半聯(lián)接FirstMatch聯(lián)接快捷方式策略

• Full scan on NULL key

子查詢優(yōu)化無法利用到索引時的一種備選方案

• Impossible HAVING

HAVING子句中的條件永遠(yuǎn)不滿足，結(jié)果一定是空

• Impossible WHERE

WHERE子句中的條件永遠(yuǎn)不滿足，結(jié)果一定是空

• Impossible WHERE noticed after reading const tables

MySQL已經(jīng)讀取了所有 const（和 system）表，并判斷該WHERE子句始終為false。

• LooseScan(m..n)

使用了半連接的LooseScan策略。m 和 n是索引部分的編號。

• No matching min/max row

使用MIN和MAX函數(shù)的時候發(fā)現(xiàn)沒有滿足條件的記錄

• no matching row in const table

對于具有聯(lián)接的查詢，存在一個空表或沒有滿足唯一索引條件的行的表。

• No matching rows after partition pruning

對于DELETE或 UPDATE，在分區(qū)修剪后，優(yōu)化器未發(fā)現(xiàn)任何要刪除或更新的內(nèi)容。類似于Impossible WHERE for SELECT語句。

• No tables used

沒有FROM子句

• Not exists

對LEFT JOIN 的一種優(yōu)化，在找到符合LEFT JOIN條件的一行后，不再檢查更多行是否滿足條件。

這個例子中dept_manager.emp_no定義為not null

• Plan isn't ready yet

使用EXPLAIN FOR CONNECTION才會出現(xiàn)的值，當(dāng)優(yōu)化器未完成為在指定連接中執(zhí)行的語句創(chuàng)建執(zhí)行計劃時，就會出現(xiàn)此值。

• Range checked for each record (index map: N)

MySQL找不到很好的索引來使用，但是發(fā)現(xiàn)在知道先前表中的列值之后可能會使用某些索引。

• Scanned N databases

查詢INFORMATION_SCHEMA中的表時，顯示具體查詢了多少目錄，可取0、1或者all。

• Select tables optimized away

要查詢的信息是現(xiàn)成的，不要遍歷索引或者表，比較有代表性的例子就是查詢引擎是MyISAM的某個表的總記錄數(shù)，因為MyISAM引擎是記錄這個數(shù)據(jù)的，所以直接獲取即可。

• Skip_open_table, Open_frm_only, Open_full_table

查詢INFORMATION_SCHEMA才會出現(xiàn)的值

1. Skip_open_table：不需要打開表文件，通過掃描數(shù)據(jù)庫目錄就可以了。

2. Open_frm_only：只需要打開數(shù)據(jù)庫表的.frm文件。

3. Open_full_table：未優(yōu)化，.frm、.MYD和 .MYI文件都必須被打開。

4. Start temporary, End temporary

這表明臨時表用于半聯(lián)接重復(fù)淘汰策略，不常見。

• unique row not found

對于諸如SELECT … FROM tbl_name的查詢，沒有行滿足表上的UNIQUE索引或PRIMARY KEY的條件。

• Using filesort

需要額外進(jìn)行一次排序及查找，應(yīng)該盡量避免。實際上MySQL會按照WHERE條件找出所有排序鍵和行記錄指針，然后按照排序鍵進(jìn)行一次排序，然后再根據(jù)指針查出所有記錄。

這是一種比較常見的情況，order by后面的列沒有建立索引，而where條件后的列建立了索引，優(yōu)化器最終選擇了按照emp_no查詢記錄，此時的邏輯就是：

1. 按照emp_no 小于 20000查出所有birth_date及對應(yīng)記錄的指針；

2. 根據(jù)birth_date排序；

3. 根據(jù)指針查出所有記錄后返回。

4. Using index

簡單來說就是不需要回表，查詢的數(shù)據(jù)直接根據(jù)索引就能拿到，不需要查詢真正的數(shù)據(jù)行。

上面的查詢因為dept_name上有索引，所以這里直接根據(jù)索引就能拿到需要查詢的數(shù)據(jù)。還有一種特殊情況，看下面的索引

由于索引的結(jié)構(gòu)設(shè)計，key是索引列的值，value是聚簇索引(一般就是主鍵)，所以查詢列帶上聚簇索引包含的列依然可以Using index。

• Using index condition

這里涉及一個概念，即索引條件下推（ICP），5.6版本后提供的新特性。where中關(guān)于索引的過濾條件下推到存儲引擎減少不必要的網(wǎng)絡(luò)IO，有兩個重要的特點：

1. 單表單索引
2. 聚集索引無效

下面的例子我是為了測試特地加了first_name和last_name的聯(lián)合索引。

• Using index for group-by

與Using index類似，表示MySQL找到了一個索引，該索引可用于檢索GROUP BY或 DISTINCT查詢的所有列，但是不需要對實際表進(jìn)行任何額外的磁盤訪問。

• Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)

本次查詢之前的查詢結(jié)果被緩沖起來，然后本次查詢是與上一次的連接是通過讀取緩沖區(qū)的數(shù)據(jù)來執(zhí)行的，Block Nested Loop和Batched Key Access是兩種不同的算法。

• Using MRR

使用了多范圍讀取優(yōu)化。MRR的目的是為了減少磁盤的隨機(jī)IO（存儲引擎先按照聚集索引排序再從磁盤獲取數(shù)據(jù)），而且如果不是想獲取數(shù)據(jù)的所有列，MRR是不具有優(yōu)勢的。二級索引在物理存儲上是不連續(xù)的，所以如果沒有MRR，隨機(jī)IO將會很明顯(大多數(shù)情況)。

• Using sort_union(...), Using union(...), Using intersect(...)

索引合并優(yōu)化時用到的優(yōu)化算法，比如下面這個，兩個索引的結(jié)果是通過union連接的。

• Using temporary

為了執(zhí)行該查詢，MySQL需要創(chuàng)建一個臨時表來保存結(jié)果。常見的就是GROUP BY和 ORDER BY子句后面跟著不同的列。

• Using where

用WHERE子句作過濾，限制行記錄范圍去匹配下一個表或最終發(fā)送到客戶端的行記錄。

• Using where with pushed condition

適用于NDB集群，類似于索引下推，將where條件下推到數(shù)據(jù)節(jié)點來避免一些無謂的網(wǎng)絡(luò)消耗。

• Zero limit

含有一個LIMIT 0子句，選不到任何記錄。

寫在最后

其中重要的幾個就是 key、type 、rows、extra，其中key為null時，說明沒有使用到索引，需要調(diào)整索引。type為ALL的地方,需要進(jìn)行優(yōu)化，一般需要達(dá)到ref、eq_ref級別，范圍查找需要達(dá)到range。extra有Using filesort、Using temporary 的一定需要優(yōu)化，根據(jù)rows可以直觀看出優(yōu)化結(jié)果。