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

What are the common SQL errors? For this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more small partners who want to solve this problem find a simpler and easier way.

SQL programming is exciting and challenging. Even experienced SQL programmers, developers and database administrators (DBAs) sometimes face challenges with the SQL language.

Let's dive into the eight most serious SQL errors in the following sections.

1)LIMIT statement

Pagination queries are one of the most common cases, but they are also a very common problem. For example, for the following simple statement, DBAs typically add compound indexes to the type, name, and create_time fields. This conditional sort makes efficient use of indexes and quickly improves performance. This is a common approach used by more than 90% of DBAs to solve this problem. However, programmers often complain that retrieving only 10 records takes too long when the LIMIT clause is changed to " LIMIT 100000,10." This happens because the database does not know where the 1,000,000 record starts. Therefore, even if the index is available, it must be calculated from scratch. This performance problem is usually caused by programmer laziness. In scenarios such as front-end data browsing and pagination or bulk export of large data, you can use the maximum value of the previous page as a query criterion. Rewrite the SQL code as follows:

With the new design, query time is essentially fixed and does not change as the amount of data increases.

2)implicit conversion

Another common error occurs when a query variable does not match a field definition type in an SQL statement. The following statement is an example of this:

The bpn field is defined as varchar(20) and MySQL policy is to convert strings to numbers before comparing. When a function is applied to a table field, the index becomes invalid. The previous problem may have been caused by parameters that the application framework automatically completed, rather than by conscious error on the part of the programmer. Today, many application frameworks are complex. Although they are very convenient to use, you must also be aware of the potential problems they can cause.

3)Update and delete add

Although instantiation was introduced in MySQL 5.6, note that it is currently optimized only for query statements. Manually rewrite UPDATE or Delete statements as JOIN statements.

For example, in the UPDATE statement below, MySQL actually runs a loop or nested subquery (DEPENDENT SUBQUERY) and takes a relatively long time to execute.

Consider the following implementation plan.

When the statement is rewritten as a JOIN statement, the subquery selection mode changes from DEPENDENT SUBQUERY to DERIVED, reducing the time required from 7 seconds to 2 milliseconds.

Please refer to the simplified implementation plan below.

4)mixed sort

MySQL cannot use indexes for mixed sort. However, in some cases, users can still use special methods to improve performance.

The execution plan is presented as a full table scan.

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

5)EXISTS Statement

MySQL still uses nested subqueries to process the EXISTS clause. For example, consider the following SQL statement:

Please refer to the implementation plan below.

Changing the EXISTS statement to a JOIN statement avoids nested subqueries and reduces execution time from 1.93 seconds to 1 millisecond.

Consider the new implementation plan below.

6)conditional deduction

You cannot push down an external query condition to a complex view or subquery if:

Summarize subqueries

LIMIT Subquery

UNION or UNION ALL subquery

Subqueries in Output Field

In the execution plan for the following statement, note that the condition takes effect after the subquery is aggregated.

Make sure to push down the semantic query condition directly and rewrite it as follows:

Please refer to the updated implementation plan below.

7)Narrow the scope ahead of time

Start with the initial SQL statement as follows.

The number is 900,000 and the execution time is 12 seconds.

Because the last WHERE condition and sort is performed on the leftmost main table, the data size is first reduced for my_order sort before performing the left join. When the SQL statement is rewritten as shown below, execution time is reduced to approximately 1 millisecond.

View the implementation plan. After implementing the subquery, select_type = DERIVED participates in the JOIN operation. Although the estimated number of rows to scan is still 900,000, the actual execution time decreases after applying the index and LIMIT clauses.

8)Push down the intermediate result set

Let's look at the following example of initial optimization (query condition first applied to the main table in the left join):

Do you have any other questions about this statement? It is easy to see that subquery c is a full table aggregate query. Therefore, when the number of tables is particularly large, the performance of the entire statement degrades.

In fact, for subquery c, the final result set of the left join only relates to data matching the main table resourceid. Therefore, rewrite the statement as follows to reduce execution time from 2 seconds to 2 milliseconds.

However, subqueries appear multiple times in SQL statements. This approach not only incurs additional overhead, but also makes the entire statement more complex. Rewrite the statement again with the WITH statement.

The database compiler generates an execution plan that determines the actual execution of SQL statements. However, compilers only do what they can, and no database compiler is perfect.

In most of these cases, performance issues occur in other databases as well. You must understand the capabilities of the database compiler to avoid its shortcomings and write high-performance SQL statements.

When designing data models and writing SQL statements, incorporate your algorithmic thinking and understanding. For example, when writing complex SQL statements, use the WITH clause whenever possible. Simple, straightforward SQL statements can also reduce the burden on the database.

About the common SQL error specific what questions the answer to share here, I hope the above content can have some help for everyone, if you still have a lot of doubts not solved, you can pay attention to the industry information channel to learn more related knowledge.

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.