MySQL語句執行分析(一)

瑜戈發表於2018-09-26

今天客戶那邊遇到一個問題:多選檔案進行操作,資料量一大後臺處理就特別慢,瀏覽器顯示504超時。為了驗證問題是否出在sql語句,所以用以下方法來分析:

  • 查詢SQL執行記錄
  • explain 分析
  • MySQL 語句執行時間

下面會分別介紹三個方法的開啟方法。

查詢SQL執行記錄

查詢日誌功能是否開啟

show variables LIKE 'general%';
複製程式碼

MySQL語句執行分析(一)

general_log:日誌記錄功能是否開啟,預設為OFF
general_log_file:日誌存放路徑

開啟日誌功能

set GLOBAL general_log = 'ON'; 
複製程式碼

然後再次查詢是否開啟成功

在指定路徑檢視SQL記錄

explain 分析

大部分的效能分析都需要使用到該命令,可以用來檢視SQL語句的執行效果,可以幫助選擇更好地索引和優化語句。

語法

explain + SQL語句

輸出:

MySQL語句執行分析(一)

引數解析

id SELECT識別符。這是SELECT的查詢序列號
select_type

SELECT型別,可以為以下任何一種:

  • SIMPLE:簡單SELECT(不使用UNION或子查詢)
  • PRIMARY:最外面的SELECT
  • UNION:UNION中的第二個或後面的SELECT語句
  • DEPENDENT UNION:UNION中的第二個或後面的SELECT語句,取決於外面的查詢
  • UNION RESULT:UNION 的結果
  • SUBQUERY:子查詢中的第一個SELECT
  • DEPENDENT SUBQUERY:子查詢中的第一個SELECT,取決於外面的查詢
  • DERIVED:匯出表的SELECT(FROM子句的子查詢)
table

輸出的行所引用的表

type

聯接型別。下面給出各種聯接型別,按照從最佳型別到最壞型別進行排序:

  • system:表僅有一行(=系統表)。這是const聯接型別的一個特例。
  • const:表最多有一個匹配行,它將在查詢開始時被讀取。因為僅有一行,在這行的列值可被優化器剩餘部分認為是常數。const表很快,因為它們只讀取一次!
  • eq_ref:對於每個來自於前面的表的行組合,從該表中讀取一行。這可能是最好的聯接型別,除了const型別。
  • ref:對於每個來自於前面的表的行組合,所有有匹配索引值的行將從這張表中讀取。
  • ref_or_null:該聯接型別如同ref,但是新增了MySQL可以專門搜尋包含NULL值的行。
  • index_merge:該聯接型別表示使用了索引合併優化方法。
  • unique_subquery:該型別替換了下面形式的IN子查詢的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一個索引查詢函式,可以完全替換子查詢,效率更高。
  • index_subquery:該聯接型別類似於unique_subquery。可以替換IN子查詢,但只適合下列形式的子查詢中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
  • range:只檢索給定範圍的行,使用一個索引來選擇行。
  • index:該聯接型別與ALL相同,除了只有索引樹被掃描。這通常比ALL快,因為索引檔案通常比資料檔案小。
  • ALL:對於每個來自於先前的表的行組合,進行完整的表掃描。
possible_keys

指出MySQL能使用哪個索引在該表中找到行

key 顯示MySQL實際決定使用的鍵(索引)。如果沒有選擇索引,鍵是NULL。
key_len 顯示MySQL決定使用的鍵長度。如果鍵是NULL,則長度為NULL。
ref 顯示使用哪個列或常數與key一起從表中選擇行。
rows 顯示MySQL認為它執行查詢時必須檢查的行數。多行之間的資料相乘可以估算要處理的行數。
filtered 顯示了通過條件過濾出的行數的百分比估計值。
Extra

該列包含MySQL解決查詢的詳細資訊

  • Distinct:MySQL發現第1個匹配行後,停止為當前的行組合搜尋更多的行。
  • Not exists:MySQL能夠對查詢進行LEFT JOIN優化,發現1個匹配LEFT JOIN標準的行後,不再為前面的的行組合在該表內檢查更多的行。
  • range checked for each record (index map: #):MySQL沒有發現好的可以使用的索引,但發現如果來自前面的表的列值已知,可能部分索引可以使用。
  • Using filesort:MySQL需要額外的一次傳遞,以找出如何按排序順序檢索行。
  • Using index:從只使用索引樹中的資訊而不需要進一步搜尋讀取實際的行來檢索表中的列資訊。
  • Using temporary:為了解決查詢,MySQL需要建立一個臨時表來容納結果。
  • Using where:WHERE 子句用於限制哪一個行匹配下一個表或傳送到客戶。
  • Using sort_union(...), Using union(...), Using intersect(...):這些函式說明如何為index_merge聯接型別合併索引掃描。
  • Using index for group-by:類似於訪問表的Using index方式,Using index for group-by表示MySQL發現了一個索引,可以用來查 詢GROUP BY或DISTINCT查詢的所有列,而不要額外搜尋硬碟訪問實際的表。

MySQL 語句執行時間

show profile 以及show profiles語句可以顯示當前會話過程中執行SQL語句的效能資訊。

檢視profile是否開啟

show variables like '%profil%';
複製程式碼

MySQL語句執行分析(一)

profiling:OFF 預設此功能關閉

設定開啟狀態

set profiling = 1;
複製程式碼

再次檢視是否開啟

MySQL語句執行分析(一)

已經是開啟狀態

執行sql語句後進行分析

執行完後,輸入

show profiles複製程式碼

即可檢視所有的sql的執行時間

MySQL語句執行分析(一)

show profile for query 1 
複製程式碼

檢視第1個sql語句的執行的各個操作的耗時詳情。

MySQL語句執行分析(一)

show profile cpu, block io, memory,swaps,context switches,source for query 6;
複製程式碼

可以檢視出一條SQL語句執行的各種資源消耗情況,比如CPU,IO等

show profile all for query 6 檢視第6條語句的所有的執行資訊。
複製程式碼

測試完畢後,關閉引數:

mysql> set profiling=0
複製程式碼

下一篇來說下慢查詢

相關文章