MySQL mysqldumpslow analysis query slow SQL statement

For most programmers, the most easily discovered and solved problem is MySQL's slow query or no indexed query, so here's how to use the official Mysqldumpslow tool to easily view this information. How to open a slow query for MySQL,
MySQL Slow query record

Show STATUS
Login directly under the command line MySQL run show STATUS; query statements, detailed below

The same statement and show Variables;,show status is to look at the MySQL operation, and the above kind of information through the PMA look at the basic similarity.

Show VARIABLES
Show VARIABLES is looking at MySQL's configuration parameters, and you can also use a VARIABLES like ' key% '

Show Processlist
Show Processlist is a review of ongoing processes and is useful for troubleshooting situations such as locking tables. Under normal circumstances, open MySQL slow query record is also conducive to troubleshooting.

Show OPEN TABLES
Show open tables displays a list of the tables that are currently open.

Mysqladmin status
Use the Mysqladmin tool with MySQL to view status, using the following command

The code is as follows	Copy Code
Mysqladmin-uroot--password= ' password ' status

The results shown are as follows:

The code is as follows	Copy Code
uptime:87117 threads:1 questions:5481626 Slow queries:16 opens:2211 Flush tables:1 Open tables:512 queries per Seco nd avg:62.923

You can also add the-I 5 parameter to automatically refresh every five seconds.

The code is as follows	Copy Code
The code is as follows Copy Code Mysqladmin-uroot--password= ' Password ' status-i 5 Mysqladmin Extended-status You can also use the Mysqladmin-uroot--password= ' password ' extended-status to see more MySQL running information, which is basically the same as the first view of the information.

All right, back to the file.

The code is as follows	Copy Code
Mysqldumpslow command /path/mysqldumpslow-s c-t 10/database/mysql/slow-log

This outputs the 10 SQL statements that have the highest number of records:

-S, is to indicate the way in which C, T, L, R are sorted according to the number of records, time, query time, the number of records returned, AC, at, AL, AR, the corresponding flashback;
-T is the meaning of top N, which is the data that returns the previous number of bars;
-G, you can write a regular matching mode, the case is not sensitive;
Like what

The code is as follows	Copy Code
/path/mysqldumpslow-s r-t 10/database/mysql/slow-log

Gets the 10 queries that return the recordset the most.

The code is as follows	Copy Code
/path/mysqldumpslow-s t-t 10-g "left join"/database/mysql/slow-log

Get the first 10 items in chronological order that contain the query statement with the left connection.

Summary
Using the Mysqldumpslow command can be very clear to all kinds of query statements we need, the MySQL query statement monitoring, analysis, optimization is the first step of MySQL optimization, but also a very important step.