龚哥哥 爱生活、做自己!
查看MySQL SQL语句执行的时间
发表于 2015-9-1 | 浏览(484) | 数据库

1;在控制台操作,查看是否已开启profile

show variables like "%pro%";

+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| have_profiling            | YES   |
| profiling                 | OFF   |未开启
| profiling_history_size    | 15    |
| protocol_version          | 10    |
| proxy_user                |       |
| slave_compressed_protocol | OFF   |
| stored_program_cache      | 256   |
+---------------------------+-------+

2;开启profile

set profiling=1;
show variables like "%pro%";

+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| have_profiling            | YES   |
| profiling                 | ON    |
| profiling_history_size    | 15    |
| protocol_version          | 10    |
| proxy_user                |       |
| slave_compressed_protocol | OFF   |
| stored_program_cache      | 256   |
+---------------------------+-------+

3;进行测试(以毫秒为单位)

select * from user;
show profiles;

+----------+------------+-----------------------------+
| Query_ID | Duration| Query                       |
+----------+------------+-----------------------------+
| 1        | 0.00152800 | show variables like "%pro%" |
| 2        | 0.00098075 | show tables                 |
| 3        | 0.00035975 | select * from user          |
| 4        | 0.00077625 | select * from user      |
| 5        | 0.00090725 | show variables like "%pro%" |
| 6        | 0.00068650 | select * from user      |
| 7        | 0.00069100 | select * from user      |
| 8        | 0.00088225 | select * from user      |
+----------+------------+-----------------------------+

4;查看指定语句的详细执行(Query_ID)

show profile for query 8;

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000084 |
| checking permissions | 0.000016 |
| Opening tables       | 0.000038 |
| init                 | 0.000068 |
| System lock          | 0.000020 |
| optimizing           | 0.000011 |
| statistics           | 0.000032 |
| preparing            | 0.000019 |
| executing            | 0.000005 |
| Sending data         | 0.000347 |
| end                  | 0.000014 |
| query end            | 0.000008 |
| closing tables       | 0.000054 |
| freeing items        | 0.000141 |
| cleaning up          | 0.000027 |
+----------------------+----------+

阅读全文

TOP