的需求是列出所有尚未标记为完
成状态的订单(假设所有jiāo易都已终止)的下列字段:订单号、客户名、订单的最后状态,以
及设置状态的时间。最终,我们写出下列查询,滤掉已完成的订单,并找出订单当前状态:
乍一看,这个查询很合理,但事实上,它让人非常担心。首先,上面代码中有两个子查询,但
它们嵌入的方式和前一个例子的方式不同,它们只是彼此间接相关的。最让人担心的是,这两
个子查询访问相同的表,而且该表在外层已经被访问过。我们编写的过滤条件质量如何呢?因
为只检查了订单是否完成,所以它不是非常精确。
这个查询如何执行的呢?很显然,可以扫描orders 表,检查每一条订单记录是否为已完成状
态——注意,仅通过表orders 即可找出所要信息似乎令人高兴,但实际情况并非如此,因为
只有上述活动之后,才能检查最新状态的日期,即必须按照子查询编写的顺序来执行。
上述两个子查询是关联子查询,这很不好。因为必须要扫描orders 表,这意味着我们必须检
查orders 的每条订单记录状态是否为“COMPLETE”,虽然检查状态的子查询执行很快,但多
次重复执行就不那么快了。而且,若第一个子查询没找到“COMPLETE” 状态时,还必须执行
第二个子查询。那么,何不试试非关联子查询呢?
要编写非关联子查询,最简单的办法是在第二个子查询上做文章。事实上,在某些SQL 方言
中,我们可以这么写:
这个子查询会对orderestatus 作“全扫描”,但未必是坏事,下面会对此加以解释。
重写的子查询条件中,等号左端的“字段对”有点别扭,因为这两个字段来自不同的表,其实不
必这样。我们想让orders和orderstatus的订单ID相等,但优化器能感知这一点吗?答案是不一
select c.custncom, o.ordid, os.status, os.statusdate
from custcomrs c,
orders o,
orderstatus os
where o.ordid = os.ordid
and not exists (select null
from orderstatus os2
where os2.status = 'COMPLETE'
and os2.ordid = o.ordid)
and os.statusdate = (select max(statusdate)
from orderstatus os3
where os3.ordid = o.ordid)
and o.custid = c.custid
and (o.ordid, os.statusdate) = (select ordid, max(statusdate)
from orderstatus
group by ordid)
定。所以优化器可能依然先执行子查询,依然要把orders和orderstatus这两个表连接起来。我
们应该将查询稍加修改,使优化器更容易明白我们的描述,最终按照“先获得子查询的结果,然
后再连接orders和orderstatus表”的顺序工作:
这次,等号左端的字段来自相同的表,从而不必连接orders和orderstatus这两个表了。尽管好
的优化器可能会帮我们做到这一点,但保险起见,一开始就指定这两个字段来自相同的表是更
明智的选择。为优化器保留最大的自由度总是上策。
前面已经看到了,非关联子查询可以变成内嵌视图,且改动不大。下面,我们写出“列出待办订
单”的整个查询语句:
但还有问题,如果最终状态确实是“COMPLETE”,我们就没有必要用子查询检查其最新状态了。
内嵌视图能帮我们找出最后状态,无论它是不是“COMPLETE”。所以我们把查询改为“检查已知
的最新状态”,这个过滤条件非常令人满意:
and (os.ordid, os.statusdate) = (select ordid, max(statusdate)
from orderstatus
group by ordid)
select c.custncom, o.ordid, os.status, os.statusdate
from custcomrs c,
orders o,
orderstatus os,
(select ordid, max(statusdate) laststatusdate
from orderstatus
group by ordid) x
where o.ordid = os.ordid
and not exists (select null
from orderstatus os2
where os2.status = 'COMPLETE'
and os2.ordid = o.ordid)
and os.statusdate = x.laststatusdate
and os.ordid = x.ordid
and o.custid = c.custid
select c.custncom, o.ordid, os.status, os.statusdate
from custcomrs c,
orders o,
orderstatus os,
(select ordid, max(statusdate) laststatusdate
from orderstatus
group by ordid) x
where o.ordid = os.ordid
and os.statusdate = x.laststatusdate
and os.ordid = x.ordid
and os.status != 'COMPLETE'
如果进一步利用OLAP 或SQL 引擎提供的分析功能,还可以避免对orderstatus的重复参照。
不过就此打住,来思考一下我们是如何修改查询的,更重要的是“执行路径(execution path)”
为何。基本上,正常路径是先扫描orders表,接着利用orderstatus表上预计非常高效的索引进
行访问。在最后一版的代码中,我们改用完整扫描orderstatus的方法,这是为了执行group by。
orderstatus中的记录条数一定会比orders 中的大好几倍,然而,只以要扫描的数据量来看,
估计前者比较小(而且可能小很多),这取决于为每张订单保存了多少信息。
无法确定哪种方法一定更好,这一切都取决于实际数据。补充说明一点,最好别在预期会增大
的表上做全表扫描cāo作(若能把搜索限制在最近一个月或几个月的数据上则会好些)。不过,最
后一版的代码肯定比第一版的(在where子句用子查询)要好。
在结束“大数据量查询”的话题之前,有个特殊情况值得一提。当查询要返回非常大量的数据时,
该查询很可能不是某个用户坐在电脑前敲入的命令,而是来自于某个批处理cāo作。即便“预备阶
段”稍长,只要整个处理能达到令人满意的结果,就是可以接受的。当然,不要忘了,无论是不
是预备阶段,都会需要资源——CPU、内存,可能还有临时磁盘空间。即使最基本的查询完全
相同,优化器在返回大量数据时所选择的路径,仍可能会与返回少量数据时完全不同,了解这
一点是有用的。
总结:尽早过滤掉不需要的数据。
取出数据在表中的比例
The Proportions of Retrieved Data
有个典型的说法:当查询返回的记录数超过表中数据总量的10% 时,就不要使用索引。这种
说法暗示,当(常规)索引的键指向表中不足10%的记录时,它是高效的。正如第3章中所指出
的,这个经验法则建立于许多公司仍对关系数据库有所怀疑的年代,那时,关系数据库一般用
于部门级数据库,包含十万行数据的表就被认为是大型表。与含有五亿行数据的表相比,十万
行的10% 不值一提。所以,执行计划“佳者恒佳”仅是个美好的愿望罢了。