• 转载
  • 数据库
  • 8个SQL错误:您是否犯了这些错误?

    2020.07.03 14:51发布

    6157人阅读

    0人评论

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


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


    1)LIMIT语句


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


    8个SQL错误:您是否犯了这些错误?

     



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


    2)隐式转换


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


    8个SQL错误:您是否犯了这些错误?

     



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


    3)更新和删除加入


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


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


    8个SQL错误:您是否犯了这些错误?

     



    考虑以下执行计划。


    8个SQL错误:您是否犯了这些错误?

     



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


    8个SQL错误:您是否犯了这些错误?

     



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


    8个SQL错误:您是否犯了这些错误?

     



    4)混合排序


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


    8个SQL错误:您是否犯了这些错误?

     



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


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


    8个SQL错误:您是否犯了这些错误?

     



    5)EXISTS 陈述


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


    8个SQL错误:您是否犯了这些错误?

     



    请参考以下执行计划。


    8个SQL错误:您是否犯了这些错误?

     



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


    8个SQL错误:您是否犯了这些错误?

     



    考虑下面的新执行计划。


    8个SQL错误:您是否犯了这些错误?

     



    6)有条件下推


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


    · 汇总子查询


    · LIMIT个子查询


    · UNION或UNION ALL子查询


    · 输出字段中的子查询


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


    8个SQL错误:您是否犯了这些错误?

     



    8个SQL错误:您是否犯了这些错误?

     



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


    8个SQL错误:您是否犯了这些错误?

     



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


    8个SQL错误:您是否犯了这些错误?

     



    7)提前缩小范围


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


    8个SQL错误:您是否犯了这些错误?

     



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


    8个SQL错误:您是否犯了这些错误?

     



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


    8个SQL错误:您是否犯了这些错误?

     



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


    8个SQL错误:您是否犯了这些错误?

     



    8)下推中间结果集


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


    8个SQL错误:您是否犯了这些错误?

     



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


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


    8个SQL错误:您是否犯了这些错误?

     



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


    8个SQL错误:您是否犯了这些错误?

     



    摘要


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


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


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


    文章来源于网络,版权归原作者所有,内容为作者个人观点,文章仅供学习,如有侵权请联系客服删除,本站拥有对此声明的最终解释权。

  • SQL常见错误
  • 举报文章

  • 收藏博客:

  • 分享至:
  • 添加评论

    请先登录再评论...

    登录

    评论列表(条评论)

    没有更多评论了