MYSQL的profiling功能要在Mysql版本5.0.37以上才能使用。
查看profile是否开启
| mysql> show variables like '%profil%'; |
| +------------------------+-------+| Variable_name | Value |+------------------------+-------+| profiling | OFF | --开启SQL语句剖析功能 | profiling_history_size | 15 | --设置保留profiling的数目，缺省为15，范围为0至100，为0时将禁用profiling+------------------------+-------+2 rows in set (0.00 sec) |
基于会话级别开启
| mysql> set profiling = 1; --关闭则用set profiling = off |
| Query OK, 0 rows affected (0.00 sec) |
| mysql> select distinct d.account,a.server_id from tab_appserver_user a -> inner join tab_department_parent b on a.key_id = b.parent_id -> inner join tab_department_member c on b.department_id = c.department_id and c.state=1 -> and c.isdefault=1 inner join tab_user_info d on c.user_id = d.user_id and d.state=1 -> where a.type=1 -> union -> select distinct b.account,a.server_id from tab_appserver_user a -> inner join tab_user_info b on a.key_id = b.user_id and b.state=1 -> where a.type=0; |
查看是否设置生效：
| select @@profiling; |
默认是0，设置成功是1
运行SQL语句：
| mysql> select * FROM hx_line WHERE id = '1455023'; |
查看profiles
| mysql> show profiles; |
| +----------+------------+---------------------------------------------+| Query_ID | Duration | Query |+----------+------------+---------------------------------------------+| 1 | 0.00036150 | select * FROM hx_line WHERE id = '1455023' |+----------+------------+---------------------------------------------+ |
查看具体某条的profile
| mysql> show profile FOR QUERY 1; |
| +--------------------------------+----------+| Status | Duration |+--------------------------------+----------+| starting | 0.000013 || Waiting for query cache lock | 0.000014 || checking query cache for query | 0.000038 || checking permissions | 0.000006 || Opening tables | 0.000013 || System lock | 0.000009 || Waiting for query cache lock | 0.000024 || init | 0.000060 || optimizing | 0.000014 || statistics | 0.000046 || preparing | 0.000017 || executing | 0.000004 || Sending data | 0.000081 || end | 0.000005 || query end | 0.000004 || closing tables | 0.000008 || freeing items | 0.000009 || Waiting for query cache lock | 0.000003 || freeing items | 0.000013 || Waiting for query cache lock | 0.000003 || freeing items | 0.000003 || storing result in query cache | 0.000005 || logging slow query | 0.000003 || cleaning up | 0.000004 |+--------------------------------+----------+24 rows |
