The MySQL version number is mysql5.6.22. Installation Environment Windows7.
1. Use this query log to find SQL statements that have an efficiency problem. and record them and monitor them.
Ability to query and set slow query logs using, for example, 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)
Query results such as the following
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)
Query results such as the following
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)
Query results such as the following:
Variable_name:long_query_time
value:10.000000
The top value:10s represents a record of SQL running longer than 10 seconds
Set run time is 1s
Set global long_query_time = 1;
Exit
Note 1: After the change runs, it will not work until you log out again.
Note 2: Setting the time too short causes too many log records to be covered with disk space very quickly, so you should run Disk Cleanup regularly, which is set to 1 in order to see how it works, which you need to set up in your production environment.
After the three steps have been completed, running from the database regardless of the SQL statement will be logged in the log, to the first step in the log to view the log information.
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 under the MySQL installation folder, if there is no such file, only the Mysql-default.ini file
Then back up the file and rename it to Mysql.ini. You can 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 for example the following:
(1) Time: Runtime
(2) [email protected]: host information for running SQL
(3) Query_time:sql operation information, Lock_time: Lockout time, Rows_sent: Number of rows sent (result), rows_examined: Number of rows scanned
(4) Timestamp: Run 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
Apology: I have not found the way these tools are used on window at this moment. If the great God has the relevant tutorial, please @ me, thank you!
MySQL slow query log open and save format
