explain詳解

來源：程序員人生發布時間：2016-11-16 08:54:56 閱讀次數：5062次

explain顯示了mysql如何使用索引來處理select語句和連接表。可以幫助選擇更好的索引和寫出更優化的查詢語句。

先解析1條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語句的第2個或說是后面那1個.現履行1條語句，explain
select * from uchome_space limit 10 union select * from uchome_space limit 10,10

會有以下結果

第2條語句使用了union

2.4 dependent union UNION中的第2個或后面的SELECT語句，取決于外面的查詢

2.5 union result UNION的結果,如上面所示

還有幾個參數，這里就不說了，不重要

3 table

輸出的行所用的表，這個參數不言而喻，容易理解

4 type

連接類型。有多個參數，先從最好類型到最差類型介紹 重要且困難

4.1 system

表唯一1行，這是const類型的特列，平時不會出現，這個也能夠疏忽不計

4.2 const

表最多有1個匹配行，const用于比較primary key 或unique索引。由于只匹配1行數據，所以很快

記住1定是用到primary key 或unique，并且只檢索出兩條數據的情況下才會是const,看下面這條語句

explain SELECT * FROM `asj_admin_log` limit 1,結果是

雖然只搜索1條數據，但是由于沒有用到指定的索引，所以不會使用const.繼續看下面這個

explain SELECT * FROM `asj_admin_log` where log_id = 111

log_id是主鍵，所以使用了const。所以說可以理解為const是最優化的

4.3 eq_ref

對eq_ref的解釋，mysql手冊是這樣說的:"對每一個來自于前面的表的行組合，從該表中讀取1行。這多是最好的聯接類型，除const類型。它用在1個索引的所有部份被聯接使用并且索引是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_space1個表用到了eq_ref,并且sql語句如果變成

explain select * from uchome_space,uchome_spacefield where uchome_space.uid = uchome_spacefield.uid

結果還是1樣，需要說明的是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值的行。在解決子查詢中常常使用該聯接類型的優化。

上面這5種情況都是很理想的索引使用情況

4.6 index_merge 該聯接類型表示使用了索引合并優化方法。在這類情況下，key列包括了使用的索引的清單，key_len包括了使用的索引的最長的關鍵元素。

4.7 unique_subquery

4.8 index_subquery

4.9 range 給定范圍內的檢索，使用1個索引來檢查行。看下面兩條語句

explain select * from uchome_space where uid in (1,2)

explain select * from uchome_space where groupid in (1,2)

uid有索引，groupid沒有索引，結果是第1條語句的聯接類型是range,第2個是ALL.以為是1定范圍所以說像 between也能夠這類聯接,很明顯

explain select * from uchome_space where friendnum = 17

這樣的語句是不會使用range的，它會使用更好的聯接類型就是上面介紹的ref

4.10 index 該聯接類型與ALL相同，除只有索引樹被掃描。這通常比ALL快，由于索引文件通常比數據文件小。（也就是說雖然all和Index都是讀全表，但index是從索引中讀取的，而all是從硬盤中讀的）

當查詢只使用作為單索引1部份的列時，MySQL可使用該聯接類型。

4.11 ALL 對每一個來自于先前的表的行組合，進行完全的表掃描。如果表是第1個沒標記const的表，這通常不好，并且通常在它情況下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常數值或列值被檢索出。

5 possible_keys 提示使用哪一個索引會在該表中找到行，不太重要

6 keys MYSQL使用的索引，簡單且重要

7 key_len MYSQL使用的索引長度

8 ref ref列顯示使用哪一個列或常數與key1起從表當選擇行。

9 rows 顯示MYSQL履行查詢的行數，簡單且重要，數值越大越不好，說明沒有用好索引

10 Extra 該列包括MySQL解決查詢的詳細信息。

10.1 Distinct MySQL發現第1個匹配行后，停止為當前的行組合搜索更多的行。1直沒見過這個值

10.2 Not exists

10.3 range checked for each record

沒有找到適合的索引

10.4 using filesort

MYSQL手冊是這么解釋的“MySQL需要額外的1次傳遞，以找出如何按排序順序檢索行。通過根據聯接類型閱讀所有行并為所有匹配WHERE子句的行保存排序關鍵字和行的指針來完成排序。然后關鍵字被排序，并按排序順序檢索行。”目前不太明白

10.5 using index 只使用索引樹中的信息而不需要進1步搜索讀取實際的行來檢索表中的信息。這個比較容易理解，就是說明是不是使用了索引

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需要創建1個臨時表來容納結果。典型情況如查詢包括可以按不同情況列出列的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 BY city.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 where; Using filesort
     1 SIMPLE       ads    eq_ref PRIMARY         PRIMARY 4        city.ads_id       1    100.00 Using where

這是為何呢？他倆之間只是1個order by不同，MySQL 表關聯的算法是 Nest Loop Join，是通過驅動表的結果集作為循環基礎數據，然后1條1條地通過該結果集中的數據作為過濾條件到下1個表中查詢數據，然后合并結果。EXPLAIN 結果中，第1行出現的表就是驅動表（Important!）以上兩個查詢語句，驅動表都是 city，如上面的履行計劃所示！

對驅動表可以直接排序，對非驅動表（的字段排序）需要對循環查詢的合并結果（臨時表）進行排序（Important!）

因此，order by ads.id desc 時，就要先 using temporary 了！

驅動表的定義

wwh999 在 2006年總結說，當進行多表連接查詢時， [驅動表] 的定義為：
1）指定了聯接條件時，滿足查詢條件的記錄行數少的表為[驅動表]；
2）未指定聯接條件時，行數少的表為[驅動表]（Important!）。

永久用小結果集驅動大結果集

今天學到了1個很重要的1點：當不肯定是用哪一種類型的join時，讓mysql優化器自動去判斷，我們只需寫select * from t1,t2 where t1.field = t2.field

10.7 using where

WHERE子句用于限制哪個行匹配下1個表或發送到客戶。除非你專門從表中索取或檢查所有行，如果Extra值不為Using where并且表聯接類型為ALL或index，查詢可能會有1些毛病。（這個說明不是很理解，由于很多很多語句都會有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發現了1個索引，可以用來查詢GROUP BY或DISTINCT查詢的所有列，而不要額外搜索硬盤訪問實際的表。并且，按最有效的方式使用索引，以便對每一個組，只讀取少許索引條目。

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈
程序員人生