Shulou(Shulou.com)06/01 Report--

In this issue, the editor will bring you about how to optimize the group by slow query in the sql query. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

I. background of the question

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, there is no index, resulting in slow query, but in fact, even if the where condition does not change, as long as the group by is removed, it is very fast. Therefore, it should not be a matter of where conditions.

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).

The speed at which sqlyog executes sql and the speed at which the server executes sql are very different in some sql and are not reliable.

The above is how to optimize the group by slow query in the sql query shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.