按键盘上方向键 ← 或 → 可快速上下翻页,按键盘上的 Enter 键可回到本书目录页,按键盘上方向键 ↑ 可回到本页顶部!
————未阅读完?加入书签已便下次继续阅读!
select distinct orders。custid
from orders
where
andexists (select 1
from orderdetail
join articles
on (articles。artid=orderdetail。artid)
where articles。artname ='SOAP'
andorderdetails。ordid =orders。ordid)
在这个方法中,为了使关联子查询速度较快,需要orderdetail表的 ordid字段上有索引(就可以
通过主键artid取得商品,无需其他索引)。
第3章已提到,事务处理型数据库(transactional database)的索引是种奢侈,因为它处在经常更
改的环境中,维护的成本很高。于是选择“次佳”解决方案:当表orderdetail 上的索引并不重要,
而且也有充足理由不再另建索引时,我们考虑以下方式:
select distinct orders。custid
from orders;
(select orderdetails。ordid
from orderdetail;
articles
where articles。artid=orderdetail。artid
andarticles。artname ='SOAP') assub_q
where sub_q。ordid =orders。ordid
and
…………………………………………………………Page 59……………………………………………………………
这第二个方法对索引的要求有所不同:如果商品数量不超过数百万项,即使artname字段上没有
索引,基于商品名称条件的查询性能也不错。表orderdetail的artid字段可能也不需索引:如果商
品很畅销,出现在许多订单中,则表orderdetail和articles之间的连接通过哈希或合并连接(merge
join)更高效,而artid字段上的索引会引起嵌套的循环。与第一种方法相比,第二种方法属于索
引较少的解决方案。一方面,我们无法承受为表的每个字段建立索引;另一方面,应用中都有
一些“次要的”查询,它们不太重要,对响应时间要求也不苛刻,索引较少的解决方案完全满足
它们的要求。
总结:为现存的查询增加搜索条件,可能彻底改变先前的构想:修改过的查询成了新查询。
多个间接宽泛条件的交集
Small Intersection; Indirect BroadCriteria
为了构造查询条件,需要连接(join)源表之外的表,并在条件中使用该表的字段,就叫间接条
件(indirect criterion)。正如上一节“多个宽泛条件的交集”的情况,通过两个或多个宽泛条件的
交集处理获取小结果集,是项艰难的工作;若是涉及多次join操作,或者对中心表(centraltable)
进行join操作,则会更加困难——这是典型的“星形schema(starschema)”(第10章详细讨论),
实际的数据库系统中经常遇到。对于多个可选择性差的条件,一些罕见的组合要求我们预测哪
些地方会执行完整扫描。当牵涉到多个表时,这种情况颇值得研究。
DBMS引擎的执行始于一个表、一个索引或一个分区,就算DBMS引擎能并行处理数据也是如
此。虽然由多个大型数据集合的交集所定义的结果集非常小,但前期的全表扫描、两次扫描等
问题依然存在,还可能在结果上执行嵌套循环(nested loop)、哈希连接
(hash join)或合并连接(merge join)。此时,困难在于确定结果集的哪种表组合产生的记录数
最少。这就好比,找到防线最弱的环节,然后利用它获得最终结果。
下面通过一个实际的 Oracle 案例说明这种情况。原始查询相当复杂,有两个表在from 子句中
都出现了两次,虽然表本身不太庞大(大的包含700000 行数据),但传递给查询的九个参数可
选择性都太差:
select (datafrom ttex_a;
ttex_b;
ttraoma;
topeoma;
ttypobj;
ttrcap_a;
ttrcap_b;
trgppdt;
tstg_a)
from ttrcappttrcap_a;
ttrcapp ttrcap_b;
tstgtstg_a;
…………………………………………………………Page 60……………………………………………………………
topeoma;
ttraoma;
ttexttex_a;
ttexttex_b;
tbooks;
tpdt;
trgppdt;
ttypobj
where (ttraoma。txnum=topeoma。txnum )
and(ttraoma。bkcod =tbooks。trscod )
and(ttex_b。trscod =tbooks。permor )
and(ttraoma。trscod =ttrcap_a。valnumcod )
and(ttex_a。nttcod =ttrcap_b。valnumcod )
and(ttypobj。objtyp =ttraoma。objtyp)
and(ttraoma。trscod =ttex_a。trscod )
and(ttrcap_a。colcod =:0)……not selective
and(ttrcap_b。colcod =:1)……not selective
and(ttraoma。pdtcod =tpdt。pdtcod )
and(tpdt。risktyp=trgppdt。risktyp )
and(tpdt。riskflg=trgppdt。riskflg)
and(tpdt。pdtcod =trgppdt。pdtcod )
and(trgppdt。risktyp =:2)……not selective
and(trgppdt。riskflg =:3)……not selective
and(ttraoma。txnum=tstg_a。txnum)
and(ttrcap_a。refcod =:5)……not selective
and(ttrcap_b。refcod =:6)……not selective
and(tstg_a。risktyp =:4)……not selective
and(tstg_a。chncod =:7)……not selective
and(tstg_a。stgnum =:8)……not selective
我们提供适当的参数(这里以 :0 到 :8 代表)执行此查询:耗时超过 25 秒,返回记录不到20
条,做了3000 次物理 I/O,访问数据块3 000000 次。上述统计数据反映了实际执行的情况,
这是必须首先明确的。下面,通过查询数据字典,得到表记录数情况:
TABLE_NAME NUM_ROWS
………………………………………………………………………………………………
ttypobj 186
trgppdt 366
tpdt 5370
topeoma 12118
ttraoma 12118
tbooks 12268
…………………………………………………………Page 61……………………………………………………………
ttex 102554
ttrcapp 187759
tstg 702403
认真研究表及表的关联情况,得到图6…2所示的分析图:小箭头代表较弱的选择条件,方块为表,
方块的大小代表记录数多少。注意:在中心位置的 tTRaoma表,几乎和其他所有表有关联关系,
但很不幸,选择条件都不在tTRaoma表。另一个有趣的事实是:上述的查询语句中,我们必须
提供TRgppdt表的 risktyp字段 和 riskflg字段的值作为条件——为了连接(join)TRgppdt表和tpdt
表要使用这两个字段和pdtcod 字段。在这种情况下,应该思考倒转此流程——例如把 tpdt表的
字段与所提供的常数做比较,然后只从 trgppdt表取得数据。
…………………………………………………………Page 62……………………………………………………………
图6…2:数据的位置关系
多数 DBMS提供“检查优化器选择的执行计划”这一功能,比如通过explain命令直接检查内存中
执行的项目。上述查询花了 25 秒(虽然不是特别糟),通常是先完整扫描tTRaoma表,接着进
行一连串的嵌套循环,使用了各种高效的索引(详述这些索引
很乏味,我们假设所有字段都建立了合适的索引)。速度慢的原因是完整扫描吗?当然不是。为
了证明完整扫描所花时间占的比例甚微,只需做如下简单的测试:读取tTRaoma表的所有记录;
为了避免受到字符显示时间的干扰,这些记录无需显示。
优化器发现:tstg表有“大量敌军”,而查询中针对此表的选择条件比较弱,所以难以对它形成“正
…………………………………………………………Page 63……………………………………………………………
面攻击”;而ttrcapp表在查询的from子句中出现两次,但基于该表的判断条件也较弱,所以也不
会带来查询效率的提升;但是,ttraoma表的位置显然很关键,且该表比较小,适合作为“第一攻
击点”——优化器会毫不犹豫地这么做。
那么,既然对tTRaoma表的完整扫描无可厚非,优化器到底错在哪里呢?请看图6…3所示的查询
执行情况。
…………………………………………………………Page 64……………………………………………………………
图6…3:优化器选择的执行路径
…………………………………………………………Page 65……………………………………………………………
注意观察图中所示的操作执行顺序,查询速度慢的原因显露无遗:我们的查询条件很糟糕,优
化器选择完全忽略它们。优化器决定先对ttraoma表进行完整扫描;接着,访问和表ttraoma关联
的所有小型表;最后,对其他表运用我们的过滤条件。这样执行是错误的:虽然优化器决定首
先访问表ttraoma有道理(该表的索引可能非常高效,每个键平均对应的记录数较少,或者索引
与记录的顺序有较好的对应关系),但将我们提供的查询条件推迟执行,不利于减少要处理的数
据量。
既然已访问了ttraoma这个关键表,应该紧接着执行语句中的查询条件,这样可以借助这些表与
ttraoma表之间的连接(join)先去除ttraoma表中无用的记录——甚至在结果集更大时,如此执
行的效率仍比较高。但是上述信息我们知道,“优化器”却无从知道。
怎样才能迫使DBMS 依我们所要求的方式执行查询呢?要依靠SQL 方言(SQLdialect)。正如
你将在第11章看到的,多数 SQL 方言都支持针对优化器的指示或提示(hint),虽然各种方言
所用语法不同;例如,告诉优化器按表名在from 子句中出现的顺序依次访问各表。不过,“提
示”的实际影响远比它的名字暗示的要大得多,采用“提示”的问题在于,每个提示都是在“赌未
来”——我们已强制规定了执行路径,所以环境、数据量、数据库算法、硬件等因素的发展变化
即使不能绝对适合我们的执行路径,也应该基本适合。例如,既然索引的嵌套循环是最高效选
择,并且嵌套循环不会因并行化而受益,那么命令优化器按照表的排列顺序访问它们几乎没什
么风险。明确指定表的访问顺序,就是这个案例中实际采用的方法,最终查询不到1秒即可完成,
不过物理 I/O 次数减少并不明显(原来3000次,现在2340次,因为我们仍以ttraoma表的完整
扫描开始),但逻辑 I/O 次数的大幅降低(从3000000次降到16500次)使总体响应时间显著缩
短,因为我们“建议”了更高效的执行路径。
总结:记住,你应该详细说明所有强迫 DBMS 做的事。
显式地通过优化器指令,指定表的访问顺序,是个笨拙的方法。更优雅的方法是在from子句中
采用嵌套查询,在数值表达式中建议连接关系,这样不必大幅修改SQL子句:
select (select list)
from (select ttraoma。txnum;