MySql 效能憂化隨記

標籤：

    前一段時間接觸MySql 伺服器，關於查詢憂化方面整理，最佳化主要唯繞幾個工具函數 : show profiling  , explain ,  索引 , limit

    如果上司抱怨伺服器查詢太慢，這時候，你可能會說，可能是網路不好，伺服器效能太差。給上司一個合理的說法，私底下，是什麼原因，心中要有數。從用戶端發起http 請求，到服務端後台業務處理，具體到資料庫相關。大多數效能問題出現在資料庫上。通常會有這樣情況，伺服器上線之時，效能還不錯，半年之後，網站慢的像蝸牛。在這段時間，在大的架構不變情況，變化部份是資料庫檔案暴增。因此很可能是資料庫拖後腿了。

       解決辦法有許多種:

             1.業務重構，簡化現有業務。

             2.增加資料緩衝組件

             3.最佳化現有資料庫或表結構

       假設現在已經拿到一段sql 語句，如何對這段sql 做常歸效能分析呢？ MySql 提供一些常用工具。首先 將 sql 語句前加 explain ,  mysql 會列印出查詢計劃:例如:

       explain select * from test limit 1

     

      

   這裡有許多列，具體含義可另行搜尋 mysql explain 。 重點部份: type possible_key , key , key_len , ref , rows , extra 。

   type 屬性:  ALL , INDEX , RANGE , REF , EQ_REF , CONST , 從左往右，表現從差到優。

   ALL 表明: 當前查詢執行全表掃描， 如果在資料量較大表中執行篩選，  顯示全表掃描，表明需要添加索引了。

   possible_keys 屬性: 例舉當前sql 可用索引

   key:當前查詢實際使用索引 (留意關鍵字 force index(...)  , ignore index(...))

   key_len : 當前使用索引顆粒大小 

   ref : where 子條件哪些列被索引使用

   rows:執行查詢過程中，掃描了多少行

   extra : 額外做了哪些動作,如 Using temporary , Using index , Using  filesort.

 

例如: explain select SQL_NO_CACHE * from test where year = 2015 and month = 2  ;

以上樣本表明:使用了索引 type: ref  , 其中 possible_keys 枚舉了能夠使用的索引，key 註明實際使用的索引 , key_len 表明索引長度 10 byte, 該值並不是固定的，本例中使用了複合索引，隨著複合索引命中的例數增多，key_len 會變大。 ref : const , const , 表明命中複合索引前兩列, 如果是: ref:const , const , const ; key_len 的值是15。

rows: 掃描表的行數 ,  值愈小愈好。 limit 關鍵字無法影響 rows 大小。

 

例如: explain select SQL_NO_CACHE  * from test where year = 2015 and month = 2 and date = 20 limit 10

請注意，在末尾加了 limit 10 , 但是 MYSQL 仍然掃描了 136176 行 。

 

開啟 MYSQL 執行耗時統計 :

set profiling = 1 ;

顯示最近查詢sql  耗時

show profiles ;

 

 

顯示指定查詢詳細耗時show profile for query [id]

 

MySql 效能憂化隨記