Mysql Slow Query Use detailed _mysql

1 Slow query definition
MySQL records all SQL statements that execute a time threshold that exceeds the Long_query_time parameter setting. The slow query log is the log that logs these SQL.

2 Open Slow query log
Locate the MySQL configuration file my.cnf. Add under Mysqld

Copy Code code as follows:

Log-slow-queries = D:/mysql/log/mysqld-slow-query.log #日志存在的位置. (attention to the issue of permissions, you can not set, the system will give a default file Host_name-slow.log)
Long-query-time = 5 #SQL执行时间阈值, default is 10 seconds.
#log-long-format #说log-long-format option is used to format the log, which records related events in an extended manner. The extension method records the information about who issued the query and when the query was issued. Allows us to better understand the operation of the client.
#log-slow-admin-statements #将慢管理语句例如OPTIMIZE table, ANALYZE table, and ALTER TABLE are recorded in a slow query log.
Log-queries-not-using-indexes #如果运行的SQL语句没有使用索引, the MySQL database will also record this SQL statement in the slow query log file.

Restart the MySQL service after the configuration is complete.

3 Test　　
For convenience I only configured the path of the slow query log in MY.CNF.

MySQL version for 5.1.73

About Long_query_time: Starting with MySQL 5.1, Long_query_time begins to record the run time of SQL statements in microseconds, which is recorded only in seconds. This allows you to more accurately log the running time of SQL for DBA analysis.

into MySQL.

Copy Code code as follows:

Mysql> show VARIABLES like "%slow%"; #查看慢查询日志相关的信息
mysql> set global long_query_time=0.01; #将mysql位置的值改为 0.01
Mysql> Show variables like "Long_query_time"; #查看是否生效
Mysql> Select Sleep (2); #延迟2秒执行 NOTE: This SQL should have been recorded in a slow query log file after it has been executed.
Mysql> show global status like '%slow% '; #查看慢查询语句的次数

You can see in the log file that SQL has been logged.

Summarize:

windows open MySQL slow query
MySQL in Windows system configuration file is generally My.ini find [mysqld] below plus
Log-slow-queries = F:\MySQL\log\mysqlslowquery.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:\MySQL\log\ Mysqlslowquery.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;