MySQL mysqldumpslow analysis and query slow SQL statements

Nowadays, many programmers do not take into account the large load when designing databases and developing programs. Sometimes, a certain amount of data is stuck, and the more difficult it will be to query it later, next we will provide a mysql tool mysqldumpslow to help you find slow SQL statements.

For most programmers, the easiest problem to find and solve is slow query of MySQL or query without indexes, therefore, we will introduce how to use the official mysqldumpslow tool to conveniently view the information. How to enable MySQL slow query,
Slow query records of MySQL

SHOW STATUS
Log on to MySQL directly under the command line to run the show status command. The query statement is as follows:

The same statement also has show variables; and show status is used to view the MySQL running STATUS, which is similar to the above information viewed through pma.

SHOW VARIABLES
Show variables is used to view the configuration parameters of MySQL. You can also use show variables like 'key %'

SHOW PROCESSLIST
Show processlist is a process that is currently in progress. It is useful for troubleshooting situations such as locked tables. In general, enabling MySQL slow query records is also helpful for troubleshooting.

SHOW OPEN TABLES
Show open tables displays the list of opened TABLES.

Mysqladmin status
Run the following command to view the status using the mysqladmin tool that comes with MySQL:

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

The result is 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 second avg: 62.923

You can also add the-I 5 parameter to automatically refresh it 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 Similarly, you can use mysqladmin-uroot -- password = 'Password' extended-status to view more MySQL running information. This method is basically the same as that of the first method.

Now, return to the file.

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

This will output 10 SQL statements with the maximum number of records, of which:

-S indicates the sorting method. c, t, l, and r are sorted by the number of records, time, query time, and number of returned records, ac, at, al, and ar indicate reverse descriptions;
-T indicates the top n, that is, the number of previous data records returned;
-G, followed by a regular expression matching mode, which is case insensitive;
For example

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

You can obtain up to 10 queries from the returned record set.

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

Obtain the query statements containing the left join in the first 10 results sorted by time.

Summary
The mysqldumpslow command can be used to obtain various query statements that we need very clearly. The monitoring, analysis, and optimization of MySQL query statements is the first step of MySQL optimization and a very important step.