mysql trace工具_mysql中的trace工具

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可以用此方法来帮助找原因了！