這里還需要注意一點就是，需要安裝profile模塊才能實現。

1、不過版本要在5.0.37之后。(SHOW PROFILES and SHOW PROFILE were added in MySQL 5.0.37.)

SELECT @@profiling;

來查看是否已經啟用profile，如果profilng值為0，可以通過

SET profiling = 1;

來啟用。啟用profiling之后，我們執行一條查詢語句，比如：

select count(*) from roi_summary;

然后show profiles查看如下：

+----------------+------------+----------------------------------+

| Query_ID | Duration | Query |

+----------------+------------+----------------------------------+

| 1 | 0.00021500 | select @@profiling |

| 2 | 0.05522700 | select count(*) from roi_summary |

+----------------+------------+----------------------------------+

2 rows in set (0.00 sec)

其中ID為5的語句是剛執行的查詢語句

2、變量profiling是用戶變量，每次都得重新啟用。

以下是我做的一些實驗。數據很明顯，就不多解釋了。

mysql>use test

Database changed

mysql> set profiling=1;

Query OK, 0 rows affected (0.00sec)

mysql>show tables;+----------------+

| Tables_in_test |

+----------------+

| bag_item |

| bag_user |

| score |

| t |

+----------------+

4 rows in set (0.03sec)

mysql> select count(*) fromt;+----------+

| count(*) |

+----------+

| 2097152 |

+----------+

1 row in set (0.74sec)

mysql>show profiles;+----------+------------+------------------------+

| Query_ID | Duration | Query |

+----------+------------+------------------------+

| 1 | 0.02717000 | show tables |

| 2 | 0.74770100 | select count(*) from t |

+----------+------------+------------------------+

2 rows in set (0.00sec)

mysql> show profile for query 2;+--------------------------------+----------+

| Status | Duration |

+--------------------------------+----------+

| (initialization) | 0.000004 |

| checking query cache for query | 0.000044 |

| Opening tables | 0.000012 |

| System lock | 0.000017 |

| Table lock | 0.00003 |

| init | 0.000013 |

| optimizing | 0.000008 |

| statistics | 0.000013 |

| preparing | 0.000011 |

| executing | 0.000006 |

| Sending data | 0.747313 |

| end | 0.000014 |

| query end | 0.000006 |

| storing result in query cache | 0.000006 |

| freeing items | 0.000012 |

| closing tables | 0.000009 |

| logging slow query | 0.000183 |

+--------------------------------+----------+

17 rows in set (0.00sec)

mysql> show profile block io,cpu for query 2;+--------------------------------+----------+----------+------------+--------------+---------------+

| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |

+--------------------------------+----------+----------+------------+--------------+---------------+

| (initialization) | 0.000004 | 0 | 0 | 0 | 0 |

| checking query cache for query | 0.000044 | 0 | 0 | 0 | 0 |

| Opening tables | 0.000012 | 0 | 0 | 0 | 0 |

| System lock | 0.000017 | 0 | 0 | 0 | 0 |

| Table lock | 0.00003 | 0 | 0 | 0 | 0 |

| init | 0.000013 | 0 | 0 | 0 | 0 |

| optimizing | 0.000008 | 0 | 0 | 0 | 0 |

| statistics | 0.000013 | 0 | 0 | 0 | 0 |

| preparing | 0.000011 | 0 | 0 | 0 | 0 |

| executing | 0.000006 | 0 | 0 | 0 | 0 |

| Sending data | 0.747313 | 0.746887 | 0 | 0 | 0 |

| end | 0.000014 | 0 | 0 | 0 | 0 |

| query end | 0.000006 | 0 | 0 | 0 | 0 |

| storing result in query cache | 0.000006 | 0 | 0 | 0 | 0 |

| freeing items | 0.000012 | 0 | 0 | 0 | 0 |

| closing tables | 0.000009 | 0 | 0 | 0 | 0 |

| logging slow query | 0.000183 | 0 | 0 | 0 | 0 |

+--------------------------------+----------+----------+------------+--------------+---------------+

17 rows in set (0.00sec)

mysql> insert into t(username) select username fromt;

Query OK,2097152 rows affected (34.17sec)

Records:2097152Duplicates: 0 Warnings: 0

mysql>show profiles;+----------+-------------+------------------------------------------------+

| Query_ID | Duration | Query |

+----------+-------------+------------------------------------------------+

| 1 | 0.02717000 | show tables |

| 2 | 0.74770100 | select count(*) from t |

| 3 | 0.00004200 | show prifile for query 2 |

| 4 | 34.30410100 | insert into t(username) select username from t |

+----------+-------------+------------------------------------------------+

4 rows in set (0.00sec)

mysql> show profile cpu,block io,memory,swaps for query 4;

mysql> select count(*) fromt;+----------+

| count(*) |

+----------+

| 4194304 |

+----------+

1 row in set (1.51sec)

mysql>show profiles;+----------+-------------+------------------------------------------------+

| Query_ID | Duration | Query |

+----------+-------------+------------------------------------------------+

| 1 | 0.02717000 | show tables |

| 2 | 0.74770100 | select count(*) from t |

| 3 | 0.00004200 | show prifile for query 2 |

| 4 | 34.30410100 | insert into t(username) select username from t |

| 5 | 1.50563800 | select count(*) from t |

+----------+-------------+------------------------------------------------+

5 rows in set (0.00sec)

mysql> show profile cpu,block io,memory,swaps,context switches,source for query 5;

……

mysql> update t set username = 'waill';

Query OK,4194304 rows affected (44.82sec)

Rows matched:4194304 Changed: 4194304Warnings: 0

mysql>show profiles;+----------+-------------+------------------------------------------------+

| Query_ID | Duration | Query |

+----------+-------------+------------------------------------------------+

| 1 | 0.02717000 | show tables |

| 2 | 0.74770100 | select count(*) from t |

| 3 | 0.00004200 | show prifile for query 2 |

| 4 | 34.30410100 | insert into t(username) select username from t |

| 5 | 1.50563800 | select count(*) from t |

| 6 | 44.82054700 | update t set username = 'waill' |

+----------+-------------+------------------------------------------------+

6 rows in set (0.00sec)

mysql> show profile cpu,block io,memory,swaps,context switches,source for query 6;

可以通過help profile;命令來查看profile幫助文檔，里面的例子說明已經很詳細了

mysql>help profile;

Name:'SHOW PROFILE'Description:

Syntax:

SHOW PROFILE [type [, type] ... ]

[FOR QUERY n]

[LIMIT row_count [OFFSET offset]]

type:

ALL|BLOCK IO|CONTEXT SWITCHES|CPU|IPC|MEMORY|PAGE FAULTS|SOURCE|SWAPS

The SHOW PROFILEandSHOW PROFILES statements display profiling

information that indicates resource usageforstatements executed

during the course of the current session.

Profilingiscontrolled by the profiling session variable, which has a

default value of 0 (OFF). Profilingisenabled by setting profiling to1 orON:

mysql> SET profiling = 1;

SHOW PROFILES displays a list of the most recent statements sent to the

server. The size of the listiscontrolled by the

profiling_history_size session variable, which has a default value of15. The maximum value is 100. Setting the value to 0 has the practical

effect of disabling profiling.

All statements are profiledexcept SHOW PROFILE andSHOW PROFILES, so

you will find neither of those statementsinthe profile list.

Malformed statements are profiled. For example, SHOW PROFILINGisan

illegal statement,and a syntax error occurs if you tryto execute it,

but it will show upinthe 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 nisincluded, SHOW PROFILE

displays informationforstatement n. The values of n correspond to the

Query_ID values displayed by SHOW PROFILES.

The LIMIT row_count clause may be given to limit the output to

row_count rows. If LIMITisgiven, OFFSET offset may be added to begin

the output offset rows into the full set of rows.

By default, SHOW PROFILE displays StatusandDuration columns. The

Status values are like the State values displayed by SHOW PROCESSLIST,

although there might be some minor differencesin interpretion forthe

two statementsforsome status values (see

http://dev.mysql.com/doc/refman/5.5/en/thread-information.html).

Optional type values may be specified to display specific additional

types of information:

o ALL displays all information

o BLOCK IO displays countsfor block input andoutput operations

o CONTEXT SWITCHES displays countsfor voluntary andinvoluntary

context switches

o CPU displays userandsystem CPU usage times

o IPC displays countsfor messages sent andreceived

o MEMORYis notcurrently implemented

o PAGE FAULTS displays countsfor major andminor page faults

o SOURCE displays the names of functionsfromthe source code, together

with the nameand line number of the file inwhich the function

occurs

o SWAPS displays swap counts

Profilingisenabled per session. When a session ends, its profiling

informationislost.

URL: http://dev.mysql.com/doc/refman/5.5/en/show-profile.html

Examples:

mysql>SELECT @@profiling;+-------------+

| @@profiling |

+-------------+

| 0 |

+-------------+

1 row in set (0.00sec)

mysql> SET profiling = 1;

Query OK, 0 rows affected (0.00sec)

mysql>DROP TABLE IF EXISTS t1;

Query OK, 0 rows affected,1 warning (0.00sec)

mysql>CREATE TABLE T1 (id INT);

Query OK, 0 rows affected (0.01sec)

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.00sec)

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.00sec)

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.00sec)

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)

help profile

本文轉自：http://www.cnblogs.com/adforce/archive/2012/06/02/2532287.html

總結

以上是生活随笔為你收集整理的mysql profile 查谒_MYSQL查询优化：profile功能的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。