SQL编程既令人兴奋又充满挑战。即使是经验丰富的SQL程序员、开发人员和数据库管理员(DBA)有时也会面临SQL语言方面的挑战。本文旨在帮助用户识别此类严重错误并学会克服它们。让我们在以下部分中深入了解前8个最严重的SQL错误。1）LIMIT语句分页查询是最常见的一种情况，但也是一个很常见的问题。例如，对于下面的简单语句，DBA通常会为type、name和create_time字段添加复合索引。这种条件排序可以有效地利用索引并快速提高性能。这是90%以上的DBA解决这个问题的常用方法。但是，当LIMIT子句更改为“LIMIT1000000,10”时，程序员经常抱怨只检索10条记录花费的时间太长。发生这种情况是因为数据库不知道第1,000,000条记录从哪里开始。因此，即使有指标，也必须从头计算。这种性能问题通常是由于程序员的懒惰而发生的。在前端数据浏览分页或批量导出大数据等场景下，可以使用上一页的最大值作为查询条件。改写SQL代码如下：新的设计，查询时间基本固定，不随着数据量的增加而改变。2）隐式转换当查询变量与SQL语句中字段定义的类型不匹配时，会出现另一种常见错误。下面的语句就是一个例子：bpn字段定义为varchar(20)，MySQL的策略是先将字符串转换为数字再进行比较。当函数应用于表字段时，索引将变得无效。上述问题可能是应用程序框架自动补全参数导致的，而不是程序员有意识的错误。今天，许多应用程序框架都很复杂。尽管它们使用起来非常方便，但您也必须了解它们可能导致的潜在问题。3）更新和删除连接虽然在MySQL5.6中引入了实例化功能，但请注意，目前仅针对查询语句进行了优化。手动将UPDATE或DELETE语句重写为JOIN语句。比如下面的UPDATE语句，MySQL实际上运行了一个循环或者嵌套子查询（DEPENDENTSUBQUERY），执行时间比较长。考虑以下执行计划。改写为JOIN语句后，子查询选择方式由DEPENDENTSUBQUERY改为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或UNIONALL子查询输出字段中的子查询在下面语句的执行计划中，注意该条件在聚合子查询之后起作用。确保直接下推语义查询条件，然后重写如下：请参考下面更新的执行计划。7)提前缩小范围从最初的SQL语句开始，如下所示。数量为900,000，执行时间为12秒。由于最后的WHERE条件和排序是在最左边的主表上进行的，所以在进行leftjoin之前，先缩减my_order排序的数据大小。如下所示重写SQL语句后，执行时间减少到1毫秒左右。查看执行计划。子查询实现后，select_type=DERIVED参与JOIN操作。虽然估计要扫描的行数仍为900,000，但在应用索引和LIMIT子句后实际执行时间减少了。8）下推中间结果集我们来看下面这个初步优化的例子（查询条件先作用在leftjoin中的主表）：这个语句还有其他问题吗？不难看出，子查询c是一个全表聚合查询。因此，当表数特别多时，整个语句的性能就会下降。实际上，对于子查询c，leftjoin的最终结果集只与匹配主表resourceid的数据有关。因此，将语句重写如下，将执行时间从2秒减少到2毫秒。但是，子查询在SQL语句中出现了多次。这种方法不仅会产生额外的开销，还会使整个语句更加复杂。使用WITH语句再次重写该语句。总结数据库编译器生成一个执行计划，该计划决定了SQL语句将如何实际执行。然而，编译器只是尽可能好，没有数据库编译器是完美的。在上述大多数情况下，性能问题也可能发生在其他数据库中。您必须了解数据库编译器的功能，才能避免其缺点并编写出高性能的SQL语句。在设计数据模型和编写SQL语句时，请结合自己的算法思维和理解。例如，在编写复杂的SQL语句时，尽可能使用WITH子句。简单明了的SQL语句也可以减轻数据库的负担。
