How to enable slow query logging in MySQL

As the programmer may not write the best things, we only need to enable the mysql slow query log function to analyze the performance of each SQL statement. Let's take a look at it.

In MySQL, the definition time of a slow query is specified by the MySQL built-in parameter long_query_time. The default value is 10 (unit: seconds ), we can use the show variables like 'long _ query_time '; command to view the information of this parameter variable:
The default value of long_query_time is 10 seconds.

However, in the process of program development, we believe that the definition time of slow query is not as long as 10 seconds, based on different needs of different projects, we generally set the definition time of slow queries to 1 ~ Within 5 seconds. We can use the command set long_query_time = seconds to set the value of the long_query_time variable.
Set long_query_time to 1 second.

After modifying the long_query_time parameter, we also need to enable MySQL to record slow query log information. Because, by default, MySQL does not record slow query log information. To record slow query logs, run the command line to enter the in directory of the MySQL installation directory (you can also add this directory to the path environment variable) and run the following command to restart MySQL:

The Code is as follows:	Copy code
# The part in [] is optional, and file_name indicates the log file path. # Run the following command in MySQL 5.5 or later versions: Mysqld -- show-query-log [= 1] [-- show-query-log-file = file_name] # Use the following command to start MySQL in lower versions such as MySQL 5.0 and 5.1: Mysqld -- log-slow-queries [= file_name]

In the preceding command, if no log file name is specified, the default log file name is host name-slow. log. If no file path is specified or the specified file path is not an absolute path, the log file is stored in the MySQL configuration file my. under the directory specified by datadir in ini.

In addition, you can configure the above command line startup command to the [mysqld] node in my. ini, so that you do not need to manually type the above command to start each time.

The Code is as follows:	Copy code
[Mysqld] # Set the slow query time to 1 second Long_query_time = 1 #5.0, 5.1, and other versions are configured as follows Log-slow-queries = "mysql_slow_query.log" #5.5 and later versions: Slow-query-log = On Slow_query_log_file = "mysql_slow_query.log"

Note: although the slow query name only contains "query", it does not only represent SELECT query operations, such as INSERT, UPDATE, DELETE, CALL, and other DML operations, as long as it exceeds the specified time, it can be called "Slow query" and will be recorded in the slow query log.

Use explain to obtain the execution plan information of the SELECT statement.

At this time, we need to use the EXPLAIN command to obtain details about the execution of the SELECT statement. In MySQL databases, the usage of the EXPLAIN command is very simple, as shown below:

Explain select statement

The Code is as follows:	Copy code
-- Example: explain select * FROM admin WHERE user_id = 1;

If it is in the Oracle database, we can also use the following SQL statement to obtain the execution plan information of the SELECT statement:

The Code is as follows:	Copy code
Explain plan for select statement -- Example: explain plan for select * FROM admin WHERE user_id = 1;

Here, we take MySQL as an example. Executing the above EXPLAIN command in the database will output the following results (user_id is the primary key column of the admin table ):
Explain to obtain the details of the SELECT statement execution plan

Explain to obtain the details of the SELECT statement execution plan

To further illustrate the meaning of the preceding execution plan information, we will execute the EXPLAIN command with formatting again.

The Code is as follows:	Copy code
Explain select * FROM admin WHERE user_id = 1G;

(G indicates formatting the output result in MySQL), we will get the following information:

* *************************** 1. row ***************************
Id: 1
Select_type: SIMPLE
Table: admin
Type: const
Possible_keys: PRIMARY
Key: PRIMARY
Key_len: 1
Ref: const
Rows: 1
Extra:
1 row in set (0.00 sec)