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

mysql>set profiling=1;

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使用方法

开启:

mysql>set profiling=1;


查看SQL Profile

mysql>show profiles;
mysql>show profiles;
+----------+------------+--------------------------------------------+
|Query_ID|Duration  |Query                                    |
+----------+------------+--------------------------------------------+
|      11|0.00006600|show global variables buffer              |
|      12|0.00059900|show global variables like'%buffer%'    |
|      15|0.00052900|show global variables like'%buffer%'    |
|      16|0.00048700|show global variables like'%cache%'      |
|      17|0.00032600|select versioin()                        |
|      18|0.00010300|select version()                          |
+----------+------------+--------------------------------------------+

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

mysql>showprofileforquery19;# Query ID=19
+--------------------------------+----------+
|Status                        |Duration|
+--------------------------------+----------+
|starting                      |0.000019|
|checking query cache for query|0.000009|
|checking privileges on cached  |0.000005|
|sending cached result to client|0.000011|
|logging slow query            |0.000002|
|cleaning up                    |0.000002|
+--------------------------------+----------+
6 rows in set(0.01sec)

mysql>SHOW PROFILE CPU FOR QUERY 2;
+----------------------+----------+----------+------------+
|Status              |Duration|CPU_user|CPU_system|
+----------------------+----------+----------+------------+
|checking permissions|0.000040|0.000038|  0.000002|
|creating table      |0.000056|0.000028|  0.000028|
|After create        |0.011363|0.000217|  0.001571|
|query end            |0.000375|0.000013|  0.000028|
|freeing items        |0.000089|0.000010|  0.000014|
|logging slow query  |0.000019|0.000009|  0.000010|
|cleaning up          |0.000005|0.000003|  0.000002|
+----------------------+----------+----------+------------+
7 rows in set(0.00sec)

Syntax:
SHOWPROFILE[type[,type]...]
    [FORQUERYn]
    [LIMITrow_count[OFFSEToffset]]
 
type:
    ALL
  |BLOCKIO
  |CONTEXTSWITCHES
  |CPU
  |IPC
  |MEMORY
  |PAGEFAULTS
  |SOURCE
  |SWAPS
 
 ALL displays all information
 
 BLOCKIO displays counts for block input and output operations
 
CONTEXTSWITCHES displays counts for voluntary and involuntary context switches
 
CPU displays user and system CPU usage times
 
IPC displays counts for messages sent and received
 
MEMORY is not currently implemented
 
PAGEFAULTSdisplayscountsformajorandminorpagefaults
 
SOURCE displays the names off unctions from the source code,together
  with the name and line number of the file in which the function
  occurs
 
SWAPS displays swap counts

设置profileing存储记录的size

mysql>showvariableslike'%profiling%';
+------------------------+-------+
|Variable_name          |Value|
+------------------------+-------+
|have_profiling        |YES  |
|profiling              |OFF  |
|profiling_history_size|15    |
+------------------------+-------+
3rowsinset(0.00sec)
 
mysql>

关闭profile

mysql>set profiling=0;

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

information is lost.


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值