mysql显示SQL语句执行时间

查看MySQL語法詳細執行時間與CPU/記憶體使用量:MySQLQueryProfiler

MySQL的SQL語法調整主要都是使用EXPLAIN,但是這個並沒辦法知道詳細的Ram(Memory)/CPU等使用量.

於MySQL5.0.37以上開始支援MySQLQueryProfiler,可以查詢到此SQL會執行多少時間,並看出CPU/Memory使用量,執行過程中Systemlock,Tablelock花多少時間等等.

MySQLQueryProfile詳細介紹可見:UsingtheNewMySQLQueryProfiler(2007.04.05發表)

效能分析主要分下述三種(轉載自上篇):

Bottleneckanalysis-focusesonansweringthequestions:Whatismydatabaseserverwaitingon;whatisauserconnectionwaitingon;whatisapieceofSQLcodewaitingon?

Workloadanalysis-examinestheserverandwhoisloggedontodeterminetheresourceusageandactivityofeach.

Ratio-basedanalysis-utilizesanumberofrule-of-thumbratiostogaugeperformanceofadatabase,userconnection,orpieceofcode.

MySQLQueryProfile使用方法

啟動

mysql>setprofiling=1;#此命令於MySQL會於information_schema的database建立一個PROFILING的table來紀錄.

SQLprofilesshow

mysql>showprofiles;#從啟動之後所有語法及使用時間,含錯誤語法都會紀錄.

ex:(root@localhost)[test]>showprofiles;#注意Query_ID,下面執行時間統計等,都是依Query_ID在紀錄

+----------+------------+---------------------------+

|Query_ID|Duration|Query|

+----------+------------+---------------------------+

|1|0.00090400|showprofileforquery1|

|2|0.00008700|select*fromusers|

|3|0.00183800|showtables|

|4|0.00027600|mysql>showprofiles|

+----------+------------+---------------------------+

查詢所有花費時間加總

mysql>selectsum(duration)frominformation_schema.profilingwherequery_id=1;#QueryID=1

+---------------+

|sum(duration)|

+---------------+

|0.000447|

+---------------+

查詢各執行階段花費多少時間

mysql>showprofileforquery1;#QueryID=1

+--------------------+------------+

|Status|Duration|

+--------------------+------------+

|(initialization)|0.00006300|

|Openingtables|0.00001400|

|Systemlock|0.00000600|

|Tablelock|0.00001000|

|init|0.00002200|

|optimizing|0.00001100|

|statistics|0.00009300|

|preparing|0.00001700|

|executing|0.00000700|

|Sendingdata|0.00016800|

|end|0.00000700|

|queryend|0.00000500|

|freeingitems|0.00001200|

|closingtables|0.00000800|

|loggingslowquery|0.00000400|

+--------------------+------------+

查詢各執行階段花費的各種資源列表

mysql>showprofilecpuforquery1;#QueryID=1

+--------------------------------+----------+----------+------------+

|Status|Duration|CPU_user|CPU_system|

+--------------------------------+----------+----------+------------+

|(initialization)|0.000007|0|0|

|checkingquerycacheforquery|0.000071|0|0|

|Openingtables|0.000024|0|0|

|Systemlock|0.000014|0|0|

|Tablelock|0.000055|0.001|0|

|init|0.000036|0|0|

|optimizing|0.000013|0|0|

|statistics|0.000021|0|0|

|preparing|0.00002|0|0|

|executing|0.00001|0|0|

|Sendingdata|0.015072|0.011998|0|

|end|0.000021|0|0|

|queryend|0.000011|0|0|

|storingresultinquerycache|0.00001|0|0|

|freeingitems|0.000018|0|0|

|closingtables|0.000019|0|0|

|loggingslowquery|0.000009|0|0|

+--------------------------------+----------+----------+------------+

mysql>showprofileIPCforquery1;

+--------------------------------+----------+---------------+-------------------+

|Status|Duration|Messages_sent|Messages_received|

+--------------------------------+----------+---------------+-------------------+

|(initialization)|0.000007|0|0|

|checkingquerycacheforquery|0.000071|0|0|

|Openingtables|0.000024|0|0|

|Systemlock|0.000014|0|0|

|Tablelock|0.000055|0|0|

|init|0.000036|0|0|

|optimizing|0.000013|0|0|

|statistics|0.000021|0|0|

|preparing|0.00002|0|0|

|executing|0.00001|0|0|

|Sendingdata|0.015072|0|0|

|end|0.000021|0|0|

|queryend|0.000011|0|0|

|storingresultinquerycache|0.00001|0|0|

|freeingitems|0.000018|0|0|

|closingtables|0.000019|0|0|

|loggingslowquery|0.000009|0|0|

+--------------------------------+----------+---------------+-------------------+

其它屬性列表

ALL-displaysallinformation

BLOCKIO-displayscountsforblockinputandoutputoperations

CONTEXTSWITCHES-displayscountsforvoluntaryandinvoluntarycontextswitches

IPC-displayscountsformessagessentandreceived

MEMORY-isnotcurrentlyimplemented

PAGEFAULTS-displayscountsformajorandminorpagefaults

SOURCE-displaysthenamesoffunctionsfromthesourcecode,togetherwiththenameandlinenumberofthefileinwhichthefunctionoccurs

SWAPS-displaysswapcounts

設定Profiling存的Size

mysql>showvariableswherevariable_name='profiling_history_size';#預設是15筆

關閉

mysql>setprofiling=0;