網站運行很慢的時候,我就特別起知道為什麼這麼慢,所以我查啊查,數據庫絕對是很重要的一部分,裡面運行的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,因為真正影響速度的是他們,關於慢查詢的東西,有空寫一下。