Shulou(Shulou.com)05/31 Report--

This article is about which mysql slow query statement is shared with you. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

In mysql, the slow query statement refers to the statement whose response time exceeds the threshold in the slow query log, and the specific running time exceeds the "long_query_time" value of the SQL statement; you can set the parameter of "slow_query_log" to set whether to open the slow query. "1" means on and "0" means off.

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

What is the mysql slow query statement?

The slow query log of MySQL is a kind of log record provided by MySQL, which is used to record statements whose response time exceeds the threshold in MySQL. Specifically, SQL whose running time exceeds the long_query_ time value will be recorded in the slow query log.

The default value for long_query_time is 10, which means to run statements above 10s.

By default, the Mysql database does not start the slow query log, so we need to set this parameter manually. Of course, if it is not necessary for tuning, it is generally not recommended to enable this parameter, because enabling the slow query log will more or less affect the performance. Slow logging supports writing log records to files as well as database tables.

Explanation of parameters related to MySQL slow query:

Slow_query_log: whether to enable slow query log. 1: enable, 0: disable.

Log-slow-queries: the storage path of slow query logs in MySQL database in the old version (version 5.6 or later). If you do not set this parameter, the system will default to a default file host_name-slow.log

Slow-query-log-file: the storage path of slow query logs in the new version (version 5.6 and above) of MySQL database. If you do not set this parameter, the system will default to a default file host_name-slow.log

Long_query_time: slow query threshold. Log is recorded when the query time exceeds the set threshold.

Log_queries_not_using_indexes: queries that do not use indexes are also recorded in 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 slow log is disabled and can be enabled 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 works for the current database, and will become invalid after MySQL restart.

If you want to take effect permanently, you must modify the configuration file my.cnf (as do other system variables).

My.cnf adds or modifies the parameters slow_query_log and slow_query_log_file, as shown below

Slow_query_log = 1slow_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 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) Thank you for your reading! This is the end of this article on "which is the mysql slow query sentence". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.