查看mysql语句运行时间的2种方法

网站运行很慢的时候，我就特别起知道为什么这么慢，所以我查啊查,数据库绝对是很重要的一部分，里面运行的sql是绝对不能放过的。平时做项目的时候，我也会注意sql语句的书写，写出一些高效的sql来，所以我会经常测试自己写的sql语句。我把我知道的二个方法，总结一下发出来。

一，show profiles 之类的语句来查看

1，查一下profile是不是打开了，默认是不打开的。

mysql> show profiles;

Empty set (0.02 sec)

 

mysql> show variables like "%pro%";

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

| Variable_name             | Value |

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

| profiling                 | OFF   |

| profiling_history_size    | 15    |

| protocol_version          | 10    |

| slave_compressed_protocol | OFF   |

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

4 rows in set (0.00 sec)

我查看一下profiles里面没有东西，所以公司的电脑里面profile是没有打开的，我查看了一下mysql变量，果然是OFF的。

2，开启profile，然后测试

开启profile

mysql> set profiling=1;

Query OK, 0 rows affected (0.00 sec)

测试如下：

mysql> show tables;

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

| Tables_in_test |

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

| aa             |

| bb             |

| comment        |

| string_test    |

| user           |

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

5 rows in set (0.00 sec)

 

mysql> select * from aa;

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

| id | name | nname      | sex  |

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

|  2 | tank | bbbb,4bbbb | NULL |

|  3 | zhang| 3,c,u      | NULL |

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

2 rows in set (0.00 sec)

 

mysql> update aa set name='d';

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2  Changed: 2  Warnings: 0

 

mysql> delete from bb;

Query OK, 2 rows affected (0.00 sec)

 

mysql> show profiles;

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

| Query_ID | Duration   | Query                  |

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

|        1 | 0.00054775 | show tables            |

|        2 | 0.00022400 | select * from aa       |

|        3 | 0.00026275 | update aa set name='d' |

|        4 | 0.00043000 | delete from bb         |

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

4 rows in set (0.00 sec)

 

mysql> show profile;

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

| Status               | Duration  |

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

| (initialization)     | 0.0000247 |

| checking permissions | 0.0000077 |

| Opening tables       | 0.0000099 |

| System lock          | 0.000004  |

| Table lock           | 0.000005  |

| init                 | 0.0003057 |

| query end            | 0.0000062 |

| freeing items        | 0.000057  |

| closing tables       | 0.000008  |

| logging slow query   | 0.0000015 |

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

10 rows in set (0.00 sec)

 

mysql> show profile for query 1;

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

| Status               | Duration  |

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

| (initialization)     | 0.000028  |

| checking permissions | 0.000007  |

| Opening tables       | 0.0000939 |

| System lock          | 0.0000017 |

| Table lock           | 0.0000055 |

| init                 | 0.000009  |

| optimizing           | 0.0000027 |

| statistics           | 0.0000085 |

| preparing            | 0.0000065 |

| executing            | 0.000004  |

| checking permissions | 0.000258  |

| Sending data         | 0.000049  |

| end                  | 0.0000037 |

| query end            | 0.0000027 |

| freeing items        | 0.0000307 |

| closing tables       | 0.0000032 |

| removing tmp table   | 0.0000275 |

| closing tables       | 0.0000037 |

| logging slow query   | 0.000002  |

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

19 rows in set (0.00 sec)

 

mysql> show profile for query 3;

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

| Status               | Duration  |

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

| (initialization)     | 0.0000475 |

| checking permissions | 0.0000077 |

| Opening tables       | 0.000026  |

| System lock          | 0.0000042 |

| Table lock           | 0.0000045 |

| init                 | 0.0000205 |

| Updating             | 0.0000787 |

| end                  | 0.0000567 |

| query end            | 0.000004  |

| freeing items        | 0.0000067 |

| closing tables       | 0.000004  |

| logging slow query   | 0.000002  |

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

12 rows in set (0.00 sec)

二，timestampdiff来查看测试时间

mysql> set @d=now();

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from comment;

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

| c_id | mid | name | content    | datetime            |

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

|    1 |   1 | ??   | 2222222211 | 2010-05-12 00:00:00 |

|    2 |   1 | ??   | ????(??)   | 2010-05-13 00:00:00 |

|    3 |   2 | tank | ??????     | 0000-00-00 00:00:00 |

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

3 rows in set (0.00 sec)

 

mysql> select timestampdiff(second,@d,now());

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

| timestampdiff(second,@d,now()) |

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

|                              0 |

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

1 row in set (0.00 sec)

这种方法有一点要注意，就是三条sql语句要尽量连一起执行，不然误差太大，根本不准

set @d=now();

select * from comment;

select timestampdiff(second,@d,now());

如果是用命令行来执行的话，有一点要注意，就是在select timestampdiff(second,@d,now());后面，一定要多copy一个空行，不然最后一个sql要你自己按回车执行，这样就不准了。

其实我觉得吧，真正要我们关心的是，那些查询慢的sql,因为真正影响速度的是他们，关于慢查询的东西，有空写一下。

作者:海底苍鹰
地址:http://blog.51yip.com/mysql/968.html