mysql profile详解

对某一条sql的性能进行分析

 

查看当前环境的profile设置

show variables like '%profiling%';

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | ON    |
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)

开启profile功能

set global profiling = 1;

查看最近sql

show profiles;

+----------+------------+-----------------------------------+
| Query_ID | Duration   | Query                             |
+----------+------------+-----------------------------------+
|        1 | 0.00013100 | select @@version_comment limit 1  |
|        2 | 0.00115800 | show variables like '%profiling%' |
|        3 | 0.00013375 | SELECT DATABASE()                 |
|        4 | 0.00070625 | show databases                    |
|        5 | 0.00073675 | show tables                       |
|        6 | 0.00034975 | select * from student where id=1  |
+----------+------------+-----------------------------------+
6 rows in set, 1 warning (0.00 sec)

查看某一条的具体情况

show profile for query 6;

+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000082 |
| Executing hook on transaction  | 0.000003 |
| starting                       | 0.000011 |
| checking permissions           | 0.000007 |
| Opening tables                 | 0.000048 |
| init                           | 0.000009 |
| System lock                    | 0.000011 |
| optimizing                     | 0.000013 |
| statistics                     | 0.000055 |
| preparing                      | 0.000014 |
| executing                      | 0.000013 |
| end                            | 0.000003 |
| query end                      | 0.000004 |
| waiting for handler commit     | 0.000009 |
| closing tables                 | 0.000009 |
| freeing items                  | 0.000015 |
| cleaning up                    | 0.000045 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

show profile cpu,block io,memory,swaps for query 6;

+--------------------------------+----------+----------+------------+--------------+---------------+-------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |
+--------------------------------+----------+----------+------------+--------------+---------------+-------+
| starting                       | 0.000082 | 0.000057 |   0.000024 |            0 |             0 |     0 |
| Executing hook on transaction  | 0.000003 | 0.000003 |   0.000001 |            0 |             0 |     0 |
| starting                       | 0.000011 | 0.000007 |   0.000003 |            0 |             0 |     0 |
| checking permissions           | 0.000007 | 0.000005 |   0.000002 |            0 |             0 |     0 |
| Opening tables                 | 0.000048 | 0.000034 |   0.000014 |            0 |             0 |     0 |
| init                           | 0.000009 | 0.000006 |   0.000003 |            0 |             0 |     0 |
| System lock                    | 0.000011 | 0.000008 |   0.000003 |            0 |             0 |     0 |
| optimizing                     | 0.000013 | 0.000009 |   0.000004 |            0 |             0 |     0 |
| statistics                     | 0.000055 | 0.000038 |   0.000016 |            0 |             0 |     0 |
| preparing                      | 0.000014 | 0.000010 |   0.000004 |            0 |             0 |     0 |
| executing                      | 0.000013 | 0.000010 |   0.000004 |            0 |             0 |     0 |
| end                            | 0.000003 | 0.000002 |   0.000001 |            0 |             0 |     0 |
| query end                      | 0.000004 | 0.000002 |   0.000001 |            0 |             0 |     0 |
| waiting for handler commit     | 0.000009 | 0.000007 |   0.000003 |            0 |             0 |     0 |
| closing tables                 | 0.000009 | 0.000006 |   0.000002 |            0 |             0 |     0 |
| freeing items                  | 0.000015 | 0.000010 |   0.000005 |            0 |             0 |     0 |
| cleaning up                    | 0.000045 | 0.000032 |   0.000013 |            0 |             0 |     0 |
+--------------------------------+----------+----------+------------+--------------+---------------+-------+
17 rows in set, 1 warning (0.00 sec)