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

最新推荐文章于 2022-03-17 10:36:30 发布

swffsdgasdg

最新推荐文章于 2022-03-17 10:36:30 发布

阅读量1.1k

点赞数 1

分类专栏：数据库文章标签： mysql profiling

本文链接：https://blog.csdn.net/u013011841/article/details/47336581

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

打开profiling,默认是没开启的.

mysql> set 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)