MySQL Slow query configuration method detailed

MySQL server has a function, you can detect which SQL statement query slow, that is, slow query Slowlog, now describes how to open.
Add the following code under [Mysqld]:

Long_query_time = 1
Log-slow-queries =/usr/local/mysql/data/slow.log
Log-queries-not-using-indexes

Long_query_time = 1 #定义超过1秒的查询计数到变量Slow_queries.
Log-slow-queries =/usr/local/mysql/data/slow.log #定义慢查询日志路径.
Log-queries-not-using-indexes #未使用索引的查询也被记录到慢查询日志中 (optional).
MySQL has a tool mysqldumpslow to view the slow log.
Perform mysqldumpslow–h to view help information.
Mainly introduces two parameters-S and-t
-S This is the sort parameter, which is optional:
Al: Average lockout time
AR: Average number of returned records
At: Average query time
C: Counting
L: Lock Time
R: Returning Records
T: Query time

T-n Displays header N records.

MySQL Slow query analysis Mysqldumpslow

/path/mysqldumpslow-s c-t 10/database/mysql/slow-log
This outputs the 10 SQL statements that have the highest number of records:

-s, is to indicate the way to sort, C, T, L, R, respectively, according to the number of records, time, query time, the number of records returned to the order, AC, at, AL, AR, indicating the corresponding flashback;
-t, is the meaning of top N, that is, to return the number of previous data;
-g, behind can write a regular match pattern, the case is not sensitive;
Like what
/path/mysqldumpslow-s r-t 10/database/mysql/slow-log
Gets the 10 queries that return the recordset the most.
/path/mysqldumpslow-s t-t 10-g "left join"/database/mysql/slow-log
Get the first 10 items in chronological order that contain the query statement with the left connection.

MySQL slow query record/log

windows open MySQL slow query

MySQL in Windows system configuration file is generally My.ini find [mysqld] below plus
Log-slow-queries = F:mysqllogmysqlslowquery.log
Long_query_time = 2

MySQL slow query enabled under Linux

MySQL in Windows system configuration file is generally my.cnf find [mysqld] below plus
Log-slow-queries=/data/mysqldata/slowquery.log
long_query_time=2

Attention
Log-slow-queries = F:mysqllogmysqlslowquery.log for slow query log storage location, general this directory to have MySQL running account can write permission, generally this directory is set to MySQL data storage directory;
2 of the long_query_time=2 means that the query is recorded for more than two seconds;

September 24, 2009 update
Adds the log-queries-not-using-indexes parameter to the MY.CNF or My.ini, which indicates that a query with no indexes is logged. For example,
Log-slow-queries=/data/mysqldata/slowquery.log
long_query_time=2
Log-queries-not-using-indexes