Mysql slow query Usage Details

Mysql slow query Usage Details

1. Definition of slow Query
Mysql records all SQL statements that have been executed beyond the time threshold set by the long_query_time parameter. Slow query logs are the logs that record these SQL statements.

2. Enable slow query logs
Find the mysql configuration file my. cnf. Add it under mysqld

Copy codeThe Code is as follows:
Log-slow-queries = D:/MySQL/log/mysqld-slow-query.log # location where the log exists. (Pay attention to permission issues, you can not set, the system will give a default file host_name-slow.log)
Long-query-time = 5 # SQL Execution time threshold. The default value is 10 seconds.
# Log-long-format # the log-long-format option is used to set the log format. It records related events in extended mode. Extended Mode can record who sends the query and when the query is sent. This allows us to better understand the operations of the client.
# Log-slow-admin-statements # log slow query statements such as optimize table, analyze table, and alter table.
Log-queries-not-using-indexes # If the running SQL statement does not use an index, the mysql database also records this SQL statement to the slow query log file.

Restart the mysql service after the configuration is complete.

3 Test　　
For convenience, I only configured the path for slow query logs in my. cnf.

Mysql version 5.1.73

About long_query_time: Starting from mysql 5.1, long_query_time records the SQL statement running time in microseconds. Previously, it was recorded in seconds only. This allows you to more accurately record the SQL running time for DBA analysis.

Enter mysql.

Copy codeThe Code is as follows:
Mysql> show VARIABLES like "% slow %"; # view slow query log information
Mysql> set global long_query_time = 0.01; # change the mysql Location Value to 0.01.
Mysql> show variables like "long_query_time"; # Check whether it takes effect
Mysql> select sleep (2); #2 seconds delayed execution Note: After the SQL statement is executed, related information records should be generated in the log file for slow query.
Mysql> show global status like '% slow %'; # view the number of slow query statements

In the log file, you can see that SQL has been recorded.

Summary:

Enable MySQL slow query in Windows
In Windows, the configuration file of MySQL is usually my. ini. Find [mysqld] and add
Log-slow-queries = F: \ MySQL \ log \ mysqlslowquery. log
Long_query_time = 2

Enable MySQL slow query in Linux
The configuration file of MySQL in Windows is usually my. cnf. Find [mysqld] and 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 slow query logs are stored. Generally, this directory requires the write permission of the MySQL running account. Generally, this directory is set to the MySQL data storage directory;
2 In long_query_time = 2 indicates that the query takes more than two seconds to record;