In-depth analysis of slow MySQL Sending data query, mysqlsending

In-depth analysis of slow MySQL Sending data query, mysqlsending

An example is provided to show you how to solve the slow query problem in the MySQL Sending data table.

Recently, in code optimization, I found that an SQL statement is very slow, so I used various methods for troubleshooting and finally found the cause.

I. accident site

SELECT og.goods_barcode, og.color_id, og.size_id, SUM(og.goods_number) AS sold_number FROM order o LEFT JOIN order_goods og ON o.order_id = og.order_id WHERE o.is_send = 0 AND o.shipping_status = 0 AND o.create_time > '2017-10-10 00:00:00' AND o.ck_id = 1 AND og.goods_id = 13421 AND o.is_separate = 1 AND o.order_status IN (0, 1) AND og.is_separate = 1 GROUP BY og.color_id, og.size_id

The preceding statement is a join table grouping query statement.

Execution result:

We can see that this statement uses1.300Seconds, andSending dataIt is used.1.28Seconds, taking nearly 99% of the time, so we optimized this.

How to optimize it?

Ii. SQL Statement Analysis

1. explain Analysis

Run the preceding statementexplainAnalysis:

explain SELECT og.goods_barcode, og.color_id, og.size_id, SUM(og.goods_number) AS sold_number FROM order o LEFT JOIN order_goods og ON o.order_id = og.order_id WHERE o.is_send = 0 AND o.shipping_status = 0 AND o.create_time > '2017-10-10 00:00:00' AND o.ck_id = 1 AND og.goods_id = 13421 AND o.is_separate = 1 AND o.order_status IN (0, 1) AND og.is_separate = 1 GROUP BY og.color_id, og.size_id

Execution result:

Passexplain, We can see the above statement, useful to the indexkey.

2. show processlist

I don't know what the problem is. What is the slow speed?

So I thought of usingshow processlistCheck the SQL statement execution status. The query results are as follows:

The query is in the "Sending data" status for a long time.

Check the meaning of the "Sending data" status. The original status name is misleading. The so-called "Sending data" is not simply Sending data, it includes "collecting and sending data ".

The key here is why we need to collect data. The reason is that after mysql uses "Index" to complete the query, mysql gets a bunch of row IDs. If some columns are not indexed, mysql needs to read the data to be returned from the "Data row" and return a client.

3. show profile

To further verify the query time distributionshow profileCommand to view detailed time distribution

First open the configuration: set profiling = on;

After the query is executed, use show profiles to view the query id;

Use show profile for query query_id to view details;

Iii. troubleshooting and Optimization

1. troubleshooting and Comparison

After the above steps, it has been determined that the query is slow because a large amount of time is spent on the Sending data status. Combined with the Sending data definition, the target is focused on the return column of the query statement.

After a troubleshooting, it is finally set to a description column. The design of this column is as follows:descriptionVarchar (8000) default null comment 'game description ',

So we adopted a comparison method to see how "the description result is not returned. The result of show profile is as follows:

[Solution]

Finding the root cause of the problem makes it difficult to solve the problem. There are several methods:

1) The description Query is removed during the query, but this is restricted by the implementation of the business and may need to be greatly adjusted by the business.

2) Table Structure Optimization: Split descripion into another table. This change is large and requires the modification of existing services. If the business still needs to query the description information, the optimized performance will not be greatly improved.