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

The editor today takes you to understand how to achieve group by slow query optimization. The knowledge points in this article are introduced in great detail. Friends who feel helpful can browse the content of the article together with the editor, hoping to help more friends who want to solve this problem to find the answer to the problem. Follow the editor to learn more about "how to achieve group by slow query optimization".

Slow query occurs in the current network. In the case of an order of magnitude of 5 million, the query speed of a single table is more than 30 seconds, and sql needs to be optimized. The sql is as follows:

I constructed 5 million pieces of data in the test environment to simulate this slow query.

To put it simply, it is to query which users there are under certain conditions. In a very simple sql, you can see that the query takes 37 seconds.

Talking about the distribution of the app_account field, 5000 different random numbers are randomly generated and then distributed into these 5 million pieces of data. On average, each app_account has 1000 duplicate values, with a total of 5000 categories.

Second, look at the implementation plan

As you can see, I indexed the group by field, and I also used it.

III. Optimization

To tell you the truth, I don't know how to optimize, how can this thing be optimized? First of all, the following ideas are useless.

Idea 1:

Order by null; should be added later to avoid useless sorting, but it actually has little effect on the result time-consuming, still very slow.

Idea 2:

The where condition is too complex and has no index, which leads to slow query, but I added a combined index to all the fields of the where condition, which is still useless.

Idea 3:

Since group by is slow, try changing distinct. (this is the magical place in this blog.)

Shit?! What's going on, so fast in an instant?!

Although I know that there is a small performance gap between group by and distinct, I did not expect that the gap is so big! What a discovery!

4. Do you think it's over?

I really hope that this is the end, then this problem is easily solved, and by the way, I think I have discovered a new knowledge.

But!

After the bug was transferred to the test, the test took more than 30 seconds!? What's going on?!??

Of course I don't believe it. It took more than 30 seconds to test the execution of sql on the computer.

I went back to my computer, connected to the same database, and executed sql,0.8 seconds!?

What is the situation, the same library, the same sql, how can there be such a big gap between the two computers?

It is then executed directly on the server:

Drunk, unexpectedly still more than 30 seconds.

Then it seems to be my computer problem.

Later, I used the computer experiments of many colleagues, and finally came to the conclusion that:

Because I use SQLyog!

Alas, it has been found that only the "optimized" sql executed with sqlyog will be 0.8 seconds, and it will be executed directly on navcat and the server, both in more than 30 seconds.

That's the problem with sqlyog, and it's not clear whether sqlyog has been optimized or not, and this slow query problem is still being solved (I think the problem may be due to the parameters of mysql itself).

This is just a record of the pit, the speed at which sqlyog executes sql, and the speed at which the server executes sql, which varies greatly in some sql and is not reliable.

5. Follow-up (not yet resolved)

Thank you for your advice. Let me reply to the progress of the question:

1. The reason for the so-called fast sqlyog query and slow command line query has been found. It is because sqlyog adds limit 1000 by default after the query statement, so it is very fast. The problem is no longer entangled.

two。 The methods I have tried (none of them work):

① indexes the app_account field.

② adds order by null to the sql statement.

③ adjusts the query order of the fields in the where condition, putting those with indexes in front of them.

④ indexes the fields of all where conditions.

⑤ uses a subquery to check the contents of the where condition first, and then to repeat it.

The data of the test environment and the current network environment are still a little different. I will post a picture of the current network performing sql (1 minute. ):

VI. Final solution

Thanks to @ Yanfeng boss on the 42nd floor in the comments!

After your reminder, I do find that the index does not seem to use the idx_end_time I created in the explain execution plan.

Then decisively under the current network try, force to specify the use of idx_end_time index, the result is only 0.19 seconds!

To solve this problem, in fact, colleagues were wondering yesterday whether the index of this table was built too much, which led to the wrong use of idx_org_id and idx_mvno_id.

Now the mandatory assignment of idx_end_time is ok!

Finally, compare the implementation plan before and after the change.

Before changing (query will take about 1 minute):

After modification (query takes only a few hundred milliseconds):

Thank you for your reading, the above is "how to achieve group by slow query optimization" all the content, learn friends hurry up to operate it. I believe that the editor will certainly bring you better quality articles. Thank you for your support to the website!

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.