1.profile在mysql5.0.37及其以上版本中可用
2.查看profile是否可用
SELECT @@profiling;
来查看是否已经启用profile,如果profilng值为0,可以通过
SET profiling = 1;
启用
mysql>select count(*) from roi_summary;
然后show profiles查看如下:
+----------------+------------+----------------------------------+
| Query_ID | Duration | Query |
+----------------+------------+----------------------------------+
| 1 | 0.00021500 | select @@profiling |
| 2 | 0.05522700 | select count(*) from roi_summary |
+----------------+------------+----------------------------------+
其中ID为5的语句是刚执行的查询语句
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.00 sec)
#查看cpu io memory swaps
mysql> show profile cpu,block io,memory,swaps for query 2;
注意:
1.profile是用户级变量,每次都得重新启用
2.需在终端使用,在phpmyadmin下无效