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

What are the 8 most awkward misuses of SQL? I believe many inexperienced people are at a loss about this. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. LIMIT statement

Paging queries are one of the most common scenarios, but they are also usually the most prone to problems. For example, for the following simple statement, the general idea of DBA is to add a combined index to the type, name, and create_time fields. In this way, conditional sorting can be effectively used to the index, and the performance is improved rapidly.

Well, maybe more than 90% of DBA solves the problem so much. But when the LIMIT clause changes to "LIMIT 1000000 million 10", programmers still complain: why am I taking only 10 records? why is it still slow? To know that the database does not know where the 1000000 record starts, even if there is an index, it needs to be calculated from scratch. In most cases, programmers are lazy when this kind of performance problem occurs. In scenarios such as front-end data browsing and paging, or big data exporting in batches, the maximum value of the previous page can be used as a parameter as a query condition. SQL has been redesigned as follows:

Under the new design, the query time is basically fixed and will not change with the growth of the amount of data.

2. Implicit conversion

Another common mistake is a mismatch between query variables and field definition types in SQL statements. For example, the following statement:

The field bpn is defined as varchar (20), and the strategy of MySQL is to convert a string into a number before comparing. The function acts on the table field and the index is invalid. The above situation may be the parameters automatically filled in by the application framework, not the original intention of the programmer. Nowadays, many application frameworks are very complicated, so when it is easy to use, you should also be careful that it may dig holes for yourself.

3. Associate updates and deletes

Although MySQL5.6 introduces materialization features, it needs to be noted that it is currently only optimized for query statements. For updates or deletions, you need to manually rewrite to JOIN. For example, in the following UPDATE statement, MySQL actually executes a circular / nested subquery (DEPENDENT SUBQUERY), and its execution time is conceivable.

Execute the plan:

After rewriting to JOIN, the selection mode of the subquery changes from DEPENDENT SUBQUERY to DERIVED, and the execution speed is greatly reduced from 7 seconds to 2 milliseconds.

The execution plan is simplified to:

4. Mixed sorting

MySQL cannot use indexes for mixed sorting. However, in some scenarios, there is an opportunity to use special methods to improve performance.

The execution plan is displayed as a full table scan:

Since is_reply has only 0 and 1 states, the execution time is reduced from 1.58 seconds to 2 milliseconds after rewriting as follows.

5. EXISTS statement

When MySQL treats the EXISTS clause, it still executes the nested subquery. Such as the following SQL statement:

The implementation plan is:

Removing exists and changing it to join can avoid nesting subqueries and reduce the execution time from 1.93 seconds to 1 milliseconds.

New implementation plan:

6. Push external query conditions under conditions can not be pushed down to complex views or subqueries:

Aggregate subquery

Subquery with LIMIT

UNION or UNION ALL subquery

Subqueries in the output field

As in the following statement, you can see from the execution plan that its condition acts on the aggregate subquery:

Make sure that the semantic query condition can be pushed down directly, and rewrite as follows:

The execution plan is changed to:

7. Narrow the scope in advance and start with the initial SQL statement:

The number is 900000 and the time consumption is 12 seconds.

Since the final WHERE condition and sorting are for the leftmost main table, we can reduce the amount of data in advance of my_order sorting before making a left join. After SQL rewriting, the execution time is reduced to about 1 millisecond.

Check the execution plan again: the subquery is materialized (select_type=DERIVED) to participate in the JOIN. Although the estimated row scan is still 900000, the actual execution time becomes very small after using the index and the LIMIT clause.

8. Push down the intermediate result set

Let's take a look at the following example that has been initially optimized (the main table precedence query condition in the left join):

So are there any other problems with this statement? It is not difficult to see that the subquery c is a full table aggregate query, which will degrade the performance of the entire statement in the case of a particularly large number of tables. In fact, for subquery c, the left concatenation of the final result set is only concerned with the data that can match the main table resourceid. So we can rewrite the statement as follows, and the execution time is reduced from 2 seconds to 2 milliseconds.

But the subquery an appears multiple times in our SQL statement. This way of writing not only has extra overhead, but also makes the whole sentence complicated. Rewrite it again using the WITH statement:

The summary database compiler generates the execution plan, which determines the actual execution mode of SQL. But compilers only try their best to serve, and all database compilers are not perfect. Most of the scenarios mentioned above also have performance problems in other databases. Only by understanding the characteristics of the database compiler can we avoid its shortcomings and write high-performance SQL statements. Programmers should bring in the idea or consciousness of algorithms when designing data models and writing SQL statements. To write complex SQL statements, you should get into the habit of using WITH statements. Concise and clear-thinking SQL statements can also reduce the burden on the database.

After reading the above, have you mastered which of the 8 worst misuses of SQL are? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.