MySQL enable slow query logging method

In MySQL, the definition time of the slow query is specified by the mysql built-in parameter variable long_query_time, its default value is 10 (in seconds), we can show variables like ' long_query_time '; Directive to view the information for this parameter variable:
The default value for Long_query_time is 10 seconds

However, in the process of development, we think that the slow query definition time is not as long as 10 seconds, depending on the different requirements of different projects, we generally set the slow query time to the definition of 1-5 seconds. We can set the value of the long_query_time variable using the instruction set long_query_time = the number of seconds.
Set the Long_query_time value to 1 seconds

After modifying the long_query_time parameters, we also need to let MySQL record the slow query log information. Because, by default, MySQL does not record slow query log information. To record a slow query log, we need to enter the MySQL installation directory in directory by command line (or add the directory to the PATH environment variable) and restart MySQL using the following command:

The code is as follows	Copy Code
The part of the #中括号 [] is optional, file_name represents the log file path #在5.5 and above MySQL, use the following command to start: Mysqld--show-query-log[=1] [--show-query-log-file=file_name] #在5.0, 5.1, and low version of MySQL, use the following command to start: Mysqld--log-slow-queries[=file_name]

In the above command, if you do not specify a log file name, The log file name defaults to the hostname-slow.log, if no file path is specified or the specified file path is not an absolute path, the log file is stored by default in the directory specified by the parameter DataDir in the MySQL profile My.ini.

Alternatively, you can configure the command line startup command above to the [Mysqld] node in My.ini so that you do not have to manually type the above command each time you start.

The code is as follows	Copy Code
[Mysqld] #设置慢查询界定时间为1秒 Long_query_time=1 Versions of #5.0, 5.1 are configured with the following options Log-slow-queries= "Mysql_slow_query.log" #5.5 and above are configured with the following options Slow-query-log=on Slow_query_log_file= "Mysql_slow_query.log"

Note: Although the name of the slow query contains only "queries," it does not actually merely represent select query operations, such as INSERT, UPDATE, DELETE, call, and other DML operations, as long as it is more than the specified time, can be called "Slow query", And will be recorded in the slow query log.

Use explain to get execution plan information for a SELECT statement

At this point, we need to use the explain directive to get the details of the execution of the SELECT statement. In MySQL database, the usage of the explain instruction is very simple, as follows:

EXPLAIN SELECT statement

The code is as follows	Copy Code
-for example: EXPLAIN SELECT * from admin WHERE user_id = 1;

In the case of an Oracle database, we can also use the following SQL statement to get the execution plan information for a SELECT statement:

The code is as follows	Copy Code
EXPLAIN plan for SELECT statement -for example: EXPLAIN plan for SELECT * from admin WHERE user_id = 1;

Here, we take MySQL as an example, the above explain instruction is executed in the database, and the following result will be output (user_id is the primary key column of the admin table):
Explain get SELECT statement execution plan details

Explain get SELECT statement execution plan details

To further illustrate the implications of the above execution plan information, we execute the formatted explain command again

The code is as follows	Copy Code
EXPLAIN SELECT * from admin WHERE user_id = 1G;

(g indicates formatted output 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)