8.利用show profiles分析慢sql语句

Show profile

　　show profile也是分析慢sql语句的一种手段，通过它可以分析出一条sql语句的性能瓶颈在什么地方。它可以定位一条sql语句执行的各种资源消耗情况，比如CPU/IO等。　

root@localhost 20:41:  [(none)]> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |  ##查看该功能是否开启
+---------------+-------+

开启该功能： set profiling=on;   关闭用off

　　##这里可以查看所有执行过sql语句

root@localhost 20:44:  [liulin]> show profiles;
+----------+------------+----------------------+
| Query_ID | Duration   | Query                |
+----------+------------+----------------------+
|        1 | 0.00011400 | show warings         |
|        2 | 0.00012725 | show warnings        |
|        3 | 0.00100525 | select * from test.t |
|        4 | 0.00729025 | show databases       |
|        5 | 0.00035350 | SELECT DATABASE()    |
|        6 | 0.00043100 | show tables          |
|        7 | 0.01314200 | select * from t      |
+----------+------------+----------------------+

oot@localhost 20:46:  [liulin]> show profile cpu,block io  for query 7;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000068 | 0.000022 |   0.000039 |            0 |             0 |
| checking permissions | 0.000009 | 0.000004 |   0.000006 |            0 |             0 |
| Opening tables       | 0.011741 | 0.000000 |   0.010701 |           24 |             0 |
| init                 | 0.000033 | 0.000000 |   0.000027 |            0 |             0 |
| System lock          | 0.000012 | 0.000000 |   0.000011 |            0 |             0 |
| optimizing           | 0.000006 | 0.000000 |   0.000006 |            0 |             0 |
| statistics           | 0.000015 | 0.000000 |   0.000015 |            0 |             0 |
| preparing            | 0.000013 | 0.000000 |   0.000013 |            0 |             0 |
| executing            | 0.000004 | 0.000000 |   0.000004 |            0 |             0 |
| Sending data         | 0.001170 | 0.000000 |   0.000526 |          192 |             0 |
| end                  | 0.000013 | 0.000000 |   0.000008 |            0 |             0 |
| query end            | 0.000012 | 0.000000 |   0.000011 |            0 |             0 |
| closing tables       | 0.000010 | 0.000000 |   0.000010 |            0 |             0 |
| freeing items        | 0.000023 | 0.000000 |   0.000023 |            0 |             0 |
| cleaning up          | 0.000016 | 0.000000 |   0.000016 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+

从上面可以查到select * from t这条语句消耗的cpu以及block和io消耗情况