Show Profile是MySQL提供的可以用来分析当前会话中SQL都做了什么、执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。|
查看show profile是否开启
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1 row in set (0.00 sec)
开启 show profile
mysql> set profiling = 'ON';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | ON |
+---------------+-------+
1 row in set (0.02 sec)
查看当前会话都有哪些 profiles
mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------+
| 1 | 0.02887100 | show variables like 'profiling' |
| 2 | 0.00010500 | select * from student where stuno = 1234567 |
| 3 | 0.00062425 | show databases |
| 4 | 0.00016825 | SELECT DATABASE() |
| 5 | 0.00034500 | show databases |
| 6 | 0.01144500 | show tables |
| 7 | 2.41117450 | select * from student where stuno = 1234567 |
| 8 | 1.27697000 | select * from student where stuno = 1231234 |
| 9 | 1.33853225 | select * from student where stuno = 3453451 |
| 10 | 0.00023825 | show create table student |
| 11 | 0.00043500 | select * from student where id = 1231231 |
| 12 | 1.23249650 | select * from student where classID = 1231231 |
| 13 | 1.29235175 | select * from student where classID = 1000000 |
| 14 | 1.45108200 | select * from student where name = 'jnoefp' |
+----------+------------+-----------------------------------------------+
14 rows in set, 1 warning (0.00 sec)
查看最近的一次查询的开销
mysql> show profile;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000093 |
| checking permissions | 0.000009 |
| Opening tables | 0.000022 |
| init | 0.000033 |
| System lock | 0.000011 |
| optimizing | 0.000020 |
| statistics | 0.000022 |
| preparing | 0.000016 |
| executing | 0.000004 |
| Sending data | 1.437393 |
| end | 0.000027 |
| query end | 0.000013 |
| closing tables | 0.000013 |
| freeing items | 0.000679 |
| cleaning up | 0.012730 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)
查看指定查询的开销
查询Query_ID为3的开销：
mysql> show profile for query 3;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000114 |
| checking permissions | 0.000011 |
| Opening tables | 0.000050 |
| init | 0.000014 |
| System lock | 0.000008 |
| optimizing | 0.000007 |
| statistics | 0.000015 |
| preparing | 0.000016 |
| executing | 0.000314 |
| Sending data | 0.000019 |
| end | 0.000005 |
| query end | 0.000008 |
| closing tables | 0.000004 |
| removing tmp table | 0.000007 |
| closing tables | 0.000005 |
| freeing items | 0.000015 |
| cleaning up | 0.000015 |
+----------------------+----------+
17 rows in set, 1 warning (0.00 sec)
show profile的常用查询参数
- ALL:显示所有的开销信息。
- BLOCK IO:显示块IO开销。
- CONTEXT SWITCHES: 上下文切换开销。
- CPU:显示CPU开销信息。
- IPC:显示发送和接收开销信息。
- MEMORY:显示内存开销信息。
- PAGE FAULTS:显示页面错误开销信息。
- SOURCE:显示和Source_function，Source_file，Source_line相关的开销信息。
- SWAPS:显示交换次数开销信息。
