MySQL version is mysql5.6.22, installation environment Windows7.
1. Use this query log to find SQL statements of efficiency problems and record them for monitoring.
You can query and set the slow query log using the following statement
(1) Check if the slow query log is turned on
SHOW VARIABLES like '%show_query_log% '; or SHOW VARIABLES like '%show_query_log% ' \g (used in MySQL command line)
The query results are as follows
Variable_name:slow_query_log
Value:off
Top value indicates not open
Variable_name:slow_query_log_file
Value:d:\programfiles\mysql5.6.22\mysql_master\data\lhy-slow.log
The top value indicates the path where the log is stored
Set the on state:
Set global slow_query_log = on;
(2) Check if query log for unused index is turned on
SHOW VARIABLES like '%log_queries_not_using_indexes% '; or SHOW VARIABLES like '%log_queries_not_using_indexes% ' \g (used in MySQL command line)
The query results are as follows
Variable_name:log_queries_not_using_indexes
Value:off
Top value indicates not open
Set the on state:
Set global log_queries_not_using_indexes = on;
(3) View query log over set time
SHOW VARIABLES like '%long_query_time% '; or SHOW VARIABLES like '%long_query_time% ' \g (used in MySQL command line)
The query results are as follows:
Variable_name:long_query_time
value:10.000000
The top value:10s represents SQL that records execution time of more than 10 seconds
Set execution time to 1s
Set global long_query_time = 1;
Exit
Note 1: After you modify the execution, you have to exit to log back in before it will work.
Note 2: Setting time Too short causes too many log records to be covered quickly, so disk cleanup should be performed on a regular basis, and this is set to 1 to see the effect of the execution, which needs to be set up in the production environment.
When the above three steps are completed, any SQL statements executed from the database will be recorded in the log, and the log information can be viewed in the first step.
The above settings are processed in the console, and when the database restarts, the setting is invalidated;
The long-term effective way is to find the My.ini file in the MySQL installation directory, if there is no such file, only the Mysql-default.ini file
Then back up the file and rename it to Mysql.ini, then add the following configuration information in the file [mysqld] below.
Slow_query_log=on
Slow_query_log_file=d:/programfiles/mysql5.6.22/mysql_master/data/lhy-slow.log
Log_queries_not_using_indexes=on
Long_query_time=1
2. Storage format
# time:150401 11:24:27
# [email protected]: root[root] @ localhost [127.0.0.1] Id:7
# query_time:0.034002 lock_time:0.000000 Rows_sent:3 Rows_examined:3
Use Libu;
SET timestamp=1427858667;
Select * from AAA;
The analysis is as follows:
(1) Time: Execution
(2) [email protected]: host information for executing SQL
(3) Query_time:sql execution information, Lock_time: Lockout time, Rows_sent: Number of rows sent (result), rows_examined: Number of rows scanned
(4) Timestamp: Execution time
(5) SELECT * FROM AAA; : Querying the contents of a statement
3. Slow Query Log Analysis tool
5 Types of tools: Mysqldumpslow,mysqlsla,myprofi,mysql-explain-slow-log,mysqllogfilter
Mysqldumpslow MySQL comes with analysis tools
Apologies: I have not found these tools in the window to use the way, if the great God has the relevant tutorials, please @ me, thank you!!!
MySQL slow query log open and save format
