MySQL中explain的使用以及性能分析

mysql 提供了一個(gè) explain 命令, 它可以對 select 語句進(jìn)行分析, 并輸出 select 執(zhí)行的詳細(xì)信息, 以供開發(fā)人員針對性優(yōu)化.explain 命令用法十分簡單,mysql性能分析及explain用法的知識是本文我們主要要介紹的內(nèi)容,接下來就讓我們通過一些實(shí)際的例子來介紹這一過程!

1.使用explain語句去查看分析結(jié)果

explain?select?*?from?test1?where?id=1;

會(huì)出現(xiàn):

id??selecttype??table??type?possible_keys??key?key_len??ref?rows??extra各列。

其中,

type=const表示通過索引一次就找到了;

key=primary的話,表示使用了主鍵;

type=all,表示為全表掃描;

key=null表示沒用到索引。type=ref,因?yàn)檫@時(shí)認(rèn)為是多個(gè)匹配行,在聯(lián)合查詢中,一般為REF。

2.MYSQL中的組合索引

假設(shè)表有id,key1,key2,key3,把三者形成一個(gè)組合索引,則

如:

where?key1=....?????  where?key1=1?and?key2=2?????  where?key1=3?and?key3=3?and?key2=2

根據(jù)最左原則,這些都是可以使用索引的,如from test where key1=1 order by key3,用explain分析的話,只用到了normal_key索引,但只對聯(lián)合查詢起作用,而后面的order by需要排序。

3.使用慢查詢分析

在my.ini中:

long_query_time=1  log-slow-queries=d:mysql5logsmysqlslow.log

把超過1秒的記錄在慢查詢?nèi)罩局?/p>

可以用mysqlsla來分析之。也可以在mysqlreport中,有如

DMS分別分析了select ,update,insert,delete,replace等所占的百份比

4.MYISAM和INNODB的鎖定

myisam中,注意是表鎖來的,比如在多個(gè)聯(lián)合查詢后,再SELECT時(shí),會(huì)發(fā)現(xiàn)SELECT操作被鎖定了,必須等所有UPDATE操作完畢后,再能SELECT

innodb的話則不同了,用的是行鎖,不存在上面問題。

5.MYSQL的事務(wù)配置項(xiàng)

innodb_flush_log_at_trx_commit=1

表示事務(wù)提交時(shí)立即把事務(wù)聯(lián)合查詢磁盤,同時(shí)數(shù)據(jù)和索引也更新。

innodb_flush_log_at_trx_commit=0

事務(wù)提交時(shí),不立即把事務(wù)日志寫入磁盤,每隔1秒寫一次

innodb_flush_log_at_trx_commit=2

事務(wù)提交時(shí),立即寫入磁盤文件(這里只是寫入到內(nèi)核緩沖區(qū),但不立即刷新到磁盤,而是每隔1秒刷新到盤,同時(shí)更新數(shù)據(jù)和索引

explain用法

EXPLAIN?tbl_name或:EXPLAIN?[EXTENDED]?SELECT?select_options

前者可以得出一個(gè)表的字段結(jié)構(gòu)等等,后者主要是給出相關(guān)的一些索引信息,而今天要講述的重點(diǎn)是后者。

舉例

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)

各個(gè)聯(lián)合查詢的含義

id

select查詢的序列號

select_type

select查詢的類型,主要是區(qū)別普通查詢和聯(lián)合查詢、子查詢之類的復(fù)雜查詢。

table

輸出的行所聯(lián)合查詢的表。

type

聯(lián)合查詢所使用的類型。

type顯示的是訪問類型,是較為重要的一個(gè)指標(biāo),結(jié)果值從好到壞依次是:

system?>?const?>?eq_ref?>?ref?>?fulltext?>?ref_or_null?>?index_merge?>?unique_subquery?>?index_subquery?>?range?>?index?>?ALL

一般來說,得保證查詢至少達(dá)到range級別,最好能達(dá)到ref。

possible_keys

指出MySQL能使用哪個(gè)索引在該表中找到行。如果是空的,沒有相關(guān)的索引。這時(shí)要提高性能,可通過檢驗(yàn)WHERE子句,看是否引用某些字段,或者檢查字段不是適合索引。

key

顯示MySQL實(shí)際決定使用的鍵。如果沒有索引被選擇,鍵是NULL。

key_len

顯示MySQL決定使用的鍵長度。如果鍵是NULL,長度就是NULL。文檔提示特別注意這個(gè)值可以得出一個(gè)多重主鍵里mysql實(shí)際使用了哪一部分。

ref

顯示哪個(gè)字段或常數(shù)與key一起被使用。

rows

這個(gè)數(shù)表示mysql要遍歷多少數(shù)據(jù)才能找到,在innodb上是不準(zhǔn)確的。

Extra

如果是Only index,這意味著信息只用索引樹中的信息檢索出的,這比掃描整個(gè)表要快。

如果是where used,就是使用上了where限制。

如果是impossible where 表示用不著where,一般就是沒查出來啥。

如果此信息顯示Using filesort或者Using temporary的話會(huì)很吃力,WHERE和ORDER BY的索引經(jīng)常無法兼顧,如果按照WHERE來確定索引,那么在ORDER BY時(shí),就必然會(huì)引起Using filesort,這就要看是先過濾再排序劃算,還是先排序再過濾劃算。

總結(jié):

關(guān)于MySQL性能分析及explain用法的知識就介紹到這里了,相信小伙伴對此也是有了一定的了解!

相關(guān)推薦:

聯(lián)合查詢

聯(lián)合查詢

聯(lián)合查詢

聯(lián)合查詢

? 版權(quán)聲明
THE END
喜歡就支持一下吧
點(diǎn)贊12 分享