MySQL 5.6 Set slow query

MySQL 5.6 Turn on slow query log

Slow_query_log = On#turn on slow query 1 or onLong_query_time= 3#record over time in seconds, default is 10sSlow_query_log_file=/data/mysql/dev-service-node2-slow.log#Specify the log file location, can be empty, the system will give a default file Host_name-slow.loglog_queries_not_using_indexes=true#if the slow query log is enabled, this variable controls whether queries that do not use the index should be logged. If you make a record, the log file will quickly become larger. for this parameter, a more special case is also described in the official documentation: this option does notNecessarily mean that no index isused. For example, a query that Usesa full index scan uses an index but would be logged because the index would notLimit the number of rows.

Query the number of slow query occurrences;

' slow_queries ';

To view the slow query log:

If you have a lot of records in the slow query log, you can use the Mysqldumpslow tool to subtotal the slow query log.
Mysqldumpslow is a tool that comes with MySQL database

Perform:

Mysqldumpslow-a-S T-t 7/data/mysql/dev-service-node2-slow.log

This command outputs 7 SQL statements with the longest execution time.
The meaning of each parameter in this command:
-S, which is how to sort by, C, T, L, and R are sorted by the total number of statement query times, query time, lock time, rows sent,
At, AL, and AR are sorted by the average of query time, lock time, and rows sent;
-T, which is the meaning of top N, is to return the number of SQL statements in the first order.

-G, you can write a regular matching pattern, the case is not sensitive;

#mysqldumpslow-S T-t 7/data/mysql/dev-service-node2-slow.log

#输出参数解释:

count:15831 time=8.46s (133943s) lock=0.00s (7s) rows=578.3 (9154400)

Count: This statement was executed 15,831 times

Time:8.46s is the average time that the statement is executed (some say the maximum time it takes to execute). (133943s) is the sum of the execution time of the statement

Lock: That is the time that lock times waits for locks

Rows: That is rows sent, 578.3 represents the total number of rows sent to the client. (9154400) indicates the total number of rows scanned

MySQL 5.6 Setting slow query