? 在工作中，經(jīng)常會(huì)碰到一些慢查詢，Explain可以幫我們更詳細(xì)的了解MySQL查詢的執(zhí)行計(jì)劃，用法也很簡單Explain 后面跟上SELECT語句即可。執(zhí)行完之后，會(huì)顯示一行有多個(gè)列的記錄，可能很多人和我一樣，對(duì)EXPLAIN里面字段的含義，并沒有深入的去了解過，處于一知半解的狀態(tài)，只知道一些最常見的。

? 下面我根據(jù)MySQL官方文檔，查閱了很多資料，再結(jié)合我自己的理解，對(duì)EXPLAIN的字段和值做了詳細(xì)的描述，在總結(jié)過程中，也發(fā)現(xiàn)了自己的很多知識(shí)漏洞，很多時(shí)候，總是會(huì)想當(dāng)然的認(rèn)為，這個(gè)就是對(duì)的，并沒有嚴(yán)密的邏輯驗(yàn)證，大腦喜歡偷懶，正所謂好記性不如爛筆頭，寫的過程也是對(duì)自己知識(shí)點(diǎn)掌握程度的批判和考驗(yàn)。

關(guān)于EXPLAIN

? EXPLAIN返回一行記錄，通過Explain可以獲取到很多信息，如：不同表的查詢順序，查詢用了哪些表，能使用哪些索引以及真正用到了哪些索引，用了哪種連接類型，是否有臨時(shí)表和文件排序等。這些因素對(duì)查詢的效率有直接的相關(guān)，想要使查詢更高效，需要對(duì)這些條件做一個(gè)好的優(yōu)化。

? EXPLAIN有12個(gè)字段，每個(gè)字段對(duì)查詢優(yōu)化的權(quán)重比不一樣，也就是說并不是所有字段都很重要。type,key,Extra字段相對(duì)其它字段來說，對(duì)查詢效率的影響更大，優(yōu)化查詢的時(shí)候，先把注意力放到這些字段會(huì)比其它字段來得更加直接有效，下面開始具體內(nèi)容。

EXPLAIN語法

以user_info表為例：

explain select * from `user_info` where uid = 5

結(jié)果：

EXPLAIN字段說明

注： 標(biāo)注星號(hào)的字段為重點(diǎn)

id：

SELECT語句的標(biāo)識(shí)符，代表SELECT查詢?cè)谡麄€(gè)查詢中的序號(hào)。這個(gè)值也可能為NULL，如果這一行是UNION的結(jié)果。

select_type:

SELECT查詢的類型，該類型的值有11種類型。例如，示例中的值為SIMPLE，表示該查詢是一個(gè)簡單的查詢（即：沒有子查詢和UNION）。

table:

大多數(shù)情況下表示輸出行所引用的表名，它也可能是下列值之一：

partitions:

只對(duì)分區(qū)表有意義。意思是查詢所匹配到的分區(qū)，如果該表為非分區(qū)表，則它的值為NULL。

*type:

查詢的join類型，注意單表查詢也被當(dāng)做join的特例，并不一定要兩張表。連接類型詳情下面會(huì)詳細(xì)介紹。

possible_key:

possible_key列是指，在查詢中能夠被MySQL用到的索引，但在實(shí)際情況中，不一定會(huì)被全部用到，這取決于MySQL優(yōu)化器的選擇，假設(shè)possible_key有A,B,C,3個(gè)索引，優(yōu)化器經(jīng)過分析認(rèn)為A索引不需要用，那么實(shí)際執(zhí)行的時(shí)候只會(huì)用到B,C索引。實(shí)際應(yīng)用中，該列經(jīng)常幫我們對(duì)SQL查詢進(jìn)行優(yōu)化，如果它的值為NULL，說明沒有能被用到的索引，這種情況下，需要調(diào)整SQL語句和優(yōu)化表的索引。

*key:

查詢中實(shí)際用到的索引，要注意，該列的值可能包含possible_key列中沒有出現(xiàn)的索引，當(dāng)查詢滿足覆蓋索引的條件時(shí)，possible_keys列為NULL，索引僅在key列顯示，MySQL只需要掃描索引樹，不用到實(shí)際的數(shù)據(jù)行檢索即可得到結(jié)果，查詢會(huì)更高效，Extra列顯示USING INDEX，則證明使用了覆蓋索引。 也可以通過FORCE INDEX,USE INDEX或IGNORE INDEX來強(qiáng)制使用或忽略possible_key列中的索引。

覆蓋索引概念：

如果索引包含所有滿足查詢需要的數(shù)據(jù)的索引成為覆蓋索引(Covering Index)。

假設(shè)有一個(gè)user表，假設(shè)索引A包含了col1,col2,col3三個(gè)字段，criteria為標(biāo)準(zhǔn)條件。

Query 1:
select * from user where criteria

Query 1使用了索引查詢，獲取到數(shù)據(jù)行的主鍵，但是仍然需要根據(jù)主鍵值掃描實(shí)際的數(shù)據(jù)行。

Query 2:
select `col1`,`col2` where criteria

Query 2中，索引A已經(jīng)包含了它需的字段，也就是說Query 2不用再去實(shí)際的數(shù)據(jù)行獲取數(shù)據(jù)了，只要掃描完索引樹就行了，這樣就省了一個(gè)步驟，索引樹往往比實(shí)際的數(shù)據(jù)表小，所以效率很高，這就是覆蓋索引。

key_len:

實(shí)際用到的索引字段長度，越短越好。

ref:

ref列顯示哪個(gè)列或者常數(shù)和索引比較篩選出結(jié)果。

rows:

rows列表示MySQL認(rèn)為執(zhí)行查詢必須檢查的行數(shù)，對(duì)Innodb表來說，這是一個(gè)預(yù)估值，可能并不是確切的值。

filtered:

? filtered的意思是，首先MySQL利用索引，例如，用range范圍掃描出符合的行，如果掃描符合條件的估計(jì)值是100行，rows顯示估計(jì)的值就是100，這一步是存儲(chǔ)引擎根據(jù)索引篩選后的值，然后在Server層根據(jù)其余的WHERE條件過濾。

? 被過濾器過之后，符合條件的還剩下20行，也就是剩下20%，20%就是filtered中的值。很顯然，直接在存儲(chǔ)引擎層篩選出20行比先篩選出100行再過濾要更好，通常情況下，filtered的值越大可能意味著索引越好。

? 另一方面看，你也可以完全忽略filtered，因?yàn)檫@個(gè)值在大多數(shù)情況下只是一個(gè)不準(zhǔn)確的估計(jì)，應(yīng)該把注意力放到優(yōu)化其它更有用的字段上，尤其是type,key,Extra。例如：盡量避免filesort排序，使用索引排序。或者有一個(gè)更好的type值，對(duì)性能的提升是非常巨大的，這種情況，即使filtered的值低也沒關(guān)系。假設(shè)一個(gè)查詢A， type=all,filtered=0.1%。那么首要先關(guān)注type字段，可通過添加索引來優(yōu)化，可以先不管filtered。

? 所以對(duì)這個(gè)值不需要太認(rèn)真，即使100%也不意味著索引一定好，反過來也不一定說明索引差，type比它更能說明索引的好壞。

*Extra:

這個(gè)列包含Mysql解決查詢的詳細(xì)信息，詳情見下方。

EXPLAIN字段值說明：

select_type:

通過 **物化 ** 優(yōu)化子查詢的原理：

? 優(yōu)化器使用物化的方式能讓子查詢更高效的執(zhí)行，類似緩存技術(shù)，把第一次查詢的結(jié)果存起來，避免多次的耗時(shí)操作，同時(shí)也有它自身的限制，不是所有子查詢都能被物化的。物化技術(shù)把子查詢產(chǎn)生的結(jié)果放在一個(gè)臨時(shí)表中，如果數(shù)據(jù)量小的話，通常是在內(nèi)存中完成，數(shù)據(jù)大的時(shí)候就降級(jí)到磁盤進(jìn)行，速度也會(huì)慢很多。首先，MySQL得到子查詢的結(jié)果，然后把結(jié)果放到臨時(shí)表中，在隨后的任何時(shí)間，當(dāng)需要這個(gè)結(jié)果時(shí)，MySQ就再次引用這個(gè)臨時(shí)表，不需要再執(zhí)行計(jì)算了。優(yōu)化器可能會(huì)使用哈希索引（復(fù)雜度為O(1)，很快）來快速且低成本的查找表，這個(gè)索引是唯一的，避免了重復(fù)，能使表更小。

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

type（連接類型）：

• system

  當(dāng)表只有一行數(shù)據(jù)的時(shí)候，這是const連接類型的特例。

• const

  表中最多只有一行匹配，在查詢開始時(shí)被讀取。因?yàn)橹挥幸恍?，該行中列的值可以被?yōu)化器的其余部分視為常量。const表非?？?，因?yàn)樗麄儍H被讀取一次。將PRIMARY KEY 或 UNIQUE INDEX索引和常量值比較時(shí),會(huì)使用const。例如：

  SELECT * FROM tbl_name WHERE primary_key=1;
  
  SELECT * FROM tbl_name
  WHERE primary_key_part1=1 AND primary_key_part2=2;
  

• eq_ref

  假設(shè)A JOIN B，B表讀取A表的各個(gè)行組合的一行時(shí)，通過B表的PRIMARY KEY或UNIQUE NOT NULL索引列連接時(shí)，優(yōu)化器會(huì)使用eq_ref類型，這是除了system和const之外最快的JOIN類型。

  舉例說明：

  表tableA,有（id,text）字段，id為PRIMARY KEY,A表數(shù)據(jù)為：

  id	text
  1	HELLO
  2	THANK

  表tableB有（id,text）字段，id為PRIMARY KEY,B表數(shù)據(jù)為：

  id	text
  1	WORLD
  2	YOU

  現(xiàn)在通過JOIN將兩個(gè)表關(guān)聯(lián)起來

  SELECT A.text,B.text 
  FROM tableA AS A,tableB as B 
  WHERE A.id=B.id
  

  這個(gè)連表查詢是非?？斓?，因?yàn)樵?strong>A表中掃描的每一行，在B表中也僅一行滿足條件。

• ref

  ? A JOIN C時(shí)，A表中的每一行不是唯一的，對(duì)單表查詢也一樣，有多個(gè)滿足條件的行，查詢的KEY是單個(gè)索引或復(fù)合索引的最左前綴(不是唯一索引和主鍵)，也就是說C表的id是一個(gè)非唯一索引。這種情況下，優(yōu)化器會(huì)使用ref優(yōu)化，如果只有少部分行(rows)滿足條件，這個(gè)連接類型(join type)是很好的。ref用于索引的比較操作，注意：僅對(duì)于=,<=> 操作有效，對(duì)于>,<,BETWEEN,IN的范圍操作優(yōu)化器可能會(huì)使用range類型(見下方),也可能是ALL全表掃描。

  舉例說明：

  現(xiàn)在有tableC，id為索引，不唯一。數(shù)據(jù)為：

  id (非唯一索引)	text
  1	HANGZHOU
  1	SHANGHAI

  現(xiàn)在通過LEFT JOIN將A和C關(guān)聯(lián)起來:

  SELECT A.text,C.text 
  FROM `tableA` AS A 
  LEFT JOIN `tableC` AS C ON A.id=C.id
  

  這個(gè)JOIN不像之前的那么快，因?yàn)樵诒鞟中掃描的每一行，在表C中可能有很多行滿足條件，C的id不是唯一索引。

• fulltext

  使用了全文索引，Innodb不支持全文索引。

• ref_or_null

  如果一個(gè)查詢的WHERE子句中包含colA IS NULL的條件，但是colA已經(jīng)被聲明為NOT NULL，此時(shí)優(yōu)化器會(huì)使用ref_or_null類型。

  SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;
  

• index_merge

  ? 在MYSQL5.0之前是沒有索引合并功能的，假設(shè)A表有3個(gè)單獨(dú)的索引col1 ,col2,col3，然后執(zhí)行如下SQL:

  SELECT * FROM A WHERE col1=1 AND col2=2 AND col3=3
  

  實(shí)際查詢中只有一個(gè)索引能被用到，這種情況，只能通過建立復(fù)合索引(col1,col2,col3)才能在索引中用到所有字段。

  ? 5.0之后有了索引合并，當(dāng)檢索數(shù)據(jù)行時(shí)出現(xiàn)多個(gè)范圍掃描條件時(shí)，在滿足索引合并前提條件時(shí)(單個(gè)索引覆蓋WHERE條件的字段)，MySQL優(yōu)化器可能會(huì)使用索引合并(不一定)，首先分別對(duì)多個(gè)索引進(jìn)行掃描，然后合并來自單個(gè)表的掃描結(jié)果，它不能合并多個(gè)表的掃描結(jié)果，合并的方式有3種：

  • unions：索引取并集
  • intersections：索引取交集
  • Sort-Union:先對(duì)取出的數(shù)據(jù)按主鍵排序，再取并集

  索引合并條件：

  1. WHERE子句中的范圍條件，WHERE中出現(xiàn)字段必須被索引覆蓋，如果colA沒添加索引，則只會(huì)對(duì)colB和colC進(jìn)行索引合并，Extra字段顯示Using intersect(colB,colC);，type為index_merge，則說明用到了索引合并。

     WHERE colA = const1 AND colB = const2 AND colC = const3
     

  2. Innodb表中的主鍵的任何范圍條件，>,<,<>等。

     SELECT * FROM innodb_table
       WHERE primary_key < 10 AND key_col1 = 20;
     

  ? 滿足了條件，MYSQL會(huì)選擇索引行數(shù)最少的字段對(duì)索引結(jié)果進(jìn)行合并，最終使用哪個(gè)索引字段來合并也不一定，也可能不使用合并，這取決于優(yōu)化器，如果優(yōu)化器認(rèn)為沒必要使用索引合并優(yōu)化，就會(huì)使用其它優(yōu)化，也許會(huì)選擇type 為range或更高效的ref的優(yōu)化。

  ? 當(dāng)優(yōu)化器決定使用索引合并優(yōu)化，如果WHERE條件用AND連接，優(yōu)化器會(huì)使用INTERSECTIONS合并算法，對(duì)多個(gè)索引掃描的結(jié)果取交集。如果用OR連接，優(yōu)化器會(huì)選擇UNIONS或SORT-UNIONS合并算法，對(duì)多個(gè)索引掃描的結(jié)果取合集，SORT-UNIONS和UNIONS的主要區(qū)別是，前者在掃描完數(shù)據(jù)時(shí)，需要先對(duì)數(shù)據(jù)按主鍵排序，再取它們的合集。

  ? 在WHERE子句中使用AND時(shí)，使用復(fù)合索引比索引合并更高效，因?yàn)閺?fù)合索引只用一個(gè)索引篩選，沒有匹配合并的過程，這個(gè)過程節(jié)省了很多時(shí)間。

  ? 在使用OR時(shí)，復(fù)合索引是不起作用的，這種情況下，使用UNIONS索引合并效果更好。如果不想使用某種索引合并，也可以選擇關(guān)閉?？赏ㄟ^optimzer_switch系統(tǒng)變量查看各個(gè)索引合并的開啟狀況。如下：

  SELECT @@optimizer_switch
  

  索引合并算法的默認(rèn)都是開啟的，可以通過關(guān)閉某個(gè)合并算法。例如：

  SET optimizer_switch = 'index_merge_intersection=off'
  

• unique_subquery

  這種類型是eq_ref類型在子查詢中的替代類型。例如

  SELECT * FROM A WHERE
  value IN (SELECT id FROM B WHERE some_expr)
  

  B表中的id在A表中有唯一對(duì)應(yīng)的記錄。

• range

  ? 在WHERE子句中，執(zhí)行>,<,<>,=,BETWEEN,IN() 等操作時(shí)，MySQL可能會(huì)(不一定)使用range類型，Explain中key列的值就是實(shí)際用到的索引，key_len是它們中最長的索引的長度。如果優(yōu)化器認(rèn)為使用索引篩選沒有全表掃描來得及，例如：條件篩選后的行占全表的50%以上，即使有索引可用，優(yōu)化器也會(huì)選擇全表掃描，即type=ALL。

  ? 為什么呢？解釋這個(gè)問題之前，需要先了解幾個(gè)概念。對(duì)Innodb表來說，每個(gè)表都有一個(gè)聚簇索引，InnoDB的聚簇索引實(shí)際上在同一個(gè)結(jié)構(gòu)中保存了B-Tree索引和數(shù)據(jù)行信息。因?yàn)闊o法把數(shù)據(jù)行存放在兩個(gè)不同的地方，所以一個(gè)表只能有一個(gè)聚簇索引。二級(jí)索引存儲(chǔ)的是記錄的主鍵，而不是數(shù)據(jù)存儲(chǔ)的地址，索引數(shù)據(jù)和存儲(chǔ)數(shù)據(jù)是分離的，唯一索引、普通索引、前綴索引等都是二級(jí)索引。實(shí)際上，InnoDB在查詢?nèi)魏螖?shù)據(jù)時(shí)，最后都是通過主鍵來查詢的。首先我們根據(jù)索引條件在索引樹上掃描出對(duì)應(yīng)的主鍵值。然后根據(jù)這個(gè)值去聚簇索引總超找到對(duì)應(yīng)的行(如果是覆蓋索引則省略這一步)。

  ? 在某些情況下，索引條件掃描出的數(shù)據(jù)行非常大，可能占了全表的50%，此時(shí)再根據(jù)主鍵找到對(duì)應(yīng)的數(shù)據(jù)塊是不劃算。主鍵的BTree查找屬于文件的隨機(jī)搜索，但是如果隨機(jī)搜索文件數(shù)據(jù)的目的是為了查找一半的數(shù)據(jù)，這并不是最優(yōu)化的，只要對(duì)數(shù)據(jù)文件進(jìn)行大量的順序讀寫要更快，這種情況下，索引會(huì)被忽略。

• index

  ? index類型和ALL類型幾乎相同。有兩種情況：

  1. 若SELECT中列全部被索引覆蓋，所需要的數(shù)據(jù)可以直接在索引中讀取，MySQL只需對(duì)索引樹進(jìn)行掃描，這通常比掃描實(shí)際數(shù)據(jù)行要快，因?yàn)樗饕龢渫ǔ１葦?shù)據(jù)表更小，這種情況下，Extran的值會(huì)顯示USING INDEX。
  2. 使用索引中讀取的主鍵值，按索引順序?qū)θ磉M(jìn)行掃描，此時(shí)Extra中沒有USING INDEX。

• ALL

  對(duì)表的每一行進(jìn)行掃描，這是最糟糕的情況。一般，你可以通過添加索引來避免這種情況發(fā)生。

Extra列值的含義:

? Extra列包含了MySQL處理查詢的一些額外信息，下面的列出了Extra中可能出現(xiàn)的值，如果你想讓查詢盡可能的快，應(yīng)該注意下Extra字段中是否出現(xiàn)了using filesort 和using temporary。下面只列除了在實(shí)際應(yīng)用中經(jīng)常會(huì)出現(xiàn)，相對(duì)比較重要的一部分，若描述的不夠詳細(xì)，可查看MySQL官方文檔。

• const row not found

  SELECT * FROM A
  

  如果A表為空，則會(huì)出現(xiàn)改值。

• DISTINCT

  mysql在尋找不同的值，當(dāng)它找到第一個(gè)匹配的行之后，就停止搜索更多的行了。例子：

• no matching row in const table

  用唯一索引或者主鍵查詢時(shí)，沒有匹配到的數(shù)據(jù)。

• Not exists

  MySQL優(yōu)化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標(biāo)準(zhǔn)的行，就不再搜索了，。例如：

  SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
   WHERE t2.id IS NULL;
  

• Using filesort

  ? 這個(gè)值表示，MySQL必須對(duì)檢索到的結(jié)果進(jìn)行額外的排序。排序是按照連接類型遍歷所有行并存儲(chǔ)排序鍵和指向行的指針，以匹配滿足where子句條件的所有行，然后對(duì)鍵進(jìn)行排序，并按排序順序檢索行。根據(jù)不同情況，MySQL會(huì)選擇不同的排序算法，在數(shù)據(jù)比較小的時(shí)候，MySQL會(huì)利用排序緩沖區(qū)作為優(yōu)先級(jí)隊(duì)列將結(jié)果在內(nèi)存中排序，否則只能通過合并文件的方式合并，那會(huì)慢很多，排序緩沖區(qū)的大小取決于sort_buffer_size變量的大小。

  ? 總之，當(dāng)看到filesort的時(shí)候就應(yīng)該引起重視，通過優(yōu)化索引來避免額外的文件排序，這對(duì)性能影響是很大的。

• Using index

  單個(gè)索引覆蓋了SELECT的所有列（即：覆蓋索引），不需要對(duì)實(shí)際的數(shù)據(jù)行進(jìn)行掃描。

• Using index condition

  ? Index Condition Pushdown (ICP)是MySQL 5.6 版本中的新特性,是一種在存儲(chǔ)引擎層使用索引過濾數(shù)據(jù)的一種優(yōu)化方式。當(dāng)關(guān)閉ICP時(shí),index 僅僅是data access 的一種訪問方式，存儲(chǔ)引擎通過索引回表獲取的數(shù)據(jù)會(huì)傳遞到MySQL Server 層進(jìn)行where條件過濾。

  ? 當(dāng)打開ICP時(shí),如果部分where條件能使用索引中的字段,MySQL Server 會(huì)把這部分下推到引擎層,可以利用index過濾的where條件在存儲(chǔ)引擎層進(jìn)行數(shù)據(jù)過濾,而非將所有通過index access的結(jié)果傳遞到MySQL server層進(jìn)行where過濾.
  優(yōu)化效果:ICP能減少引擎層訪問基表的次數(shù)和MySQL Server 訪問存儲(chǔ)引擎的次數(shù),減少io次數(shù)，提高查詢語句性能。

• Using index for group-by

  和USING INDEX很相似，區(qū)別是，當(dāng)查詢語句中含有DISTINCT和GROUP BY操作時(shí)，僅需訪問索引樹，不需要訪問實(shí)際的表時(shí)，使用該優(yōu)化。

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

  當(dāng)查詢產(chǎn)生索引合并時(shí)會(huì)顯示該值，type為index_merge。

• Using temporary

  為了處理查詢，MySQL必須建立一個(gè)臨時(shí)表才能產(chǎn)生結(jié)果。典型的情況是，在使用GROUP BY和ORDER BY子句時(shí)，兩者使用了不同的列會(huì)導(dǎo)致產(chǎn)生臨時(shí)表。

• Using where

  using where 是指使用WHERE或ON子句，MySQL Server層收到存儲(chǔ)引擎返回的結(jié)果時(shí)，需要對(duì)結(jié)果再次過濾，不需要返回所有結(jié)果，注意LIMIT不算限制條款。如果沒有用到索引using where只是說明，使用了顧慮條件過濾。

參考

MySQL官方文檔