最近慢慢接觸MySQL，了解如何優化它也迫在眉睫了，話說工欲善其事，必先利其器。最近我就打算了解下幾個優化MySQL中經常用到的工具。今天就簡單介紹下EXPLAIN。
auto_increment () () () () () () ()
,,,,,,,,,,,, ,,,,,,,,,)
people (zipcode,firstname,lastname);
先從一個最簡單的查詢開始：
Query-1 zipcode,firstname,lastname people;
EXPLAIN輸出結果共有id,select_type,table,type,possible_keys,key,key_len,ref,rows和Extra幾列。
zipcode ( people a) b;
id是用來順序標識整個查詢中SELELCT 語句的，通過上面這個簡單的嵌套查詢可以看到id越大的語句越先執行。該值可能為NULL，如果這一行用來說明的是其他行的聯合結果，比如UNION語句：
people zipcode people zipcode ;
SELECT語句的類型，可以有下面幾種。
SIMPLE
最簡單的SELECT查詢，沒有使用UNION或子查詢。見。
PRIMARY
在嵌套的查詢中是最外層的SELECT語句，在UNION查詢中是最前面的SELECT語句。見和。
UNION
UNION中第二個以及後面的SELECT語句。 見。
DERIVED
派生表SELECT語句中FROM子句中的SELECT語句。見。
UNION RESULT
一個UNION查詢的結果。見。
DEPENDENT UNION
顧名思義，首先需要滿足UNION的條件，及UNION中第二個以及後面的SELECT語句，同時該語句依賴外部的查詢。
people id ( id people zipcode id people zipcode );
Query-4中select id from people where zipcode = 200000的select_type為DEPENDENT UNION。你也許很奇怪這條語句並沒有依賴外部的查詢啊。
這裡順帶說下MySQL優化器對IN操作符的優化，優化器會將IN中的uncorrelated subquery優化成一個correlated subquery（關於correlated subquery參見這裡）。
... t1 t1.a ( b t2);
類似這樣的語句會被重寫成這樣：
... t1 ( t2 t2.b t1.a);
所以實際上被重寫成這樣：
people o ( id people zipcode id o.id id people zipcode id o.id);
題外話：有時候MySQL優化器這種太過“聰明” 的做法會導致WHERE條件包含IN()的子查詢語句性能有很大損失。可以參看《高性能MySQL第三版》6.5.1關聯子查詢一節。
SUBQUERY
子查詢中第一個SELECT語句。
people id ( id people zipcode );
DEPENDENT SUBQUERY
和DEPENDENT UNION相對UNION一樣。見。
除了上述幾種常見的select_type之外還有一些其他的這裡就不一一介紹了，不同MySQL版本也不盡相同。
顯示的這一行信息是關於哪一張表的。有時候並不是真正的表名。
( ( people a) b ) c;
可以看到如果指定了別名就顯示的別名。
<derivedN>N就是id值，指該id值對應的那一步操作的結果。
還有<unionM,N>這種類型，出現在UNION語句中，見。
注意：MySQL對待這些表和普通表一樣，但是這些“臨時表”是沒有任何索引的。
type列很重要，是用來說明表與表之間是如何進行關聯操作的，有沒有使用索引。MySQL中“關聯”一詞比一般意義上的要寬泛，MySQL認為任何一次查詢都是一次“關聯”，並不僅僅是一個查詢需要兩張表才叫關聯，所以也可以理解MySQL是如何訪問表的。主要有下面幾種類別。
const
當確定最多只會有一行匹配的時候，MySQL優化器會在查詢前讀取它而且只讀取一次，因此非常快。const只會用在將常量和主鍵或唯一索引進行比較時，而且是比較所有的索引字段。people表在id上有一個主鍵索引，在(zipcode,firstname,lastname)有一個二級索引。因此的type是const而並不是：
people id;
people zipcode ;
注意下面的Query-10也不能使用const table，雖然也是主鍵，也只會返回一條結果。
people id ;
system
這是const連接類型的一種特例，表僅有一行滿足條件。
( people id )b;
<derived2>已經是一個const table並且只有一條記錄。
eq_ref
eq_ref類型是除了const外最好的連接類型，它用在一個索引的所有部分被聯接使用並且索引是UNIQUE或PRIMARY KEY。
需要注意InnoDB和MyISAM引擎在這一點上有點差別。InnoDB當數據量比較小的情況type會是All。我們上面創建的people 和 people_car默認都是InnoDB表。
people a,people_car b a.id b.people_id;
我們創建兩個MyISAM表people2和people_car2試試：
auto_increment () () () () () () () MyISAM;
people2 a,people_car2 b a.id b.people_id;
我想這是InnoDB對性能權衡的一個結果。
eq_ref可以用於使用 = 操作符比較的帶索引的列。比較值可以為常量或一個使用在該表前面所讀取的表的列的表達式。如果關聯所用的索引剛好又是主鍵，那麼就會變成更優的const了：
people2 a,people_car2 b a.id b.people_id b.people_id ;
ref
這個類型跟eq_ref不同的是，它用在關聯操作只使用了索引的最左前綴，或者索引不是UNIQUE和PRIMARY KEY。ref可以用於使用=或<=>操作符的帶索引的列。
為了說明我們重新建立上面的people2和people_car2表，仍然使用MyISAM但是不給id指定primary key。然後我們分別給id和people_id建立非唯一索引。
reate people_id people_id people_car2(people_id);
然後再執行下面的查詢：
people2 a,people_car2 b a.id b.people_id a.id ;
Query-16 people2 a,people_car2 b a.id b.people_id a.id ;
Query-17 people2 a,people_car2 b a.id b.people_id;
people2 id ;
看上面的Query-15，Query-16和Query-17，Query-18我們發現MyISAM在ref類型上的處理也是有不同策略的。
對於ref類型，在InnoDB上面執行上面三條語句結果完全一致。
fulltext
鏈接是使用全文索引進行的。一般我們用到的索引都是B樹，這裡就不舉例說明了。
ref_or_null
該類型和ref類似。但是MySQL會做一個額外的搜索包含NULL列的操作。在解決子查詢中經常使用該聯接類型的優化。（詳見這裡)。
Query-19 explain people2 id id ;
people2 id id ;
注意Query-20使用的並不是ref_or_null，而且InnnoDB這次表現又不相同（數據量大的情況下有待驗證）。
index_merger
該聯接類型表示使用了索引合並優化方法。在這種情況下，key列包含了使用的索引的清單，key_len包含了使用的索引的最長的關鍵元素。關於索引合並優化看這裡。
unique_subquery
該類型替換了下面形式的IN子查詢的ref：
value ( primary_key single_table some_expr)
unique_subquery是一個索引查找函數，可以完全替換子查詢，效率更高。
index_subquery
該聯接類型類似於unique_subquery。可以替換IN子查詢，但只適合下列形式的子查詢中的非唯一索引：
value ( key_column single_table some_expr)
range
只檢索給定范圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引。key_len包含所使用索引的最長關鍵元素。在該類型中ref列為NULL。當使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關鍵字列時，可以使用range:
people id id ;
注意在我的測試中：發現只有id是主鍵或唯一索引時type才會為range。
這裡順便挑剔下MySQL使用相同的range來表示范圍查詢和列表查詢。
explain people id ;
explain people id (,);
但事實上這兩種情況下MySQL如何使用索引是有很大差別的：
我們不是挑剔：這兩種訪問效率是不同的。對於范圍條件查詢，MySQL無法使用范圍列後面的其他索引列了，但是對於“多個等值條件查詢”則沒有這個限制了。
——出自《高性能MySQL第三版》
index
該聯接類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引文件通常比數據文件小。這個類型通常的作用是告訴我們查詢是否使用索引進行排序操作。
people id;
至於什麼情況下MySQL會利用索引進行排序，等有時間再仔細研究。最典型的就是order by後面跟的是主鍵。
ALL
最慢的一種方式，即全表掃描。
總的來說：上面幾種連接類型的性能是依次遞減的（system>const），不同的MySQL版本、不同的存儲引擎甚至不同的數據量表現都可能不一樣。
possible_keys列指出MySQL能使用哪個索引在該表中找到行。
key列顯示MySQL實際決定使用的鍵（索引）。如果沒有選擇索引，鍵是NULL。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
key_len列顯示MySQL決定使用的鍵長度。如果鍵是NULL，則長度為NULL。使用的索引的長度。在不損失精確性的情況下，長度越短越好 。
ref列顯示使用哪個列或常數與key一起從表中選擇行。
rows列顯示MySQL認為它執行查詢時必須檢查的行數。注意這是一個預估值。
Extra是EXPLAIN輸出中另外一個很重要的列，該列顯示MySQL在查詢過程中的一些詳細信息，包含的信息很多，只選擇幾個重點的介紹下。
Using filesort
MySQL有兩種方式可以生成有序的結果，通過排序操作或者使用索引，當Extra中出現了Using filesort 說明MySQL使用了後者，但注意雖然叫filesort但並不是說明就是用了文件來進行排序，只要可能排序都是在內存裡完成的。大部分情況下利用索引排序更快，所以一般這時也要考慮優化查詢了。
Using temporary
說明使用了臨時表，一般看到它說明查詢需要優化了，就算避免不了臨時表的使用也要盡量避免硬盤臨時表的使用。
Not exists
MYSQL優化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標准的行， 就不再搜索了。
Using index
說明查詢是覆蓋了索引的，這是好事情。MySQL直接從索引中過濾不需要的記錄並返回命中的結果。這是MySQL服務層完成的，但無需再回表查詢記錄。
Using index condition
這是MySQL 5.6出來的新特性，叫做“索引條件推送”。簡單說一點就是MySQL原來在索引上是不能執行如like這樣的操作的，但是現在可以了，這樣減少了不必要的IO操作，但是只能用在二級索引上，詳情點這裡。
Using where
注意：Extra列出現Using where表示MySQL服務器將存儲引擎返回服務層以後再應用WHERE條件過濾。
EXPLAIN的輸出內容基本介紹完了，它還有一個擴展的命令叫做EXPLAIN EXTENDED，主要是結合SHOW WARNINGS命令可以看到一些更多的信息。一個比較有用的是可以看到MySQL優化器重構後的SQL。
Ok,EXPLAIN了解就到這裡，其實這些內容網上都有，只是自己實際操練下會印象更深刻。下一節會介紹SHOW PROFILE、慢查詢日志以及一些第三方工具。
