条,做了3 000 次物理I/O,访问数据块3 000 000 次。上述统计数据反映了实际执行的情况,
这是必须首先明确的。下面,通过查询数据字典,得到表记录数情况:
TABLE_NAME NUM_ROWS
--------------------------- ----------
ttypobj 186
trgppdt 366
tpdt 5370
topeoma 12118
ttraoma 12118
tbooks 12268
ttex 102554
ttrcapp 187759
tstg 702403
认真研究表及表的关联情况,得到图6-2所示的分析图:小箭头代表较弱的选择条件,方块为表,
方块的大小代表记录数多少。注意:在中心位置的tTRaoma表,几乎和其他所有表有关联关系,
但很不幸,选择条件都不在tTRaoma表。另一个有趣的事实是:上述的查询语句中,我们必须
提供TRgppdt表的risktyp字段和riskflg字段的值作为条件——为了连接(join)TRgppdt表和tpdt
表要使用这两个字段和pdtcod 字段。在这种情况下,应该思考倒转此流程——例如把tpdt表的
字段与所提供的常数做比较,然后只从trgppdt表取得数据。
图6-2:数据的位置关系
多数DBMS提供“检查优化器选择的执行计划”这一功能,比如通过explain命令直接检查内存中
执行的项目。上述查询花了25 秒(虽然不是特别糟),通常是先完整扫描tTRaoma表,接着进
行一连串的嵌套循环,使用了各种高效的索引(详述这些索引
很乏味,我们假设所有字段都建立了合适的索引)。速度慢的原因是完整扫描吗?当然不是。为
了证明完整扫描所花时间占的比例甚微,只需做如下简单的测试:读取tTRaoma表的所有记录;
为了避免受到字符显示时间的干扰,这些记录无需显示。
优化器发现:tstg表有“大量敌军”,而查询中针对此表的选择条件比较弱,所以难以对它形成“正
面攻击”;而ttrcapp表在查询的from子句中出现两次,但基于该表的判断条件也较弱,所以也不
会带来查询效率的提升;但是,ttraoma表的位置显然很关键,且该表比较小,适合作为“第一攻
击点”——优化器会毫不犹豫地这么做。
那么,既然对tTRaoma表的完整扫描无可厚非,优化器到底错在哪里呢?请看图6-3所示的查询
执行情况。
图6-3:优化器选择的执行路径
注意观察图中所示的cāo作执行顺序,查询速度慢的原因显露无遗:我们的查询条件很糟糕,优
化器选择完全忽略它们。优化器决定先对ttraoma表进行完整扫描;接着,访问和表ttraoma关联
的所有小型表;最后,对其他表运用我们的过滤条件。这样执行是错误的:虽然优化器决定首
先访问表ttraoma有道理(该表的索引可能非常高效,每个键平均对应的记录数较少,或者索引
与记录的顺序有较好的对应关系),但将我们提供的查询条件推迟执行,不利于减少要处理的数
据量。
既然已访问了ttraoma这个关键表,应该紧接着执行语句中的查询条件,这样可以借助这些表与
ttraoma表之间的连接(join)先去除ttraoma表中无用的记录——甚至在结果集更大时,如此执
行的效率仍比较高。但是上述信息我们知道,“优化器”却无从知道。
怎样才能迫使DBMS 依我们所要求的方式执行查询呢?要依靠SQL 方言(SQL dialect)。正如
你将在第11章看到的,多数SQL 方言都支持针对优化器的指示或提示(hint),虽然各种方言
所用语法不同;例如,告诉优化器按表名在from 子句中出现的顺序依次访问各表。不过,“提
示”的实际影响远比它的名字暗示的要大得多,采用“提示”的问题在于,每个提示都是在“赌未
来”——我们已强制规定了执行路径,所以环境、数据量、数据库算法、硬件等因素的发展变化
即使不能绝对适合我们的执行路径,也应该基本适合。例如,既然索引的嵌套循环是最高效选
择,并且嵌套循环不会因并行化而受益,那么命令优化器按照表的排列顺序访问它们几乎没什
么风险。明确指定表的访问顺序,就是这个案例中实际采用的方法,最终查询不到1秒即可完成,
不过物理I/O 次数减少并不明显(原来3 000次,现在2 340次,因为我们仍以ttraoma表的完整
扫描开始),但逻辑I/O 次数的大幅降低(从3 000 000次降到16 500次)使总体响应时间显著缩
短,因为我们“建议”了更高效的执行路径。
总结:记住,你应该详细说明所有强迫DBMS 做的事。
显式地通过优化器指令,指定表的访问顺序,是个笨拙的方法。更优雅的方法是在from子句中
采用嵌套查询,在数值表达式中建议连接关系,这样不必大幅修改SQL子句:
select (select list)
from (select ttraoma.txnum,
ttraoma.bkcod,
ttraoma.trscod,
ttraoma.pdtcod,
ttraoma.objtyp,
...
from ttraoma,
tstg tstg_a,
ttrcapp ttrcap_a
where tstg_a.chncod = :7
and tstg_a.stgnum = :8
and tstg_a.risktyp = :4
and ttraoma.txnum = tstg_a.txnum
and ttrcap_a.colcod = :0
and ttrcap_a.refcod = :5
and ttraoma.trscod = ttrcap_a.valnumcod) a,
ttex ttex_a,
ttrcapp ttrcap_b,
tbooks,
topeoma,
ttex ttex_b,
ttypobj,
tpdt,
trgppdt
where ( a.txnum = topeoma.txnum )
and ( a.bkcod = tbooks.trscod )
and ( ttex_b.trscod = tbooks.permor )
and ( ttex_a.nttcod = ttrcap_b.valnumcod )
and ( ttypobj.objtyp = a.objtyp )
and ( a.trscod = ttex_a.trscod )
and ( ttrcap_b.colcod = :1 )
and ( a.pdtcod = tpdt.pdtcod )
and ( tpdt.risktyp = trgppdt.risktyp )
and ( tpdt.riskflg = trgppdt.riskflg )
and ( tpdt.pdtcod = trgppdt.pdtcod )
and ( tpdt.risktyp = :2 )
and ( tpdt.riskflg = :3 )
and ( ttrcap_b.refcod = :6 )
通常,没有必要采用非常具体的方式和难以理解的提示,其实,提供正确的最初指导就可使优
化器找到正确的执行路径。嵌套查询是个不错的选择,它使表的关联变得明确,而SQL语句的
阅读也相当容易。
总结:混乱的查询会让优化器困惑。结构清晰的查询及合理的连接建议,通常足以帮助优化器
提升xìng能。
大结果集
Large Result Set
无论结果集是如何获得的,只要结果集“很大”,就符合我们下面要讨论的“大结果集”的情况。
批处理环境下,产生大结果集是明智的。当需要返回大量记录时,只要查询条件的可选择xìng不
高,那么即使结果集只占表中数据量的一小部分,也会引起DBMS引擎执行全表扫描;只有某
些数据仓库例外,我们将在第10章中讨论之。
如果查询返回几万条记录,那么使用索引是没有意义的,无论索引用于产生最终结果,还是用
于复杂查询的中间步骤。相比而言,借助哈希或合并连接进行全表扫描是合适的。当然,强力
松语文学免费小说阅读_www.16sy.com