mysql5.6使用profile工具分析sql
mysql> SELECT count(*) from table where publishtime >='2017-01-01' and publishtime;+----------+
| count(*) |
+----------+
|1317564 |
+----------+
1 row in set (2.46 sec)
mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------------------------------------------------------------------+
| 1 | 0.00109175 | show variables like '%profil%' |
| 2 | 2.46288425 | SELECT count(*) from table where publishtime >='2017-01-01' and publishtime
获取指定的开销：
mysql> show profile for query 2;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000147 |
| checking permissions | 0.000023 |
| Opening tables | 0.000047 |
| init | 0.000081 |
| System lock | 0.000031 |
| optimizing | 0.000034 |
| statistics | 0.001650 |
| preparing | 0.000046 |
| executing | 0.000018 |
| Sending data | 2.460588 |
| end | 0.000041 |
| query end | 0.000019 |
| closing tables | 0.000022 |
| freeing items | 0.000055 |
| cleaning up | 0.000085 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)
可以看到Sending data ，这个还是比较耗时，这个是sending data 不是发送数据的意思，
是收集和发送集合的数据。
查看cpu的数据：
mysql> show profile cpu for query 2;
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting | 0.000147 | 0.000000 | 0.000000 |
| checking permissions | 0.000023 | 0.000000 | 0.000000 |
| Opening tables | 0.000047 | 0.000000 | 0.000000 |
| init | 0.000081 | 0.000000 | 0.000000 |
| System lock | 0.000031 | 0.000000 | 0.000000 |
| optimizing | 0.000034 | 0.000000 | 0.000000 |
| statistics | 0.001650 | 0.001000 | 0.001000 |
| preparing | 0.000046 | 0.000000 | 0.000000 |
| executing | 0.000018 | 0.000000 | 0.000000 |
| Sending data | 2.460588 | 2.464625 | 0.025996 |
| end | 0.000041 | 0.000000 | 0.000000 |
| query end | 0.000019 | 0.000000 | 0.000000 |
| closing tables | 0.000022 | 0.000000 | 0.000000 |
| freeing items | 0.000055 | 0.000000 | 0.000000 |
| cleaning up | 0.000085 | 0.000000 | 0.000000 |
+----------------------+----------+----------+------------+
15 rows in set, 1 warning (0.00 sec)
也是这个操作，
type:
ALL --显示所有的开销信息
| BLOCK IO --显示块IO相关开销
| CONTEXT SWITCHES --上下文切换相关开销
| CPU --显示CPU相关开销信息
| IPC --显示发送和接收相关开销信息
| MEMORY --显示内存相关开销信息
| PAGE FAULTS --显示页面错误相关开销信息
| SOURCE --显示和Source_function，Source_file，Source_line相关的开销信息
| SWAPS --显示交换次数相关开销的信息
这里边的可以任意特换就可以看到对应的参数的开销，自己试试看。
页:
[1]