MySQL slow query log open and use detailed

MySQL Slow log is often the problem of locating SQL performance. Index usage can be adjusted by slow log analysis. This is one of the most used features.

At present, MySQL also supports the slow logging below the millisecond level, this feature has a lot of useful, here is probably for you to explain. The function opens the parameter: long_query_time

To see if a slow query is open:

The code is as follows

Copy Code

Mysql> Show variables like '%slow% '; +---------------------+-------------------------+ | variable_name | Value | +---------------------+-------------------------+ | log_slow_queries | On | | Slow_launch_time | 2 | | Slow_query_log | On | | Slow_query_log_file | D:/log/slow.txt | +---------------------+-------------------------+ Among them, the parameters are described as follows: Slow_launch_time: Slow query exceeds the execution time value Slow_query_log: Open slow query log feature Show_query_log_file: Slow Query log directory

Several environments that typically use this parameter are:

Need to get a business all SQL can be set to 0.000001 in the test environment MySQL Long_query_time:

The code is as follows	Copy Code
Set global long_query_time=0.000001; Flush logs;

You can also directly locate the MySQL profile, my.cnf (Windows for My.ini), add the following lines under MySQL

The code is as follows

Copy Code

Log-slow-queries = "D:/xampp/mysql/long.txt" Long_query_time = 1 Log-slow-queries=/var/lib/mysql/slowquery.log (Specify the location of the log file, can be empty, the system will give a default file Host_name-slow.log) long_query_time=2 (Record exceeds time, default is 10s) Log-queries-not-using-indexes (log down without using the index query, you can decide whether or not to open) Log-long-format (if set, all queries that do not use the index will also be logged)

The above 2 is the time of the query, that is, when a SQL execution time of more than 2 seconds to record,/usr/var/slowquery.log is the location of the log record.

Then restart the MySQL service

2, the location of the MySQL configuration file

The windows:windows configuration file is My.ini, usually under the MySQL installation directory or c:windows.

The Linux:linux configuration file is my.cnf, usually under/etc.

So basically all the SQL requests for MySQL are logged in the slow log. Log analysis allows you to get a complete picture of the SQL style and the overall run call distribution. (Important libraries may consider turning on this parameter to log the corresponding log for audit use)

Also for the production environment, you can open this parameter to regularly collect the SQL run on the line, do some comparison, to facilitate understanding of the current online SQL operation. can provide more guidance. With a slow query log, it's easy to get a good analysis through the Slow query log analysis tool.