- 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