认识九大经典sql模式

小结果集,源表较少,查询条件直接针对源表 对于典型的OLTP应用,多为返回小结果集的查询。如果过滤条件直接针对源表,我们必须保证这些过滤条件高效,对于重要的字段,考虑加上索引。如果涉及连接多表的情况,需要优化连接顺序,尽快过滤不符合条件的记录。如果统计数据足够精确地反映了表的内容,优化器有可能对连接顺序做出适当选择 在使用索引字段的时候要注意,函数或者隐式转换会导致索引失效。在确定重要字段有索引的情况下,还必须如果是非唯一性索引或者基于唯一性索引的范围扫描,还需要考虑聚集索引与分区,物理数据的顺序是否与索引一致,对性能影响很大 小结果集,查询条件涉及源表之外的表 我们想要的数据来自一个表,但查询条件是针对其它表的,且不需要从这些表返回任何数据。就像之前讨论过的订单的例子,这类查询可以使用连接,加上distinct去除结果中的重复记录。但较好的方式是使用子查询,在没有其它条件的情况下,优先考虑非关联子查询,因为关联子查询需要扫描源表 小结果集,多个宽泛条件,结果集取交集 分别使用各个条件时,会产生大量数据,但各个条件的交集是小结果集。使用正规连接,关联子查询,还是非关联子查询,要根据不同条件的过滤能力和已存在哪些索引而定 小结果集,一个源表,查询条件宽泛且涉及多个源表之外的表 如果查询条件可选择性较差,优化器可能会选择忽略它们,先访问关联的所有小型,再对其它表运用过滤条件。将提供的查询条件推迟执行,不利于减少要处理的数据量。这时我们必须迫使DBMS依我们所需的方式执行查询。多数SQL方言都支持优化器的提示(hint),但这种方法会随着未来的环境,数据量,硬件等因素的变化而变得不适用。更优雅的方法是在from子句中采用嵌套查询,在数值表达式中建议连接关系。通常没有必要采用非常具体的的方式和难以理解的提示,提供正确的最初指导就可使优化器找到正确的执行路径。混乱的查询会让优化器困惑,结构清晰的查询及合理的连接建议,通常足以帮助优化器提升性能 大结果集 如果查询返回几万条记录,那么使用索引是没有意义的,借助hash join或者merge join进行全表扫描是合适的。我们必须扫描数据返回比例最高的表,它违背了尽快去除不必要数据这一原则,但一旦扫描结束应立即重新贯彻该原则 在大结果集的情况下,每条记录的处理都必须小心,避免性能不佳的自定义函数的调用,另外处理大量记录时,关联查询是性能杀手。录一个查询包含多个子查询时,必须让它们操作各不相同的数据子集,避免子查询相互依赖,到查询执行的最后阶段,多个子查询得到的不同数据集经过hash join或者集合操作得到结果集 结果集来自基于一个表的自连接 自连接的情况除了一般规则之外,比如保证索引高效,应该尽量通过一次处理收集所有感兴趣的记录,再使用诸如case语句等结构分别显示记录。通常当需要查找和最小,最大,最早或最近的值相关的数据时,首先必须找到这些值本身,接下来用这些值作为第二遍扫描的搜索条件。而以滑动窗口(sliding window)为基础的OLAP函数,可以将两遍扫描合而为一。当多个选取条件作用于同一个表的不同记录时,可以使用基于滑动窗口工作的函数 结果集以聚合函数为基础获得 此时结果集大小取决于group by的字段基数而不是查询条件的精确性。实际上最让人感兴趣的SQL聚合使用技巧,不是显式的sum或avg,而是如何将过程性处理转化为以聚合为基础的纯SQL替代方案。优秀SQL编程的困难,多半在于解决问题的方式,不要将一个问题转换成对数据库的一系列查询,而是转换成少数查询。程序中大量中间变量保存从数据库读出的值,然后根据变量进行简单判断,最后再把它们作为其它查询的输入,这样做是错误的。糟糕的SQL编程有个显著特点,就是SQL之外存在大量代码,以循环的方式对返回数据进行加,减,乘,除之类的处理,这里的工作应该交给SQL的聚合函数 比如以下的查询语句:

  select shipment_id from shipments where shipment_id not in (select shipment_id from orders where order_complete = 'N')

可以转化为聚合函数:

  select shipment_id from orders group by shipment_id having sum(case when order_complete = 'N' then 1 else 0 end) =0

甚至可以不进行任何转换:

  select shipment_id from orders group by shipment_id having min(order_complete) = 'Y'

聚合操作的数据应尽量少,把条件放到where子句中,能让多余的记录尽早被过滤掉,因而更高效 结果集通过简单搜索或基于日期的范围搜索获得 如果历史数据较少,那么各项ID的可选择性很高,比如:

  select whatever from hist_data as outer where (outer.item_id, outer.record_date) in (select inner.item_id, max(inner.record_date) from hist_data as inner where inner.item_id = somevalue and inner.record_date <= reference_date group by inner.item_id)

OLAP在查询特定日期某数据项的值时也同样有用,但OLAP属于SQL的非关系层 对于大量历史数据的情况下,难度在于排序,对大量数据的排序代价是很高的。而且排序是非关系操作,降低非关系层厚度的唯一方法就是在关系层多做一些工作,增加过滤条件的数量。此时,针对所需数据更精确地归类日期以缩小范围,便非常重要。如果我们把数据控制在可管理的大小,就相当于回到了少量历史记录的情况。如果无法同时指定上限和下限,我们的唯一希望就是根据数据项分区,在单一分区上操作,这比较接近大结果集的情况 结果集和别的数据存在与否有关 对于识别例外的需求,最常用的解决方案有两个:not in搭配非关联子查询,或者not exists搭配关联子查询。在子查询出现在高效搜索条件之后,使用not exists是对的,但当子查询是唯一条件时,使用not in比较好 使用count(*)测试某些数据是否存在是个糟糕的主意,为此DBMS必须搜索并找出所有相符的记录,此时应该使用exists,它会在遇到第一个相符的数据时就停止。 集合操作符的重大优点是彻底打破了子查询强加的时间限制。当存在关联子查询时,就必须执行外层查询,接着对所有通过过滤条件的记录执行内层查询。外层查询和内层查询相互依赖,因为外层查询会把数据传递给内层查询。非关联子查询必须先完成内层查询之后,外层查询才能介入。相比之下,使用集合操作符union, intersect或except时,查询中的这些组成部分不会彼此依赖,从而不同部分的查询可以并行执行,最后把不完整的结果集组合起来,这就是分而治之 另一个表达非存在性的方法是使用外连接(out join),通过检查连接表的字段值是否为null找出它们。数据集可以通过各种技巧进行比较,但一般而言,使用外连接和子集合操作符更高效

本文分享自微信公众号 - java达人(drjava)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2015-09-08

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏人工智能的秘密

如何才能成为一名自然语言处理工程师?

自然语言处理和大部分的机器学习或者人工智能领域的技术一样,是一个涉及到多个技能、技术和领域的综合体。

61450
来自专栏电光石火

mysql 5.7版本目录无data文件夹的解决办法

安装mysql 5.7+版本时,若发现因根目录下,缺少data文件夹的情况, ***请不要去拷贝其他版本的data文件夹!*** 因为此操作会出现很多潜...

22370
来自专栏Vamei实验室

Java进阶05 多线程

多线程 多线程(multiple thread)是计算机实现多任务并行处理的一种方式。 在单线程情况下,计算机中存在一个控制权,并按照顺序依次执行指令。单线程好...

25460
来自专栏偏前端工程师的驿站

MyBatis魔法堂:Insert操作详解(返回主键、批量插入)

一、前言                                      数据库操作怎能少了INSERT操作呢?下面记录MyBatis关于INSERT...

1.5K80
来自专栏LanceToBigData

MySQL(二)之服务管理与配置文件修改和连接MySQL

上一篇给大家介绍了怎么在linux和windows中安装mysql,本来是可以放在首页的,但是博客园说“安装配置类文件”不让放在首页。接下来给大家介绍一下在li...

28580
来自专栏Vamei实验室

纸上谈兵: 队列 (queue)

作者:Vamei 出处:http://www.cnblogs.com/vamei 欢迎转载,也请保留这段声明。谢谢! 队列(queue)是一个简单而常见的数据结...

21760
来自专栏Vamei实验室

纸上谈兵: 栈 (stack)

栈(stack)是简单的数据结构,但在计算机中使用广泛。它是有序的元素集合。栈最显著的特征是LIFO (Last In, First Out, 后进先出)。当我...

26850
来自专栏Vamei实验室

纸上谈兵: 伸展树 (splay tree)

我们讨论过,树的搜索效率与树的深度有关。二叉搜索树的深度可能为n,这种情况下,每次搜索的复杂度为n的量级。AVL树通过动态平衡树的深度,单次搜索的复杂度为log...

236100
来自专栏LanceToBigData

MySQL之乱码问题解决详解

今天在写一个项目的时候,在数据库中手动插入数据不会产生中文乱码,但是通过javaWeb却出现乱码,把提交表单和响应中的乱码问题解决后,还是乱码。所以我锁定一定是...

24970
来自专栏Vamei实验室

纸上谈兵: 表 (list)

表 表(list)是常见的数据结构。从数学上来说,表是一个有序的元素集合。在C语言的内存中,表储存为分散的节点(node)。每个节点包含有一个元素,以及一个指向...

20680

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励