MySQL如何利用profiling分析SQL查询语句

查看profiling是否开启:
Mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)


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

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

执行若干条SQL查询:
mysql> use mysql
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

mysql> select host,user,PassWord from user limit 10;
+-----------+-----------------+-------------------------------------------+
| host      | user            | Password                                  |
+-----------+-----------------+-------------------------------------------+
| localhost | root            | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| %         | chenfeng        | *716E7D76E850A91A8311F35B6BFB1213B751F230 |
| %         | test@localhost  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| %         | sa@localhost    | *4D0DD2673C1DE57138354E81A957460B774C4BC2 |
| %         | admin@localhost | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| localhost | dsf             | *AFED783E50FCEF7B1C89AC89C6E1A0405BD7F313 |
| localhost | dsf1            | *98D1D8D997EA32BABDFEEC2EBB62BB6D010036C6 |
| %         | dsf2            | *0007B182797FAD2A8A37C3F73A011BBD36C49857 |
+-----------+-----------------+-------------------------------------------+
8 rows in set (0.00 sec)

查询上述语句的Profiling情况:
mysql> show profiles;
+----------+------------+----------------------------------------------+
| Query_ID | Duration   | Query                                        |
+----------+------------+----------------------------------------------+
|        1 | 0.00012600 | select @@profiling                           |
|        2 | 0.00018450 | SELECT DATABASE()                            |
|        3 | 0.00011950 | select database()                            |
|        4 | 0.11296025 | show columns from user                       |
|        5 | 0.00038725 | select host,user,Password from user limit 10 |
+----------+------------+----------------------------------------------+
14 rows in set (0.00 sec)

查看Query_ID=5语句的I/O消耗情况：
mysql> show profile block io for query 5;
+--------------------------------+----------+--------------+---------------+
| Status                         | Duration | Block_ops_in | Block_ops_out |
+--------------------------------+----------+--------------+---------------+
| starting                       | 0.000018 |         NULL |          NULL |
| Waiting for query cache lock   | 0.000003 |         NULL |          NULL |
| checking query cache for query | 0.000077 |         NULL |          NULL |
| checking permissions           | 0.000009 |         NULL |          NULL |
| Opening tables                 | 0.000020 |         NULL |          NULL |
| System lock                    | 0.000014 |         NULL |          NULL |
| init                           | 0.000020 |         NULL |          NULL |
| optimizing                     | 0.000005 |         NULL |          NULL |
| statistics                     | 0.000011 |         NULL |          NULL |
| preparing                      | 0.000009 |         NULL |          NULL |
| executing                      | 0.000003 |         NULL |          NULL |
| Sending data                   | 0.000071 |         NULL |          NULL |
| end                            | 0.000004 |         NULL |          NULL |
| query end                      | 0.000002 |         NULL |          NULL |
| closing tables                 | 0.000011 |         NULL |          NULL |
| freeing items                  | 0.000101 |         NULL |          NULL |
| logging slow query             | 0.000006 |         NULL |          NULL |
| cleaning up                    | 0.000004 |         NULL |          NULL |
+--------------------------------+----------+--------------+---------------+
18 rows in set (0.00 sec)

查看Query_ID=5语句的CPU消耗情况：
mysql> show profile cpu for query 5;
+--------------------------------+----------+----------+------------+
| Status                         | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting                       | 0.000018 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000003 | 0.000000 |   0.000000 |
| checking query cache for query | 0.000077 | 0.000000 |   0.000000 |
| checking permissions           | 0.000009 | 0.000000 |   0.000000 |
| Opening tables                 | 0.000020 | 0.000000 |   0.000000 |
| System lock                    | 0.000014 | 0.000000 |   0.000000 |
| init                           | 0.000020 | 0.000000 |   0.000000 |
| optimizing                     | 0.000005 | 0.000000 |   0.000000 |
| statistics                     | 0.000011 | 0.000000 |   0.000000 |
| preparing                      | 0.000009 | 0.000000 |   0.000000 |
| executing                      | 0.000003 | 0.000000 |   0.000000 |
| Sending data                   | 0.000071 | 0.000000 |   0.000000 |
| end                            | 0.000004 | 0.000000 |   0.000000 |
| query end                      | 0.000002 | 0.000000 |   0.000000 |
| closing tables                 | 0.000011 | 0.000000 |   0.000000 |
| freeing items                  | 0.000101 | 0.000000 |   0.000000 |
| logging slow query             | 0.000006 | 0.000000 |   0.000000 |
| cleaning up                    | 0.000004 | 0.000000 |   0.000000 |
+--------------------------------+----------+----------+------------+
18 rows in set (0.00 sec)

mysql>