SetProfiling

在最新版的mysql，5.0.37中加入了一个新的变量：profiling。可以用来查看query执行的细节，方便优化。

使用的方法很简单。

mysql>set profiling=1;

这样会生成一个information_schema.profiling内存表，此表会一直保存到会话结束。之后所有执行的查询都会再此表中记录一些信息。

mysql>select country.name, count(*) as city_num from city, country where city.CountryCode = country.Code group by country.Code;
mysql>set profiling=0;
mysq>show profiles;

+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                                      |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------+
|        0 | 0.00015500 | set profiling=1                                                                                                                            |
|        1 | 0.00463800 | select country.name, count(*) as city_num from city, country where city.CountryCode = country.Code group by country.Code                   |
|        2 | 0.00003100 | set profiling=0                                                                                                                            |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------+
mysql>show profile for query 1;
+--------------------------------+------------+
| Status                         | Duration   |
+--------------------------------+------------+
| checking query cache for query | 0.00000400 |
| Opening tables                 | 0.00007800 |
| System lock                    | 0.00001000 |
| Table lock                     | 0.00000600 |
| init                           | 0.00001800 |
| optimizing                     | 0.00001900 |
| statistics                     | 0.00001600 |
| preparing                      | 0.00002700 |
| Creating tmp table             | 0.00001100 |
| executing                      | 0.00002700 |
| Copying to tmp table           | 0.00000300 |
| Sorting result                 | 0.00403900 |
| Sending data                   | 0.00009700 |
| end                            | 0.00023500 |
| removing tmp table             | 0.00000400 |
| end                            | 0.00001900 |
| query end                      | 0.00000300 |
| storing result in query cache  | 0.00000300 |
| freeing items                  | 0.00000400 |
| closing tables                 | 0.00000700 |
| logging slow query             | 0.00000500 |
| cleaning up                    | 0.00000300 |
+--------------------------------+------------+