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

A recent development team complained that our platform included MySQL cluster, and always reported MySQL's "Heartbeat Error". Analyzed the logs they collected. Did not find the MySQL cluster node network between the problem, but found a very interesting phenomenon, in the MySQL slow log found a lot of slow query. Basically, it takes more than 1 seconds, and it's obvious that a slow, time-consuming query can affect system behavior. Pointing out that this allows the development team to optimize their applications, it is curious that they do not know that there is such a thing to analyze.

。。。

。。

The most problematic database response is queries. Most databases today 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, it is necessary to turn on slow logging for analysis (records can be saved in a table or file. The default is to save in the file, our system is using 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 very well understood, the first # records the timestamp, the second # Records the user and address information to run the command, the third # records the time the query was run, the time of the lock, the number of rows returned, and the number of rows scanned.

The SQL statement that is actually running 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 references

Go to start MySQL, 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 |+---------------------------+------- ---------------------------------+

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 at the same time over a specified period need to be recorded in a slow log query.

So how do they look at the number of references?

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 number of parametersindicates whether to open a query without index, and the secondThe parameters are used for logging traffic control and how many entries can be recorded in a minute. The default of 0 is to indicate 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 more than a length of time for a parameter needs 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 indicates the number of rows not scanned today

Set to turn on MySQL slow log parameters

Go to MySQL, enter the following command or change it in MySQL's boot configuration file or add a startup parameter to MySQL. Changes after entering MySQL such as the following:

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. Preventing too many logs has an impact on performance.

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

Note:mysql also has 2 slow log references (log_slow_slave_statements and log_slow_admin_statements). We are able to participate in MySQL slow Query Log Official Document link

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