sql limit不接具体数字_8个SQL错误：您是否犯了这些错误？

最新推荐文章于 2022-11-05 08:44:43 发布

weixin_39736150

最新推荐文章于 2022-11-05 08:44:43 发布

阅读量406

点赞数

文章标签： sql limit不接具体数字

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)