查看mysql语句运行时间

show profiles 之类的语句来查看

mysql> show profiles; 
Empty set

mysql> 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   |
+---------------------------+-------+
7 rows in set

profile是没有打开的

开启profile

mysql> set profiling=1; 
Query OK, 0 rows affected

mysql> 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   |
+---------------------------+-------+
7 rows in set

测试

执行几条sql语句

mysql> show profiles; 
+----------+------------+-----------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                 |
+----------+------------+-----------------------------------------------------------------------+
|        1 |  0.0020095 | show variables like "%pro%"                                           |
|        2 |  0.0012765 | select * from shop where shop_tradearea_id=14 and shop_fristtype=1001 |
|        3 |   0.004761 | show tables                                                           |
|        4 |  0.0360445 | show profile for query2                                               |
|        5 | 0.00023675 | set profiling=1                                                       |
|        6 | 0.36132975 | select * from account where type=4                                    |
+----------+------------+-----------------------------------------------------------------------+
6 rows in set

mysql>  show profile for query 2; 
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000107 |
| checking permissions | 1.3E-5   |
| Opening tables       | 5E-5     |
| System lock          | 2.5E-5   |
| init                 | 0.000161 |
| optimizing           | 2.5E-5   |
| statistics           | 2.8E-5   |
| preparing            | 3E-5     |
| executing            | 4E-6     |
| Sending data         | 0.000536 |
| end                  | 1E-5     |
| query end            | 4E-6     |
| closing tables       | 1.4E-5   |
| freeing items        | 0.000242 |
| logging slow query   | 6E-6     |
| cleaning up          | 2.2E-5   |
+----------------------+----------+

 

timestampdiff来查看测试时间

mysql> set @d=now(); 
mysql> select * from comment; 
mysql> select timestampdiff(second,@d,now());