原文位址：http://blog.csdn.net/zhuxineli/article/details/14455029
explain顯示了mysql如何使用索引來處理select語句以及連接配接表。可以幫助選擇更好的索引和寫出更優化的查詢語句。
先解析一條sql語句，看出現什麼内容
EXPLAINSELECTs.uid,s.username,s.name,f.email,f.mobile,f.phone,f.postalcode,f.address
FROM
uchome_space ASs,uchome_spacefieldASf
WHERE
1
AND
s.groupid=0
AND
s.uid=f.uid
1. id
SELECT識别符。這是SELECT查詢序列号。這個不重要,查詢序号即為sql語句執行的順序，看下面這條sql
EXPLAINSELECT*FROM(
SELECT*
FROMuchome_space LIMIT10)ASs
它的執行結果為
可以看到這時的id變化了
2.select_type
select類型，它有以下幾種值
2.1 simple 它表示簡單的select,沒有union和子查詢
2.2 primary 最外面的select,在有子查詢的語句中，最外面的select查詢就是primary,上圖中就是這樣
2.3 union union語句的第二個或者說是後面那一個.現執行一條語句，explain
select * from uchome_space limit 10 union select * from uchome_space limit 10,10
會有如下結果
第二條語句使用了union
2.4 dependent union UNION中的第二個或後面的SELECT語句，取決于外面的查詢
2.5 union result UNION的結果,如上面所示
還有幾個參數，這裡就不說了，不重要
3 table
輸出的行所用的表，這個參數顯而易見，容易了解
4 type
連接配接類型。有多個參數，先從最佳類型到最差類型介紹 重要且困難
4.1 system
表僅有一行，這是const類型的特列，平時不會出現，這個也可以忽略不計
4.2 const
表最多有一個比對行，const用于比較primary key 或者unique索引。因為隻比對一行資料，是以很快
記住一定是用到primary key 或者unique，并且隻檢索出一條資料的情況下才會是const,看下面這條語句
explain SELECT * FROM `asj_admin_log` limit 1,結果是
雖然隻搜尋一條資料，但是因為沒有用到指定的索引，是以不會使用const.繼續看下面這個
explain SELECT * FROM `asj_admin_log` where log_id = 111
log_id是主鍵，是以使用了const。是以說可以了解為const是最優化的
4.3 eq_ref
對于eq_ref的解釋，mysql手冊是這樣說的:"對于每個來自于前面的表的行組合，從該表中讀取一行。這可能是最好的聯接類型，除了const類型。它用在一個索引的所有部分被聯接使用并且索引是UNIQUE或PRIMARY KEY"。eq_ref可以用于使用=比較帶索引的列。看下面的語句
explain select * from uchome_spacefield,uchome_space where uchome_spacefield.uid = uchome_space.uid
得到的結果是下圖所示。很明顯，mysql使用eq_ref聯接來處理uchome_space表。
目前的疑問：
4.3.1 為什麼是隻有uchome_space一個表用到了eq_ref,并且sql語句如果變成
explain select * from uchome_space,uchome_spacefield where uchome_space.uid = uchome_spacefield.uid
結果還是一樣，需要說明的是uid在這兩個表中都是primary
4.4 ref 對于每個來自于前面的表的行組合，所有有比對索引值的行将從這張表中讀取。如果聯接隻使用鍵的最左邊的字首，或如果鍵不是UNIQUE或PRIMARY KEY（換句話說，如果聯接不能基于關鍵字選擇單個行的話），則使用ref。如果使用的鍵僅僅比對少量行，該聯接類型是不錯的。
看下面這條語句 explain select * from uchome_space where uchome_space.friendnum = 0，得到結果如下，這條語句能搜出1w條資料
4.5 ref_or_null 該聯接類型如同ref，但是添加了MySQL可以專門搜尋包含NULL值的行。在解決子查詢中經常使用該聯接類型的優化。
上面這五種情況都是很理想的索引使用情況
4.6 index_merge 該聯接類型表示使用了索引合并優化方法。在這種情況下，key列包含了使用的索引的清單，key_len包含了使用的索引的最長的關鍵元素。
4.7 unique_subquery
4.8 index_subquery
4.9 range 給定範圍内的檢索，使用一個索引來檢查行。看下面兩條語句
explain select * from uchome_space where uid in (1,2)
explain select * from uchome_space where groupid in (1,2)
uid有索引，groupid沒有索引，結果是第一條語句的聯接類型是range,第二個是ALL.以為是一定範圍是以說像 between也可以這種聯接,很明顯
explain select * from uchome_space where friendnum = 17
這樣的語句是不會使用range的，它會使用更好的聯接類型就是上面介紹的ref
4.10 index 該聯接類型與ALL相同，除了隻有索引樹被掃描。這通常比ALL快，因為索引檔案通常比資料檔案小。（也就是說雖然all和Index都是讀全表，但index是從索引中讀取的，而all是從硬碟中讀的）
當查詢隻使用作為單索引一部分的列時，MySQL可以使用該聯接類型。
4.11 ALL 對于每個來自于先前的表的行組合，進行完整的表掃描。如果表是第一個沒标記const的表，這通常不好，并且通常在它情況下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常數值或列值被檢索出。
5 possible_keys 提示使用哪個索引會在該表中找到行，不太重要
6 keys MYSQL使用的索引，簡單且重要
7 key_len MYSQL使用的索引長度
8 ref ref列顯示使用哪個列或常數與key一起從表中選擇行。
9 rows 顯示MYSQL執行查詢的行數，簡單且重要，數值越大越不好，說明沒有用好索引
10 Extra 該列包含MySQL解決查詢的詳細資訊。
10.1 Distinct MySQL發現第1個比對行後，停止為目前的行組合搜尋更多的行。一直沒見過這個值
10.2 Not exists
10.3 range checked for each record
沒有找到合适的索引
10.4 using filesort
MYSQL手冊是這麼解釋的“MySQL需要額外的一次傳遞，以找出如何按排序順序檢索行。通過根據聯接類型浏覽所有行并為所有比對WHERE子句的行儲存排序關鍵字和行的指針來完成排序。然後關鍵字被排序，并按排序順序檢索行。”目前不太明白
10.5 using index 隻使用索引樹中的資訊而不需要進一步搜尋讀取實際的行來檢索表中的資訊。這個比較容易了解，就是說明是否使用了索引
explain select * from ucspace_uchome where uid = 1的extra為using index（uid建有索引）
explain select count(*) from uchome_space where groupid=1 的extra為using where(groupid未建立索引)
10.6 using temporary
為了解決查詢，MySQL需要建立一個臨時表來容納結果。典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時。
出現using temporary就說明語句需要優化了，舉個例子來說
EXPLAIN SELECT ads.id FROM ads, city WHERE city.city_id = 8005 AND ads.status = 'online' AND city.ads_id=ads.id ORDER BY ads.id desc
id select_type table type possible_keys key key_len ref rows filtered Extra
------ ----------- ------ ------ -------------- ------- ------- -------------------- ------ -------- -------------------------------
1 SIMPLE city
ref ads_id,city_id city_id 4 const 2838 100.00
Using temporary; Using filesort
1 SIMPLE ads eq_ref PRIMARY PRIMARY 4 city.ads_id 1 100.00 Using where
這條語句會使用using temporary,而下面這條語句則不會
EXPLAIN SELECT ads.id FROM
ads, city WHERE city.city_id = 8005 AND ads.status = 'online' AND city.ads_id=ads.id ORDER BYcity.ads_id desc
id select_type table type possible_keys key key_len ref rows filtered Extra
------ ----------- ------ ------ -------------- ------- ------- -------------------- ------ -------- ---------------------------
Using where; Using filesort
1 SIMPLE ads eq_ref PRIMARY PRIMARY 4 city.ads_id 1 100.00 Using where
這是為什麼呢？他倆之間隻是一個order by不同，MySQL 表關聯的算法是 Nest Loop Join，是通過驅動表的結果集作為循環基礎資料，然後一條一條地通過該結果集中的資料作為過濾條件到下一個表中查詢資料，然後合并結果。EXPLAIN
結果中，第一行出現的表就是驅動表（Important!）以上兩個查詢語句，驅動表都是 city，如上面的執行計劃所示！
對驅動表可以直接排序，對非驅動表（的字段排序）需要對循環查詢的合并結果（臨時表）進行排序（Important!）
是以，order by ads.id desc 時，就要先 using temporary 了！
驅動表的定義
wwh999 在 2006年總結說，當進行多表連接配接查詢時， [驅動表] 的定義為：
1）指定了聯接條件時，滿足查詢條件的記錄行數少的表為[驅動表]；
2）未指定聯接條件時，行數少的表為[驅動表]（Important!）。
永遠用小結果集驅動大結果集
今天學到了一個很重要的一點：當不确定是用哪種類型的join時，讓mysql優化器自動去判斷，我們隻需寫select * from t1,t2 where t1.field = t2.field
10.7 using where
WHERE子句用于限制哪一個行比對下一個表或發送到客戶。除非你專門從表中索取或檢查所有行，如果Extra值不為Using where并且表聯接類型為ALL或index，查詢可能會有一些錯誤。（這個說明不是很了解，因為很多很多語句都會有where條件，而type為all或index隻能說明檢索的資料多，并不能說明錯誤，useing where不是很重要，但是很常見）
如果想要使查詢盡可能快，應找出Using filesort 和Using temporary的Extra值。
10.8 Using sort_union(...), Using union(...),Using intersect(...)
這些函數說明如何為index_merge聯接類型合并索引掃描
10.9 Using index for group-by
類似于通路表的Using index方式，Using index for group-by表示MySQL發現了一個索引，可以用來查詢GROUP BY或DISTINCT查詢的所有列，而不要額外搜尋硬碟通路實際的表。并且，按最有效的方式使用索引，以便對于每個組，隻讀取少量索引條目。
執行個體講解
通過相乘EXPLAIN輸出的rows列的所有值，你能得到一個關于一個聯接如何的提示。這應該粗略地告訴你MySQL必須檢查多少行以執行查詢。當你使用max_join_size變量限制查詢時，也用這個乘積來确定執行哪個多表SELECT語句。
不要害怕今日的苦，你要相信明天，更苦！
![資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)