MySQL Slow Query log

MySQL has a log, called the slow query log , is mainly used to record some time-consuming query

For With this log we can analyze what operations are impacting performance, and we need to

Perform some optimization measures.

View open Status

Above is the version of MySQL5.7 I installed under Windows, we can see that this version is open for slow query. I installed the MySQL5.7 in Yum mode under CentOS6.9. The slow query log is not turned on by default. Regardless of whether the default is open to us, we need to understand how the slow query log is opened and the way it is opened is very simple. Find the configuration file for MySQL, under Windows My.ini,linux is my.cnf. The following configuration is possible.

Slow-query-log=1


Slow_query_log_file= "Mysql-slow.log"


long_query_time=10

The first line is to specify that the slow query log is turned on

The second line is the path to the specified slow query log

The third line is to specify that the query time is greater than the number of records, but the milliseconds, that is, operations greater than 10ms will be logged.

After the configuration is complete, restart MySQL to take effect.

Here's a look at the contents of the slow query log

1C:\Program files\mysql\mysql Server 5.7\bin\mysqld.exe, version:5.7.16-Log (MySQL2 Community Server (GPL)). Started with:3TCP port:3306, Named Pipe: (NULL)4 Timeid CommandArgument5#Time: 2017-07-07t06:35:46. 995201Z6#[email protected]: root[root] @ localhost [:: 1] Id:107#Query_time: 12.522116 lock_time:0.000501 rows_sent:0 rows_examined:483968Use test;8SET timestamp=1499409346;9 INSERT INTO Test (id,name) (select UUID () id,name from test);Ten#Time: 2017-07-07t06:36:15. 258316Z One#[email protected]: root[root] @ localhost [:: 1] Id:10 A#Query_time: 24.543267 lock_time:0.000501 rows_sent:0 rows_examined:967936 SET timestamp=1499409375; - INSERT INTO Test (id,name) (select UUID () id,name from test); -#Time: 2017-07-07t06:37:15. 021922Z the#[email protected]: root[root] @ localhost [:: 1] Id:10 -#Query_time: 56.283040 lock_time:0.000499 rows_sent:0 rows_examined:1935872 SET timestamp=1499409435; - INSERT INTO Test (id,name) (select UUID () id,name from test); -#Time: 2017-07-07t06:40:07. 866659Z +#[email protected]: root[root] @ localhost [:: 1] Id:10 -#Query_time: 133.866927 lock_time:0.000000 rows_sent:0 rows_examined:3871744 SET timestamp=1499409607; +INSERT into Test (id,name) (select UUID () id,name from Test);

We can see that in 2017-07-07, there were multiple slow queries generated. Separate a group, as follows

1 #Time: 2017-07-07t06:35:46. 995201Z2 #[email protected]: root[root] @ localhost [:: 1]  Id:103 #Query_time: 12.522116 lock_time:0.000501 rows_sent:0 rows_examined:483968 use test; 4 SET timestamp=1499409346; 5 INSERT INTO Test (id,name) (select UUID () id,name from Test);

Query_time means time-consuming.

Here are some operations, the main operation of which is an insert

This is the slow query log.

MySQL Slow Query log