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

This article introduces the knowledge of "how to configure mysql slow query diary". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

In mysql, a slow query is a slow SQL statement recorded in a log, which refers to the execution of a SQL statement query that exceeds the time threshold set by the "long_query_time" parameter. The slow query is recorded in the slow query log. Through the slow query log, we can find out which query statements are inefficient for optimization.

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

What is slow query?

Slow query, as the name implies, is to record slow SQL statements in the log, which means that mysql records all SQL statement queries that exceed the time threshold set by the long_query_time parameter.

The slow query is recorded in the slow query log. Through the slow query log, we can find out which query statements are inefficient for optimization. This log can be very helpful for the optimization of SQL statements.

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

Slow query log configuration 1. Basic configuration of slow query

Slow_query_log start-stop technology slow query log

Slow_query_log_file specifies the storage path and files for slow log (default is put with data file)

Long_query_time specifies the value of SQL execution time for recording slow query logs (in seconds, default is 10 seconds)

Does log_queries_not_using_indexes record SQL that do not use indexes

Where log_output logs are stored [TABLE] [FILE] [FILE,TABLE]

When a slow query is configured, it records eligible SQL

These include:

Query statement

Data modification statement

SQL has been rolled back

two。 Real fuck:

View the above configuration with 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 the slow query:

Set global long_query_time=0;-10 seconds by default, set here to 0set GLOBAL slow_query_log = 1 for demonstration convenience;-- enable slow log set global log_output='FILE,TABLE'-- logs in project development can only be recorded in log files, not in tables.

After setting up, query some lists and find that there is data in the log file of the slow query.

But on my computer, I don't know why, the sql result can't be executed normally at this time. Can't update.

So we can do it in this way:

Find my.cnf and add the following

# add slow query log _ output=fileslow_query_log=onslow_query_log_file = / tmp/mysql-slow.loglog_queries_not_using_indexes=onlong_query_time = 13. View slow query log

If you want to see which query statements are inefficient, you can get information from the slow query log. Like error logs and query logs, slow query logs are stored as 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)

Since we need to demonstrate that 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 changedmysql > SELECT * FROM tb_student;+----+-+ | id | name | +-- +-+ | 1 | Java | | 2 | MySQL | | 3 | Python | +-+-- + 3 rows in set (0.08 sec)

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

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

The method of deleting slow log is the same as that of general log. You can use the mysqladmin command to delete. You can also delete it manually. The syntax of the mysqladmin command is:

Mysqladmin-uroot-p flush-logs

After executing the command, the command line prompts for a password. After you enter the correct password, the delete operation is performed. The new slow query log directly overwrites the old query log and does not need to be deleted manually.

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

Note: both the general query log and the slow query log use this command, and it is important to note that once this command is executed, both the general query log and the slow query log only exist in the new log file. If you need to back up the old slow log file, you must first rename the old log and then restart the MySQL service or execute the mysqladmin command.

This is the end of the content of "how to configure mysql slow query Diary". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.