《MYSQL數(shù)據(jù)庫(kù)查看mysql語(yǔ)句運(yùn)行時(shí)間的2種方法》要點(diǎn)：
本文介紹了MYSQL數(shù)據(jù)庫(kù)查看mysql語(yǔ)句運(yùn)行時(shí)間的2種方法，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。

網(wǎng)站運(yùn)行很慢的時(shí)候,我就特別起知道為什么這么慢,所以我查啊查,數(shù)據(jù)庫(kù)絕對(duì)是很重要的一部分,里面運(yùn)行的sql是絕對(duì)不能放過(guò)的.平時(shí)做項(xiàng)目的時(shí)候,我也會(huì)注意sql語(yǔ)句的書(shū)寫(xiě),寫(xiě)出一些高效的sql來(lái),所以我會(huì)經(jīng)常測(cè)試自己寫(xiě)的sql語(yǔ)句.我把我知道的二個(gè)方法,總結(jié)一下發(fā)出來(lái).MYSQL數(shù)據(jù)庫(kù)

一,show profiles 之類(lèi)的語(yǔ)句來(lái)查看MYSQL數(shù)據(jù)庫(kù)

1,查一下profile是不是打開(kāi)了,默認(rèn)是不打開(kāi)的.MYSQL數(shù)據(jù)庫(kù)

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里面沒(méi)有東西,所以公司的電腦里面profile是沒(méi)有打開(kāi)的,我查看了一下mysql變量,果然是OFF的.MYSQL數(shù)據(jù)庫(kù)

2,開(kāi)啟profile,然后測(cè)試MYSQL數(shù)據(jù)庫(kù)

開(kāi)啟profileMYSQL數(shù)據(jù)庫(kù)

mysql> set profiling=1; 
Query OK, 0 rows affected (0.00 sec)

測(cè)試如下：MYSQL數(shù)據(jù)庫(kù)

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來(lái)查看測(cè)試時(shí)間MYSQL數(shù)據(jù)庫(kù)

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) 

這種方法有一點(diǎn)要注意,就是三條sql語(yǔ)句要盡量連一起執(zhí)行,不然誤差太大,根本不準(zhǔn)MYSQL數(shù)據(jù)庫(kù)

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

如果是用命令行來(lái)執(zhí)行的話,有一點(diǎn)要注意,就是在select timestampdiff(second,@d,now());后面,一定要多copy一個(gè)空行,不然最后一個(gè)sql要你自己按回車(chē)執(zhí)行,這樣就不準(zhǔn)了.MYSQL數(shù)據(jù)庫(kù)

其實(shí)我覺(jué)得吧,真正要我們關(guān)心的是那些查詢慢的sql,因?yàn)檎嬲绊懰俣鹊氖撬麄?關(guān)于慢查詢的東西,有空寫(xiě)一下.MYSQL數(shù)據(jù)庫(kù)

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.snjht.com/jiaocheng/2419.html