Use mysqldumpslow to analyze and execute slow SQL statements _ MySQL

Use mysqldumpslow to analyze and execute slow SQL statements mysqldump

BitsCN.com

Use mysqldumpslow to analyze and execute slow SQL statements

Mysqldumpslow-s c-t 10/var/log/mysql/mysql-slow.log

A function of MySQL database is to record slow SQL statements in the form of logs. This function is disabled by default. To enable this function, you need to modify my. cnf or add some parameters when starting mysql. If you modify it in my. cnf, add the following lines:

Long_query_time = 1

Log-slow-queries =/var/youpath/slow. log

Log-queries-not-using-indexes

Long_query_time indicates how long the SQL statement will be logged after execution, which is 1 second.

Log-slow-queries is set to write logs there, which can be blank. The system will give a default file host_name-slow. log, and the log I generated will be in the mysql data directory.

Log-queries-not-using-indexes is the literal meaning, and no index query is used in log.

Turn on the above parameters and shut down after a period of operation, saving the trouble of affecting the production environment.

Next is the analysis, my file here is called host-slow.log.

Mysqldumpslow-help first, mainly used

-S ORDER what to sort by (t, at, l, al, r, ar etc), 'at' is default

-T NUM just show the top n queries

-G PATTERN grep: only consider into Ts that include this string

-S is the order, indicating that the write is not detailed enough. I used it, including reading the code, mainly including c, t, l, r, ac, at, al, ar is sorted by the number of queries, time, lock time, and the number of returned Records. the time flashback with a is added in front.

-S: this is the sorting parameter and can be selected as follows:

Al: average lock time

Ar: average number of returned Records

At: average query time

C: Count

L: lock time

R: return record

T: query time

-T indicates the top n, that is, the number of data records returned.

-G, followed by a regular expression matching mode, which is case insensitive.

Mysqldumpslow-s c-t 20 host-slow.log

Mysqldumpslow-s r-t 20 host-slow.log

The preceding command shows the 20 most frequently accessed SQL statements and the 20 most returned SQL statements in the record set.

Mysqldumpslow-t 10-s t-g left join host-slow.log

Return the first 10 SQL statements containing the left join according to the time.

With this tool, you can find out which SQL statements are performance bottlenecks for optimization, such as adding indexes and implementing the application.

BitsCN.com