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

This article mainly explains "what is the method of SQL slow query optimization". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what is the method of SQL slow query optimization"?

1. Background

The page could not get the data correctly, and it turned out that the API call timed out, but it was found that the problem occurred because the SQL query was more than 20 seconds long.

Here, there is no advanced theory or technology, just to forget to experience and interpret some ideological misunderstandings.

two。 The composition of complex SQL statements

There is not much description of the business function here, but in order to highlight the problem, analogical statements will be used to describe the scene at that time.

Complex SQL statements can be expressed as follows:

SELECT * FROM a_table AS a LEFT JOIN b_table AS b ON a.id=b.id WHERE a.id IN (SELECT DISTINCT id FROM a_table WHERE user_id IN (100102103) GROUP BY user_id HAVING count (id) > 3) 3. Association query

From the simplified SQL statement above, you can see that the first thing to do is the associative query.

4. Subquery

Second, there are nested subqueries. The purpose of this subquery is to find out the group ID shared by multiple users. So the "100102103" in the statement is based on the scenario and needs to correspond to the number of subsequent "count (id) > 3". To put it simply, it is to find the group ID where the user intersects.

5. Where's the time?

Suppose that the data volume of the a_table table is now 20W, while that of b_table is 2000W. You can think about it, do you think the main time-consuming part is in the related query section or in the sub-query section?

(thinking space. )

(thinking space. )

(thinking space. )

6. Problem positioning

For the underlying principles and advanced theories of SQL, I do not have enough depth for the time being. But I know that analogies and simple tests can be used to verify which part of the problem went wrong.

7. Preliminary conclusion

First of all, for only one user ID, I will simplify the above statement to:

ELECT * FROM a_table AS a LEFT JOIN b_table AS b ON a.id=b.id WHERE user_id IN (100)

Therefore, the preliminary conclusion should be that the nested subquery part takes up most of the time.

9. Further verification

Now that the problem of nested subquery statements is located, it is divided into two areas to be investigated: is the subquery itself time-consuming, or is it nested resulting in slow queries?

As a result, it is easy to find that when I execute the subquery in DB alone, it is very fast. So rule it out.

The rest goes without saying that 20-second slow queries are caused by nesting.

But because of the emergency process of launching, to make sure, I quickly verified my conclusion:

1. Execute the ID of the subquery separately, and manually assemble the result sequence into an ID, such as: 1, 2, 3, 4, … , 999

2. Manually replace the sequence ID obtained above with the original SQL statement

3. Execute, discover, soon! It only took about 150 ms

Well Done! Get ready to repair it online!

10. Solution

Online problems, a lot of time is to locate the problem and analyze the cause, now that the problem has been found, the reason has also been found, the solution is self-evident. The code is simple to deal with.

11. Another point to pay attention to

Currently, the actual SQL statement will be more complex than this, but it is enough to express the problem. But in the early days, the author also did some SQL code.

Because b_table is larger than a_table, at the beginning, when b_table left correlates a_table, it is very slow, about 1 second, and the amount of data is very small; but vice versa, when a_table left associates b_table, it is very fast, about 100ms.

So, another interesting phenomenon was found:

Large table left associated with small table, very slow; small table left associated with large table, very fast.

Of course, we know all this in theory, but the actual development will forget it. Or when both tables are empty at the beginning, and do not take into account the rate of growth of the two tables in the later stage, they will be buried in the future.

At this point, I believe that everyone on the "SQL slow query optimization method is what" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.