MySQL5.6 how to optimize slow query SQL statements -- slow log introduction _ MySQL

Recently, a development team complained that the mysqlcluster on our platform was not good and always reported mysql heartbeatError. after analyzing the logs they collected, they did not find any network problems between mysqlcluster nodes, however, I found a very interesting phenomenon. Recently, a development team in mysql's sl complained that the mysql cluster included in our platform was not good and always reported the "heartbeat Error" of mysql. I analyzed the logs they collected and found no network problems between mysql cluster nodes. However, I found a very interesting phenomenon. I found a large number of slow queries in mysql slow logs, it usually takes more than one second. it is obvious that this time-consuming slow query will affect the system behavior. it is pointed out that this allows the development team to optimize their applications, i'm curious that they don't even know that these things can be analyzed ......

Query is the most common cause of slow database response. Currently, most databases provide performance analysis tools. For example, Oracle will help you find slow statements and provide optimization solutions. In MySQL, you need to enable slow log records for analysis (records can be saved in tables or files. by default, they are saved in files, and our system uses the default method ).

First, let's take a look at what the records in the MySQL slow query log look like:

Time                 Id Command    Argument# Time: 141010  9:33:57# User@Host: root[root] @ localhost []  Id:     1# 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 one is to record the timestamp, and the second one is to record the user and address information for executing the command, third # record the query execution time, lock time, number of returned rows, and number of scanned rows. Next, record the actually executed SQL statements. 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, let's talk about how to obtain and analyze slow logs.

View MySQL slow log parameters

Log on to the started MySQL instance and run 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 |+---------------------------+----------------------------------------+

Here we will show you the location where slow logs are stored and whether slow logs are enabled.

So what kind of query needs to be logged? In MySQL,Query without indexAndQueries that exceed the specified number of scanned rows at the same time within the specified timeIt must be recorded in the slow log query.

How can we view their parameters?

Query record switch without index

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

The first parameter indicates whether to enable query without index. The second parameter is used to control the traffic of log records. the default value 0 indicates no limit.

Query switch that exceeds the specified time length

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

A query with a specified length of time must be recorded.

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 value is 0, indicating the number of rows not scanned now

Set MySQL slow log parameters

Enter MySQL, enter the following command, modify it in the MySQL startup configuration file, or add startup parameters to MySQL. the following changes are made after entering MySQL:

set global long_query_time=0.1;set global log_queries_not_using_indexes=on;set global slow_query_log = on;

There are two points to consider here. First, there is a problem with logs that exceed the specific duration, which is determined by the system requirements. The second is how many logs are recorded per minute without using indexes. to prevent too many logs from affecting performance.