MySQL Slow Query

Briefly:

The method of analyzing the performance of MySQL statement query in addition to using the EXPLAIN output execution plan, you can also let the MySQL record query over a specified time of the statement, we will exceed the specified time of the SQL statement query is called "Slow query."

It records all SQL statements that execute more than long_query_time time, helping you to find SQL that executes slowly, so we can optimize these SQL.

In the optimization of MySQL, usually need to analyze the database, common analysis means have slow query log, EXPLAIN analysis query, profiling analysis and Show command query system state and system variables, through the location analysis performance bottleneck, can better optimize the performance of the database system.

Ideas:

--Analyze the slow query log--view table structure, table state--View table index--Parse SQL statement--explain modify the SQL statement---Validate the result

Slow query Log configuration:

1. Add the following options in the My.ini configuration file:

Log-slow-queries=master-0-slow.log Slow Query Log location

Log-queries-not-using-indexes=on queries that do not use indexes are also counted in the slow query log

Long_query_time=1 a slow query log when the query statement is greater than 1 seconds

-linux MySQL Open slow Query method:

MySQL configuration file in the Linux system is generally my.cnf, my path is/ETC/MY.CNF, you based on the path of the compiled installation to find VI/ETC/MY.CNF

The same is added under [mysqld]:

Log-slow-queries=/var/lib/mysql/slowquery.log

long_query_time=2

Log-queries-not-using-indexes

Here is no longer explained, plus after restarting MySQL, you can go to/var/lib/mysql to see if there are slowquery.log generated. cd/var/lib/mysql/

2. Query configuration information in command Window through command query

① view query statement execution time greater than how many seconds count into slow query log

Dynamic configuration of:mysql> set long_query_time=1 by command line;

② to see if the slow query log opens and holds the path

Dynamically configure:mysql> set global slow_query_log= ' on ' via the command line

Slow query log format

# time:120331 10:05:48

# [email protected]: Root[root] @ 91sk-b49337164e [10.10.10.99]

# query_time:14.031250 lock_time:0.218750 rows_sent:0 rows_examined:90785 SET timestamp=1333159548; Delete from OrderInfo;

This is a slow query log, took 14.03125 seconds, locked for 0.218750 seconds, returned 0 lines, a total of 90785 lines

To view the slow query log with the Mysqldumpslow command:

1. Since MySQL commands mysqldumpslow.pl using Perl scripts, the Perl environment needs to be installed. Download the ActivePerl.exe installation package and install it.

2. Execute the mysqldumpslow command under DOS command line

Use the Mysqldumpslow command to find the top 10 SQL statements in the log for the highest number of records in the F:\master-0-slow.log slow query log. And put them in the F:\master-slow-0.txt file.

Mysqldumpslow Command parsing:

-S, which means the sort, C, T, L, R are in accordance with the number of records, time, query time, the number of records returned to sort, AC, at, AL, AR, indicating the corresponding flashbacks;

-T, which is the meaning of top N, which is to return the data of the previous number of bars;

-G, you can write a regular matching pattern, the case is not sensitive;

For example: returns the maximum 10 queries for a recordset.

The first 10 lines sorted by time contain a query statement with a left connection.

MySQL Slow Query