MySQL Slow query

As we all know, in the case of large traffic, can add node or change the schema can effectively alleviate the database pressure, but all the origin, all from a single MySQL start. The following summarizes some of the experiences that have been used or studied, and some optimizations are made to MySQL, starting with the configuration and adjusting the index. Troubleshoot problems and find bottlenecks, so start with the logs

MySQL has a function that can log down to run the slow SQL statement, the default is not the log, in order to enable this function, to modify the my.cnf or when MySQL started to add some parameters.

If you modify it in my.cnf, you need to add the following lines

Long_query_time = 1
Log-slow-queries =
Log-queries-not-using-indexes

Long_query_time is the execution of SQL over how long it will be log down, here is 1 seconds.
Log-slow-queries settings to write the log there, can be empty, the system will give a default file Log-queries-not-using-indexes is to record the SQL that does not use the index
Analysis:
MYSQLDUMPSLOW–HELP below, the main use is
-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 stmts that include the This string

-S, is the order, the description is not detailed enough to write, I use down, including read the code, mainly have
C,t,l,r and Ac,at,al,ar, respectively, are sorted by query count, time, lock time, and number of records returned, preceded by A's flashback
-T, which is the meaning of top N, which is to return the data of the previous number of bars
-G, you can write a regular match pattern behind, case insensitive

Mysqldumpslow-s c-t Host-slow.log
Mysqldumpslow-s r-t Host-slow.log

The above command shows the 20 most visited SQL statements and 20 SQL that returns the maximum number of recordsets.
Mysqldumpslow-t 10-s t-g "left join" Host-slow.log
This is the SQL statement that contains the left link in the first 10 lines.

time:060908 22:17:43
# query_time:12 lock_time:0 rows_sent:86345 rows_examined:580963
Q: This is a slow check of the log, what is the meaning?
A: The query used 12 wonderful, returned 86345 lines, a total of 580963 lines

MySQL Slow query