Configuration and usage related to MySQL slow query log

The MySQL slow query log provides query information that exceeds the specified time threshold, providing a main reference for performance optimization. It is a very practical function.
The opening and configuration of the MySQL slow query log is very simple and can be specified Recorded files (or tables), exceeded time thresholds, etc. can be recorded to slow sql.
To be honest, compared with sqlserver's trace or extended events (although the role of these two is not only that), MySQL's configuration It always gives people a very refreshing feeling.

1. To open the slow query log, under normal circumstances, you only need to add the slow_query_log = 1 configuration in the configuration file, that is, to open the slow query log. If slow_query_log_file is not specified, a file with the host name + will be automatically generated. 'slow'.log file

　2. By default, the time threshold for recording slow queries is 10s

　

　By default, slow_query_log is specified = 1, when you start MySQL, you can turn on slow queries, and automatically generate a default file with the host name ++'slow'.log to record slow queries that exceed execution for more than 10 seconds.

You can also explicitly specify the slow query log file name (it will be automatically created if it does not exist) and the time threshold for recording slow queries (non-default 10s).

　

Note that when specifying long_query_time in the configuration file, no time unit is required, only a value is required, such as 1 represents 1s. If the time unit is specified, the service will not be able to start up.

　

　The following is an example of slow sql recorded in the log file

　

　3. Record the slow query log to Table

Configuration: You need to add a log_output configuration so that slow queries can be recorded in the table

There is a default slow_log table under the mysql library , you can directly set slow_query_log_file = slow_log to record the slow query log into the table.

　

The recorded slow sql is as follows. It can be found that sql_text is a binary information, not the original sql text

It can be converted through the CONVERT function Just one click.

　

About the difference between slow queries recorded in log files and tables:

1. Slow queries are recorded in log files and tables, and there is no difference in the records themselves. Large, if it is recorded in the table, the execution time information of the slow query cannot be accurate to the subtle,

2. If the slow query information is recorded in the table, it is convenient for query, but because it is structured data, it may It will be a little slower than recording in a slow query log file (flat text file) (personal guess). If it is recorded to a file, the mysqldumpslow tool needs to be parsed.

3. Slow queries do not record queries that fail to execute. For example, long_query_time is set to 10 (10 seconds). If a query exceeds 10 seconds, but fails to execute due to other reasons, MySQL's slow queries will not be recorded. This query information.

The above is the detailed content of Configuration and usage related to MySQL slow query log. For more information, please follow other related articles on the PHP Chinese website!