MySQL Slow query log

MySQL slow query log can record the query time is too long SQL, for performance problem positioning is very important, this article is intended to introduce the slow query log management use.

Parameters

1, Slow_query_log:on indicates slow query log on, off indicates slow query log off;

2, Slow_query_log_file: Slow query log file;

3, Long_query_time: Indicates the execution time exceeds the number of seconds of SQL records to the slow query log;

4, Log_queries_not_using_indexes:on indicates that the slow query log will record the execution of the non-indexed sql,off is not recorded;

5, Log_throttle_queries_not_using_indexes: Limit the number of non-indexed SQL recorded per minute;

6, Log_output: Slow query logging mode, file represents a record file, table represents a record table, file/table represents the simultaneous recording of files, tables.

Actual combat

SetGlobal Log_output='file,table';SetGlobal Long_query_time=2;Create Databasecoshaho003; Usecoshaho003;show Variables like 'Long_query_time';SelectSleep3);Select *  fromMysql.slow_log;

It is important to note that the set global long_query_time=2 is executed, and a new database is required to take effect.

The slow query log information is as follows:

Time                 Id Command    argument# time:2017-08-13t04:48:11.591942z# [email protected]: root[root] @ localhost [ 127.0.0.1]  Id:     4# query_time:3.015729  lock_time:0.000000 rows_sent:1  rows_examined:0use coshaho001; SET Timestamp=1502599691;select Sleep (3) LIMIT 0, 1000;# time:2017-08-13t04:51:41.790726z# [email protected]: root[root ] @ localhost [127.0.0.1]  Id:     4# query_time:3.000108  lock_time:0.000000 rows_sent:1  rows_examined: 0use coshaho003; SET Timestamp=1502599901;select Sleep (3) LIMIT 0, 1000;

MySQL Slow query log