Mysql Open Slow Query log Log-slow-queries method _mysql

A common web site often need to query n SQL statements to get the results of the page, when the Web site access speed is slow and the front-end done a lot of optimization work, the database bottleneck of the search is also an important part of the Web optimization.

MySQL provides a slow query logging function, you can query the SQL statement time is greater than the number of seconds to write to the slow query log, daily maintenance can be slow query log information quickly and accurately judge the problem.

Turn on slow query function

Log-slow-queries Slow query log file path
Long_query_time queries for more than a few seconds are written to the log

Open the MY.CNF configuration file and add the following code:

Log-slow-queries =/tmp/mysql-slow.log
Long_query_time = 2

If it's windows, add it to the My.ini.

My.ini

Copy Code code as follows:

Log_slow_queries
Long_query_time = 2

Save exit, restart MySQL.

about Long_query_time Settings
Normally we set the value of Long_query_time to 2, which means that the query SQL statement is logged for more than two seconds, usually 2 seconds is enough, and the default is 10 seconds. However, for many web programs, a 2-second query is still too long. Indeed, in many sites, a SQL statement is slower than 1 seconds of execution time.
Mysql5.1.21 later to provide finer-grained long_query_time settings, the previous version can only be used as a second unit.

View Log

Copy Code code as follows:

[Root@lizhong tmp]# tail-f/tmp/mysql_slow.log
time:120815 23:22:11
User@host:root[root] @ localhost []
query_time:9.869362 lock_time:0.000035 rows_sent:1 rows_examined:6261774
SET timestamp=1294388531;
Select COUNT (*) from blog;

First line: Execution time
Second line: Execute user
Third line (important):

Query_time SQL execution time, the longer the slower
Lock_time waits for table lock time in the MySQL server phase (not in the storage engine phase)
Number of rows returned by the rows_sent query
Number of rows rows_examined query checks

At last

1, the log can not explain all the problems, knowledge representation, may be linked to the table, the system busy incidental, of course, if a SQL statement often query slow that basic can be judged can be again optimized.
2, do not open log-queries-not-using-indexes No index query logging function, this function is not really useful. is when the SQL query is recorded, there is no index of all records. Although the index has an effect on the speed of the query, it depends on the amount of data. Since this feature is turned on, queries such as SELECT * from Tab will also be recorded in the log, and soon the log file will be filled with spam, which can affect the viewing of the main query slow log.
3, MySQL with the Mysqldumpslow tool used to analyze the slow query log, or other tools can also be better through the tool matching analysis.