MYSQL profiling分析SQL慢的具体原因

• profile是session级生效，当退出窗口时就失效了

1.查看profile是否打开
select @@profiling;

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

mysql>

0代表关闭；1代表打开。

2.打开profile

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

如果想打开，可以执行set profiling=1
3.使用
profiles一般为已经定位到慢sql的情况下，分析sql到底慢到哪里时使用。步骤如下：
第一：开启profile
第二：执行sql（定位到的慢sql）
第三：show profiles 查看自己执行的主要sql的query_id

mysql> show profiles;
+----------+------------+--------------------+
| Query_ID | Duration   | Query              |
+----------+------------+--------------------+
|        1 | 0.00010325 | select @@profiling |
|        2 | 0.00011750 | SELECT DATABASE()  |
|        3 | 0.00023675 | show databases     |
|        4 | 0.00015475 | show tables        |
|        5 | 0.00026000 | select * from user |
+----------+------------+--------------------+
5 rows in set, 1 warning (0.00 sec)

第四步：分析sql的性能
这里我主要关注query_id为5的sql

mysql> show profile for query 5;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000048 |
| checking permissions | 0.000006 |
| Opening tables       | 0.000018 |
| init                 | 0.000020 |
| System lock          | 0.000008 |
| optimizing           | 0.000005 |
| statistics           | 0.000011 |
| preparing            | 0.000011 |
| executing            | 0.000002 |
| Sending data         | 0.000053 |
| end                  | 0.000004 |
| query end            | 0.000006 |
| closing tables       | 0.000008 |
| freeing items        | 0.000011 |
| logging slow query   | 0.000040 |
| cleaning up          | 0.000010 |
+----------------------+----------+
16 rows in set, 1 warning (0.00