在数据库中查看一个SQL执行一次耗时多少

发布网友发布时间：2022-04-08 06:37

共2个回答

懂视网时间：2022-04-08 10:59

profiling=1;

执行要分析的SQL语句

mysql> select count(1) from wechat_employee,Employee;

显示profiles表

mysql> show profiles;

查询结果：

+----------+------------+-----------------------------------------------+
| Query_ID | Duration | Query      |
+----------+------------+-----------------------------------------------+
| 1 | 0.00072975 | select count(1) from wechat_employee,Employee |
| 2 | 0.00028450 | set profiling=1    |
| 3 | 0.00324875 | select count(1) from wechat_employee,Employee |
+----------+------------+-----------------------------------------------+

查询详细信息：

show profile cpu,block io for query 3;

结果：

+--------------------------------+----------+----------+------------+--------------+---------------+
| Status    | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting   | 0.000041 | 0.000000 | 0.000000 |  0 |  0 |
| Waiting for query cache lock | 0.000073 | 0.000000 | 0.000000 |  0 |  0 |
| checking query cache for query | 0.000025 | 0.000000 | 0.000000 |  0 |  0 |
| checking privileges on cached | 0.000023 | 0.000000 | 0.000000 |  0 |  0 |
| checking permissions  | 0.000018 | 0.000000 | 0.000000 |  0 |  0 |
| checking permissions  | 0.000012 | 0.000000 | 0.000000 |  0 |  0 |
| sending cached result to clien | 0.003009 | 0.000000 | 0.004000 |  0 |  0 |
| logging slow query  | 0.000032 | 0.000000 | 0.000000 |  0 |  0 |
| cleaning up   | 0.000018 | 0.000000 | 0.000000 |  0 |  0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
9 rows in set (0.00 sec)

使用Profiles分析SQL语句执行时间和消耗资源

标签：mysql profiling

热心网友时间：2022-04-08 08:07

下面这种是SQL Server中比较简单的查询SQL语句执行时间方法源码天空
，通过查询前的时间和查询后的时间差来计算的：
declare @begin_date datetime
declare @end_date datetime
select @begin_date = getdate()
select @end_date = getdate()
select datediff(ms,@begin_date,@end_date) as '用时/毫秒'
2：下面这种方法比较全面，将执行每个语句时采取的步骤作为行集返回，通过层次结构树的形式展示出来
set statistics profile on
set statistics io on
set statistics time ongo
<这里写上你的语句...go
set statistics profile off