In-depth analysis of the slow query problem of MySQL Sending data

Through an example, I shared with you the solution to the slow query problem of MySQL Sending data table.

Recently, during code optimization, I found that a SQL statement was very slow, so I used various methods to investigate it, and finally found the cause.

1. Accident Scene

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 above statement is a joint table grouping query statement.

Execution Result:

We can see that this statement took 1.300 seconds, and Sending data took 1.28 seconds, which took up nearly 99% of the time, so we optimize this.

How to optimize it?

2. Three tricks for SQL statement analysis

1. Explain analysis

explain the above statement:

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:

Through explain , we can see that the above statement uses the index key .

2. show processlist

Explain doesn't reveal the problem, so what's the slowness?

So I thought of using show processlist to view the execution status of the SQL statement. The query results are as follows:

It was found that the query was in the "Sending data" state for a long time.

Look up the meaning of the "Sending data" status. It turns out that the name of this status is very misleading. The so-called "Sending data" does not simply send data, but includes "collecting + sending data".

The key here is why data needs to be collected. The reason is that after MySQL uses the "index" to complete the query, MySQL gets a bunch of row IDs. If some columns are not in the index, MySQL needs to go back to the "data row" to read the data to be returned and return it to the client.

3. show profile

In order to further verify the time distribution of the query, show profile command is used to view the detailed time distribution

First open the configuration: set profiling=on;

After executing the query, use show profiles to view the query id;

Use show profile for query query_id to view detailed information;

3. Troubleshooting and Optimization

1. Check and compare

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

After checking one by one, we finally decided to put it on a description column. The design of this column is: description varchar(8000) DEFAULT NULL COMMENT 'Game description',

So we took a comparative approach to see what the "result without returning description" would look like. The results of show profile are as follows:

【Solution】

Once the root cause of the problem is found, the solution will not be difficult. There are several ways:

1) Remove the description query when searching, but this is limited by the business implementation and may require major adjustments to the business

2) Optimize the table structure and split the description into another table. This is a major change and requires the existing business to cooperate with the modification. If the business still needs to continue to query the information of this description, the performance after optimization will not be greatly improved.