三十六、【进阶】show profiles分析

1、profiles

（1）详情

可以帮助清楚的展现，每一条SQL语句的执行耗时，以及时间都耗费到哪里去了

（2）基础语句

2、查看是否支持profiles

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set, 1 warning (0.00 sec)

3、查询profiles是否打开

默认情况下，MySQL数据库的profiles是关闭的，我们需要手动将其打开

（1）查询是否打开

select @@profiling;

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

（2）打开profiles

通过set语句，将profiling参数设置为1，即开启

mysql> set profiling =1;
Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

4、使用show profiles;语句

（1）查询语句的执行时间

show profiles;

mysql> show profiles;
+----------+------------+--------------------+
| Query_ID | Duration   | Query              |
+----------+------------+--------------------+
|        1 | 0.00041325 | select @@profiling |
|        2 | 0.00017100 | show tables        |
|        3 | 0.00032850 | SELECT DATABASE()  |
|        4 | 0.00130175 | show databases     |
|        5 | 0.00325000 | show tables        |
|        6 | 0.00115725 | show tables        |
|        7 | 0.00250400 | select * from stu  |
+----------+------------+--------------------+
7 rows in set, 1 warning (0.00 sec)

（2）查询某一条语句在各个阶段的耗时

show profiles for query 第几条语句;

mysql> show profile for query 5;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000044 |
| Executing hook on transaction  | 0.000005 |
| starting                       | 0.000018 |
| checking permissions           | 0.000006 |
| checking permissions           | 0.000006 |
| Opening tables                 | 0.002531 |
| init                           | 0.000011 |
| System lock                    | 0.000010 |
| optimizing                     | 0.000023 |
| statistics                     | 0.000114 |
| preparing                      | 0.000038 |
| Creating tmp table             | 0.000050 |
| executing                      | 0.000041 |
| checking permissions           | 0.000115 |
| end                            | 0.000007 |
| query end                      | 0.000006 |
| waiting for handler commit     | 0.000012 |
| closing tables                 | 0.000015 |
| freeing items                  | 0.000127 |
| cleaning up                    | 0.000073 |
+--------------------------------+----------+
20 rows in set, 1 warning (0.00 sec)