mysql显示SQL语句执行所消耗的时间

来源：互联网发布：java编写计算器代码编辑：程序博客网时间：2024/05/16 14:45

MySQL中查看SQL的执行计划主要是使用explain来查看，但这个没有办法知道内存及CPU等使用情况，自MySQL 5.0.37以后，mysql提供MySQL Query Profile可以查询到SQL执行所花费的时间，例如SQL执行过程中的SYSTEM LOCK,TABLE LOCK等所花费的时间。

MySQL Query Profile主要功能：

Bottleneck Analysis
Workload Analysis
Ratio-based analysis

MySQL Query Profile使用方法

开启：

1
mysql>setprofiling=1;

查看SQL Profile

1
2
3
4
5
6
7
8
9
10
11
12
mysql>showprofiles;
mysql>showprofiles;
+----------+------------+--------------------------------------------+
|Query_ID|Duration  |Query                                    |
+----------+------------+--------------------------------------------+
|      11|0.00006600|showglobalvariablesbuffer              |
|      12|0.00059900|showglobalvariableslike'%buffer%'    |
|      15|0.00052900|showglobalvariableslike'%buffer%'    |
|      16|0.00048700|showglobalvariableslike'%cache%'      |
|      17|0.00032600|selectversioin()                        |
|      18|0.00010300|selectversion()                          |
+----------+------------+--------------------------------------------+

查询各SQL执行所花费的时间

1
2
3
4
5
6
7
8
9
10
11
12
mysql>showprofileforquery19;# Query ID=19
+--------------------------------+----------+
|Status                        |Duration|
+--------------------------------+----------+
|starting                      |0.000019|
|checkingquerycacheforquery|0.000009|
|checkingprivilegesoncached  |0.000005|
|sendingcachedresulttoclien|0.000011|
|loggingslowquery            |0.000002|
|cleaningup                    |0.000002|
+--------------------------------+----------+
6rowsinset(0.01sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql>SHOWPROFILECPUFORQUERY2;
+----------------------+----------+----------+------------+
|Status              |Duration|CPU_user|CPU_system|
+----------------------+----------+----------+------------+
|checkingpermissions|0.000040|0.000038|  0.000002|
|creatingtable      |0.000056|0.000028|  0.000028|
|Aftercreate        |0.011363|0.000217|  0.001571|
|queryend            |0.000375|0.000013|  0.000028|
|freeingitems        |0.000089|0.000010|  0.000014|
|loggingslowquery  |0.000019|0.000009|  0.000010|
|cleaningup          |0.000005|0.000003|  0.000002|
+----------------------+----------+----------+------------+
7rowsinset(0.00sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Syntax:
SHOWPROFILE[type[,type]...]
    [FORQUERYn]
    [LIMITrow_count[OFFSEToffset]]
 
type:
    ALL
  |BLOCKIO
  |CONTEXTSWITCHES
  |CPU
  |IPC
  |MEMORY
  |PAGEFAULTS
  |SOURCE
  |SWAPS
 
oALLdisplaysallinformation
 
oBLOCKIOdisplayscountsforblockinputandoutputoperations
 
oCONTEXTSWITCHESdisplayscountsforvoluntaryandinvoluntary
  contextswitches
 
oCPUdisplaysuserandsystemCPUusagetimes
 
oIPCdisplayscountsformessagessentandreceived
 
oMEMORYisnotcurrentlyimplemented
 
oPAGEFAULTSdisplayscountsformajorandminorpagefaults
 
oSOURCEdisplaysthenamesoffunctionsfromthesourcecode,together
  withthenameandlinenumberofthefileinwhichthefunction
  occurs
 
oSWAPSdisplaysswapcounts

设置profileing存储记录的size

1
2
3
4
5
6
7
8
9
10
11
mysql>showvariableslike'%profiling%';
+------------------------+-------+
|Variable_name          |Value|
+------------------------+-------+
|have_profiling        |YES  |
|profiling              |OFF  |
|profiling_history_size|15    |
+------------------------+-------+
3rowsinset(0.00sec)
 
mysql>

关闭profile

1
mysql>setprofiling=0;

Profiling is enabled per session. When a session ends, its profiling
information is lost.