从零开始的Linux运维屌丝之路,资源免费分享平台   运维人员首选:简单、易用、高效、安全、稳定、社区活跃的开源软件
  • 首页
  • MYSQL
  • 16 - show profiles SQL语句深入分析(了解)

16 - show profiles SQL语句深入分析(了解)

发布:蔺要红08-27分类: MYSQL


show profile是由Jeremy Cole捐献给MySQL社区版本的,默认的是关闭的,但是会话级别可以开启这个功能
这个命令只是在本会话内起作用,无法分析本会话外的语句

 
SET profiling = 1;      # 启用功能/临时生效
SELECT @@profiling;     # 查询是否生效 1 为生效

# 当执行SQL语句后查看效果

mysql> show profiles;
+----------+------------+-------------------------------+
| Query_ID | Duration   | Query                         |
+----------+------------+-------------------------------+
|        1 | 0.00018850 | SELECT @@profiling            |
|        2 | 0.00007525 | select * from blog            |
|        3 | 0.00034300 | select * from linyaohong.blog |
+----------+------------+-------------------------------+
3 rows in set (0.00 sec)

使用命令查看MYSQL执行过程
mysql> show profile for query 3;  
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000020 |
| Waiting for query cache lock   | 0.000001 |
| checking query cache for query | 0.000033 |
| checking permissions           | 0.000005 |
| Opening tables                 | 0.000044 |
| System lock                    | 0.000008 |
| Waiting for query cache lock   | 0.000011 |
| init                           | 0.000027 |
| optimizing                     | 0.000005 |
| statistics                     | 0.000008 |
| preparing                      | 0.000006 |
| executing                      | 0.000002 |
| Sending data                   | 0.000139 |
| end                            | 0.000007 |
| query end                      | 0.000006 |
| closing tables                 | 0.000006 |
| freeing items                  | 0.000005 |
| Waiting for query cache lock   | 0.000001 |
| freeing items                  | 0.000007 |
| Waiting for query cache lock   | 0.000001 |
| freeing items                  | 0.000001 |
| storing result in query cache  | 0.000001 |
| logging slow query             | 0.000001 |
| cleaning up                    | 0.000001 |
+--------------------------------+----------+
24 rows in set (0.01 sec)
-- 查询这条语句占用ip cpu 内存 等等的使用情况
show profile cpu,block io,memory,swaps,context switches,source for query 2\G
show profile block io,cpu for query 2;
mysql> show profile source,cpu  for query 10; # 详细查看CPU等占用的资源
+--------------------------------+----------+----------+------------+-----------------------+---------------+-------------+
| Status                         | Duration | CPU_user | CPU_system | Source_function       | Source_file   | Source_line |
+--------------------------------+----------+----------+------------+-----------------------+---------------+-------------+
| starting                       | 0.000027 | 0.000000 |   0.000000 | NULL                  | NULL          |        NULL |
| Waiting for query cache lock   | 0.000005 | 0.000000 |   0.000000 | try_lock              | sql_cache.cc  |         458 |
| checking query cache for query | 0.000097 | 0.000000 |   0.000000 | send_result_to_client | sql_cache.cc  |        1568 |
| checking permissions           | 0.000015 | 0.000000 |   0.000000 | check_access          | sql_parse.cc  |        4837 |
| Opening tables                 | 0.000027 | 0.000000 |   0.000000 | open_tables           | sql_base.cc   |        4896 |
| System lock                    | 0.000016 | 0.000000 |   0.000000 | mysql_lock_tables     | lock.cc       |         299 |
| Waiting for query cache lock   | 0.000028 | 0.000000 |   0.000000 | try_lock              | sql_cache.cc  |         458 |
| init                           | 0.000017 | 0.000000 |   0.000000 | mysql_select          | sql_select.cc |        2594 |
| optimizing                     | 0.000007 | 0.000000 |   0.000000 | optimize              | sql_select.cc |         865 |
| statistics                     | 0.000013 | 0.000000 |   0.000000 | optimize              | sql_select.cc |        1071 |
| preparing                      | 0.000012 | 0.000000 |   0.000000 | optimize              | sql_select.cc |        1093 |
| executing                      | 0.000008 | 0.000000 |   0.000000 | exec                  | sql_select.cc |        1851 |
| Sending data                   | 0.036263 | 0.201900 |   0.000000 | exec                  | sql_select.cc |        2395 |
| end                            | 0.000013 | 0.000000 |   0.000000 | mysql_select          | sql_select.cc |        2630 |
| query end                      | 0.000013 | 0.000000 |   0.000000 | mysql_execute_command | sql_parse.cc  |        4516 |
| closing tables                 | 0.000008 | 0.000000 |   0.000000 | mysql_execute_command | sql_parse.cc  |        4568 |
| freeing items                  | 0.000006 | 0.000000 |   0.000000 | mysql_parse           | sql_parse.cc  |        5844 |
| Waiting for query cache lock   | 0.000003 | 0.000000 |   0.000000 | try_lock              | sql_cache.cc  |         458 |
| freeing items                  | 0.000016 | 0.000000 |   0.000000 | NULL                  | NULL          |        NULL |
| Waiting for query cache lock   | 0.000002 | 0.000000 |   0.000000 | try_lock              | sql_cache.cc  |         458 |
| freeing items                  | 0.000002 | 0.000000 |   0.000000 | NULL                  | NULL          |        NULL |
| storing result in query cache  | 0.000002 | 0.000000 |   0.000000 | end_of_result         | sql_cache.cc  |        1024 |
| logging slow query             | 0.000002 | 0.000000 |   0.000000 | log_slow_statement    | sql_parse.cc  |        1516 |
| cleaning up                    | 0.000002 | 0.000000 |   0.000000 | dispatch_command      | sql_parse.cc  |        1472 |
+--------------------------------+----------+----------+------------+-----------------------+---------------+-------------+
24 rows in set (0.00 sec)



 
温馨提示如有转载或引用以上内容之必要,敬请将本文链接作为出处标注,如有侵权我会在24小时之内删除!

欢迎使用手机扫描访问本站