友情提示:如果本网页打开太慢或显示不完整,请尝试鼠标右键“刷新”本网页!阅读过程发现任何错误请告诉我们,谢谢!! 报告错误
一世书城 返回本书目录 我的书架 我的书签 TXT全本下载 进入书吧 加入书签

SQL语言艺术(PDF格式)-第19章

按键盘上方向键 ← 或 → 可快速上下翻页,按键盘上的 Enter 键可回到本书目录页,按键盘上方向键 ↑ 可回到本页顶部!
————未阅读完?加入书签已便下次继续阅读!




如前所述,换成下列条件更加合理: 

andd。tax 》0 

上述的例子中,使用集合操作符会相当笨拙,因为必须访问invoice_detail表好几次——如你所 

料,那不是个轻量级的表。当然,还要看每个条件的可选择性,如果 type_ code=4很少见,那 

么它就是个可选择性很高的条件,exists或许会比notin()更适合。另外,如果trans_description正 

好是个小型表(或者相对较小),尝试通过单独操作测试存在性,并起不到改善性能的效果。 



另一个表达非存在性的方法很有趣——而且通常相当高效——是使用外连接(outerjoin)。外连 

接的主要目的是,返回来自一个表的所有信息及连接表中的对应信息。无对应信息的记录也需 

返回——查找另一个表中无对应信息的数据时,这些记录正好是我们的兴趣所在,可通过检查 

连接表的字段值是否为null找出它们。 



例如: 

select whatever 

from invoice_detail 

where type_code =4 



andsubtype_code notin 

(select trans_code 

from trans_description 

where trans_categoryin(6; 7)) 

或重写为: 

select whatever 

from invoice_detail 

outerjoin trans_description 

on trans_description。trans_category in(6; 7) 

andtrans_description。trans_code =invoice_detail。subtype_code 


…………………………………………………………Page 80……………………………………………………………

where trans_description。trans_code isnull 

我故意在join子句中加上trans_category的条件。有人认为它应该出现在where 子句中,实际上, 

在连接之前或在连接之后过滤都不影响结果(当然,根据这个条件和连接条件本身的可选择性 

不同,会有不同的性能表现)。然而,在使用空值上的条件时,我们别无选择,只有在连接后才 

能做检查。 



外连接有时需要加 distinct。实际上,通过外连接或notin()非关联子查询,来检查数据是否存在 

的差异很小,因为连接所使用的字段,正好与比较子查询结果集的字段完全相同。不过,众所 

周知的是,SQL 语言的“查询表达式风格”对“执行模式”影响很大,尽管理论上不是这么说的。 

这取决于优化器的复杂程度,以及它是否会以类似方法处理这两类查询。换言之,SQL 不是真 

正的声明性语言(SQLis not a truly declarative language),尽管优化器不断推陈出新改善SQL的 

可靠性(reliability)。 



最后提醒一下,应密切注意null,这个舞会扫兴者(party…poopers)经常出现。虽然在in()子查 

询中,null与大量非空值连接不会对外层查询造成影响,但在使用notin()子查询时,由内层查 

询返回的null会造成notin()条件不成立。要确保子查询不会返回null并不需要太高的代价,而且 

这么做可以避免许多灾难。 

总结:数据集可以通过各种技巧进行比较,但一般而言,使用外连接和子集合操作符更高效。 



当前值 



CurrentValues 



当我们只对最近或当前值感兴趣时,如何避免使用嵌套子查询或 OLAP 函数(两者都引起排序) 

而直接找到适当值,是非常吸引人的设计。如第1章所述,解决该问题的方法之一,就是把每个 

值与某个“截止日期”相关联 —— 就像麦片外盒上的“保质期(bestbefore)”一样——并让当前 

值的“截止日期”是遥远的未来(例如公元 2999 年 12 月 31 日)。这种设计存在一些与实际相 

关的问题,下面讨论这些问题。 



使用“固定日期”,确定当前值变得非常容易。查询如下所示: 

select whatever 

from hist_data 

where item_id =somevalue 

andrecord_date =fixed_date_in_the future 



接着,通过主键找到正确的记录。(当然,要参照的日期如果不是当前日期,就必须使用子查询 

或 OLAP 函数了。)然而,这种方法有两个主要缺点。 

  较明显的缺点:插入新的历史数据之前,先要更新“当前值”(例如今天),接着,将最新“当 

前值”和历史数据一起插入表中。这个过程导致工作量加倍。更糟的是,关系理论中的主键用于 

识别记录,但具有唯一性的(item_id; record_date)却不能作为主键,因为我们会对它做“部分更新 

(partiallyupdate)”。因此,必须有一个能让外键参照的代理键(ID字段或序列号),结果程序 

变得更加复杂。大型历史表的麻烦就是,通常它们也经历过高频率的数据插入,所以数据量才 


…………………………………………………………Page 81……………………………………………………………

会这么大。快速查询的好处,能抵销缓慢插入的缺点吗?这很难说,但绝对是个值得考虑的问 

题。 



  还有个微妙的缺点与优化器有关。优化器使用各种详细程度不同的统计数据,检查字段的最 

低值和最高值,尝试评估值的分布情况。假设历史表包含了自 2000 年 1 月 1 日开始的历史 

数据。于是,我们的数据组成是“散布在几年间的99。9% 的历史数据”加上“2999 年 12 月 31 日 

的0。1% 的‘当前数据’”。因此,优化器会认为数据散布在一千年的范围内。优化器在数据范围 

上的偏差是由于查询中出现的上限日期的误导(即“andrecord_date =fixed_date_in_the future”)。 

此时的问题就是,如果你当查询的不是当前值(例如,你要统计不同时段的数据变化),优化器 

可能错误地做出“使用索引”的决定——因为你访问的只是千年中的极小部分——但实际上需要 

的是对数据进行扫描。是优化器的评估偏差导致它做出完全错误的执行计划决定,这很难修正。 



总结:要理解优化器如何看待你的系统,就必须理解你的数据和数据分布方式。 



通过聚合获得结果集 



Result Set ObtainedbyAggregation 



本节讨论一类极常见的情况:对一个或多个主表(main table)中的详细数据进行汇总,动态计 

算出结果集。换言之,我们面临数据聚合(aggregation of data)的问题。此时,结果集大小取 

决于groupby的字段的基数,而不是查询条件的精确性。正如第一节“小结果集,直接条件”中所 

述,对表进行一趟(asingle pass)处理获得的并非真正聚合的结果(否则就需要自连接和多次 

处理),但此时聚合函数(或聚合)也相当有用。实际上,最让人感兴趣的SQL聚合使用技巧, 

不是明显需要sum或avg的情况,而是如何将过程性处理转化为以聚合为基础的纯 SQL替代方 

案。 



如第2章所强调的,编写高效SQL代码的关键,第一是“勇往直前”,即不要预先检查,而是查询 

完成后测试是否成功 —— 毕竟,蹑手蹑脚地用脚趾试水赢不了游泳比赛。第二是尽量把更多 

“动作”放到SQL 查询中,此时聚合函数特别有用。 



优秀SQL编程的困难,多半在于解决问题的方式:不要将“一个问题”转换成对数据库的“一系列 

查询”,而是要转换成“少数查询”。程序用大量中间变量保存从数据库读出的值,然后根据变量 

进行简单判断,最后再把它们作为其他查询的输入……这样做是错误的。糟糕的SQL编程有个 

显著特点,就是在 SQL 查询之外存在大量代码,以循环的方式对返回数据进行些加、减、乘、 

除之类的处理。这样做毫无价值、效率低下,这里工作应该交给SQL的聚合函数。 



注意: 



聚合函数非常有用,可以解决不少SQL问题(第11章会再次讨论)。然而,我发现开发者通常只 

使用最平常的聚合函数count(),它对大多数程序是否真的有用值得怀疑。 

第2章说明了使用count(*)判定是否要更新记录(插入新记录)是很浪费的。你可能在报表中误 


…………………………………………………………Page 82……………………………………………………………

用了count(*)。测试存在性有时会以模仿布尔值的方式实现: 

casecount(*) 

when 0then 'N' 

else'Y' 

end 

对于上述实现,只要存在与条件相符的记录,就会读取其中每条记录。其实,只需找到一条记 

录就足以判断要显示 Y 还是 N,通过测试存在性或限制返回记录数可以写出更高效的语句, 

一旦发现条件相符就停止处理即可。 



当要解决的问题与最多、最少、最大、第一、最后有关时,聚合函数(可能会当成 OLAP 函数 

使用)很可能是最佳选择。也就是说,不要认为聚合函数仅支持count、sum、max、min、avg 

等功能,否则就说明你还没有充分理解聚合函数。 

有趣的是,聚合函数在作用范围上非常狭窄。除了计算最大值和最小值,它们唯一能做的就是 

简单的算术运算:count()每遇到的一行加 1;avg()一方面将字段值累加,另一方面不断加 1计 

数,最后进行除法运算。 

聚合函数有时可取得令人吃惊的效果,比如通过sum就可以做很多事情。喜欢数学的朋友知道, 

通过对数和次方函数,要在sum和乘积(product)之间转换有多简单。喜欢逻辑的朋友也会知 

道OR 很依赖sum,而AND很依赖乘积。 

下面通过简单的例子说明聚合的强大作用。假设要进行装运(shipment)处理,一次装运由一 

些不同的订单组成,每张订单都必须分别做准备;只有装运涉及的每张订单都完成时装运才准 

备就绪。问题就是,如何判断装运涉及的所有订单都已完成。 

这样的情况常会发生,有多种方法可以判定装运是否就绪。最糟的方法是逐一判断每批装运, 

而每批装运内部进行第二个循环,查看有多少张订单的order_plete字段值为“N”,并返回计 

数为 0 的装运 ID。更好的解决方案是理解“‘N’值的不存在性测试”的意图,并用子查询(无论 

是关系还是非关系)完成: 

select shipment_id 

from shipments 

where not exists(select null from orders 

where order_plete ='N' 

andorders。shipment_id =shipments。shipment_id) 



如果表shipments上没有其他条件了,则上述方法很糟糕,当shipments表数据量大时(而且未完 

成订单占少数),换成以下查询会更高效: 



select shipment_id 

from shipments 

where shipment_id not in(select shipment_id 

from orders 

where order_plete ='N') 



上述查询也可以稍作变形,优化器比较喜欢这个变形,但要求orders表 的shipment_ id字段上有 


…………………………………………………………Page 83……………………………………………………………

索引: 

select shipments。shipment_id 

from shipments 

返回目录 上一页 下一页 回到顶部 0 0
未阅读完?加入书签已便下次继续阅读!
温馨提示: 温看小说的同时发表评论,说出自己的看法和其它小伙伴们分享也不错哦!发表书评还可以获得积分和经验奖励,认真写原创书评 被采纳为精评可以获得大量金币、积分和经验奖励哦!