Two Methods for viewing the mysql statement running time

When the website runs slowly, I know why it is so slow. So I checked and checked that the database is definitely a very important part, and the SQL statements running in it cannot be ignored. When I work on projects, I also pay attention to the writing of SQL statements and write some efficient SQL statements. Therefore, I will often test my own SQL statements. I will summarize the two methods that I know and send them out.

1. View statements such as show profiles.

1. Check whether the profile is enabled. It is disabled by default.

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)

I checked that there was nothing in profiles, so the profile in the company's computer was not opened. I checked the mysql variable and it was OFF.

2. Enable profile and Test

Enable profile

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

The test is as follows:

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)

2. view the test time using 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) 

One thing to note about this method is that the three SQL statements should be executed together as much as possible. Otherwise, the error is too large and not accurate at all.

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

If you use the command line for execution, note that you must copy an empty line after select timestampdiff (second, @ d, now, otherwise, you must press enter to execute the last SQL statement.

In fact, I think we really need to care about the slow query SQL statements, because they really affect the speed and write about slow queries.