版权声明:原创作品,允许转载,转载时请务必以超链接形式标明文章
原始出处 、作者信息和本声明。否则将追究法律责任。 http://zhaizhenxing.blog.51cto.com/643480/134592 |
mysql从5.0.37后开始支持show profiles语法,用来对sql语句进行trace跟踪,下面是mysql help中对show profiles的解释:
mysql> ? show profiles;
Name: ‘SHOW PROFILES’ Description: Syntax: SHOW PROFILES
SHOW PROFILE [type [, type] … ]
[FOR QUERY n] [LIMIT n [OFFSET n]]
type:
ALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE | SWAPS
The SHOW PROFILES and SHOW PROFILE statements display profiling
information that indicates resource usage for statements executed during the course of the current session.
Profiling is controlled by the profiling session variable, which has a
default value of 0 (OFF). Profiling is enabled by setting profiling to 1 or ON:
mysql> SET profiling = 1;
SHOW PROFILES displays a list of the most recent statements sent to the
master. The size of the list is controlled by the profiling_history_size session variable, which has a default value of 15. The maximum value is 100. Setting the value to 0 has the practical effect of disabling profiling.
All statements are profiled except SHOW PROFILES and SHOW PROFILE, so
you will find neither of those statements in the profile list. Malformed statements are profiled. For example, SHOW PROFILING is an illegal statement, and a syntax error occurs if you try to execute it, but it will show up in the profiling list.
SHOW PROFILE displays detailed information about a single statement.
Without the FOR QUERY n clause, the output pertains to the most recently executed statement. If FOR QUERY n is included, SHOW PROFILE displays information for statement n. The values of n correspond to the Query_ID values displayed by SHOW PROFILES.
The LIMIT n clause may be given to limit the output to n rows. If LIMIT
is given, OFFSET n may be added to begin the output n rows into the full set of rows.
By default, SHOW PROFILE displays Status and Duration columns. The
Status values are like the State values displayed by SHOW PROCESSLIST, althought there might be some minor differences in interpretion for the two statements for some status values (see [url]http://dev.mysql.com/doc/refman/5.0/en/thread-information.html[/url]).
Optional type values may be specified to display specific additional
types of information:
o ALL displays all information
o BLOCK IO displays counts for block input and output operations
o CONTEXT SWITCHES displays counts for voluntary and involuntary
context switches
o CPU displays user and system CPU usage times
o IPC displays counts for messages sent and received
o MEMORY is not currently implemented
o PAGE FAULTS displays counts for major and minor page faults
o SOURCE displays the names of functions from the source code, together
with the name and line number of the file in which the function occurs
o SWAPS displays swap counts
Profiling is enabled per session. When a session ends, its profiling
information is lost.
Examples:
mysql> SELECT @@profiling; +————-+ | @@profiling | +————-+ | 0 | +————-+ 1 row in set (0.00 sec)
mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE T1 (id INT);
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW PROFILES;
+———-+———-+————————–+ | Query_ID | Duration | Query | +———-+———-+————————–+ | 0 | 0.000088 | SET PROFILING = 1 | | 1 | 0.000136 | DROP TABLE IF EXISTS t1 | | 2 | 0.011947 | CREATE TABLE t1 (id INT) | +———-+———-+————————–+ 3 rows in set (0.00 sec)
mysql> SHOW PROFILE;
+———————-+———-+ | Status | Duration | +———————-+———-+ | checking permissions | 0.000040 | | creating table | 0.000056 | | After create | 0.011363 | | query end | 0.000375 | | freeing items | 0.000089 | | logging slow query | 0.000019 | | cleaning up | 0.000005 | +———————-+———-+ 7 rows in set (0.00 sec)
mysql> SHOW PROFILE FOR QUERY 1;
+——————–+———-+ | Status | Duration | +——————–+———-+ | query end | 0.000107 | | freeing items | 0.000008 | | logging slow query | 0.000015 | | cleaning up | 0.000006 | +——————–+———-+ 4 rows in set (0.00 sec)
mysql> SHOW PROFILE CPU FOR QUERY 2;
+———————-+———-+———-+————+ | Status | Duration | CPU_user | CPU_system | +———————-+———-+———-+————+ | checking permissions | 0.000040 | 0.000038 | 0.000002 | | creating table | 0.000056 | 0.000028 | 0.000028 | | After create | 0.011363 | 0.000217 | 0.001571 | | query end | 0.000375 | 0.000013 | 0.000028 | | freeing items | 0.000089 | 0.000010 | 0.000014 | | logging slow query | 0.000019 | 0.000009 | 0.000010 | | cleaning up | 0.000005 | 0.000003 | 0.000002 | +———————-+———-+———-+————+ 7 rows in set (0.00 sec)
例子中的使用方法已经写的很清楚了,以后有性能问题的sql可以用此方法来帮助找原因了!
|
转载于:https://blog.51cto.com/tianya23/258807
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/110823.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...