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

The following mainly brings you what are the commonly used SQL misuses of Mysql, and I hope that the more commonly used SQL misuses of Mysql can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

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.

SELECT * FROM operation WHERE type = 'SQLStats' AND name =' SlowLog' ORDER BY create_time LIMIT 1000, 10

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:

SELECT * FROM operation WHERE type = 'SQLStats' AND name =' SlowLog' AND create_time > '2017-03-16 1414 SQLStats' AND name 0000' ORDER BY create_time limit 10

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:

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. Conditional push-down

Situations where external query conditions cannot be pushed down to complex views or subqueries are:

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:

SELECT target Count (*) FROM operation WHERE target = 'rm-xxxx' GROUP BY target

The execution plan is changed to:

For the above about the more common misuse of Mysql SQL, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.