mysql性能這塊，其實(shí)都是DBA做的事情，一問DBA的朋友就會(huì)很快拿出一大堆工具就讓你來使用，并且?guī)椭闩挪槌龊芏鄦栴}。并不是每個(gè)公司都有DBA的，很多公司都是開發(fā)來弄的，如果業(yè)務(wù)量特別大并且上來的時(shí)候，我們可以招聘一個(gè)DBA，一般運(yùn)維都會(huì)幫助來弄。

profile使用工具；

查看自己版本的是否開啟了Profile， profiling=on是開啟了這個(gè)profile功能，

mysql>?select?version(); +------------+ |?version()??| +------------+ |?5.6.35-log?| +------------+ 1?row?in?set?(0.00?sec)mysql>?show?variables?like?'%profil%'; +------------------------+-------+ |?Variable_name??????????|?Value?| +------------------------+-------+ |?have_profiling?????????|?YES???| |?profiling??????????????|?ON????| |?profiling_history_size?|?15????| +------------------------+-------+ 3?rows?in?set?(0.01?sec)

Help profile 查看一下，profile的幫助，但是看一下，官方在這個(gè)時(shí)候會(huì)讓你注意一下，profile會(huì)在5.7以后會(huì)廢棄了，不用了，所以先查看一下自己的版本，估計(jì)好多公司都沒用到5.7,還是老的版本。

mysql>?help?profile; Name:?'SHOW?PROFILE' Description: Syntax: SHOW?PROFILE?[type?[,?type]?...?][FOR?QUERY?n][LIMIT?row_count?[OFFSET?offset]]type:??ALL????????????????--顯示所有的開銷信息??|?BLOCK?IO???????????--顯示塊IO相關(guān)開銷??|?CONTEXT?SWITCHES???--上下文切換相關(guān)開銷??|?CPU????????????????--顯示CPU相關(guān)開銷信息??|?IPC????????????????--顯示發(fā)送和接收相關(guān)開銷信息??|?MEMORY?????????????--顯示內(nèi)存相關(guān)開銷信息??|?PAGE?FAULTS????????--顯示頁面錯(cuò)誤相關(guān)開銷信息??|?SOURCE?????????????--顯示和Source_function，Source_file，Source_line相關(guān)的開銷信息??|?SWAPS??????????????--顯示交換次數(shù)相關(guān)開銷的信息The?SHOW?PROFILE?and?SHOW?PROFILES?statements?display?profiling information?that?indicates?resource?usage?for?statements?executed during?the?course?of?the?current?session.*Note*:?These?statements?are?deprecated?as?of?MySQL?5.6.7?and?will?be removed?in?a?future?MySQL?release.?Use?the?Performance?Schema?instead; see http://dev.mysql.com/doc/refman/5.6/en/performance-schema-query-profili ng.html.Profiling?is?controlled?by?the?profiling?session?variable,?which?has?a開啟一下profiling功能；mysql>?set?profile=1; ERROR?1193?(HY000):?Unknown?system?variable?'profile' mysql>?set?profiling=1; Query?OK,?0?rows?affected,?1?warning?(0.00?sec)mysql>?show?variables?like?'%profil%'; +------------------------+-------+ |?Variable_name??????????|?Value?| +------------------------+-------+ |?have_profiling?????????|?YES???| |?profiling??????????????|?ON????| |?profiling_history_size?|?15????| +------------------------+-------+ 3?rows?in?set?(0.00?sec)

輸入sql語句查看一下，profiles是否產(chǎn)生了。

mysql>?SELECT?count(*)?from?table?where?publishtime?>='2017-01-01'?and?publishtime?<='2017-08-30'->?; +----------+ |?count(*)?| +----------+ |??1317564?| +----------+ 1?row?in?set?(2.46?sec)mysql>?show?profiles; +----------+------------+--------------------------------------------------------------------------------------------------------+ |?Query_ID?|?Duration???|?Query??????????????????????????????????????????????????????????????????????????????????????????????????| +----------+------------+--------------------------------------------------------------------------------------------------------+ |????????1?|?0.00109175?|?show?variables?like?'%profil%'?????????????????????????????????????????????????????????????????????????| |????????2?|?2.46288425?|?SELECT?count(*)?from?table?where?publishtime?>='2017-01-01'?and?publishtime?<='2017-08-30'?| +----------+------------+--------------------------------------------------------------------------------------------------------+ 2?rows?in?set,?1?warning?(0.00?sec)mysql>獲取指定的開銷： mysql>?show?profile?for?query?2; +----------------------+----------+ |?Status???????????????|?Duration?| +----------------------+----------+ |?starting?????????????|?0.000147?| |?checking?permissions?|?0.000023?| |?Opening?tables???????|?0.000047?| |?init?????????????????|?0.000081?| |?System?lock??????????|?0.000031?| |?optimizing???????????|?0.000034?| |?statistics???????????|?0.001650?| |?preparing????????????|?0.000046?| |?executing????????????|?0.000018?| |?Sending?data?????????|?2.460588?| |?end??????????????????|?0.000041?| |?query?end????????????|?0.000019?| |?closing?tables???????|?0.000022?| |?freeing?items????????|?0.000055?| |?cleaning?up??????????|?0.000085?| +----------------------+----------+ 15?rows?in?set,?1?warning?(0.00?sec)可以看到Sending?data?，這個(gè)還是比較耗時(shí)，這個(gè)是sending?data?不是發(fā)送數(shù)據(jù)的意思， 是收集和發(fā)送集合的數(shù)據(jù)。查看cpu的數(shù)據(jù)： mysql>?show?profile?cpu?for?query?2; +----------------------+----------+----------+------------+ |?Status???????????????|?Duration?|?CPU_user?|?CPU_system?| +----------------------+----------+----------+------------+ |?starting?????????????|?0.000147?|?0.000000?|???0.000000?| |?checking?permissions?|?0.000023?|?0.000000?|???0.000000?| |?Opening?tables???????|?0.000047?|?0.000000?|???0.000000?| |?init?????????????????|?0.000081?|?0.000000?|???0.000000?| |?System?lock??????????|?0.000031?|?0.000000?|???0.000000?| |?optimizing???????????|?0.000034?|?0.000000?|???0.000000?| |?statistics???????????|?0.001650?|?0.001000?|???0.001000?| |?preparing????????????|?0.000046?|?0.000000?|???0.000000?| |?executing????????????|?0.000018?|?0.000000?|???0.000000?| |?Sending?data?????????|?2.460588?|?2.464625?|???0.025996?| |?end??????????????????|?0.000041?|?0.000000?|???0.000000?| |?query?end????????????|?0.000019?|?0.000000?|???0.000000?| |?closing?tables???????|?0.000022?|?0.000000?|???0.000000?| |?freeing?items????????|?0.000055?|?0.000000?|???0.000000?| |?cleaning?up??????????|?0.000085?|?0.000000?|???0.000000?| +----------------------+----------+----------+------------+ 15?rows?in?set,?1?warning?(0.00?sec)也是這個(gè)操作，type:??ALL????????????????--顯示所有的開銷信息??|?BLOCK?IO???????????--顯示塊IO相關(guān)開銷??|?CONTEXT?SWITCHES???--上下文切換相關(guān)開銷??|?CPU????????????????--顯示CPU相關(guān)開銷信息??|?IPC????????????????--顯示發(fā)送和接收相關(guān)開銷信息??|?MEMORY?????????????--顯示內(nèi)存相關(guān)開銷信息??|?PAGE?FAULTS????????--顯示頁面錯(cuò)誤相關(guān)開銷信息??|?SOURCE?????????????--顯示和Source_function，Source_file，Source_line相關(guān)的開銷信息??|?SWAPS??????????????--顯示交換次數(shù)相關(guān)開銷的信息這里邊的可以任意特?fù)Q就可以看到對(duì)應(yīng)的參數(shù)的開銷，自己試試看。

轉(zhuǎn)載于:https://blog.51cto.com/shangdc/1961085

總結(jié)

以上是生活随笔為你收集整理的mysql5.6使用profile工具分析sql的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。