MySQL Slow query log process

Original address: http://itlab.idcquan.com/linux/MYSQL/922126.html

The MySQL slow query log is very useful for tracking problematic queries, which can be used to analyze the SQL statements that consume resources in the code implementation, and have a high reference to the optimization of our program. This article is mainly about the slow query log on, log analysis, which is also an important step in the general steps to optimize the SQL program.

1.mysql Slow Query log

Open MySQL Slow query log is very simple, only need to in the MySQL configuration file (Windows system is My.ini,linux system is my.cnf) under the [mysqld] Add

Java code

Log-slow-queries=mysql_slow.log

Long_query_time=3

Where Log-slow-queries is the file name of the log, you can specify the directory, such as Log-slow-queries=d:\mysql_slow.log; Long_query_time defines how long a query we define as a slow query and is recorded in a file specified by Log-slow-queries, where we define a query that exceeds 3 seconds to record.

After the configuration is completed, restart the MySQL server, execute show variables like '%slow% '; check if the slow query log is turned on, if Slow_query_log and Log_slow_queries are on, then the slow query log for the server is

has been opened. Shown below:

Java code

Mysql> Show variables like '%slow% ';

+---------------------+----------------+

| variable_name | Value |

+---------------------+----------------+

| log_slow_queries | On |

| Slow_launch_time | 2 |

| Slow_query_log | On |

| Slow_query_log_file | Mysql_slow.log |

+---------------------+----------------+

4 rows in Set (0.00 sec)

Slow_launch_time has nothing to do with the slow query log, which represents a threshold value for thread create, and if you want to see Long_query_time, you can use the

Java code

Mysql> Show variables like '%long% ';

+-----------------+----------+

| variable_name | Value |

+-----------------+----------+

| Long_query_time | 3.000000 |

+-----------------+----------+

1 row in Set (0.00 sec)

2.mysql Slow Query log analysis

If you build a table, you should not index the text, or you may not have more than 3 seconds of query, the data table is populated with 4,194,304 data.

Let's execute a query for more than 3 seconds, as follows:

Java code

Mysql> select * from Wei where text= ' orange ';

+---------+--------+

| ID | Text |

+---------+--------+

| 4103519 | Orange |

+---------+--------+

1 row in Set (3.79 sec)

Execute one more than 3 seconds and one without more than 3 seconds:

Java code

Mysql> select * from Wei where text= ' Xishizhaohua ';

Empty Set (3.82 sec)

Java code

[mysql> select * from Wei where id=4564;

+------+--------------------+

| ID | Text |

+------+--------------------+

| 4564 | yyyyyyyyyyyyyyyyyy |

+------+--------------------+

1 row in Set (0.02 sec)

The following command can be used to see how many slow queries are present in this session:

Now we can view Mysql_slow.log (win7 default under C:\ProgramData\MySQL\MySQL Server 5.1\data), the contents are as follows, the content is quite clear, including the query spent the statement and time, It also includes information such as the timestamp of the query, where rows_examined is the number of rows checked, which is also helpful for us to optimize.

# time:121017 17:38:54

# [email protected]: root[root] @ localhost [127.0.0.1]

# query_time:3.794217 lock_time:0.000000 rows_sent:1 rows_examined:4194304

SET timestamp=1350466734;

Select * from Wei where text= ' orange ';

# time:121017 17:46:22

# [email protected]: root[root] @ localhost [127.0.0.1]

# query_time:3.819219 lock_time:0.000000 rows_sent:0 rows_examined:4194304

SET timestamp=1350467182;

Select * from Wei where text= ' Xishizhaohua ';

3. Optimization

In fact, to locate the slow query statement has been completed a large, execute explain or desc command to view slow query statements, such as:

The problem is obvious, and the solution is obvious, and the index is built.

Java code

Mysql> CREATE index Text_index on Wei (text);

Query OK, 4194304 rows affected (1 min 58.07 sec)

records:4194304 duplicates:0 warnings:0

Then, when you perform a query operation, the time is significantly less.

Java code

Mysql> select * from Wei where text= ' orange ';

+---------+--------+

| ID | Text |

+---------+--------+

| 4103519 | Orange |

+---------+--------+

1 row in Set (0.33 sec)

Java code

Mysql> select * from Wei where text= ' Xishizhaohua ';

Empty Set (0.01 sec)

MySQL Slow query log process