mysql profile 查谒_MYSQL查询优化：profile功能
这里还需要注意一点就是，需要安装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功能的全部内容，希望文章能够帮你解决所遇到的问题。
- 上一篇： 打开和关闭mysql服务器_启动和关闭M
- 下一篇： sql查询时间大于某一时间_查询时间从2