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

CSS3 to achieve simple white cloud floating background effect

This is a very simple pure CSS3 white cloud float...

Basic operations on invisible columns in MySQL 8.0

Table of contents 01 Create invisible columns 02 ...

Install MySQL (including utf8) using Docker on Windows/Mac

Table of contents 1. Docker installation on Mac 2...

CSS achieves the effect of two elements blending (sticky effect)

I remember that a few years ago, there was an int...

Sharing tips on using scroll bars in HTML

Today, when we were learning about the Niu Nan new...

jQuery plugin to achieve carousel effect

A jQuery plugin every day - jQuery plugin to impl...

Solution to forgetting the password of the pagoda panel in Linux 3.X/4.x/5.x

Enter ssh and enter the following command to rese...

Detailed explanation of the use of Vue mixin

Table of contents Use of Vue mixin Data access in...

How to use mqtt in uniapp project

Table of contents 1. Reference plugins in the uni...

How to configure nginx to return text or json

Sometimes when requesting certain interfaces, you...

Html+css to achieve pure text and buttons with icons

This article summarizes the implementation method...

MySQL multi-instance configuration solution

1.1 What is MySQL multi-instance? Simply put, MyS...

How to configure multiple tomcats with Nginx load balancing under Linux

The methods of installing nginx and multiple tomc...