Mysql slow query analysis

This article mainly introduces the MySQL slow query analysis method, some time ago, I have set a record in the MySQL database to query SQL statements slower than 1 second. Think of a few very set method, there are several parameters of the name of life and death memories can not afford, so re-sort it yourself as a note.

For troubleshooting bottlenecks to find out the performance, the most easy to find and solve the problem is MySQL slow query and did not have to use the index query.

OK, began to find out MySQL implementation is not "cool" SQL statement.

MySQL slow query analysis method one:

This method I am using, huh, huh, I like this kind of real-time.

MySQL5.0 or later version can support the implementation of slower SQL statements recorded.

MySQL> show variables like 'long%';

Note: The long_query_time is used to define how many seconds slower than "slow query"

+ ----------------- + ----------- +
| Variable_name | Value |
+ ----------------- + ----------- +
long_query_time | 10.000000 |
+ ----------------- + ----------- +
1 row in set (0.00 sec)
MySQL> set long_query_time = 1;

Note: I set 1, that is, the execution time of more than 1 second are slow query.

Query OK, 0 rows affected (0.00 sec)
MySQL> show variables like 'slow%';
+ --------------------- + --------------- +
| Variable_name | Value |
+ --------------------- + --------------- +
slow_launch_time | 2 |
slow_query_log | ON |

Note: Whether to open the log records

slow_query_log_file | /tmp/slow.log |

Note: Set to what position

+ --------------------- + --------------- +
3 rows in set (0.00 sec)
MySQL> set global slow_query_log = 'ON'

Note: Turn on logging

Once the slow_query_log variable is set to ON, MySQL starts recording immediately.

/etc/my.cnf Which can set the initial value of the above MySQL global variables.

long_query_time = 1 slow_query_log_file = / tmp / slow.log

MySQL slow query analysis method two:

MySQLdumpslow command

/ path / MySQLdumpslow -sc -t 10 / tmp / slow-log

This will output the most recorded 10 SQL statements, of which:

-s, is that in accordance with the sort, c, t, l, r, respectively, according to the number of records, time, query time, the number of records returned to sort ac, at, al, ar, that the corresponding flashback;

-t, is the meaning of top n, that is, to return to the front of the number of data;

-g, behind you can write a regular matching pattern, case insensitive;

such as

/ path / MySQLdumpslow -sr -t 10 / tmp / slow-log

Get back to the record set up to 10 queries.

/ path / MySQLdumpslow -st -t 10 -g "left join" / tmp / slow-log

Get sorted by time the top 10 which contains the left join the query.

The relevant content is the introduction of slow query analysis of MySQL, hope you can gain something.