In-depth explanation of mysql slow query settings

In web development, we often write some SQL statements. A bad SQL statement may make your entire program very slow. After 10 seconds, users usually choose to close the webpage, how to optimize SQL statements to find out SQL statements that have been running for a long time? MySQL provides us with a good function, that is, slow query! The so-called slow query is to set up to record SQL statements that exceed a certain period of time! So how to apply the slow query?

1. Enable the slow query log feature of MySQL
By default, MySQL does not record SQL statements that have been executed for a certain period of time. To enable this function, we need to modify the MySQL configuration file, my. ini in windows, my. cnf in Linux, and add the following command at the end of [mysqld:
Copy codeThe Code is as follows:
Slow_query_log
Long_query_time = 1

2. Test the slow query log function.
(1) log on to the MySql console and run the following statement:
Copy codeThe Code is as follows:
Select sleep (2 );

Mysql> select sleep (2 );
+ ---------- +
| Sleep (2) |
+ ---------- +
| 0 |
+ ---------- +
1 row in set (2.12 sec)
(2) view the think-slow.log of the slow query log file, found at the end of the file:
Copy codeThe Code is as follows:
# Time: 121120 20:06:23
# User @ Host: root [root] @ localhost [127.0.0.1]
# Query_time: 2.104120 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp = 1353413183;
Select sleep (2 );

3. Explanation:
(1) The slow_query_log clause enables slow query of records. slow_query_log = 0 is disabled; slow_query_log = 1 is enabled (this 1 can be left empty)

(2) long_query_time = 1 indicates the SQL Execution statement that records more than 1 second

(3) Where is the log file stored?
The default is placed in the mysql data DIRECTORY, and the file name is the host_name-slow.log that is the host name-slow. log, for example, in the author's development machine is the THINK-slow.log (because I used Thinkpad, huh, huh)

(4) If you do not want to store log files in the data directory, you can specify the directory to store and the log file name through the following Configuration:
Slow_query_log_file = file_name
Here, file_name is the Directory and file name of your logs. Note that some data may be log-slow-queries = file_name, Which is outdated in MySQL!

4. How to record slow query records less than 1 s?
The unit of the long_query_time parameter before MySQL is seconds. The default value is 10. This is equivalent to a query with a minimum execution time of more than 1 second. How can we record SQL statement records with a query time of more than 100 milliseconds? Versions later than MySQL support millisecond recording
(1) log on to the MySql console and run the following SQL statement:
Copy codeThe Code is as follows:
Set global long_query_time = 0.1

This is an SQL statement that records slow queries for more than Ms. Remember to restart mysql to take effect!
(2) test
Log on to the mysql console and run the following SQL statement:
Copy codeThe Code is as follows:
Select sleep (0.5 );

View the slow query log file. We can see the last added new information:
Copy codeThe Code is as follows:
# Time: 121120 20:42:06
# User @ Host: root [root] @ localhost [127.0.0.1]
# Query_time: 0.500028 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp = 1353415326;
Select sleep (0.5 );