Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to slow the Sending data query in MySQL. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

The scene of the accident

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-100 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 join table grouping query.

Execution result:

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

How to optimize it?

Second, SQL sentence analysis of three axes

1. Explain analysis

Explain analysis of 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-100 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 the above statement, which is useful for indexing key.

2 、 show processlist

Explain doesn't see the problem, so what's the delay?

So I thought of using show processlist to check the execution status of the sql statement. The query result is as follows:

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

Query the meaning of "Sending data" status, it turns out that the name of this state is very misleading, the so-called "Sending data" does not simply send data, but includes "collect + send data".

The key here is why to collect data, the reason is: mysql uses "index" to complete the query, mysql gets a pile of row id, if some column is not in the index, mysql needs to go back to the "data row" to read the data to return to the client.

3 、 show profile

To further verify the time distribution of the query, the 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 details

III. Optimization of investigation

1. Investigation and comparison

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

After a check, it is finally decided to a description column, the design of this column is: descriptionvarchar (8000) DEFAULT NULL COMMENT 'game description'

So I take a comparative approach to see how "the result of no description is returned". The results of show profile are as follows:

[solution]

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

1) remove the description query when querying, but this is limited by the implementation of the business, which may require major business adjustments.

2) the table structure is optimized to split the descripion into another table, which requires the existing business to cooperate with the modification. If the business still wants to query the information of this description, the performance of the optimized table will not be greatly improved.

This is the end of this article on "how to slow Sending data query in MySQL". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.