mysql中show profiles使用分析sql性能
一、首先查看是否开启profiling功能
Show profiles是5.0.37之后添加的，要想使用此功能，要确保版本在5.0.37之后。
查看一下我的数据库版本
mysql> select version();
+———–+
| version() |
+———–+
| 5.5.32 |
+———–+
1 row in set (0.00 sec)
二、开启profiling
版本是支持show profiles功能的。接下来进入mysql性能跟踪诊断的世界
查看是否打开了profiles功能,默认是关闭的
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
三、执行sql语句
mysql> use test;
Database changed
mysql> select * from user limit 2000;
四、查看性能分析
mysql> show profiles;
+———-+————+————————————-+
| Query_ID | Duration | Query |
+———-+————+————————————-+
| 1 | 0.00014225 | select @@version_comment limit 1 |
| 2 | 0.00027975 | set profiling=1 |
| 3 | 0.00044075 | SELECT DATABASE() |
| 4 | 0.00021975 | explain select * from user limit 20 |
| 5 | 0.00279875 | select * from user limit 2000 |
+———-+————+————————————-+
5 rows in set (0.00 sec)
根据query_id 查看某个查询的详细时间耗费
mysql> show profile for query 5;
+———————-+———-+
| Status | Duration |
+———————-+———-+
| starting | 0.000035 |
| checking permissions | 0.000006 |
| Opening tables | 0.000011 |
| System lock | 0.000008 |
| init | 0.000011 |
| optimizing | 0.000003 |
| statistics | 0.000008 |
| preparing | 0.000007 |
| executing | 0.000001 |
| Sending data | 0.002596 |
| end | 0.000009 |
| query end | 0.000002 |
| closing tables | 0.000007 |
| freeing items | 0.000091 |
| logging slow query | 0.000003 |
| cleaning up | 0.000002 |
+———————-+———-+
16 rows in set (0.00 sec)
SHOW profiles语法：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SHOW PROFILE [type [, type] … ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]] type: ALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE | SWAPS |
mysql> show profile cpu,memory for query 5 ;
+———————-+———-+———-+————+
| Status | Duration | CPU_user | CPU_system |
+———————-+———-+———-+————+
| starting | 0.000035 | 0.000000 | 0.000000 |
| checking permissions | 0.000006 | 0.000000 | 0.000000 |
| Opening tables | 0.000011 | 0.000000 | 0.000000 |
| System lock | 0.000008 | 0.000000 | 0.000000 |
| init | 0.000011 | 0.000000 | 0.000000 |
| optimizing | 0.000003 | 0.000000 | 0.000000 |
| statistics | 0.000008 | 0.000000 | 0.000000 |
| preparing | 0.000007 | 0.000000 | 0.000000 |
| executing | 0.000001 | 0.000000 | 0.000000 |
| Sending data | 0.002596 | 0.000000 | 0.000000 |
| end | 0.000009 | 0.000000 | 0.000000 |
| query end | 0.000002 | 0.000000 | 0.000000 |
| closing tables | 0.000007 | 0.000000 | 0.000000 |
| freeing items | 0.000091 | 0.000000 | 0.000000 |
| logging slow query | 0.000003 | 0.000000 | 0.000000 |
| cleaning up | 0.000002 | 0.000000 | 0.000000 |
+———————-+———-+———-+————+
16 rows in set (0.00 sec)