热线电话:0598-8259405
你现在的位置是:首页 > 新闻资讯 > 数据库相关> 数据库相关
查看mysql语句运行时间的办法
发布日期:2013/4/16  关注度:2738次   From:三明天意网络

    网站运行很慢的时候,我就特别起知道为什么这么慢,所以我查啊查,数据库绝对是很重要的一部分,里面运行的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要你自己按回车执行,这样就不准了。

 



首  页  |  新闻资讯  |  建站套餐  |  联系我们  |  网站地图  |  网站备案

天意网络技术有限公司   地址:三明市梅列区乾龙新村229幢307室(闽中汽车城内)   
(业务):18020822831  技术部:0598-8259405  闽ICP备14006732号-3
我们的主要服务:三明网站建设 三明网页设计 三明网站制作 三明网站维护 三明网站改版