Detailed analysis of the MySQL slow log opening method and storage format

In development projects, we can monitor SQL with efficiency issues through MySQL's slow query log.

The following commands enable logging:

Find the directory for slow query log files

show variables like 'slow_query_log'

Set the slow log file directory

set global slow_query_log_file='home/mysql/sql_log/mysql_slow.log'

Whether to record SQL statements that do not use indexes in the log

set global log_queries_not_using_indexes=on

Set the number of seconds that SQL statements that exceed this limit are recorded in the log.

set global long_query_time=1

Here this time is usually quite large, it is best to set it to 0.01s, depending on your own situation.

Now let's actually execute the command:

First check the log file location:

It can be seen that our slow_query_log is off, which means that we have not enabled the slow query log.

Use show variables like '%log' command to view the status of the log. If our log_queries_not_using_indexes option is OFF,
Then we need to set it to on.

Then use the command to check whether the value of long_query_time is set. If it is 0, it means that all queries will be recorded in the log.
It should be noted that in a production environment, there may be a large amount of logs in a short period of time.

Let's take a look at the log storage format:

The format is as follows:

#Query execution time#Time:140606 12:30:17
#SQL execution host information #User@Host:root[root] @ localhost []
#SQL execution information, query execution time, lock time, number of rows sent and number of rows scanned #Query_time:0.000031 Locak_time:0.000000 Rows_sent:0 Rows_examined:0 
#SQL execution time SET timestamp=1402029017;
#Executed SQL content show tables;

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.