锦囊妙计2

大结果集

无论结果集是如何获得的，只要结果集很大，就符合我们下面要讨论的大结果集的情况。

如果查询返回几万条记录，那么使用索引是没有意义的。借助哈希或合并连接进行全表扫描是合适的。

采取扫描方式不合适的情况下，应尽量减少要访问的数据的块数。最常用的手段就是使用索引（而不是表），尽管所有索引的总数据量经常比表还大，但单个索引则远比表要小。如果索引包含了所有需要的信息，则扫描索引而不扫描表是相当合理的，可以利用诸如聚集索引等避免访问表的技术。

子查询的使用。处理大量记录时，关联子查询时性能杀手。当一个查询包含多个子查询时，必须让它们操作各不相同、自给自足的数据子集，以避免子查询相互依赖；到查询执行的最后阶段，多个子查询分别得到不同数据经过哈希连接或集合操作得到结果集。

基于一个表的自连接

自连接指表与自身的连接，这种情况比分层查询更常见。

lag与lead函数是跟偏移量相关的两个分析函数，通过这两个函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而更方便地进行进行数据过滤。这种操作可以代替表的自联接，并且LAG和LEAD有更高的效率。

over()表示 lag()与lead()操作的数据都在over()的范围内，他里面可以使用partition by 语句（用于分组） order by 语句（用于排序）。partition by a order by b表示以a字段进行分组，再 以b字段进行排序，对数据进行查询。

 

当需要查找和最小、最大、最早、或最近的值相关联的数据时，首先必须找到这些值本身（第一遍扫描，需比较记录），接下来的用这些值作为第二遍扫描的搜索调价。而以滑动窗口为基础的OLAP函数，可以将两边扫描合二为一。

通过聚合获得结果集

对一个或多个主表中的详细数据进行汇总，动态计算出结果集。

面临数据集合的问题，结果集大小取决于group by 的字段的基数，而不是查询条件的精确性。

查询过去一个月内每个产品的每周销售情况：

基于日期的简单搜索或范围搜索

大量数据项、少量历史数据

为了查询特定日期的值，可采用两种方法：子查询和OLAP函数。

首先，内层查询与外层查询是有关联的，因为内层查询参照了item_id的值，该值是由外层查询返回的当前记录的一个字段。

如果我们误把主键定义为（record_date,item_id），而不是（item_id,reocrd_date），前列的内层查询就非常依赖item_id字段的索引，否则无法高效的向下访问树状结构索引。额外增加一个索引的代价跟高。

外层找到了保存item_id历史的各条记录，接着使用当前item_id值主次执行子查询。内层查询只依赖item_id，这与外层查询的记录相同，这意味着我们执行相同的查询，返回相同的结果。

在使用关联子查询时，如果它处理不同的记录后总是返回相同的值，就没有意义了。所以，应该为无关联子查询。

现在子查询的执行不需要访问表，只需要访问主键索引就够了。

-------

如果DBMS支持将‘子查询的输出’与多个字段进行比较

让子查询返回的字段，完全与复合主键的字段相符。

只要每个数据项的历史信息数量都较少，以in（）列表或子查询取代内层查询中的somevalue，会使整个查询执行更高效。in子句取代“相等性条件”在多数情况下没有什么不同，偶尔例外，例如：如果用户输错了item_id，采用in（）时会返回未发现数据，而采用‘相等性条件’时会返回错误数据。

 

使用olap函数

OLAP函数属于SQL的非关系层。这类函数的作用是：在查询中做最后处理。因为他们在过滤已完成后对结果集进行处理。

 

运用row_number（）函数，可以通过日期排序判断数据的新旧程度。

OLAP函数只访问一次表。

结果集和别的数据是否有关

一个表中的哪些记录和另一个表中的数据不匹配：not in（）搭配非关联子查询，或者not exists（）搭配关联子查询。

当子查询恰好是唯一条件时，使用not in比较好。

当我们只想建立一条新纪录，且新纪录要从已存在于表中的记录推导出来时，正确的做法是使用诸如except这样的集合操作符。

集合操作符的重大有点是打破了子查询强加的时间限制。

当存在关联子查询时，就必须执行外层查询，接着对所有通过过滤条件的记录，执行内层查询。外层查询和内层查询相互依赖，因为外层查询会把数据传递给内层查询。

使用非管理子查询时。必须先完成内层查询之后，外层查询才能介入。即使优化器选择把整个查询作为哈希连接执行，。因为要执行哈希连接，SQL引擎必须先进性表扫描以建立哈希数组。

使用集合操作符union、intersect或except时，查询中的这些组成部分不会彼此依赖，从而不同 部分的查询可以并行执行。

集合操作符要求：各部分查询必须返回兼容的字段---字段的类型和数量都要相同。