2012年12月20日 星期四

在MySQL使用Explain做SQL SELECT語法效能測試


在MySQL我們在使用SELECT做撈取資料的時候,有時候常常會效能低落,撈取資料需要很長的時間,有時候是SQL語法下得不好導致沒有使用到正確的索引去撈資料,我們這個時候就必須要檢查我們下的SQL語法到底有哪些地方需要改善,我建立的comment的資料表並新增幾筆假資料去做示範





解釋MySQL語法效能:撈取留言資料





解釋MySQL語法效能:撈取留言資料










而EXPLAIN後的資料有下面這些欄位


  • select_type
  • table:關連到的資料表
  • type:使用關聯查詢的類型(效率由好至壞排序)
    • System
    • const
    • eq_ref
    • ref
    • fulltext
    • ref_or_null
    • index_merge
    • unique_subquery
    • index_subquery
    • range
    • index
    • ALL
  • possible_keys:可能使用到的索引,從WHERE語法選擇出一個適合的欄位
  • key:實際使用到的索引,如果為NULL,則是沒有使用索引
  • key_len:使用索引的長度,長度越短 準確性越高
  • ref:顯示那一列的索引被使用,一般是一個常數(const)
  • rows:MySQL用來返回資料的筆數,可以簡單的把rows視為執行效能,越少越好
  • Extra:MySQL用來解析額外的查詢訊息
    • Distinct:當MySQL找到相關連的資料時,就不再搜尋。
    • Not exists:MySQL優化 LEFT JOIN,一旦找到符合的LEFT JOIN資料後,就不再搜尋。
    • Range checked for each Record(index map:#):無法找到理想的索引。此為最慢的使用索引。
    • Using filesort:當出現這個值時,表示此SELECT語法需要優化。因為MySQL必須進行額外的步驟來進行查詢。
    • Using index:返回的資料是從索引中資料,而不是從實際的資料中返回,當返回的資料都出現在索引中的資料時就會發生此情況。
    • Using temporary:同Using filesort,表示此SELECT語法需要進行優化。此為MySQL必須建立一個暫時的資料表(Table)來儲存結果,此情況會發生在針對不同的資料進行ORDER BY,而不是GROUP BY。
    • Using where:使用WHERE語法中的欄位來返回結果。
    • System:system資料表,此為const連接類型的特殊情況。
    • Const:資料表中的一個記錄的最大值能夠符合這個查詢。因為只有一行,這個值就是常數,因為MySQL會先讀這個值然後把它當做常數。
    • eq_ref:MySQL在連接查詢時,會從最前面的資料表,對每一個記錄的聯合,從資料表中讀取一個記錄,在查詢時會使用索引為主鍵或唯一鍵的全部。
    • ref:只有在查詢使用了非唯一鍵或主鍵時才會發生。
    • range:使用索引返回一個範圍的結果。例如:使用大於>或小於<查詢時發生。
    • index:此為針對索引中的資料進行查詢。
    • ALL:針對每一筆記錄進行完全掃描,此為最壞的情況,應該盡量避免。


參考資料

沒有留言:

張貼留言

ADS