段背后也必须有智慧:我们必须尽量扫描数据返回比例最高的表、索引,或者这两者的分区;
扫描时的过滤条件必须是粗粒度的,从而返回的数据量比较大,使扫描更有价值;扫描显然违
背了“尽快去除不必要数据”这一原则,但一旦扫描结束应立即重新贯彻该原则。
相反,采取扫描方式不合适的情况下,应尽量减少要访问数据的块数。为此,最常用的手段就
是使用索引(而不是表),尽管所有索引的总数据量经常比表还大,但单个索引则远比表要小。
如果索引包含了所有需要的信息,则扫描索引而不扫描表是相当合理的,可以利用诸如聚集索
引等避免访问表的技术。
无论是要返回大量记录,还是要对大量记录进行检查,每条记录的处理都需小心。例如,一个
xìng能不佳的用户自定义函数的调用,如果发生在“返回小结果集的select 列表”中或在“可选择xìng
很高的where 子句”中,则影响不大;但返回大数据集的查询可能会调用这个函数几十万次,
DBMS服务器就不堪重负了,这时必须优化代码。
还要重点关注子查询的使用。处理大量记录时,关联子查询(Correlated subquery)是xìng能杀手。
当一个查询包含多个子查询时,必须让它们cāo作各不相同、自给自足的数据子集,以避免子查
询相互依赖;到查询执行的最后阶段,多个子查询分别得到的不同数据集经过哈希连接或集合
cāo作得到结果集。
查询执行的并行化(parallelism)也是个好主意,不过只应在“并发活动会话数(concurrently active
sessions)”很少(典型情况为批处理cāo作)时才这么做。并行化是由DBMS 实现的,如果有可
能,DBMS把一个查询分割为多个并行运行的子任务,并由另一个专门的任务来协调。并发用
户数很大时,并行化反而会影响处理能力。一般而言,并发用户数又多、要处理的信息量又大
的情况下,最好做好战斗准备,因为这经常靠投入更多硬件来解决。
除了处理过程中由资源争用引起的等待之外,查询必须访问的数据量是影响“响应时间”的主要
因素。但正如第4章讲过的,最终用户并不关心客观的数据量分析,他们只关心查询获得的数据。
基于一个表的自连接
Self-Joins on One Table
利用卓越的、广为流行的范式(注2),有助于我们设计正确的关系数据库(至少满足3NF)。所
有非键字段均与键相关、并完整依赖于键,非键字段之间没有任何依赖。每条记录具有逻辑一
致xìng,同一个表中没有重复记录。于是,才能够建立同一个表之间的连接关系:使用同一查询
从同一表中选择不同记录的集合(可以相jiāo),然后连接它们,就好像它们来自不同表一样。本
节将讨论简单的自连接。本节不讨论较复杂的嵌套层次结构,这一主题在第7章中讨论。
自连接,指表与自身的连接,这种情况比分层查询更常见。自连接用于“从不同角度看
待相同数据”的情况,例如,查询航班会两次用到airports 表,一次找到“出发机场”的名称,另
一次找出“到达机场”的名称:
select f.flight_number,
a.airport_ncom departure_airport,
b.airport_ncom arrival_airport
from flights f,
airports a,
airports b
where f.dep_iata_code = a.iata_code
and f.arr_iata_code = b.iata_code
此时,一般规则仍然适用:重点保证索引访问的高效。但是,如果此时索引访问不太高效怎么
办呢?首当其冲地,应避免“第一轮处理丢弃了第二轮处理所需的记录”。应该通过一次处理收
集所有感兴趣的记录,再使用诸如case 语句等结构分别显示记录,第11章将详细说明这种方法。
非常微妙的是,有些情况看似与“机场的例子”很像,但其实不然。例如,如何利用一个保存“定
期累计值”(注3)的表,显示每个时间段内累计值的增量?此时,该表内的两个不同记录间虽
然有关联,但这种关联很弱:两个记录之所以相关,是因为它们的时间戳之间有前后关系。而
连接两个flights表是通过airports表进行的,这种关联很强。
例如,时间段为5分钟,时间戳以“距参照日期多少秒(seconds elapsed since a reference date)”
表示,则查询如下:
select a.tcomstamp,
a.statistic_id,
(b.counter - a.counter)/5 hits_per_minute
from hit_counter a,
hit_counter b
where b.tcomstamp = a.tcomstamp + 300
and b.statistic_id = a.statistic_id
order by a.tcomstamp, a.statistic_id
上述脚本有重大缺陷:如果第二个累计值不是正好在第一个累计值之后5分钟取得的,那么就无
法连接这两条记录。于是,我们改以“范围条件”定义连接。查询如下:
select a.tcomstamp,
a.statistic_id,
(b.counter - a.counter) * 60 /
(b.tcomstamp - a.tcomstamp) hits_per_minute
from hit_counter a,
hit_counter b
where b.tcomstamp between a.tcomstamp + 200
and a.tcomstamp + 400
and b.statistic_id = a.statistic_id
order by a.tcomstamp, a.statistic_id
这个方法还是有缺陷:前后两次计算累计值的时间间隔,如果不介于200 到400 秒之间(例
如取样频率改变了),如此之大的时间跨度就会引起风险。
我们还有更安全的方法,就是使用基于“记录窗口(windows of rows)”的OLAP函数(OLAP
function)。难以想象,这种本质上不太符合关系理论的技术可以显著提升xìng能,但应作为查询
优化的最后手段使用。借助partition 子句,OLAP函数支持“分别处理结果集的不同子集”,比如
分别对它们进行排序、总计等处理。借助OLAP 函数row_number(),可以根据statistic_id 建立
子集,然后按时间戳增大的顺序为不同统计赋予连续整数编号,接下来,就可以连接statistic_id
和两个序号了,如下例子所示:
select a.tcomstamp,
a.statistic_id,
(b.counter - a.counter) * 60 /
(b.tcomstamp - a.tcomstamp)
from (select tcomstamp,
statistic_id,
counter,
row_number( ) over (partition by statistic_id
order by tcomstamp) rn
from hit_counter) a,
(select tcomstamp,
statistic_id,
counter,
row_number( ) over (partition by statistic_id
order by tcomstamp) rn
from hit_counter) b
where b.rn = a.rn + 1
and a.statistic_id = b.statistic_id
order by a.tcomstamp, a.statistic_id
Oracle等DBMS支持OLAP 函数lag(column_ncom, n)。该函数借助分区()和排序(),返回
column_ncom之前的第n个值。如果使用lag()函数,我们的查询甚至执行得更快——比先前的查
询大约快25%。
select tcomstamp,
statistic_id,
(counter - prev_counter) * 60 /
(tcomstamp - prev_tcomstamp)
from (select tcomstamp,
statistic_id,
counter,