MySQL5.0.37版本以上支持profiling,通过使用profiling 功能可以查看到sql语句消耗资源的更详细的信息。通常我们使用explain 查看执行计划，结合profile 功能我们可以定位sql 执行过程中的瓶颈到底出现在哪里？
一 语法：
show profile 的语法格式如下：
SHOW PROFILE [type [, type] … ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type:
ALL - displays all information
BLOCK IO - displays counts for block input and output Operations
CONTEXT SWITCHES - displays counts for voluntary and involuntary context switches
IPC - displays counts for messages sent and received
MEMORY - is not currently implemented
PAGE FAULTS - displays counts for major and minor page faults
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
SWAPS - displays swap counts
二 如何使用
默认情况下profiling 为0 ，
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
开启profiling,则设置该值为1
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
查询一些sql
mysql> SELECT t0.id AS id1, t0.frozen AS frozen6, t0.gmt_create AS gmt_create9, t0.gmt_update AS gmt_update10
-> FROM tabname t0 WHERE t0.uid = '46061' LIMIT 1;
+------+---------+---------------------+---------------------+
| id1 | frozen6 | gmt_create9 | gmt_update10 |
+------+---------+---------------------+---------------------+
| 7922 | 0.00 | 2012-10-29 22:45:09 | 2012-10-31 15:23:24 |
+------+---------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> show profiles;
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 0.00048900 | SELECT t0.id AS id1, t0.frozen AS frozen6, t0.gmt_create AS gmt_create9, t0.gmt_update AS gmt_update10
FROM tabname t0 WHERE t0.uid = '46061' LIMIT 1 |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
Query_ID --- 是执行sql 的编号，如果你执行了7个 ，show profiles的时候，ID 会显示1---7
Duration -----sql 的执行时间
Query -----具体执行的sql 语句。
mysql> show profile for query 1;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000141 |
| Opening tables | 0.000032 |
| System lock | 0.000009 |
| Table lock | 0.000013 |
| init | 0.000031 |
| optimizing | 0.000015 |
| statistics | 0.000074 |
| preparing | 0.000020 |
| executing | 0.000006 |
| Sending data | 0.000088 |
| end | 0.000007 |
| query end | 0.000006 |
| freeing items | 0.000035 |
| logging slow query | 0.000006 |
| cleaning up | 0.000006 |
+--------------------+----------+
15 rows in set (0.00 sec)
executing,sending data都是很快的，因为该sql 的执行计划很正确了！
查看执行该sql所花费的 block io,cpu数据：
mysql> show profile BLOCK IO ,cpu for query 4;
+--------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000141 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000032 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
| Table lock | 0.000013 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000015 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000074 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000020 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000088 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000035 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
+--------------------+----------+----------+------------+--------------+---------------+
15 rows in set (0.00 sec)
当然也可以使用
show profile SOURCE,MEMORY,CONTEXT SWITCHES for query N; 查看其它信息!
当sql 的性能出现问题之后，除了定位sql的执行计划之外，可以选择使用 profile 来对sql的瓶颈进行定位！
