用 Explain 命令分析 MySQL 的 SQL 執行

程式設計師歷小冰發表於2020-06-15

在上一篇文章《MySQL常見加鎖場景分析》中,我們聊到行鎖是加在索引上的,但是複雜的 SQL 往往包含多個條件,涉及多個索引,找出 SQL 執行時使用了哪些索引對分析加鎖場景至關重要。

比如下面這樣的 SQL:

mysql> delete from t1 where id = 1 or val = 1

其中 id 和 val 都是索引,那麼執行時使用到了哪些索引,加了哪些鎖呢?為此,我們需要使用 explain 來獲取 MySQL 執行這條 SQL 的執行計劃。

什麼是執行計劃呢?簡單來說,就是 SQL 在資料庫中執行時的表現情況,通常用於 SQL 效能分析、優化和加鎖分析等場景,執行過程會在 MySQL 查詢過程中由解析器,前處理器和查詢優化器共同生成。

MySQL 查詢過程

如果能搞清楚 MySQL 是如何優化和執行查詢的,不僅對優化查詢一定會有幫助,還可以通過分析使用到的索引來判斷最終的加鎖場景。

下圖是MySQL執行一個查詢的過程。實際上每一步都比想象中的複雜,尤其優化器,更復雜也更難理解。本文只給予簡單的介紹。

mysql_sql_execute

MySQL查詢過程如下:

  • 客戶端傳送一條查詢給伺服器。
  • 伺服器先檢查查詢快取,如果命中了快取,則立刻返回儲存在快取中的結果。否則進入下一階段。
  • 伺服器端進行SQL解析、預處理,再由優化器生成對應的執行計劃。
  • MySQL根據優化器生成的執行計劃,再呼叫儲存引擎的API來執行查詢。
  • 將結果返回給客戶端。

執行計劃

MySQL會解析查詢,並建立內部資料結構(解析樹),並對其進行各種優化,包括重寫查詢、決定表的讀取順序、選擇合適的索引等。

使用者可通過關鍵字提示(hint)優化器,從而影響優化器的決策過程。也可以通過 explain 瞭解 資料庫是如何進行優化決策的,並提供一個參考基準,便於使用者重構查詢和資料庫表的 schema、修改資料庫配置等,使查詢儘可能高效。

下面,我們依次介紹 explain 中相關輸出引數,並以實際例子解釋這些引數的含義。

select_type

查詢資料的操作型別,有如下

  • simple 簡單查詢,不包含子查詢或 union,如下圖所示,就是最簡單的查詢語句。

select_type_simple

  • primary 是 SQL 中包含複雜的子查詢,此時最外層查詢標記為該值。

  • derived 是 SQL 中 from 子句中包含的子查詢被標記為該值,MySQL 會遞迴執行這些子查詢,把結果放在臨時表。下圖展示了上述兩種型別。

select_type_primary

  • subquery 是 SQL 在 select 或者 where 裡包含的子查詢,被標記為該值。

select_type_subquery

  • dependent subquery:子查詢中的第一個 select,取決於外側的查詢,一般是 in 中的子查詢。

select_type_d_subquery

  • union 是 SQL 在出現在 union 關鍵字之後的第二個 select ,被標記為該值;若 union 包含在 from 的子查詢中,外層select 被標記為 derived。

  • union result 從 union 表獲取結果的 select。下圖展示了 union 和 union result 的 SQL 案例。

select_type_union

  • dependent union 也是 union 關鍵字之後的第二個或者後邊的那個 select 語句,和 dependent subquery 一樣,取決於外面的查詢。

select_type_d_union

type

表的連線型別,其效能由高到低排列為 system,const,eq_ref,ref,range,index 和 all。

  • system 表示表只有一行記錄,相當於系統表。如下圖所示,因為 from 的子查詢派生的表只有一行資料,所以 primary 的表連線型別為 system。

select_type_primary

  • const 通過索引一次就找到,只匹配一行資料,用於常數值比較PRIMARY KEY 或者 UNIQUE索引。

select_type_simple

  • eq_ref 唯一性索引掃描,對於每個索引鍵,表中只有一條記錄與之匹配,常用於主鍵或唯一索引掃描。對於每個來自前邊的表的行組合,從該表中讀取一行。它是除了 const 型別外最好的連線型別。

    如下圖所示,對錶 t1 查詢的 type 是 ALL,表示全表掃描,然後 t1 中每一行資料都來跟 t2.id 這個主鍵索引進行對比,所以 t2 表的查詢就是 eq_ref。

ref

  • ref 非唯一性索引掃描,返回匹配某個單獨值的所有行,和 eq_ref 的區別是索引是非唯一索引,具體案例如下所示。

type_ref

  • range 只檢查給定範圍的行,使用一個索引來選擇行,當使用 =, between, >, <, 和 in 等操作符,並使用常數比較關鍵列時。如下圖所示,其中 id 為唯一索引,而 val 是非唯一索引。

range

  • index 與 ALL 型別類似,唯一區別就是隻遍歷索引樹讀取索引值,比 ALL 讀取所有資料行要稍微快一些,因為索引檔案通常比資料檔案小。這裡涉及 MySQL 的索引覆蓋

  • ALL 全表掃描,通常情況下效能很差,應該避免。

possible_keys,key 和 key_len

possible_key 列指出 MySQL 可能使用哪個索引在該表中查詢。如果該列為 NULL,則沒有使用相關索引。需要檢查 where 子句條件來建立合適的索引提高查詢效率。

key 列顯示 MySQL 實際決定使用的索引。如果沒有選擇索引,則值為 NULL。

key_len 顯示 MySQL 決定使用索引的長度。如果鍵為 NULL,則本列也為 NULL,使用的索引長度,在保證精確度的情況下,越短越好。因為越短,索引檔案越小,需要的 I/O次數也越少。

keys_ref

由上圖可以看出,對於 select * from t2 where id = 1 or val = 1這個語句,可以使用 PRIMARY 或者 idx_t2_val 索引,實際使用了 idx_t2_val 索引,索引的長度為5。

這些其實是我們分析加鎖場景最為關心的欄位,後續文章會具體講解如何根據這些欄位和其他工具一起判斷複雜 SQL 到底加了哪些鎖。

ref

ref 列表示使用其他表的哪個列或者常數來從表中選擇行。如下圖所示,從 t2 讀取資料時,要判斷 t2.id = t1.id,所以 ref 就是 mysql.t1.id

ref

rows 和 filtered

rows 列顯示 MySQL 認為它執行查詢時必須檢查的行數。

filtered 列表明瞭 SQL 語句執行後返回結果的行數佔讀取行數的百分比,值越大越好。MySQL 會使用 Table Filter 來讀取出來的行資料進行過濾,理論上,讀取出來的行等於返回結果的行數時效率最高,過濾的比率越多,效率越低。

filtered

如上圖所示,t1表中有三條資料,rows 為 3,表示所有行都要讀取出來。根據 val = 3 這個 table filter 過濾,只返回一行資料,所以 filtered 比例為33.33%,

extra

包含不適合在其他列中顯示但十分重要的額外資訊。常見的值如下

  • using index 表示 select 操作使用了覆蓋索引,避免了訪問表的資料行,效率不錯。

  • using where 子句用於限制哪一行。也就是讀取資料後使用了 Table Filter 進行過濾。

    如下圖所示,因為 id 和 val 都是有索引的,所以 select * 也是可以直接使用覆蓋索引讀取資料,所以 extra 中有 using index。而因為只使用 val 索引讀取了3行資料,還是通過 where 子句進行過濾,filtered為 55%,所以 extra 中使用了 using where。

keys_ref

  • using filesort MySQL 會對資料使用一個外部的索引排序,而不是按照表內的索引順序進行讀取,若出現該值,應該優化 SQL 語句。如下圖所示,其中 val 列沒有索引,所以無法使用索引順序排序讀取。

filesort

  • using temporary 使用臨時表儲存中間結果,比如,MySQL 在對查詢結果排序時使用臨時表,常用於 order by 和 group by,如果出現該值,應該優化 SQL。根據我的經驗,group by 一個無索引列,或者ORDER BY 或 GROUP BY 的列不是來自JOIN語句序列的第一個表,就會產生臨時表。

  • using join buffer 使用連線快取。如下圖所示,展示了連線快取和臨時表。關於連線快取的內容,大家可以自行查閱,後續有時間在寫文章解釋。

temporary

  • distinct 發現第一個匹配後,停止為當前的行組合搜尋更多的行

後記

通過 explain 瞭解到 SQL 的執行計劃後,我們不僅可以瞭解 SQL 執行時使用的索引,判斷加鎖場景,還可以針對其他資訊對 SQL 進行優化分析,比如將 type 型別從 index 優化到 ref 等。

個人部落格,歡迎來玩

相關文章