《SQL语言艺术(PDF格式)》第20章

实际的数据库系统中经常遇到。对于多个可选择性差的条件，一些罕见的组合要求我们预测哪　
些地方会执行完整扫描。当牵涉到多个表时，这种情况颇值得研究。　
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　t