MySQL Profile

1：查看MySQL当前版本是否支持profile

mysql> SELECT @@HAVE_PROFILING;
+------------------+
| @@HAVE_PROFILING |
+------------------+
| YES              |
+------------------+
1 row in set (0.00 sec)

2：默认profile是关闭的，查看当前profile是否打开

mysql> SELECT @@PROFILING;
+-------------+
| @@PROFILING |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

3：打开profile

mysql> SET @@PROFILING = 1;
Query OK, 0 rows affected (0.00 sec)

4：创建环境使用profile

a.创建测试表

mysql> CREATE TABLE T2 
    -> AS 
    -> SELECT * FROM mysql.user;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

b.查看当前语句的ID

mysql> show profiles;

c.查看线程每个状态和执行时间

mysql> show profile for query 25;
+------------------------------+----------+
| Status                       | Duration |
+------------------------------+----------+
| starting                     | 0.000050 |
| checking permissions         | 0.000006 |
| checking permissions         | 0.000005 |
| Opening tables               | 0.000115 |
| System lock                  | 0.000011 |
| init                         | 0.000095 |
| creating table               | 0.005914 |
| After create                 | 0.000118 |
| System lock                  | 0.000032 |
| optimizing                   | 0.000008 |
| statistics                   | 0.000015 |
| preparing                    | 0.000012 |
| executing                    | 0.000004 |
| Sending data                 | 0.000161 |
| Waiting for query cache lock | 0.000005 |
| Sending data                 | 0.000676 |
| end                          | 0.000006 |
| query end                    | 0.000002 |
| closing tables               | 0.000012 |
| freeing items                | 0.000190 |
| logging slow query           | 0.000003 |
| cleaning up                  | 0.000004 |
+------------------------------+----------+
22 rows in set (0.00 sec)