In-depth Mysql slow query settings in the detailed _mysql

In web development, we often write some SQL statements, a bad SQL statement may make your entire program is very slow, more than 10 seconds, the average user will choose to close the page, how to optimize the SQL statements to run longer time SQL statements to find out? MySQL provides us with a very good function, that is slow query! The so-called slow query is through the set to record more than a certain amount of time SQL statements! So how do you apply a slow query?

1. Turn on MySQL slow query log function
By default, MySQL does not record SQL statements that exceed a certain execution time. To turn on this feature, we need to modify the MySQL configuration file, under Windows Modify My.ini,linux to modify the my.cnf file, in [mysqld] finally add the following command:

Copy Code code as follows:

Slow_query_log
Long_query_time = 1

2. Test Slow query log function
(1) Enter the MySQL console, execute the following statement:

Copy Code code as follows:

Select Sleep (2);

Mysql> Select Sleep (2);
+----------+
| Sleep (2) |
+----------+
| 0 |
+----------+
1 row in Set (2.12 sec)
(2) To view the slow query log file Think-slow.log, found at the end of the file:

Copy Code code 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) Slow_query_log This sentence is open record slow query function, slow_query_log=0 close; slow_query_log=1 open (this 1 can not write)

(2) Long_query_time = 1 This sentence is a SQL execution statement that records more than 1 seconds.

(3) So where does this log file reside?
The default is placed in the MySQL data directory, and file name Host_name-slow.log is the host name-slow.log, such as in the author's development machine is think-slow.log (because I used the ThinkPad, hehe)

(4) If the log file does not want to be placed in the data directory, we can specify the stored directory and log file name by configuring the following:
Slow_query_log_file=file_name
where file_name is your store log directory and file name, here attention to some of the information may be log-slow-queries=file_name, this in the mysql5.5 version has become obsolete!

4. How to record a slow query record below 1s?
Before the MySQL5.21 version, the unit of the Long_query_time parameter is seconds, and the default value is 10. This is equivalent to say that only the minimum execution time of more than 1 seconds of queries, how to record query time more than 100 milliseconds of SQL statement records? The mysql5.21+ version supports millisecond logging after the
(1) Enter the MySQL console and run the following SQL statement:

Copy Code code as follows:

Set Global long_query_time=0.1

This sentence is set to record slow query over the time of 100ms SQL, remember to restart MySQL to be effective!
(2) test
Enter the MySQL console and execute the following SQL statement:

Copy Code code as follows:

Select Sleep (0.5);

Looking at the slow query log file, we see the last addition of the new information:

Copy Code code 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);