什麼是MySQL的執行計劃(Explain關鍵字)?

本文作者王良辰，京東中臺架構師，擅長分布式系統及高可用、高並發系統架構與設計。曾經為企業開發過多個通用腳手架，推崇以技術手段提升開發效率、約束開發行為。

什麼是Explain

Explain被稱為執行計劃，在語句之前增加 explain 關鍵字，MySQL 會在查詢上設置一個標記，模擬MySQL優化器來執行SQL語句，執行查詢時，會返回執行計劃的信息，並不執行這條SQL。（注意，如果 from 中包含子查詢，仍會執行該子查詢，將結果放入臨時表中）。

Explain可以用來分析SQL語句和表結構的性能瓶頸。通過explain的結果，可以了解到如數據表的查詢順序、數據查詢操作的操作類型、哪些索引可以被命中、哪些索引實際會命中、每個數據表有多少行記錄被查詢等信息。

Explain命令擴展
explain extended

在explain的基礎上提供一些額外的查詢信息，在explian extended執行以後，通過show warnings命令可以得到優化後的查詢語句，可以看出優化器做了哪些工作，還可以通過某些數據估算表連接的行數。

explain partitions

用於分析使用了分區的表，會顯示出可能用到的分區。

兩點重要提示
1. Explain結果是基於數據表中現有數據的。
2. Explain結果與MySQL版本有很大的關係，不同版本的優化器的優化策略不同。

 

本文示例使用的資料庫表

Explain命令(關鍵字)

explain簡單示例

mysql>explain select * from t_user;

在查詢中的每個」表」會輸出一行，這裡的「表」的意義非常廣泛，不僅僅是資料庫表，還可以是子查詢、一個union 結果等。

explain結果列說明
【id列】

id列是一個有順序的編號，是查詢的順序號，有幾個 select 就顯示幾行。id的順序是按 select 出現的順序增長的。id列的值越大執行優先級越高越先執行，id列的值相同則從上往下執行，id列的值為NULL最後執行。

【select_type列】

select_type列的值標明查詢的類型：

1）simple：表明當前行對應的select是簡單查詢，不包含子查詢和union

2）primary：表明當前行對應的select是複雜查詢中最外層的 select

3）subquery：表明當前行對應的select是包含在 select 中的子查詢（不在 from 子句中）

4）derived：表明當前行對應的select是包含在 from 子句中的子查詢。

MySQL會創建一個臨時表來存放子查詢的查詢結果。用如下的語句示例說明：

explain select (select 1 fromt_user where user_id=1) from (select * from t_group where group_id=1) tmp;

*注意，在資料收集過程中，發現不同版本的MySQL表現不一致，經反覆對比，5.7及以後版本的輸出如下：

很顯然，MySQL在這方面進行了優化.

*注意，MySQL不同版本Explain表現差異很大，有些場景，從語句層面看，是要使用到索引，但經過優化器分析，結合表中現有數據，如果MySQL認為全表掃描性能更優，則會使用全表掃描。

5）union：表明當前行對應的select是在 union 中的第二個和隨後的 select

6）union result：表明當前行對應的select是從 union 臨時表檢索結果的 select

explain select 1 union all select 2 fromdual;

       MySQL5.7及以後同樣做了優化

【table列】

table列的結果表明當前行對應的select正在訪問哪個表。當查詢的<from>子句中有子查詢時，table列是 <derivedN> 格式，表示當前的select依賴 id=N結果行對應的查詢，要先執行 id序號=N 的查詢。當存在 union 時，UNION RESULT 的 table 列的值為<unionN1,N2>，N1和N2表示參與 union 的select 行的id序號。

【type列】

type列的結果表明當前行對應的select的關聯類型或訪問類型，也就是優化器決定怎麼查找數據表中的行，以及查找數據行記錄的大概範圍。該列的取值優化程度的優劣，從最優到最差依次為：null>system> const > eq_ref > ref > range > index > ALL。一般來說，要保證查詢達到range級別，最好達到ref。

1）null，MySQL優化器在優化階段分解查詢語句，在優化過程中就已經可以得到結果，那麼在執行階段就不用再訪問表或索引。

explain select min(user_id) from t_user;

這時的函數min，在索引列user_id中選取最小值，可以直接查找索引來完成，不需要執行時再訪問數據表。

2）const和system：const出現在用 primary key（主鍵） 或 unique key（唯一鍵） 的所有列與常數比較時，優化器對查詢進行優化並將其部分查詢轉化成一個常量。最多有一個匹配行，讀取1次，速度非常快。而system是const的特例，表中數據只有一條匹配時為system。此時可以用explain extended+show warnings查看執行結果。

explain extended select * from (select * from t_user where user_id = 1) tmp;

show warnings;

MySQL5.7及以後版本優化後：

3）eq_ref：primary key（主鍵）或 unique key（唯一鍵） 索引的所有構成部分被join使用 ，只會返回一條符合條件的數據行。這是僅次於const的連接類型。

explain select * from t_group_user gu left join t_group g ong.group_id = gu.group_id;

4) ref：與eq_ref相比，ref類型不是使用primary key（主鍵） 或 unique key（唯一鍵）等唯一索引，而是使用普通索引或者聯合唯一性索引的部分前綴，索引和某個值相比較，可能會找到符合條件的多個數據行。

1. 如下示例，使用的group_name是普通索引

explain select * from t_group where group_name= 'group1';

2.關聯表查詢

explain select g.group_id from t_group gleft join t_group_user gu on gu.group_id = g.group_id;

5）range：出現在 in(),between ,> ,<, >= 等操作符中。使用一個索引來查詢給定範圍的行。

6）index：掃描全表索引（index是從索引中讀取的,所有欄位都有索引，而all是從硬碟中讀取），比ALL要快。

explain select * from t_group;

7）all：即全表掃描，需要從頭到尾去查找所需要的行。一般這種情況下這需要增加索引來進行查詢優化了

explain select * from t_user;

【possible_keys列】

這一列的結果表明查詢可能使用到哪些索引。但有些時候也會出現出現possible_keys 列有結果，而 後面的key列顯示 null 的情況，這是因為此時表中數據不多，優化器認為查詢索引對查詢幫助不大，所以沒有走索引查詢而是進行了全表掃描。 

如果possible_keys列的結果是null，則表明沒有相關的索引。這時，可以通過優化where子句，增加恰當的索引來提升查詢性能。

【key列】

這一列表明優化器實際採用哪個索引來優化對該表的訪問。如果沒有使用索引，則該列是 null。

【key_len列】

這一列表明了在索引裡使用的字節數，通過這個值可以大致估算出具體使用了聯合索引中的前幾個列。 

key_len計算規則這裡不再贅述，不同的數據類型所佔的字節數是不一致的。

【ref列】

這一列表明了在key列記錄的索引中，表查找值所用到的列或常量，常見的有：const（常量），欄位名，如user.user_id

【rows列】

這一列表明優化器大概要讀取並檢測的行數。跟實際的數據行數大部分情況是不一致的。

【Extra列】

顧名思義，這一列表明的是額外信息,這一列的取值對優化SQL非常有參考意義。常見的重要取值如下： 

1）using index：所有被查詢的欄位都是索引列(稱為覆蓋索引),並且where條件是索引的前導列，出現這樣的結果，是性能高的表現。

explainselect group_id,group_name from t_group;

2）using where：被查詢的列未被索引覆蓋，where條件也並非索引的前導列，表示 MySQL 執行器從存儲引擎接收到查詢數據,再進行「後過濾」（Post-filter）。所謂「後過濾」，就是先讀取整行數據，再檢查此行是否符合 where 句的條件，符合就留下，不符合便丟棄。

explain select * from t_user whereuser_name='user1';

3）using where Using index：被查詢的列被索引覆蓋，並且where條件是索引列之一但是不是索引的前導列，也就是沒有辦法直接通過索引來查詢到符合條件的數據

explain select * from t_group where group_name = 'group1';

4）null：被查詢的列沒有被索引覆蓋，但where條件是索引的前導列，此時用到了索引，但是部分列未被索引覆蓋，必須通過「回表查詢」來實現，不是純粹地用到了索引，也不是完全沒用到索引

explain select * from t_user where user_id='1';

5）using index condition：與using where類似，查詢的列不完全被索引覆蓋，where條件中是一個前導列的範圍；這種情況未能通過示例顯現，可能跟MySQL版本有關係。

6） using temporary：這表明需要通過創建臨時表來處理查詢。出現這種情況一般是要進行優化的，用索引來優化。創建臨時表的情況：distinct，group by，orderby，子查詢等

explain select distinct user_name from t_user;

explain select distinct group_name fromt_group; --group_name是索引列

7) usingfilesort：在使用order by的情況下出現，mysql會對結果使用一個外部索引排序，而不是按索引次序從表裡讀取行。此時mysql會根據聯接類型瀏覽所有符合條件的記錄，並保存排序關鍵字和行指針，然後排序關鍵字並按順序檢索行信息。這種情況下要考慮使用索引來優化的。

explain select * from t_user orderby user_name;

explain select * from t_group order bygroup_name;  --group_name是索引列

查詢優化建議

結合前面的描述，首先看 type列的結果，如果有類型是 all 時，表示預計會進行全表掃描（fulltable scan）。通常全表掃描的代價是比較大的，建議創建適當的索引，通過索引檢索避免全表掃描。

再來看下 Extra 列的結果，如果有出現 Using temporary 或者 Using filesort 則要多加關注：

Using temporary，表示需要創建臨時表以滿足需求，通常是因為GROUP BY的列沒有索引，或者GROUP BY和ORDER BY的列不一樣，也需要創建臨時表，建議添加適當的索引。

Using filesort，表示無法利用索引完成排序，也有可能是因為多表連接時，排序欄位不是驅動表中的欄位，因此也沒辦法利用索引完成排序，建議添加適當的索引。

Using where，通常是因為全表掃描或全索引掃描時（type 列顯示為 ALL 或index），又加上了WHERE條件，建議添加適當的索引。

索引使用情況分析
資料庫表

主鍵索引：demo_id

聯合索引：c1,c2,c3

實例說明
實例一：

explain select * from t_demo where c1='d1'and c2='d2' and c3='d3';

explain select * from t_demo where c2='d2'and c1='d1' and c3='d3';

explain select * from t_demo where c3='d3'and c1='d1' and c2='d3';

幾個Sql表現一致

type=ref,ref=const,const,const

執行常量等值查詢時，改變索引列的順序並不會更改explain的執行結果，優化器會進行優化，推薦按照索引順序列編寫sql語句。

實列二：

explain select * from t_demo where c1='d1'and c2>'d2' and c3='d3';

explain select * from t_demo where c1='d1'and c3>'d3' and c2='d2';

第一個例子範圍右側索引失效，使用到了兩個索引。

第二個例子，由於優化器優化的原因，使用到了全部的三個索引。

實例三：

explain select * from t_demo wherec1>'c' and c2='d2' and c3='d3';

explain select * from t_demo wherec1>'e' and c2='d2' and c3='d3';

從上面兩個實例可以發現，同樣使用最左的索引列範圍查詢，有些情況未用到索引，做了全表掃描（第一個例子）；有些情況使用到了索引（第二個例子）。

經反覆驗證，發現如下規律（不一定可靠），也可能與數據的第一行或最小值相關。

1. 跟存儲的數據有關

2. 在大於條件下，如果條件數據小於列數據，則索引無效；如果條件數據大於列數據，則索引有效；

在設計查詢條件時，請注意規避。

針對第一個例子，可以採用覆蓋索引的方式優化。

實例四：

explain select * from t_demo where c1='d1'and c2='d2' order by c3;

explain select * from t_demo where c1='d1'order by c3;

explain select * from t_demo where c1='d1'and c3='d3' order by c2;

order by排序使用到索引和沒使用到索引的情況

實例五：

explain select * from t_demo where c1='d1'and c4='d4' order by c1,c2;

條件列包含沒有索引的列，出現了Using filesort

實例六：

explain select * from t_demo where c1='d1'and c4='d4' group by c1,c2;

性能非常差的場景，同時出現了Using temporary和Using filesort

總結

1. 兩種方式的排序filesort和index，Usingindex是指MySQL掃描索引本身完成排序。index效率高，filesort效率低。

2. order by滿足兩種情況會使用Using index。

1）order by語句使用索引最左前列。

2）使用where子句與order by子句條件列組合滿足索引最左前列。

3. 儘量在索引列上完成排序，遵循索引建立（索引創建的順序）時的最佳左前綴法則。

4. group by與order by很類似，都是先排序後分組，遵照索引創建順序的最佳左前綴法則。

有道無術，術可成；有術無道，止於術

歡迎大家關注Java之道公眾號

好文章，我在看❤️