多个间接宽泛条件的交集

Small Intersection, Indirect Broad Criteria

为了构造查询条件，需要连接（join）源表之外的表，并在条件中使用该表的字段，就叫间接条件（indirect criterion）。正如上一节“多个宽泛条件的交集”的情况，通过两个或多个宽泛条件的交集处理获取小结果集，是项艰难的工作；若是涉及多次join操作，或者对中心表（central table）进行join操作，则会更加困难——这是典型的“星形schema（star schema）”（第10章详细讨论），实际的数据库系统中经常遇到。对于多个可选择性差的条件，一些罕见的组合要求我们预测哪些地方会执行完整扫描。当牵涉到多个表时，这种情况颇值得研究。

DBMS引擎的执行始于一个表、一个索引或一个分区，就算DBMS引擎能并行处理数据也是如此。虽然由多个大型数据集合的交集所定义的结果集非常小，但前期的全表扫描、两次扫描等问题依然存在，还可能在结果上执行嵌套循环（nested loop）、哈希连接

（hash join）或合并连接（merge join）。此时，困难在于确定结果集的哪种表组合产生的记录数最少。这就好比，找到防线最弱的环节，然后利用它获得最终结果。

下面通过一个实际的 Oracle 案例说明这种情况。原始查询相当复杂，有两个表在from 子句中都出现了两次，虽然表本身不太庞大（大的包含700 000 行数据），但传递给查询的九个参数可选择性都太差：

select (data from ttex_a,

ttex_b,

ttraoma,

topeoma,

ttypobj,

ttrcap_a,

ttrcap_b,

trgppdt,

tstg_a)

from ttrcapp ttrcap_a,

ttrcapp ttrcap_b,

tstg tstg_a,

topeoma,

ttraoma,

ttex ttex_a,

ttex ttex_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条，做了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：优化器选择的执行路径

注意观察图中所示的操作执行顺序，查询速度慢的原因显露无遗：我们的查询条件很糟糕，优化器选择完全忽略它们。优化器决定先对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 做的事。