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)