mysql中使用profile对sql语句进行性能分析

Mysql 小智 464℃ 0评论

在mysql的使用过程中,很多时候就要牵扯到mysql的性能调优,这时候profile就会派上用场了,profile可以查询到 SQL 语句的执行情况,以及执行过程中的每个步骤所消耗的时间, 并能查看 CPU和内存的使用情况,有了这些内容我们就可以以此为基础,找到一个sql的问题瓶颈。
1.查看profile是否开启

mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |  --开启SQL语句剖析功
| profiling_history_size | 15    |  --设置保留profiling的数目,缺省为15,
+------------------------+-------+  范围为0至100,为0时将禁用profiling
3 rows in set (0.00 sec)

2.基于会话级别开启

mysql> set profiling = 1;
Query OK, 0 rows affected (0.00 sec)
关闭则用set profiling = off;
mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | ON    |
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)

3.查询

mysql> SELECT
    -> id,appid,phone
    -> FROM cbh_account ct
    -> limit 0,10;
+-------+--------------------+-------------+
| id    | appid              | phone       |
+-------+--------------------+-------------+
| 10599 | 800120141120133821 | 15111111111 |
| 10600 | 800120141120133821 | 15111111111 |
| 10601 | 800120141120142804 | 15111111111 |
| 10602 | 800120141120142804 | 15111111111 |
| 10603 | 800120141120142804 | 15111111111 |
| 10604 | 800120141120142804 | 15111111111 |
| 10605 | 800120141120142937 | 15111111111 |
| 10606 | 800120141120142937 | 15111111111 |
| 10607 | 800120141120142937 | 15111111111 |
| 10608 | 800120141120142937 | 15111111111 |
+-------+--------------------+-------------+
10 rows in set (0.00 sec)

4.显示缓存的profile

mysql> show profiles;
5.具体消耗,进行详细的列出
mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.003575 |
| checking permissions | 0.000009 |
| checking permissions | 0.000019 |
| Opening tables       | 0.025834 |
| System lock          | 0.000310 |
| init                 | 0.000411 |
| optimizing           | 0.000054 |
| statistics           | 0.000886 |
| preparing            | 0.000109 |
| executing            | 0.000012 |
| Sorting result       | 0.000003 |
| Sending data         | 0.005335 |
| end                  | 0.000014 |
| query end            | 0.000008 |
| closing tables       | 0.000020 |
| freeing items        | 0.000126 |
| logging slow query   | 0.000001 |
| cleaning up          | 0.000002 |
+----------------------+----------+
18 rows in set (0.00 sec)

6.查看cpu的消耗情况

mysql> show profile cpu for query 2;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.003575 | 0.000000 |   0.000000 |
| checking permissions | 0.000009 | 0.000000 |   0.000000 |
| checking permissions | 0.000019 | 0.000000 |   0.000000 |
| Opening tables       | 0.025834 | 0.000000 |   0.000000 |
| System lock          | 0.000310 | 0.000000 |   0.000000 |
| init                 | 0.000411 | 0.000000 |   0.000000 |
| optimizing           | 0.000054 | 0.000000 |   0.000000 |
| statistics           | 0.000886 | 0.000000 |   0.000000 |
| preparing            | 0.000109 | 0.000000 |   0.000000 |
| executing            | 0.000012 | 0.000000 |   0.000000 |
| Sorting result       | 0.000003 | 0.000000 |   0.000000 |
| Sending data         | 0.005335 | 0.000000 |   0.000000 |
| end                  | 0.000014 | 0.000000 |   0.000000 |
| query end            | 0.000008 | 0.000000 |   0.000000 |
| closing tables       | 0.000020 | 0.000000 |   0.000000 |
| freeing items        | 0.000126 | 0.000000 |   0.000000 |
| logging slow query   | 0.000001 | 0.000000 |   0.000000 |
| cleaning up          | 0.000002 | 0.000000 |   0.000000 |
+----------------------+----------+----------+------------+
18 rows in set (0.00 sec)

7.查看内存消耗

mysql> show profile memory for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.003575 |
| checking permissions | 0.000009 |
| checking permissions | 0.000019 |
| Opening tables       | 0.025834 |
| System lock          | 0.000310 |
| init                 | 0.000411 |
| optimizing           | 0.000054 |
| statistics           | 0.000886 |
| preparing            | 0.000109 |
| executing            | 0.000012 |
| Sorting result       | 0.000003 |
| Sending data         | 0.005335 |
| end                  | 0.000014 |
| query end            | 0.000008 |
| closing tables       | 0.000020 |
| freeing items        | 0.000126 |
| logging slow query   | 0.000001 |
| cleaning up          | 0.000002 |
+----------------------+----------+
18 rows in set (0.00 sec)

8.查看io及cpu的消耗

mysql> show profile block io,cpu for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.003575 | 0.000000 |   0.000000 |         NULL |          NULL |
| checking permissions | 0.000009 | 0.000000 |   0.000000 |         NULL |          NULL |
| checking permissions | 0.000019 | 0.000000 |   0.000000 |         NULL |          NULL |
| Opening tables       | 0.025834 | 0.000000 |   0.000000 |         NULL |          NULL |
| System lock          | 0.000310 | 0.000000 |   0.000000 |         NULL |          NULL |
| init                 | 0.000411 | 0.000000 |   0.000000 |         NULL |          NULL |
| optimizing           | 0.000054 | 0.000000 |   0.000000 |         NULL |          NULL |
| statistics           | 0.000886 | 0.000000 |   0.000000 |         NULL |          NULL |
| preparing            | 0.000109 | 0.000000 |   0.000000 |         NULL |          NULL |
| executing            | 0.000012 | 0.000000 |   0.000000 |         NULL |          NULL |
| Sorting result       | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| Sending data         | 0.005335 | 0.000000 |   0.000000 |         NULL |          NULL |
| end                  | 0.000014 | 0.000000 |   0.000000 |         NULL |          NULL |
| query end            | 0.000008 | 0.000000 |   0.000000 |         NULL |          NULL |
| closing tables       | 0.000020 | 0.000000 |   0.000000 |         NULL |          NULL |
| freeing items        | 0.000126 | 0.000000 |   0.000000 |         NULL |          NULL |
| logging slow query   | 0.000001 | 0.000000 |   0.000000 |         NULL |          NULL |
| cleaning up          | 0.000002 | 0.000000 |   0.000000 |         NULL |          NULL |
+----------------------+----------+----------+------------+--------------+---------------+
18 rows in set (0.00 sec)

profile是一个非常量化的指标,可以根据这些量化指标来比较各项资源的消耗,这样就有利于我们对该sql语句的整体把控!

本文固定链接:心知博客 » mysql中使用profile对sql语句进行性能分析
本站内容除特别标注外均为原创,欢迎转载,但请保留出处!

喜欢 (2)
发表我的评论
取消评论
表情

请填写您的昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址