Dig into the mysql slow query Settings

In the development of web, we often write some SQL statements, a bad SQL statement may make your entire program very slow, more than 10 seconds to 1 users will choose to close the web page, how to optimize the SQL statement to find those running longer SQL statements? MySQL gives us a nice feature, which is slow queries! A slow query is a set to record SQL statements that take longer than a certain amount of time! So how do you apply slow queries?

1. Turn on the slow query log function of MySQL
By default, MySQL does not record SQL statements that take more than 1 specified execution time. To enable this function, we need to modify the configuration file of MySQL, modify my.ini under windows, modify my.cnf under Linux, and add the following command at the end of [mysqld] :

slow_query_log
long_query_time = 1

2. Test the slow query log function
(1) enter the MySql console and execute the following statement:

select sleep(2);

mysql > select sleep(2);
+----------+
| sleep(2) |
+----------+
| 0 |
+----------+
1 row in set (2.12 sec)
(2) check the slow query log file think-slow.log, and find at the end of the file:

# 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. :
(1)slow_query_log; slow_query_log=0; slow_query_log=1 on (this 1 can not be written)

(2)long_query_time = 1 is an SQL execution statement that records more than 1 second

(3) where does the log file reside?
mysql data by default, and the file name is host_name-slow. log is hostname -slow. log, for example, THINK-slow. log on my development machine (Thinkpad, hehe).

(4) if the log file is not to be placed in the data directory, we can specify the directory to be stored and the log file name through the following configuration:
slow_query_log_file=file_name
Among them, file_name is the directory and file name of your log. Please note here that some data may be log-slow-queries =file_name, which is out of date in mysql5.5!

4. How do I record slow queries below 1s?
Before version MySQL5.21, the unit of long_query_time parameter was seconds, and the default value was 10. This is equivalent to saying that at a minimum, you can only record queries that take more than 1 second to execute. How can you record SQL statements that take more than 100 milliseconds to execute? Millisecond recording is supported after mysql 5.21+
(1) enter the MySql console and run the following sql statement:

set global long_query_time=0.1

Set SQL to record slow queries over time 100ms, remember to restart mysql to take effect!
(2) test
Enter the mysql console and execute the following sql statement:

select sleep(0.5);

Looking at the slow query log file, we see the new information added at the end:

# 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);