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

Eight SQL error usage is what, I believe that many inexperienced people are helpless about this, this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

1. LIMIT statement

Pagination queries are one of the most common scenarios, but they are also often the most problematic. For example, for the following simple statement, the common DBA idea is to add a composite index to the type, name, create_time field. Such conditional sorting can effectively utilize the index, and the performance is rapidly improved.

Well, probably more than 90% of DBAs solve this problem so far. But when the LIMIT clause becomes "LIMIT 100000,10," the programmer still complains: Why am I still slow when I only get 10 records?

The database doesn't know where the 100000th record starts, and even if there is an index, it needs to be calculated from scratch. In most cases, this performance problem occurs because programmers are lazy.

In scenarios such as front-end data browsing and page turning, or batch export of large data, the maximum value of the previous page can be used as a query condition. SQL was redesigned as follows:

Under the new design, the query time is basically fixed and does not change with the increase of data volume.

2. Implicit conversion

Mismatching query variable and field definition types in SQL statements is another common error. For example, the following statement:

Where the field bpn is defined as varchar(20), MySQL's strategy is to convert strings to numbers before comparing them. Function on table field, index invalid.

These situations may be parameters that the application framework automatically fills in, rather than the programmer's intent. Now the application framework is very complex, easy to use but also careful that it may give themselves a hole.

3. Association update and deletion

Although MySQL 5.6 introduces materialization, it is important to note that it is currently optimized only for query statements. For updates or deletions it needs to be rewritten manually as JOIN.

For example, the following UPDATE statement, MySQL actually executes a loop/nested subquery (DEPENDENT SUBQUERY), and its execution time can be imagined.

Implementation plan:

After rewriting to JOIN, the selection mode of the subquery changed from DEPENDENT SUBQUERY to DERIVED, and the execution speed was greatly improved, from 7 seconds to 2 milliseconds.

The implementation plan is simplified to:

4. Mixed sorting

MySQL cannot use indexes for mixed sorting. However, in some scenarios, there are opportunities to use special methods to improve performance.

The execution plan is displayed as a full table scan:

Since is_reply has only two states, 0 and 1, we rewrite it as follows, reducing the execution time from 1.58 seconds to 2 milliseconds.

5. EXISTS statement

MySQL treats the EXISTS clause in the same way as nested subqueries. For example, the following SQL statement:

The implementation plan is:

Removing exists and changing it to join avoids nested subqueries and reduces execution time from 1.93 seconds to 1 millisecond.

New implementation plan:

6. Push under conditions

External query conditions cannot be pushed down to complex views or subqueries:

aggregate subqueries;

Subqueries containing LIMIT;

UNION or UNION ALL subquery;

Subqueries in output fields;

For example, the following statement, from the execution plan, can be seen that its conditions act after the aggregate subquery:

After determining that the query condition can be directly extrapolated from semantics, it is rewritten as follows:

The implementation plan becomes:

7. Reduce the scope in advance

Start with the initial SQL statement:

900,000, 12 seconds.

Since the last WHERE condition and sorting are for the leftmost main table, you can reduce the data amount in advance for my_order sorting before doing left join. SQL rewrite as follows, execution time reduced to about 1 millisecond.

Re-check the execution plan: after the subquery materializes (select_type=DERIVED), it participates in JOIN. Although the estimated row scan is still 900,000, the actual execution time becomes very small with the index and LIMIT clause.

8. Push down the intermediate result set

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

Is there any other problem with this statement? It is not difficult to see that subquery c is a full table aggregation query, which will lead to the performance degradation of the whole statement in the case of a particularly large number of tables.

In fact, for subquery c, the final result set of the left join is only concerned with data that matches the main table resourceid. So we can rewrite the statement as follows, reducing the execution time from 2 seconds to 2 milliseconds.

But subquery a appears multiple times in our SQL statement. Not only does this have extra overhead, but it also makes the whole sentence complex. Rewrite again with the WITH statement:

The database compiler generates execution plans that determine how SQL is actually executed. But compilers only do their best, and all database compilers are not perfect.

Most of the scenarios mentioned above have performance issues in other databases as well. Understand the characteristics of database compiler, in order to avoid its shortcomings, write high-performance SQL statements.

Programmers design data models and write SQL statements with algorithmic ideas or awareness.

Write complex SQL statements to 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, do you know what the eight SQL error usages are? If you still want to learn more skills or want to know more related content, welcome to pay attention to 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.