8 common SQL usage errors in Mysql

Preface

MySQL continued to maintain a strong growth trend in database popularity in 2016. More and more customers are building their applications on the MySQL database, or even migrating from Oracle to MySQL. However, some customers also encounter problems such as slow response time and full CPU usage when using the MySQL database.

Alibaba Cloud RDS expert service team has helped cloud customers solve many emergency problems. Some common SQL problems that appear in the "ApsaraDB Expert Diagnostic Report" are summarized as follows for your reference.

1. LIMIT statement

Paging query is one of the most commonly used scenarios, but it is also usually the most prone to problems.

For example, for the following simple statement, the general DBA's idea is to add a combined index on the type, name, and create_time fields. In this way, conditional sorting can effectively utilize the index, and the performance can be improved rapidly.

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

Well, maybe more than 90% of DBAs solve this problem and stop there.

But when the LIMIT clause becomes "LIMIT 1000000,10", programmers will still complain: Why is it still slow if I only fetch 10 records?

You must know that the database does not know where the 1,000,000th record begins. Even if there is an index, it needs to be calculated from the beginning. When this kind of performance problem occurs, in most cases the programmer is lazy. In scenarios such as front-end data browsing and page turning, or big data export in batches, the maximum value of the previous page can be used as a parameter as a query condition. SQL is redesigned as follows:

SELECT   *  FROM     operation  WHERE    type = 'SQLStats'  AND      name = 'SlowLog'  AND      create_time > '2017-03-16 14:00:00'  ORDER BY create_time limit 10;

2. Implicit conversion

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

The field bpn is defined as varchar(20). MySQL's strategy is to convert the string into a number before comparing. The function acts on table fields and the index becomes invalid.

The above situation may be the parameters automatically filled in by the application framework, rather than the programmer's original intention. Nowadays, there are many application frameworks that are very complicated. While they are easy to use, be careful that they may dig holes for you.

3. Association update and deletion

Although MySQL5.6 introduces the materialization feature, special attention should be paid to the fact that it is currently only optimized for query statements. For updates or deletions, you need to manually rewrite it as JOIN.

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

Execution plan:

4. Mixed sort

MySQL Indexes cannot be used for mixed sorting. But in some scenarios, there are still opportunities to use special methods to improve performance.

The execution plan is displayed as a full table scan:

Since is_reply only has two states: 0 and 1, we follow After the following method was rewritten, the execution time was reduced from 1.58 seconds to 2 milliseconds.

5、EXISTS语句

MySQL 对待 EXISTS 子句时，仍然采用嵌套子查询的执行方式。如下面的 SQL 语句：

执行计划为：

去掉 exists 更改为 join，能够避免嵌套子查询，将执行时间从1.93秒降低为1毫秒。

新的执行计划：

6、条件下推

外部查询条件不能够下推到复杂的视图或子查询的情况有：

聚合子查询；

含有 LIMIT 的子查询；

UNION 或 UNION ALL 子查询；

输出字段中的子查询；

如下面的语句，从执行计划可以看出其条件作用于聚合子查询之后：

确定从语义上查询条件可以直接下推后，重写如下：

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

执行计划变为：

关于 MySQL 外部条件不能下推的详细解释说明请参考文章：

http://mysql.taobao.org/monthly/2016/07/08

相了解更多相关问题请访问PHP中文网：mysql视频教程

The above is the detailed content of 8 common SQL usage errors in Mysql. For more information, please follow other related articles on the PHP Chinese website!