MySQL中的profiling

mysql中的profiling能够显示sql执行过程中各种资源的使用情况, 通过分析这些信息可以及时发现不良的Query, 对性能进行调优.

profiling据说在MySQL 5.0.37之后可以使用, 但是我试过了5.1.25-rc-log却不能用, 再试了5.1.34-log又可以, 查看官方的论坛,也没有给明确的说明, 也许是故意的, 也许是一个bug...

要使用profiling, 在编译的时候加入--enable-profiling, 然后打开profile功能.
mysq>SET profiling=1;

执行一条语句看看
mysql> select * from user_xxx limit 1000;
+----------+-----+------+
| a | b | c |
+----------+-----+------+
| 22494895 | 3 | 3 |
| 32781498 | 3 | 1 |
| 36392153 | 3 | 1 |
| 31142213 | 3 | 2 |
| 12447081 | 1 | 1 |

刚才执行的语句的profile已经被记录下来了.

mysql> show profiles;
+----------+------------+--------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------+
| 1 | 0.00005900 | select * from user_xxx limit 1000 |
+----------+------------+--------------------------------------+

还可以看cpu, io这些详细的情况.

mysql> show profile cpu, block io for query 1;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000017 | 0.001000 | 0.000000 | 0 | 0 |
| checking query cache for query | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
| checking privileges on cached | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| sending cached result to clien | 0.000018 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+

在实际中, 我们可以通过SQL的profile来对比不同的Query的情况, 使用最优的语句, 必然对提高系统的性能大有帮助.