explain mysql效能最佳化

標籤：

1 使用explain語句去查看分析結果，如
  explain select * from test1 where id=1;
會出現：
id  selecttype table  type possible_keys  keykey_len  ref rows  extra各列

其中，type=const表示通過索引一次就找到了，key=primary的話，表示使用了主鍵 type=all,表示為全表掃描，key=null表示沒用到索引；type=ref,因為這時認為是多個匹配行，在聯集查詢中，一般為REF


2 MYSQL中的複合式索引
假設表有id,key1,key2,key3,把三者形成一個複合式索引，則
如：
   where key1=....
   where key1=1 and key2=2
   where key1=3 and key3=3 andkey2=2
根據最左原則，這些都是可以使用索引的哦
如
   from test where key1=1 orderby key3
用explain分析的話，只用到了normal_key索引，但只對where子句起作用，而後面的order by需要排序


3 使用慢查詢分析：
在my.ini中：
long_query_time=1
log-slow-queries=d:\mysql5\logs\mysqlslow.log
把超過1秒的記錄在慢查詢日誌中
可以用mysqlsla來分析之。也可以在mysqlreport中，有如
DMS 分別分析了select ,update,insert,delete,replace等所佔的百份比

4 MYISAM和INNODB的鎖定
myisam中，注意是表鎖來的，比如在多個UPDATE操作後，再SELECT時，會發現SELECT操作被鎖定了，必須等所有UPDATE操作完畢後，再能SELECT innodb的話則不同了，用的是行鎖，不存在上面問題。 
5 MYSQL的事務配置項
innodb_flush_log_at_trx_commit=1
表示事務提交時立即把交易記錄寫入磁碟，同時資料和索引也更新

innodb_flush_log_at_trx_commit=0
事務提交時，不立即把交易記錄寫入磁碟，每隔1秒寫一次
innodb_flush_log_at_trx_commit=2
事務提交時，立即寫入磁碟檔案（這裡只是寫入到核心緩衝區，但不立即重新整理到磁碟，而是每隔1秒重新整理到盤，同時更新資料和索引   explain用法 

EXPLAIN tbl_name
或：
EXPLAIN [EXTENDED] SELECT select_options

前者可以得出一個表的欄位結構等等，後者主要是給出相關的一些索引資訊，而今天要講述的重點是後者。

舉例
mysql> explain select * from event;
+—-+————-+——-+——+—————+——+———+——+——+——-+
| id | select_type | table | type | possible_keys | key | key_len |ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+——-+
| 1 | SIMPLE | event | ALL | NULL | NULL | NULL | NULL | 13 ||
+—-+————-+——-+——+—————+——+———+——+——+——-+
1 row in set (0.00 sec)

各個屬性的含義
id
select查詢的序號

select_type
select查詢的類型，主要是區別普通查詢和聯集查詢、子查詢之類的複雜查詢。

table
輸出的行所引用的表。

type
聯集查詢所使用的類型。
type顯示的是訪問類型，是較為重要的一個指標，結果值從好到壞依次是：
system > const > eq_ref> ref > fulltext >ref_or_null > index_merge >unique_subquery > index_subquery >range > index > ALL
一般來說，得保證查詢至少達到range層級，最好能達到ref。

possible_keys
指出MySQL能使用哪個索引在該表中找到行。如果是空的，沒有相關的索引。這時要提高效能，可通過檢驗WHERE子句，看是否引用某些欄位，或者檢查欄位不是適合索引。

key
顯示MySQL實際決定使用的鍵。如果沒有索引被選擇，鍵是NULL。

key_len
顯示MySQL決定使用的鍵長度。如果鍵是NULL，長度就是NULL。文檔提示特別注意這個值可以得出一個多重主鍵裡mysql實際使用了哪一部分。

ref
顯示哪個欄位或常數與key一起被使用。

rows
這個數表示mysql要遍曆多少資料才能找到，在innodb上是不準確的。

Extra
如果是Only index，這意味著資訊只用索引樹中的資訊檢索出的，這比掃描整個表要快。
如果是where used，就是使用上了where限制。
如果是impossible where 表示用不著where，一般就是沒查出來啥。
如果此資訊顯示Using filesort或者Using temporary的話會很吃力，WHERE和ORDERBY的索引經常無法兼顧，如果按照WHERE來確定索引，那麼在ORDER BY時，就必然會引起Usingfilesort，這就要看是先過濾再排序划算，還是先排序再過濾划算。

常見的一些名詞解釋
Using filesort
MySQL需要額外的一次傳遞，以找出如何按排序次序檢索行。

Using index
從只使用索引樹中的資訊而不需要進一步搜尋讀取實際的行來檢索表中的列資訊。

Using temporary
為瞭解決查詢，MySQL需要建立一個暫存資料表來容納結果。

ref
對於每個來自於前面的表的行組合，所有有匹配索引值的行將從這張表中讀取

ALL
完全沒有索引的情況，效能非常地差勁。

index
與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引檔案通常比資料檔案小。

SIMPLE
簡單SELECT(不使用UNION或子查詢) 

 

explain mysql效能最佳化