bitscn.com mysql5.0.37版本以上支持了profiling – 官方手册。此工具可用来查询sql 会执行多少时间，system lock和table lock 花多少时间等等，对定位一条语句的i/o消耗和cpu消耗 非常重要。
从启动profile之后的所有查询包括错误的语句都会记录。
关闭会话或者set profiling=0 就关闭了。
# 开启profiling
mysql> set profiling=1;
mysql> select * from user limit 1;
mysql> select count(*) from user group by sexal;
# 查看这些语句的profile
mysql> show profiles;
+--------------+----------------+--------------------------------------------------------------+
| query_id | duration | query |
+--------------+----------------+--------------------------------------------------------------+
| 1 | 0.00013200 | select database() |
| 2 | 0.00044100 | select * from user limit 2 |
| 3 | 1.95544100 | select nick,count(*) from user group by online|
+--------------+----------------+--------------------------------------------------------------+
查看具体一条（query_id=3 这一条）语句的profiles，包括cpu和柱塞i/o的情况
mysql> show profile cpu,block io for query 3;
+----------------------+----------+----------+------------+--------------+---------------+
| status | duration | cpu_user | cpu_system | block_ops_in | block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000057 | 0.000000 | 0.000000 | 0 | 0 |
| opening tables | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |
| system lock | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| table lock | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000021 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
| creating tmp table | 0.000039 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| copying to tmp table | 1.900619 | 1.030785 | 0.197970 | 127 | 127 |
| sorting result | 0.000015 | 0.000000 | 0.000000 | 0 | 0 |
| sending data | 0.000015 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| removing tmp table | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000025 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
上面就获得了一条语句的cpu和block io消耗，对定位瓶颈很方便，其余的一些信息，可以用语句：“show profile *** for query 3”来获取
另外附上profiling 的学习质料
profiling 基础
http://www.xaprb.com/blog/2006/10/12/how-to-profile-a-query-in-mysql/
http://www.xaprb.com/blog/2006/10/15/a-case-study-in-profiling-queries-in-mysql/
http://www.xaprb.com/blog/2006/10/17/mysql-profiling-case-study-part-2/
profiling 进阶
http://www.mysqlperformanceblog.com/2009/01/19/profiling-mysql-stored-routines/
http://www.mysqlperformanceblog.com/2008/05/18/wanted-better-memory-profiling-for-mysql/
maatkit–perldoc mk-query-profiler
profiling高级
http://dev.mysql.com/tech-resources/articles/pro-mysql-ch6.html
http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html
profiling骨灰级
http://www.scribd.com/doc/2669413/dtrace-and-mysql-presentation
http://forge.mysql.com/wiki/using_dtrace_with_mysql
http://wikis.sun.com/display/blueprints/optimizing+mysql+database+application+performan
摘自 性能测试专栏 bitscn.com