Detailed explanation of mysql slow query

1 SlowQueryDefinition

means mysql records all SQL statements that execute beyond the time threshold set by the long_query_time parameter. The slow query log is the log that records these SQLs.
2 Enable slow query log
Find the mysql configuration filemy.cnf. Add

log-slow-queries = D:/MySQL/log/mysqld-slow-query.log  #日志存在的位置。（注意权限的问题，可以不用设置，系统会给一个缺省的文件host_name-slow.log）

long-query-time = 5   #SQL执行时间阈值，默认为10秒。

#log-long-format   #说log-long-format选项是用来设置日志的格式，它是以扩展方式记录有关事件。扩展方式可记录谁发出查询和什么时候发出查询的信息。可使我们更好地掌握客户端的操作情况。

#log-slow-admin-statements  #将慢管理语句例如OPTIMIZE TABLE、ANALYZE TABLE和ALTER TABLE记入慢查询日志。

log-queries-not-using-indexes #如果运行的SQL语句没有使用索引，则mysql数据库同样会将这条SQL语句记录到慢查询日志文件中。

under mysqld and restart after completion mysql service.
3 Test　
For convenience, I only configured the path of the slow query log in my.cnf.
Mysql version is 5.1.73
About long_query_time: starting from mysql 5.1 , long_query_time starts to record the SQL statement running time in microseconds. Previously, it was only recorded in seconds. This can more accurately record the running time of SQL for DBA analysis.
Enter mysql.

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秒执行 注：这条sql执行完毕以后应该已经在慢查询的日志文件中产生相关的信息记录

mysql> show global status like '%slow%'; #查看慢查询语句的次数

You can see in the log file that sql has been recorded.

Summary:
Enable MySQL slow query under Windows
The configuration file of MySQL in the Windows system is generally my.ini, find [mysqld] and add
log-slow-queries = F:\MySQL\log\ mysqlslowquery.log
long_query_time = 2
Enabling MySQL slow query under Linux
MySQL configuration file in Windows system is generally my.cnf to find [mysqld ] Add
log-slow-queries=/data/mysqldata/slowquery.log
long_query_time=2
Note
log-slow-queries = F:\MySQL \log\mysqlslowquery.log is the location where the slow query log is stored. Generally, this directory must have writable permissions for the MySQL running account. This directory is generally set as the MySQL data storage directory;
long_query_time=2 of 2 Indicates that the query will only be recorded if it takes more than two seconds;

The above is the detailed content of Detailed explanation of mysql slow query. For more information, please follow other related articles on the PHP Chinese website!