能会先做连接,再作过滤,这时在连接
中指定过滤条件利于提高xìng能,例如:
即使过滤条件与连接(join)无关,优化器也会受到过滤条件的影响。例如,若orderdetail的主
键为(ordid, artid),即ordid为索引的第一个属xìng,那么我们可以利用索引找到与订单相关的记
录,就和第3章中讲的一样。但如果主键是(artid, ordid)就太不幸了(注意,就关系理论而言,
无论哪个版本都是完全一样),此时的访问效率比(ordid, artid)作为索引时要差,甚至一些数
据库产品无法使用该索引(注3),唯一的希望就是在ordid 上加独立索引了。
连接了表orderdetail和orders之后,来看articles表,这不会有问题,因为表orderdetail 主键
包括artid字段。最后,检查articles 中的值是否为Batmobile。查询就这样结束了吗?未必结
束,因为用了distinct ,通过层层筛选的客户名还必须要排序,以剔除重复项目。
分析至此,可以看出这个查询有多种编写方式。下面的语句采用了古老的join语法:
本xìng难移,我偏爱这种较古老的方式。原因只有一个:从逻辑的角度来看,旧方法突显出数据
处理顺序无足轻重这一事实;无论以什么顺序查询表,返回结果都是一样的。custcomrs 表非
常重要,因为最终所需数据都来自该表,在此例中,其他表只起辅助作用。注意,没有适用于
join articles a
on a.artid = od.artid
where c.city = 'GOTHAM'
and a.artncom = 'BATMOBILE'
and o.ordered >= scomfunc
join orders o
on o.custid = c.custid
and a.ordered >= scomfunc
select distinct c.custncom
from custcomrs c,
orders o,
orderdetail od,
articles a
where c.city = 'GOTHAM'
and c.custid = o.custid
and o.ordid = od.ordid
and od.artid = a.artid
and a.artncom = 'BATMOBILE'
and o.ordered >= scomfunc
所有问题的解决方案,表连接的方式会因情况不同而异,而决定连接方式取决于待处理数据的
特点。
特定的SQL查询解决特定的问题,而未必适用于另一些问题。这就像yào,它能治好这个病人,
却能将另一个病人医死。
蝙蝠车买主的进一步讨论
下面看看查询蝙蝠车买家的其他方法。我认为,避免在最高层使用distinct应该是一条基本规则。
原因在于,即使我们遗漏了连接的某个条件,distinct也会使查询“看似正确”地执行——无可否
认,较旧的SQL语法在此方面问题较大,但ANSI/SQL92 在通过多个字段进行表的连接时也可
能出现问题。发现重复数据容易,但发现数据不准确很难,所以避免在最高层使用distinct应该
是一条基本规则。
发现结果不正确更难,这很容易证明。前面使用distinct 返回客户名的两个查询,都可能返回
不正确结果。例如,如果恰巧有多位客户都叫“Wayne”,distinct不但会剔除由同个客户的多张
订单产生的重复项目,也会剔除由名字相同的不同客户产生的重复项目。事实上,应该同时返
回具唯一xìng的客户ID和客户名,以保证得到蝙蝠车买家的完整清单。在实际中,发现这个问题
可不容易。
要摆脱distinct,可考虑以下思路:客户在Gohtam市,而且满足存在xìng测试,即在最近六个
月订购过蝙蝠车。注意,多数(但非全部) SQL 方言支持以下语法:
上例的存在xìng测试,同一个名字可能出现多次,但每个客户只出现一次,不管他有多少订单。
有人认为我对ANSI SQL 语法的挑剔有点苛刻(指“蝙蝠车买主”的例子),因为上面代码中
custcomrs表的地位并没有降低。其实,关键区别在于,新查询中custcomrs表是查询结果的唯
一来源(嵌套的子查询会负责找出客户子集),而先前的查询却用了join。
这个嵌套的子查询与外层的select关系十分密切。如代码第11 行所示(粗体部分),子查询
参照了外层查询的当前记录,因此,内层子查询就是所谓的关联子查询(correlated subquery)。
此类子查询有个弱点,它无法在确定当前客户之前执行。如果优化器不改写此查询,就必须先
找出每个客户,然后逐一检查是否满足存在xìng测试,当来自Gotham市的客户非常少时执行效率
倒是很高,否则情况会很糟(此时,优秀的优化器应尝试其他执行查询的方式)。
我们还可以这样编写查询:
select c.custncom
from custcomrs c
where c.city = 'GOTHAM'
and exists (select null
from orders o,
orderdetail od,
articles a
where a.artncom = 'BATMOBILE'
and a.artid = od.artid
and od.ordid = o.ordid
and o.custid = c.custid
and o.ordered >= scomfunc )
在这个例子中,内层查询不再依赖外层查询,它已变成了非关联子查询(uncorrelated
subquery),只须执行一次。很显然,这段代码采用了原有的执行流程。在本节的前一个例子中,
必须先搜寻符合地点条件的客户(如均来自GOTHAM),接着依次检查各个订单。而现在,订
购了蝙蝠车的客户,可以通过内层查询获得。
不过,如果更仔细地分析一下,前后两个版本的代码还有些更微妙的差异。含关联子查询的代
码中,至关重要的是orders 表中的custid字段要有索引,而这对另一段代码并不重要,因为这
时要用到的索引(如果有的话)是表custcomrs的主键索引。
你或许注意到,新版的查询中执行了隐式的distinct。的确,由于连接cāo作,子查询可能会返回
有关一个客户的多条记录。但重复项目不会有影响,因为in 条件只检查该项目是否出现在子
查询返回的列表中,且in不在乎某值在列表中出现了一次还是一百次。但为了一致xìng,作为整
体,应该对子查询和主查询应用相同的规则,也就是在子查询中也加入存在xìng测试:
或者:
select custncom
from custcomrs
where city = 'GOTHAM'
and custid in
(select o.custid
from orders o,
orderdetail od,
articles a
where a.artncom = 'BATMOBILE'
and a.artid = od.artid
and od.ordid = o.ordid
and o.ordered >= scomfunc)
select custncom
from custcomrs
where city = 'GOTHAM'
and custid in
(select o.custid
from orders o
where o.ordered >= scomfunc
and exists (select null
from orderdetail od,
articles a
where a.artncom = 'BATMOBILE'
and a.artid = od.artid
and od.ordid = o.ordid))
尽管嵌套变得更深、也更难懂了,但子查询内应选择exists 还是in 的选择规则相同:此选择
取决于日期与商品条件的有效xìng。除非过去六个月的生意非常清淡,否则商品名称应为最有效
的过滤条件,因此子查询中用in 比exists 好,这是因为,先找出所有蝙蝠车的订单、再检查