MySQL5.6 How to optimize SQL statements for slow queries--Slow log introduction

Recently, a development team complained that our platform contains MySQL cluster not, always reported the MySQL "Heartbeat Error", analyzed the logs they collected, did not find the network between the MySQL cluster node problem, but found a very interesting phenomenon , in the MySQL slow log found a large number of slow queries, basically is more than 1 seconds, it is obvious that this time-consuming slow query will affect the system behavior, point out that the development team to optimize their applications, it is curious that they do not know that there are these things can be analyzed ...

The most problematic database response is queries. Most databases now provide a way to help with performance analysis. Oracle, for example, will help you find the slow statements directly and provide an optimization solution. In MySQL, you have to turn on slow logging to analyze (records can be saved in a table or file, the default is to save in the file, our system is the default mode).

Let's look at the logs in the MySQL slow query log for what it looks like:

Time                 ID Command    argument# time:141010  9:33:57# [email protected]: root[root] @ localhost []  ID:     # query_time:0.000342  lock_time:0.000142 rows_sent:3  rows_examined:3use test; SET Timestamp=1412904837;select * from T;

This log should be well understood, the first # records the timestamp, the second # Records the user and address information that executes the command, the third # records the time the query was executed, the time of the lock, the number of rows returned, and the number of rows scanned. The SQL statement that is actually executed is then logged. You can also use the following command to see the meaning of each field in the CVS storage format.

SHOW CREATE TABLE Mysql.slow_log;

Next talk about how to get and analyze slow logs.

View MySQL Slow log parameters

Go to start MySQL, execute the following command

Mysql> Show variables like '%slow_query% '; +---------------------------+---------------------------------------- +| Variable_name             | Value                                  |+---------------------------+----------------------------------------+| slow_query_log            | OFF                                    | | slow_query_log_file       |/usr/local/mysql/data/cloudlu-slow.log |+---------------------------+------- ---------------------------------+

this tells us where the log of the slow log is stored and whether the slow log is open.

So what kind of query needs to be logged? In MySQL, queries with no index and more than the specified number of scanned rows over a specified time need to be recorded in a slow log query.

So how do they look at the parameters?

No query logging switch for index

Mysql> show global variables like '%indexes% '; +----------------------------------------+-------+| Variable_name                          | Value |+----------------------------------------+-------+| Log_queries_not_using_indexes          | OFF   | | log_throttle_queries_not_using_indexes | 0     |+----------------------------------------+-------+

first parameterindicates whether to open a query without index, and the secondparameters are used to log traffic control, how many bars can be recorded in a minute, and the default 0 is no limit.

Query switch over a specified length of time

Mysql> show global variables like '%long_query% '; +-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in Set ( 0.00 sec)

A query that specifies how many times longer is required for a parameter to be logged

Scan query switch that exceeds the specified number of rows

Mysql> Show variables like  '%min_examined_row_limit% '; +------------------------+-------+| Variable_name          | Value |+------------------------+-------+| Min_examined_row_limit | 0     |+------------------------+-------+1 row in Set (0.00 sec)

The default is 0, which means the number of rows is not scanned now

Set turn on MySQL slow log parameter

Enter MySQL, enter the following command or in the MySQL startup configuration file to modify or add the startup parameters to MySQL, after the changes to MySQL as follows:

Set global Long_query_time=0.1;set Global log_queries_not_using_indexes=on;set global slow_query_log = on;

There are 2 points to consider here, and the first is that there is a problem with the log that exceeds the length of time, which is determined by the system requirements. The second is how many logs are logged per minute without using indexes, to prevent too many logs from affecting performance.

Next: MySQL5.6 How to optimize SQL statements for slow queries--SQL optimization

Note:mysql also has 2 slow log parameters (Log_slow_slave_statements and log_slow_admin_statements), you can refer to MySQL slow Query Log Official Document Link

MySQL5.6 How to optimize SQL statements for slow queries--Slow log introduction