SQL语言艺术(PDF格式)-第11部分

快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部! 如果本书没有阅读完，想下次继续接着阅读，可使用上方 "收藏到我的浏览器" 功能 和 "加入书签" 功能！


from　orderdetail　

join　articles　

on　（articles。artid=orderdetail。artid）　

where　articles。artname　='BATMOBILE'）　

或采用from子句中的子查询：　

select　distinct　orders。custid　

from　orders；　

（select　orderdetails。ordid　

from　orderdetail　

join　articles　

on　（articles。artid=orderdetail。artid）　

where　articles。artname　='BATMOBILE'）　assub_q　

where　sub_q。ordid　=orders。ordid　



我认为第一个查询较为易读，当然这取决于个人喜好。别忘了，在子查询结果上的　in（）　条件暗　

含了distinct处理，会引起排序，而排序把我们带到了关系模型的边缘。　



总结：如果要使用子查询，在选择关联子查询、还是非关联子查询的问题上，应仔细考虑。　



多个宽泛条件的交集　



Small　Intersection　of　BroadCriteria　



本节讨论对多个宽泛条件取交集获得较小结果集的情况。在分别使用各个条件时，会产生大型　

数据集，但最终各个大型数据集的交集却是小结果集。　



继续上一节的例子。如果“判断订购的商品是否存在”可选择性较差，就必须考虑其他条件（否　

则结果集就不是小结果集）。在这种情况下，使用正规连接、关联子查询，还是非关联子查询，　

要根据不同条件的过滤能力和已存在哪些索引而定。　

例如，由于不太畅销，我们不再检索订购蝙蝠车的人，而是查找上周六购买某种肥皂的客户。　

此时，我们的查询语句为：　



select　distinct　orders。custid　

from　orders　

join　orderdetail　


…………………………………………………………Page　58……………………………………………………………

on　（orderdetail。ordid　=orders。ordid）　

join　articles　

on　（articles。artid=orderdetail。artid）　

where　articles。artname　='SOAP'　

and　

这个处理流程很合逻辑，该逻辑和商品具有高可选择性时相反：先取得商品，再取得包含商品　

的明细订单，最后处理订单。对目前讨论的肥皂订单的情况而言，我们应该先取得在较短期间　

内下的少量订单，再检查哪些订单涉及肥皂。从实践角度来看，我们将使用完全不同的索引：　

第一个例子需要orderdetail表的商品名称、商品ID这两个字段上的索引，以及orders表的主键　

orderid上的索引；而此肥皂订单的例子需要orders表日期字段的索引、orderdetail表的订单ID字　

段的索引，以及articles表的主键orderid上的索引。当然，我们首先假设索引对上述两例都是最　

佳方式。　



要知道哪些客户在上星期六买了肥皂，最明显而自然的选择是使用关联子查询：　



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；　

ttraoma。bkcod；　

ttraoma。trscod；　

ttraoma。pdtcod；　

ttraoma。objtyp；　

。。。　

from　ttraoma；　

tstgtstg_a；　

ttrcapp　ttrcap_a　

where　tstg_a。chncod　=：7　


…………………………………………………………Page　66……………………………………………………………

andtstg_a。stgnum　=：8　

andtstg_a。risktyp　=：4　

andttraoma。txnum　=tstg_a。txnum　

andttrcap_a。colcod　=：0　

andttrcap_a。refcod　=：5　

andttraoma。trscod　=ttrcap_a。valnumcod）　a；　

ttexttex_a；　

ttrcapp　ttrcap_b；　

tbooks；　

topeoma；　

ttexttex_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（tp