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

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.

You may also be interested in:
  • Detailed explanation of the reason why the MySQL 5.7 slow query log time is 8 hours behind the system time
  • Causes and solutions for slow MySQL query speed and poor performance
  • Causes and solutions for slow MySQL queries
  • Reasons and solutions for slow MySQL query stuck in sending data
  • How to enable the slow query log function in MySQL
  • Examples of optimization techniques for slow query efficiency in MySQL IN statements
  • How to enable slow query log in MySQL
  • MySQL slow query method and example

<<:  How to use tcpdump to capture packets in Linux system

>>:  Vue implements graphic verification code

Recommend

Loading animation implemented with CSS3

Achieve results Implementation Code <h1>123...

JavaScript setTimeout and setTimeinterval use cases explained

Both methods can be used to execute a piece of ja...

Two ways to achieve horizontal arrangement of ul and li using CSS

Because li is a block-level element and occupies ...

Windows system mysql5.7.18 installation graphic tutorial

MySQL installation tutorial for Windows system do...

Docker data volume common operation code examples

If the developer uses Dockerfile to build the ima...

Tips to prevent others from saving as my web page and copying my site

Nowadays, copying websites is very common on the I...

How many pixels should a web page be designed in?

Many web designers are confused about the width of...

Installation and use of mysql mycat middleware

1. What is mycat A completely open source large d...

Tutorial on installing MYSQL5.7 from OEL7.6 source code

First, download the installation package from the...

XHTML no longer uses some obsolete elements in HTML

When we do CSS web page layout, we all know that i...

Detailed explanation of MySQL index principles and optimization

Preface This article was written by a big shot fr...

Vue implements the magnifying glass effect of tab switching

This article example shares the specific code of ...

How to use shell scripts in node

background During development, we may need some s...

Solution to the Multiple primary key defined error in MySQL

There are two ways to create a primary key: creat...