MySQL slow query log-related configuration and usage.

The MySQL slow query log provides query information over a specified time threshold, providing a major reference for performance optimization, and is a very useful feature.
MySQL Slow query log is very simple to open and configure, you can specify the recorded file (or table), over the time threshold, etc. can be recorded to slow SQL,
To be honest, the MySQL configuration always gives a very refreshing feeling compared to SQL Server's trace or Extended events (although this is not the only effect).

1, slow query log open

Under normal circumstances, only need to add Slow_query_log = 1 configuration in the configuration file, that is, open the slow query log, without specifying Slow_query_log_file, will automatically generate a host name + ' slow '. log file.

　　

2, by default, the time threshold for slow query logging is 10s

　　

By default, specify Slow_query_log = 1 to start MySQL, you can turn on slow query, automatically generate a default host name + + ' slow '. log file to record more than 10s of slow query execution.

You can also explicitly specify the log file name for a slow query (there is no automatic creation) and a time threshold (not the default of 10s) to record slow queries.

　　

Note When specifying Long_query_time in the configuration file, no time units are required, only one value is required, for example, 1 represents 1s, and if a time unit is taken, the service will fail to start.

　　

The following is an example of a slow SQL that is logged to a log file

　　

3, record slow query log to table

Configuration: To add a log_output configuration, you can log slow queries to the table.

There is a default Slow_log table below the MySQL library, and you can record the slow query log to the table directly by Slow_query_log_file = Slow_log.

　　

Log to the slow SQL as follows, you can find that Sql_text is a binary information, not the original SQL text

　　

Can be converted by the CONVERT function.

　　

About the difference between a slow query record and a log file and a table:

1, slow query logging to log files and tables, the record itself is not very different, if it is recorded in the table, slow query execution time information can not be accurate to subtle,

2, if the slow query information recorded in the table, easy to query, but because it is structured data, may be more slowly than recorded in the slow query log file (flat text file) a little bit slower (personal guess), if it is recorded to a file, need to Mysqldumpslow tool parsing.

3, slow query does not log execution failed query, such as Long_query_time set to 10 (10 seconds), one query more than 10 seconds, but because of other reasons for failure, MySQL slow query will not be able to log this query information.

MySQL slow query log-related configuration and usage.