MYSQL explain詳解

標籤：select   有用   解決   操作   迴圈   傳遞   條件   data-   reserve   

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

雖然這篇文章我寫的很長，但看起來真的不會困啊，真的都是乾貨啊！！！！

先解析一條sql語句，看出現什麼內容

EXPLAIN SELECT s.uid,s.username,s.name,f.email,f.mobile,f.phone,f.postalcode,f.address
FROM uchome_space AS s,uchome_spacefield AS f
WHERE 1 
AND s.groupid=0
AND s.uid=f.uid


1. id

SELECT識別符。這是SELECT查詢序號。這個不重要,查詢序號即為sql語句執行的順序，看下面這條sql

EXPLAINSELECT *FROM (SELECT* FROMuchome_space LIMIT 10)AS s

它的執行結果為

可以看到這時的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.idORDER 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.idORDER BYcity.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 了！驅動表的定義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語句。

 

 

2017年1.26的拓展      我是無所不能的coder的分界線

回頭看看幾年前寫的這篇部落格，真的也是很淺顯，只是簡單的介紹了explain後每個選項的概念，對於執行個體沒有太多的講解，而且最重要的是沒有指出那種情況下的選項（結合實際情況）才是最佳化的，ok,start again

很明顯，在所有explain的結果中最重要的要數type/key/rows/extra這4個欄位了,那接下來我著重在說一下這四個欄位代表的意思及如何最佳化

現有兩個表，一個項目表（project），一個留言表(t_message)，使用者可以針對不同的項目進行流行操作。

現有一個最基本的聯表操作，

EXPLAIN SELECT * FROM project AS p JOIN jmw_message.t_message AS t ON p.id = t.target_id
結果是這樣的

出現這種情況是最容易理解的了，因為這隻是簡單聯表查詢，沒有加任何條件，在實際情況下是不會出現這種sql的。從的結果中可以看出mysql對t_message表進行了全表掃描，對project表使用了eq_ref,這符合了mysql對什麼情況下會使用到eq_ref的定義，這是非常理想的一種連線類型。

下面我們討論一個實際情況下會遇到的例子，我們聯表取前100條資料，
EXPLAIN SELECT * FROM project AS p JOIN jmw_message.t_message AS t ON p.id = t.target_id LIMIT 100

可以發現，除了影響的行數稍微多了一點（可以忽略，甚至可以理解為沒有不同），其他所有的參數都是相同的，也就是說，這種情況下搜尋全部資料和搜尋100條資料的耗時是一樣的，為什麼會這樣呢？不應該啊！！

這裡需要著重說明的是：上面兩條語句explain得到的結果是相同的，是因為他們的索引使用原則是相同的，即都沒有很好的使用索引，（因為沒有where條件和order by語句）但他們的最終耗時是不同的，很明顯傳輸100條資料肯定要比傳送1條資料慢。所以，最終耗時會在sending data（用show profile查看）上消耗的比例最大

那實際情況下，最有可能會遇到什麼問題呢？

1 根據項目id作為搜尋條件（即使用where條件）

2 根據時間或者id來排序（即使用order by條件）

3 根據以上兩個

下面我們開始舉栗子

《1》搜尋最新的100條留言

《2》搜尋出某個項目下最新的10條留言

《3》搜尋出某個項目最近一個月每天有多少條留言

《4》搜尋出最近一個月每天有多少條留言

《5》搜尋某個使用者今天留言數量

《6》搜尋今天有多少條新增留言

下面我們開始吃栗子

《1》搜尋最新的100條留言

          EXPLAIN SELECT * FROM project AS p JOIN jmw_message.t_message AS t ON p.id = t.target_id ORDER BY t.id DESC LIMIT 100 ;
          EXPLAIN SELECT * FROM project AS p JOIN jmw_message.t_message AS t ON p.id = t.target_id ORDER BY p.id DESC LIMIT 100

以下兩條語句都能實現效果，但索引使用方式卻完全不同。第一條語句要比第二條最佳化的多，

可以看到，第一條語句的type值為index,影響結果即只有100行，也就是說非常合適的使用了索引。正所謂，福無雙至禍不單行，當你一個地方出問題的時候，難免其他地方也出問題，因為沒有使用合理的索引-->導致全表掃描-->影響結果集太大-->從而導致使用了using temporary和using filesort(這個也很重要)。那這兩條語句很明顯只有order by條件的一點小小的不同.說實話，我不是很理解為什麼會出現這種情況因為這兩個條件分表是兩個表的主鍵，都有主鍵索引，唯一合理的解釋可能是因為這時候聯表之後t_message是主表（因為他是留言表，一切以他為準），而order by排序當然應該是根據主表的主鍵拍排序才會使用到索引了，似乎有點牽強，但貌似這麼理解沒有大毛病

下面著重說一下using temporary和using filesort

using temporary 官方解釋：”為瞭解決查詢，MySQL需要建立一個暫存資料表來容納結果。典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時。“”很明顯就是通過where條件一次性檢索出來的結果集太大了，記憶體放不下了，只能通過家裡暫存資料表來輔助處理

using filesort 官方解釋：“MySQL需要額外的一次傳遞，以找出如何按排序次序檢索行。通過根據聯結類型瀏覽所有行並為所有匹配WHERE子句的行儲存排序關鍵字和行的指標來完成排序。然後關鍵字被排序，並按排序次序檢索行”

我這裡的理解是：對於order by的欄位沒有使用到欄位，所以使用了using filesort.   這兩個問題同時出現的可能性很大啊！！！

《2》搜尋出某個項目下最新的10條留言

EXPLAIN SELECT * FROM t_message WHERE target_id = 770 ORDER BY id DESC LIMIT 10;
EXPLAIN SELECT * FROM t_message WHERE target_id = 770 ORDER BY publish_time DESC LIMIT 10

以上兩條select語句的執行搜尋結果是一樣的，但explain分析結果不同，只是因為order by 條件的不同

 

MYSQL explain詳解