First, open the slow query log
1. Query slow query time default 10 seconds
like "Long_query_time"
2. Query slow query log file address
like "%slow%"
3. If the slow query log is not turned on, configure the item under My.ini [MYSQLD] and restart the MYSQLD service.
How MySQL is set less than or equal to version 5.5
#慢查询日志文件地址log-slow-queries = "E:/phpstudy/mysql/long_query_logs/buexplain-slow.log" #慢查询时间 (units in seconds), SQL that is greater than this time is logged to the slow query log file. long_query_time=0.5
How MySQL is set to be greater than or equal to version 5.6
#开启慢查询 1 or on means on, 0 or off to turn off slow_query_log=on #慢查询日志文件地址slow_query_log_file = "e:/phpstudy/mysql/long_query_logs/ Buexplain-slow.log "#慢查询时间 (in seconds), SQL that is greater than that time is logged to the slow query log file. long_query_time=0.5
4. Test Slow Query
SELECT 1
5. Check the number of slow queries
like ' %slow% '
Second, slow query log analysis
# time:151001 19:06:58# [email protected]: root[root] @ localhost [127.0.0.1]# query_time:1.000057 lock_time:0.0000 XX rows_sent:1 rows_examined:0set timestamp=1443697618;select sleep (1);
First line: Execution time
Second line: Execute user
Third line (important):
Query_time SQL execution time, the longer the more slowly
Lock_time Waiting for table lock time during the MySQL server phase (not in the storage engine phase)
Rows_sent the number of rows returned by the query
rows_examined the number of rows checked by the query
Row four: Executed SQL
The tools for analyzing processing logs are:
1.mysqldumpslow perl script provided by the official
2.mysqlsla is a log analysis tool launched by hackmysql.com
3.pt-query-digest a Perl script
RELATED links:
http://www.zrwm.com/?p=2668
http://blog.csdn.net/a600423444/article/details/6854289
Iii. Summary
Do not turn on Log-queries-not-using-indexes no index query logging function, it affects the main slow query log view.
MySQL Slow query log