SQL编程既令人兴奋又具有挑战性。 即使是经验丰富的SQL程序员，开发人员和数据库管理员（DBA）有时也要面对SQL语言的挑战。 本文旨在帮助用户识别此类严重错误并学习克服它们。

让我们深入研究以下几节中最严重的8个SQL错误。

1）LIMIT语句

分页查询是最常见的情况之一，但也是一个非常普遍的问题。 例如，对于以下简单语句，DBA通常为type，name和create_time字段添加复合索引。 这种条件排序可有效使用索引并快速提高性能。 这是90％以上的DBA解决此问题的常用方法。 但是，当LIMIT子句更改为" LIMIT 1000000,10"时，程序员经常抱怨仅检索10条记录会花费太长时间。 发生这种情况是因为数据库不知道第1,000,000条记录从何处开始。 因此，即使索引可用，也必须从头开始计算。 通常由于程序员的懒惰而导致出现此性能问题。 在前端数据浏览和分页或大数据的批量导出之类的方案中，可以将上一页的最大值用作查询条件。 重写SQL代码，如下所示：

 

使用新设计，查询时间基本上是固定的，并且不会随着数据量的增加而改变。

2）隐式转换

当查询变量与SQL语句中的字段定义类型不匹配时，会发生另一种常见错误。 以下语句就是这样的一个例子：

 

bpn字段定义为varchar（20），MySQL策略是在比较之前将字符串转换为数字。 当函数作用于表字段时，索引变为无效。 前面的问题可能是由应用程序框架自动完成的参数引起的，而不是由于程序员的有意识的错误。 当前，许多应用程序框架都很复杂。 尽管它们使用起来非常方便，但是您还必须意识到它们可能引起的潜在问题。

3）更新和删除加入

尽管实例化功能是MySQL 5.6中引入的，但是请注意，目前仅针对查询语句进行了优化。 手动将UPDATE或DELETE语句重写为JOIN语句。

例如，在下面的UPDATE语句中，MySQL实际上运行循环或嵌套子查询（DEPENDENT SUBQUERY），并且执行时间相对较长。

 

考虑以下执行计划。

 

将语句重写为JOIN语句后，子查询选择模式将从DEPENDENT SUBQUERY更改为DERIVED，从而将所需时间从7秒减少到2毫秒。

 

请参考以下简化的执行计划。

 

4）混合排序

MySQL不能将索引用于混合排序。 但是，在某些情况下，用户仍然可以使用特殊方法来提高性能。

 

执行计划以全表扫描形式呈现。

由于is_reply在按照方法重写后仅具有状态0和1，因此执行时间从1.58秒减少到2毫秒。

 

5）EXISTS 陈述

MySQL仍然使用嵌套子查询来处理EXISTS子句。 例如，考虑下面的SQL语句：

 

请参考以下执行计划。

 

将EXISTS语句更改为JOIN语句可避免嵌套子查询，并将执行时间从1.93秒减少到1毫秒。

 

考虑下面的新执行计划。

 

6）有条件下推

在以下情况下，无法将外部查询条件下推到复杂视图或子查询：

· 汇总子查询

· LIMIT个子查询

· UNION或UNION ALL子查询

· 输出字段中的子查询

在以下语句的执行计划中，请注意，条件在聚集子查询之后起作用。

 

 

确保直接向下推语义查询条件，然后将其重写如下：

 

请参考以下更新的执行计划。

 

7）提前缩小范围

从初始SQL语句开始，如下所示。

 

数量为900,000，执行时间为12秒。

 

由于最后一个WHERE条件和排序是在最左边的主表上执行的，因此在执行左连接之前，首先要缩小数据量以进行my_order排序。 如下所示重写SQL语句后，执行时间减少到大约1毫秒。

 

查看执行计划。 实现子查询后，select_type = DERIVED参与JOIN操作。 尽管估计要扫描的行数仍为900,000，但在应用索引和LIMIT子句后，实际的执行时间会减少。

 

8）下推中间结果集

让我们看一下以下最初优化的示例（查询条件首先作用于左联接中的主表）：

 

此声明还有其他问题吗？ 不难看出，子查询c是全表聚合查询。 因此，当表的数量特别多时，整个语句的性能会下降。

实际上，对于子查询c，左联接的最终结果集仅与匹配主表resourceid的数据有关。 因此，按如下所示重写该语句，以将执行时间从2秒减少到2毫秒。

 

但是，子查询多次出现在SQL语句中。 此方法不仅会导致额外的开销，而且会使整个语句更加复杂。 使用WITH语句再次重写该语句。

 

摘要

数据库编译器生成一个执行计划，该计划确定SQL语句的实际执行方法。 但是，编译器仅尽其所能提供服务，而没有哪个数据库编译器是完美的。

在大多数上述情况下，其他数据库中也会出现性能问题。 您必须了解数据库编译器的功能，以避免其缺点并编写高性能的SQL语句。

在设计数据模型和编写SQL语句时，请结合您的算法思想和认识。 例如，在编写复杂的SQL语句时，请尽可能使用WITH子句。 简单明了的SQL语句还可以减轻数据库的负担。

原始资料来源：

https://www.alibabacloud.com/blog/8-sql-pitfalls-are-you-making-these-mistakes_596168

(本文翻译自Alibaba Cloud的文章《8 SQL Pitfalls: Are You Making These Mistakes?》，参考：https://medium.com/dataseries/8-sql-pitfalls-are-you-making-these-mistakes-b8f9d8181a1c)