What is mysql slow query statement?

In mysql, slow query statements refer to statements whose response time exceeds the threshold in the slow query log. The specific running time exceeds the "long_query_time" value of the SQL statement; you can set the "slow_query_log" parameter to set Whether to enable slow query, "1" means on, "0" means off.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

What is mysql slow query statement

MySQL's slow query log is a log record provided by MySQL. It is used to record statements whose response time exceeds the threshold in MySQL, specifically referring to running SQL that exceeds the long_query_time value will be recorded in the slow query log.

The default value of long_query_time is 10, which means running statements for more than 10S.

By default, the Mysql database does not start the slow query log. We need to manually set this parameter. Of course, if it is not needed for tuning, it is generally not recommended to start this parameter, because turning on the slow query log will cause or More or less will have a certain performance impact. The slow query log supports writing log records to files and also supports writing log records to database tables.

Explanation of related parameters of MySQL slow query:

• slow_query_log: Whether to turn on the slow query log, 1 means on, 0 means off.

• log-slow-queries: Old version (version below 5.6) MySQL database slow query log storage path. You can not set this parameter, and the system will default to a default file host_name-slow.log

• slow-query-log-file: New version (5.6 and above) MySQL database Slow query log storage path. You can not set this parameter, the system will default to a default file host_name-slow.log

• long_query_time: slow query threshold, when the query time is longer than the set threshold, Keep a log.

• log_queries_not_using_indexes: Queries that do not use indexes are also logged to the slow query log (optional).

• log_output: Log storage method. log_output='FILE' means to save the log to a file, and the default value is 'FILE'. log_output='TABLE' means to store the log in the database.

slow_query_log

By default, the value of slow_query_log is OFF, which means that the slow query log is disabled. It can be turned on by setting the value of slow_query_log. , as shown below:

mysql> show variables  like '%slow_query_log%';
 +---------------------+-----------------------------------------------+
 | Variable_name       | Value                                         |
 +---------------------+-----------------------------------------------+
 | slow_query_log      | OFF                                           |
 | slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |
 +---------------------+-----------------------------------------------+

rows in set (0.00 sec)

mysql> set global slow_query_log=1;
 Query OK, 0 rows affected (0.09 sec)

Use set global slow_query_log=1 to enable the slow query log, which only takes effect on the current database, MySQL It will become invalid after restarting.

If you want it to take effect permanently, you must modify the configuration file my.cnf (the same is true for other system variables).

my.cnf To add or modify the parameters slow_query_log and slow_query_log_file, as shown below

slow_query_log = 1
slow_query_log_file = /tmp/mysql_slow.log

Then restart the MySQL server.

slow_query_log_file

This parameter is used to specify the storage path of the slow query log. The default is the host_name-slow.log file.

mysql> show variables like 'slow_query_log_file';
 +---------------------+-----------------------------------------------+
 | Variable_name       | Value                                         |
 +---------------------+-----------------------------------------------+
 | slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |
 +---------------------+-----------------------------------------------+
 1 row in set (0.00 sec)

Recommended Learning: mysql video tutorial

The above is the detailed content of What is mysql slow query statement?. For more information, please follow other related articles on the PHP Chinese website!