Enable MySQL slow query to find SQL statements that affect efficiency

MySQL slow query can help us optimize the efficiency of the MySQL database. The following describes how to enable MySQL slow query to find SQL statements that affect the efficiency.

During website operation, the website may suddenly slow down. In general, it is related to the slow MySQL query. You can enable the slow MySQL query to find the SQL statement that affects the efficiency, then take appropriate measures. MySQL has a function to log down and run slow SQL statements. This log is not available by default. To enable this function, modify my. cnf or add some parameters when MySQL is started.

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

Long_query_time = 1
Log-slow-queries =/usr/var/slowquery. 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 log-queries-not-using-indexes, which is the SQL statement that records no index is used.
Analysis:
Mysqldumpslow-help:
-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, which indicates 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 are sorted by the number of queries, time, lock time, and number of returned records, respectively, flashback with a added above
-T indicates the top n, that is, the number of data records returned.
-G. You can write a regular expression matching later. It 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.

Time: 060908 22:17:43
# Query_time: 12 Lock_time: 0 Rows_sent: 86345 Rows_examined: 580963
Q: What are the meanings of slow query logs?
A: 12 million rows are returned for the query. A total of 86345 rows are queried.

How to adjust the MySQL Query Buffer

How MySQL query optimizer works

Explanation for querying cache variables in MySQL

Usage of MySQL conditional query statements

Common MySQL command line tools