what is mysql slow query

In mysql, a slow query is a SQL statement that is recorded in the log and runs slowly. It refers to a SQL statement query that exceeds the time threshold set by the "long_query_time" parameter. Slow queries are recorded in the slow query log. Through the slow query log, you can find out which query statements have low execution efficiency for optimization.

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

What is slow query

Slow query, as the name suggests, is to record SQL statements that run slowly in the log. It means that mysql records all SQL statements that exceed the time threshold set by the long_query_time parameter. Inquire.

Slow queries are recorded in the slow query log. Through the slow query log, you can find out which query statements have low execution efficiency for optimization. This log can bring great help to the optimization of SQL statements.

By default, the slow query log is turned off. To use the slow query log function, you must first enable the slow query log function.

Slow query log configuration

1. Slow query basic configuration

## slow_query_log start and stop technology slow query log
• slow_query_log_file specifies the storage path and file of the slow query log (default is placed together with the data file)
• long_query_time specifies the value of the SQL execution time for recording the slow query log (unit: seconds, default 10 seconds)
• log_queries_not_using_indexes Whether to record SQL that does not use indexes
• log_output Where the log is stored [TABLE][FILE][FILE, TABLE]

After configuring a slow query, it will record the The SQL

of the condition includes:

Query statement
• Data modification statement
• SQL that has been rolled back

2. Practical operation:

Check the above configuration through the following command:

show VARIABLES like '%slow_query_log%'
show VARIABLES like '%slow_query_log_file%'
show VARIABLES like '%long_query_time%'
show VARIABLES like '%log_queries_not_using_indexes%'
show VARIABLES like 'log_output'

Set the parameters of slow query:

set global long_query_time=0;   ---默认10秒，这里为了演示方便设置为0
set GLOBAL  slow_query_log = 1; --开启慢查询日志
set global log_output='FILE,TABLE'  --项目开发中日志只能记录在日志文件中，不能记表中

After the setting is completed , query some lists and you can find that there is data in the slow query log file.

But on my computer, I don’t know why, but the sql results cannot be executed normally at this time. It cannot be updated;

So we can use this method:

Find my.cnf and add the following content

# 添加慢查询日志
log_output=file
slow_query_log=on
slow_query_log_file = /tmp/mysql-slow.log
log_queries_not_using_indexes=on
long_query_time = 1

3. Check the slow query log

If you want to see which query statements have low execution efficiency, you can get information from the slow query log. Like error logs and query logs, slow query logs are also stored in the form of text files and can be viewed using ordinary text file viewing tools.

Example 1

Enable the MySQL slow query log function and set the time. The command and execution process are as follows:

mysql> SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected (0.05 sec)

mysql> SET GLOBAL long_query_time=0.001;
Query OK, 0 rows affected (0.00 sec)

Due to the need to demonstrate here, we set the time to 0.001 seconds. SQL statements that take more than 0.001 seconds to execute will be logged.

To query the data in the tb_student table, the SQL statement and execution process are as follows:

mysql> USE test;
Database changed
mysql> SELECT * FROM tb_student;
+----+--------+
| id | name   |
+----+--------+
|  1 | Java   |
|  2 | MySQL  |
|  3 | Python |
+----+--------+
3 rows in set (0.08 sec)

Correspondingly, part of the slow query log is as follows:

# Time: 2020-06-01T01:59:18.368780Z
# User@Host: root[root] @ localhost [::1]  Id:     3
# Query_time: 0.006281  Lock_time: 0.000755 Rows_sent: 2  Rows_examined: 1034
use test;
SET timestamp=1590976758;
SHOW VARIABLES LIKE 'slow_query%';

4. Delete slow query Log

The deletion method of slow query log is the same as the deletion method of general log. Can be deleted using the mysqladmin command. It can also be deleted manually. The syntax of the mysqladmin command is as follows:

mysqladmin -uroot -p flush-logs

After executing this command, the command line will prompt you to enter the password. After entering the correct password, the deletion will be performed. The new slow query log will directly overwrite the old query log, and there is no need to delete it manually.

The database administrator can also manually delete the slow query log. After deletion, the MySQL service needs to be restarted.

Note: Both the general query log and the slow query log use this command. When using it, you must pay attention. Once this command is executed, the general query log and the slow query log will only exist in the new log file. . If you need to back up old slow query log files, you must first rename the old logs, then restart the MySQL service or execute the mysqladmin command.

[Related recommendations:

mysql video tutorial]

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