按键盘上方向键 ← 或 → 可快速上下翻页,按键盘上的 Enter 键可回到本书目录页,按键盘上方向键 ↑ 可回到本页顶部!
————未阅读完?加入书签已便下次继续阅读!
不正确结果。例如,如果恰巧有多位客户都叫“Wayne”,distinct不但会剔除由同个客户的多张
订单产生的重复项目,也会剔除由名字相同的不同客户产生的重复项目。事实上,应该同时返
回具唯一性的客户ID和客户名,以保证得到蝙蝠车买家的完整清单。在实际中,发现这个问题
可不容易。
要摆脱 distinct,可考虑以下思路:客户在 Gohtam市,而且满足存在性测试,即在最近六个
月订购过蝙蝠车。注意,多数(但非全部) SQL 方言支持以下语法:
select c。custname
from customers c
where c。city= 'GOTHAM'
and exists (select null
from orders o;
orderdetail od;
articles a
where a。artname = 'BATMOBILE'
and a。artid = od。artid
and od。ordid = o。ordid
and o。custid = c。custid
and o。ordered 》= somefunc )
上例的存在性测试,同一个名字可能出现多次,但每个客户只出现一次,不管他有多少订单。
有人认为我对 ANSI SQL 语法的挑剔有点苛刻(指“蝙蝠车买主”的例子),因为上面代码中
customers表的地位并没有降低。其实,关键区别在于,新查询中customers表是查询结果的唯
一来源(嵌套的子查询会负责找出客户子集),而先前的查询却用了join。
这个嵌套的子查询与外层的 select关系十分密切。如代码第 11 行所示(粗体部分),子查询
参照了外层查询的当前记录,因此,内层子查询就是所谓的关联子查询(correlated subquery)。
此类子查询有个弱点,它无法在确定当前客户之前执行。如果优化器不改写此查询,就必须先
找出每个客户,然后逐一检查是否满足存在性测试,当来自Gotham市的客户非常少时执行效率
倒是很高,否则情况会很糟(此时,优秀的优化器应尝试其他执行查询的方式)。
我们还可以这样编写查询:
…………………………………………………………Page 42……………………………………………………………
select custname
from customers
where city = 'GOTHAM'
and custid in
(select o。custid
from orders o;
orderdetail od;
articles a
where a。artname = 'BATMOBILE'
and a。artid = od。artid
and od。ordid = o。ordid
and o。ordered 》= somefunc)
在这个例子中,内层查询不再依赖外层查询,它已变成了非关联子查询(uncorrelated
subquery),只须执行一次。很显然,这段代码采用了原有的执行流程。在本节的前一个例子中,
必须先搜寻符合地点条件的客户(如均来自GOTHAM),接着依次检查各个订单。而现在,订
购了蝙蝠车的客户,可以通过内层查询获得。
不过,如果更仔细地分析一下,前后两个版本的代码还有些更微妙的差异。含关联子查询的代
码中,至关重要的是orders 表中的 custid字段要有索引,而这对另一段代码并不重要,因为这
时要用到的索引(如果有的话)是表customers的主键索引。
你或许注意到,新版的查询中执行了隐式的 distinct。的确,由于连接操作,子查询可能会返回
有关一个客户的多条记录。但重复项目不会有影响,因为 in 条件只检查该项目是否出现在子
查询返回的列表中,且in不在乎某值在列表中出现了一次还是一百次。但为了一致性,作为整
体,应该对子查询和主查询应用相同的规则,也就是在子查询中也加入存在性测试:
select custname
from customers
where city = 'GOTHAM'
and custid in
(select o。custid
from orders o
where o。ordered 》= somefunc
and exists (select null
from orderdetail od;
articles a
where a。artname = 'BATMOBILE'
and a。artid = od。artid
and od。ordid = o。ordid))
或者:
…………………………………………………………Page 43……………………………………………………………
select custname
from customers
where city = 'GOTHAM'
and custid in
(select custid
from orders
where ordered 》= somefunc
and ordid in (select od。ordid
from orderdetail od;
articles a
where a。artname = 'BATMOBILE'
and a。artid = od。artid)
尽管嵌套变得更深、也更难懂了,但子查询内应选择 exists 还是in 的选择规则相同:此选择
取决于日期与商品条件的有效性。除非过去六个月的生意非常清淡,否则商品名称应为最有效
的过滤条件,因此子查询中用in 比 exists 好,这是因为,先找出所有蝙蝠车的订单、再检查
销售是否发生在最近六个月,比反过来操作要快。如果表 orderdetail 的artid字段有索引,这
个方法会更快,否则,这个聪明巧妙的举措就会黯然失色。
注意
每当对大量记录做存在性检查时,选择in还是exists须斟酌。
利于多数 SQL 方言,非关联子查询可以被改写成from 子句中的内嵌视图。然而,一定要记住
的是,in 会隐式地剔除重复项目,当子查询改写为 from 子句中的内嵌视图时,必须要显式地
消除重复项目。例如:
select custname
from customers
where city = 'GOTHAM'
and custid in
(select o。custid
from orders o;
(select distinct od。ordid
from orderdetail od;
articles a
where a。artname = 'BATMOBILE'
and a。artid = od。artid) x
where o。ordered 》= somefunc
and x。ordid = o。ordid)
编写功能等价的查询时,不同的编写方式就好像同义词。在书面语和口语中,同义词的意思虽
…………………………………………………………Page 44……………………………………………………………
然大致相同,但又有细微差异,因此某个词在特定语境中更合适。同样,数据和处理的具体实
现细节可以决定选择哪种查询方式。
蝙蝠车买主案例总结
前面讨论的各段SQL语句,看似意义不大的编程技巧练习,实则不然。关键是“擒获(attack)”
数据的方法有很多,不必按照先customers、然后orders、接着orderdetail和articles的方式来编
写查询。
现在以箭头表示搜索条件的强度——条件分辨力越强,箭头就越大。假设 Gotham市的客户非
常少,但过去六个月的销售业绩不错,卖出了很多蝙蝠车,此时规划图如图4…6所示。虽然商品
名称之上有个过滤条件,但图中的中等大小的箭头指向了表orderdetail,因为该表是真正重要
的表。待售商品可能很少,反映出销售收入的百分比;也可能待售商品很多,最畅销的商品之
一就是蝙蝠车。
相反,如果我们假设多数客户在 Gotham市,但其中很少的客户买了蝙蝠车,则规划图如图4…7
所示。很显然,此时表orderdetail 是最大的目标。来自这个表的数据的数据量缩减速度越快,
查询执行得就越快。
还要注意的非常重要的一点是,“过去六个月”并不是个非常精确的条件。但如果我们把条件改为
过去两个月,而库中有十年的销售记录,会发生什么呢?在这种情况下,如果能先访问到近期
的订单(借助第5章中描述的一些技术,这些数据或许就聚集在一起),查询的效率就会更高些;
找出近期订单后,一方面选取Gotham 的客户,另一方面则选取蝙蝠车订单。所以,换个角度
来看,最好的执行计划并不只相依于数据值,还应该随着时间而不断进化。
好了,总结一下。首先,解决问题的方法不只一种……而且查询的编写方式经常会与数据隐含
的假设相关。殊途同归,最终的结果集都是一样的,但执行速度可能有极大差异。查询的编写
方式会影响执行路径,尤其是应用无法在真正的关系环境中表达的条件时。若想让优化器发挥
极致,我们就必须扩大关系处理的工作量,并确保非关系的部分对最后结果集的影响最小。
本章前面一直假设代码的执行方式与编写方式一样,但其实,优化器可能改写查询——有时改
动还很大。你或许认为优化器所做的改写无关紧要,因为 SQL本是一种声明性语言(declarative
language),用它来说明想要什么,并让 DBMS 予以执行。然而,你也看到了,每次用不同方
式改写查询时,都必须更新关于数据分布和已有索引的假设。因此有一点非常重要:应预先考
虑优化器的工作,以确定它能找到所需数据——这可能是索引,也可能是数据相关的详细统计
信息。
总结:保证SQL 语句返回正确结果,只是建立最佳 SQL语句的第一步。
大数据量查询
Querying Large Quantities of Data
越快剔除不需要的数据,查询的后续阶段必须处理的数据量就越少,自然查询的效率就越高,
这听起来显而易见。集合操作符(set operator)是这一原理的绝佳应用,其中的union使用最
为广泛,我们经常看到通过union操作将几个表“粘”在一起。中等复杂程度的union语句较为常见,
大多数被连接的表都会同时出现在union两端的select 语句中。例如下面这段代码:
…………………………………………………………Page 45……………………………………………………………
select 。。。
fromA;
B;
C;
D;
E1
where (condition on E1)
and (joins and other conditions)
union
select 。。。
fromA;
B;
C;
D;
E2
where (condition on E2)
and (joins and other conditions)
这类查询是典型的“照搬式”编程。为了提高效率,可以仅对代码中非共用的表(本例中即E1和
E2)使用union,然后配合筛选条件,把 union 语句降级为内嵌视图。代码如下:
select 。。。
fromA;
B;
C;
D;
(select 。。。
from E1
where (condition on E1)
union
select 。。。
from E2
where (condition on E2)) E
where (joins and other conditions)
另一个“查询条件用错了地方”的经典例子,和在含有 group by 子句的查询中进行过滤操作有