mysql 5.5 以后加了一个profile设置,可以观察到具体语句的执行步骤.

0:查看profile是否开启

> Show  variables like ‘profiling’

 +---------------+-------+

| Variable_name | Value |

+---------------+-------+

| profiling     | OFF   |

+---------------+-------+

1:> set profiling=on;

 +---------------+-------+

| Variable_name | Value |

+---------------+-------+

| profiling     | On      |

+---------------+-------+

mysql> show profiles;

+----------+------------+----------------------------------------------------------+

| Query_ID | Duration   | Query                                                    |

+----------+------------+----------------------------------------------------------+

|        1 | 0.00034225 | select cat_id,avg(shop_price) from goods group by cat_id |

+----------+------------+----------------------------------------------------------+

1 row in set (0.00 sec)

mysql> show profile for query 1;

+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000626 |
| Waiting for query cache lock   | 0.000063 |
| checking query cache for query | 0.000097 |
| checking permissions           | 0.000063 |
| Opening tables                 | 0.000066 |
| init                           | 0.002323 |
| optimizing                     | 0.000064 |
| executing                      | 0.000078 |
| end                            | 0.000064 |
| query end                      | 0.000061 |
| closing tables                 | 0.000062 |
| freeing items                  | 0.000238 |
| logging slow query             | 0.000060 |
| cleaning up                    | 0.000063 |
+--------------------------------+----------+

msyql>reset query cache;

清空所有缓存。

flush query cache