MySQL通过show profile来查看SQL执行情况

1,查看是否开启保存SQL执行记录

mysql> show variables like 'profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
2 rows in set (0.01 sec)

2，开启和关闭

mysql> set profiling =1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set profiling =0;
Query OK, 0 rows affected, 1 warning (0.00 sec)

3，查看SQL执行记录

mysql> show profiles;
+----------+------------+------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                            |
+----------+------------+------------------------------------------------------------------+
|        1 | 0.00118500 | show variables like 'profiling%'                                 |
|        2 | 0.17639500 | select count(*) from app_develop_staff_info                      |
+----------+------------+------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

4，查看单条SQL的执行情况

mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000083 |
| checking permissions | 0.000015 |
| Opening tables       | 0.000029 |
| init                 | 0.000026 |
| System lock          | 0.000021 |
| optimizing           | 0.000018 |
| statistics           | 0.000021 |
| preparing            | 0.000024 |
| executing            | 0.000012 |
| Sending data         | 0.175942 |
| end                  | 0.000023 |
| query end            | 0.000017 |
| closing tables       | 0.000020 |
| freeing items        | 0.000052 |
| cleaning up          | 0.000092 |
+----------------------+----------+
15 rows in set, 1 warning (0.01 sec)

命令可以指定显示具体内容

mysql> show profile source for query 2;
+----------------------+----------+-----------------------+------------------+-------------+
| Status               | Duration | Source_function       | Source_file      | Source_line |
+----------------------+----------+-----------------------+------------------+-------------+
| starting             | 0.000083 | NULL                  | NULL             |        NULL |
| checking permissions | 0.000015 | check_access          | sql_parse.cc     |        5256 |
| Opening tables       | 0.000029 | open_tables           | sql_base.cc      |        4911 |
| init                 | 0.000026 | mysql_prepare_select  | sql_select.cc    |        1050 |
| System lock          | 0.000021 | mysql_lock_tables     | lock.cc          |         304 |
| optimizing           | 0.000018 | optimize              | sql_optimizer.cc |         138 |
| statistics           | 0.000021 | optimize              | sql_optimizer.cc |         362 |
| preparing            | 0.000024 | optimize              | sql_optimizer.cc |         485 |
| executing            | 0.000012 | exec                  | sql_executor.cc  |         110 |
| Sending data         | 0.175942 | exec                  | sql_executor.cc  |         190 |
| end                  | 0.000023 | mysql_execute_select  | sql_select.cc    |        1105 |
| query end            | 0.000017 | mysql_execute_command | sql_parse.cc     |        4956 |
| closing tables       | 0.000020 | mysql_execute_command | sql_parse.cc     |        5004 |
| freeing items        | 0.000052 | mysql_parse           | sql_parse.cc     |        6264 |
| cleaning up          | 0.000092 | dispatch_command      | sql_parse.cc     |        1774 |
+----------------------+----------+-----------------------+------------------+-------------+
15 rows in set, 1 warning (0.00 sec)

可选type参数，取值范围可以如下：
ALL 显示所有性能信息
BLOCK IO 显示块IO操作的次数
CONTEXT SWITCHES 显示上下文切换次数，不管是主动还是被动
CPU 显示用户CPU时间、系统CPU时间
IPC 显示发送和接收的消息数量
MEMORY [暂未实现]
PAGE FAULTS 显示页错误数量
SOURCE 显示源码中的函数名称与位置
SWAPS 显示SWAP的次数