mysql sql最佳化之expain

標籤：exlain

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

1. id

  SELECT識別符。這是SELECT查詢序號。查詢序號即為sql語句執行的順序

2.select_type

  select類型，它有以下幾種值

2.1 simple 它表示簡單的select,沒有union和子查詢

2.2 primary 最外面的select,在有子查詢的語句中，最外面的select查詢就是primary,中就是這樣

2.3 union  帶有union語句的第二個sql或者說是後面那一個sql使用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

4.3 eq_ref對於eq_ref的解釋:對於每個來自於前面的表的行組合，從該表中讀取一行。這可能是最好的聯結類型，除了const類型。它用在一個索引的所有部分被聯結使用並且索引是UNIQUE或PRIMARY KEY

eq_ref可以用於使用=比較帶索引的列。看下面的語句explain select * from uchome_spacefield,uchome_space where uchome_spacefield.uid = uchome_space.uid

(第二個語句從前面表的行中讀取一行，用到UNIQUE或PRIMARY KEY索引)

4.4 ref 對於每個來自於前面的表的行組合，所有有匹配索引值的行將從這張表中讀取。

果聯結只使用鍵的最左邊的首碼，或如果鍵不是UNIQUE或PRIMARY KEY（換句話說，如果聯結不能基於關鍵字選擇單個行的話），則使用ref。

4.5 ref_or_null 該聯結類型如同ref，但是添加了MySQL可以專門搜尋包含NULL值的行。在解決子查詢中經常使用該聯結類型的最佳化。

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

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

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.

4.10 index    該聯結類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引檔案通常比資料檔案小。

（也就是說雖然all和Index都是讀全表，但index是從索引中讀取的，而all是從硬碟中讀的）

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  （需要最佳化）

10.5 using index 否使用了索引

10.6 using temporary  （需要最佳化）

出現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    

它倆之間只是一個order by不同，MySQL 表關聯的演算法是 Nest Loop Join，是通過驅動表的結果集作為迴圈基礎資料，然後一條一條地通過該結果集中的資料作為過濾條件到下一個表中查詢資料，然後合并結果。EXPLAIN 結果中，第一行出現的表就是驅動表（Important!）以上兩個查詢語句，驅動表都是 city，如上面的執行計畫所示！

對驅動表可以直接排序，對非驅動表（的欄位排序）需要對迴圈查詢的合并結果（暫存資料表）進行排序（Important!）因此，order by ads.id desc 時，就要先 using temporary 了！

驅動表的定義

當進行多表串連查詢時， [驅動表] 的定義為：

1）指定了聯結條件時，滿足查詢條件的記錄行數少的表為[驅動表]；

2）未指定聯結條件時，行數少的表為[驅動表]（Important!）。

永遠用小結果集驅動大結果集 當不確定是用哪種類型的join時，讓mysql最佳化器自動去判斷，我們只需寫select * from t1,t2 where t1.field = t2.field

10.7 using where 

WHERE子句用於限制哪一個行匹配下一個表或發送到客戶。除非你專門從表中索取或檢查所有行，

（這個說明不是很理解，因為很多很多語句都會有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查詢的所有列，而不要額外搜尋硬碟訪問實際的表。並且，按最有效方式使用索引，以便對於每個組，唯讀取少量索引條目。

mysql sql最佳化之expain