Use mysqldumpslow to analyze and execute slow SQL statements

Using mysqldumpslow to analyze and execute slow SQL statement mysqldumpslow-s c-t 10/var/log/mysql/mysql-slow.log www.2cto.com MySQL database has a function that can be recorded in the form of logs to run Ratio slow SQL statement, this function is disabled by default. To enable this function, you need to modify my. cnf or add some parameters when starting mysql. If. add the following lines to the cnf modification: long_query_time = 1 log-slow-queries =/var/youpath/slow. log-queries-not-using-indexes long_query_time indicates how long the SQL statement will be logged down after execution, which is 1 second. Www.2cto.com log-slow-queries sets the log to be written in there, which can be left 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. First, mysqldumpslow-help and below, mainly using-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 orders ts that include this string-s, which is the order, indicating that the write is not detailed enough, I used it, including reading the code, mainly including c, t, l, r and ac, at, al, ar, which are based on the query times, time, the lock time is sorted by the number of returned records, and the flashback of a is added before. -S: sorting parameter. Options include: al: average lock time ar: Average number of returned records at: Average query time c: Count l: Lock time r: return record t: the query time-t indicates the top n, that is, the number of previous data records returned. Www.2cto.com-g, followed by a regular expression matching mode, case insensitive. Mysqldumpslow-s c-t 20 host-slow.log mysqldumpslow-s r-t 20 the preceding command shows the 20 most accessed SQL statements and 20 most returned record sets. Mysqldumpslow-t 10-s t-g "left join" host-slow.log returns the first 10 SQL statements containing the left join according to time. With this tool, you can find out which SQL statements are performance bottlenecks for optimization, such as adding indexes and implementing the application.