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

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



只有上述活动之后，才能检查最新状态的日期，即必须按照子查询编写的顺序来执行。　



上述两个子查询是关联子查询，这很不好。因为必须要扫描　orders　表，这意味着我们必须检　

查　orders　的每条订单记录状态是否为　“PLETE”，虽然检查状态的子查询执行很快，但多　

次重复执行就不那么快了。而且，若第一个子查询没找到　“PLETE”　状态时，还必须执行　

第二个子查询。那么，何不试试非关联子查询呢？　



要编写非关联子查询，最简单的办法是在第二个子查询上做文章。事实上，在某些　SQL　方言　

中，我们可以这么写：　

　　　and　（o。ordid；　os。statusdate）　=　（select　ordid；　max（statusdate）　

　　　from　orderstatus　

　　　group　by　ordid）　

这个子查询会对　orderestatus　作“全扫描”，但未必是坏事，下面会对此加以解释。　

重写的子查询条件中，等号左端的“字段对”有点别扭，因为这两个字段来自不同的表，其实不　

必这样。我们想让orders和orderstatus的订单ID相等，但优化器能感知这一点吗？答案是不一　


…………………………………………………………Page　47……………………………………………………………

　定。所以优化器可能依然先执行子查询，依然要把orders和orderstatus这两个表连接起来。我　

　们应该将查询稍加修改，使优化器更容易明白我们的描述，最终按照“先获得子查询的结果，然　

　后再连接orders和orderstatus表”的顺序工作：　

and　（os。ordid；　os。statusdate）　=　（select　ordid；　max（statusdate）　

from　orderstatus　

group　byordid）　

　这次，等号左端的字段来自相同的表，从而不必连接orders和orderstatus这两个表了。尽管好　

　的优化器可能会帮我们做到这一点，但保险起见，一开始就指定这两个字段来自相同的表是更　

　明智的选择。为优化器保留最大的自由度总是上策。　



　前面已经看到了，非关联子查询可以变成内嵌视图，且改动不大。下面，我们写出“列出待办订　

　单”的整个查询语句：　



　　　　select　c。custname；　o。ordid；　os。status；　os。statusdate　

　　　　from　customersc；　

　　　　orders　o；　

　　　　orderstatus　os；　

　　　　（select　ordid；　max（statusdate）　laststatusdate　

　　　　from　orderstatus　

　　　　group　byordid）　x　

　　　　where　o。ordid　=　os。ordid　

　　　　and　not　exists　（select　null　

　　　　from　orderstatus　os2　

　　　　where　os2。status　=　'PLETE'　

　　　　and　os2。ordid　=　o。ordid）　

　　　　and　os。statusdate　=　x。laststatusdate　

　　　　and　os。ordid　=　x。ordid　

　　　　and　o。custid　=　c。custid　

　但还有问题，如果最终状态确实是“PLETE”，我们就没有必要用子查询检查其最新状态了。　

　内嵌视图能帮我们找出最后状态，无论它是不是“PLETE”。所以我们把查询改为“检查已知　

　的最新状态”，这个过滤条件非常令人满意：　

　　　　select　c。custname；　o。ordid；　os。status；　os。statusdate　

　　　　from　customers　c；　

　　　　orders　o；　

　　　　orderstatus　os；　

　　　　（select　ordid；　max（statusdate）　laststatusdate　

　　　　from　orderstatus　

　　　　group　byordid）　x　

　　　　where　o。ordid　=　os。ordid　

　　　　and　os。statusdate　=　x。laststatusdate　

　　　　and　os。ordid　=　x。ordid　

　　　　and　os。status　！=　'PLETE'　


…………………………………………………………Page　48……………………………………………………………

　　and　o。custid　=　c。custid　



如果进一步利用　OLAP　或SQL　引擎提供的分析功能，还可以避免对orderstatus的重复参照。　

不过就此打住，来思考一下我们是如何修改查询的，更重要的是“执行路径（execution　path）”　

为何。基本上，正常路径是先扫描orders表，接着利用orderstatus表上预计非常高效的索引进　

行访问。在最后一版的代码中，我们改用完整扫描orderstatus的方法，这是为了执行group　by。　

orderstatus中的记录条数一定会比　orders　中的大好几倍，然而，只以要扫描的数据量来看，　

估计前者比较小（而且可能小很多），这取决于为每张订单保存了多少信息。　



无法确定哪种方法一定更好，这一切都取决于实际数据。补充说明一点，最好别在预期会增大　

的表上做全表扫描操作（若能把搜索限制在最近一个月或几个月的数据上则会好些）。不过，最　

后一版的代码肯定比第一版的（在where子句用子查询）要好。　



在结束“大数据量查询”的话题之前，有个特殊情况值得一提。当查询要返回非常大量的数据时，　

该查询很可能不是某个用户坐在电脑前敲入的命令，而是来自于某个批处理操作。即便“预备阶　

段”稍长，只要整个处理能达到令人满意的结果，就是可以接受的。当然，不要忘了，无论是不　

是预备阶段，都会需要资源——CPU、内存，可能还有临时磁盘空间。即使最基本的查询完全　

相同，优化器在返回大量数据时所选择的路径，仍可能会与返回少量数据时完全不同，了解这　

一点是有用的。　



总结：尽早过滤掉不需要的数据。　



取出数据在表中的比例　



The　Proportions　of　Retrieved　Data　



有个典型的说法：当查询返回的记录数超过表中数据总量的　10％　时，就不要使用索引。这种　

说法暗示，当（常规）索引的键指向表中不足10％的记录时，它是高效的。正如第3章中所指出　

的，这个经验法则建立于许多公司仍对关系数据库有所怀疑的年代，那时，关系数据库一般用　

于部门级数据库，包含十万行数据的表就被认为是大型表。与含有五亿行数据的表相比，十万　

行的　10％　不值一提。所以，执行计划“佳者恒佳”仅是个美好的愿望罢了。　



就算不考虑“10％的记录”这条“经验法则（rule　of　thumb）”产生的年代（现在的表大小早已今非　

昔比了），要知道，返回的记录数除了与期望响应时间有关之外，它本身并无意义。例如，计算　

十亿行数据的某字段的平均值，虽然返回结果只有一行，但DBMS　要做大量工作。甚至没有任　

何聚合处理，DBMS要访问的数据页的数量也会造成影响。因为要访问的数据页并非只依赖索　

引：第3章曾指出，表中记录的物理顺序与索引顺序是否一致，对要访问的页数有极大影响；第　

5章将讨论的一些物理实现也会造成影响，由于数据的物理存储方式不同，检索出相同数量的记　


…………………………………………………………Page　49……………………………………………………………

录所要访问的数据页数量可能差异很大；此外，有的访问路径将以串行方式执行，有的则以大　

规模并行（parallelized）方式执行……。因此，再别拿“10％的记录”这根鸡毛当令箭了。　



总结：当查询的结果集很大时，索引未必必要。　



SQL　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　“　　”　

SSQQLL语句为了返回结果集或更改数据，必须访问一定数量的数据。““战斗””的环境和条件，决定　

　　　　“　　　”　　　　　　　　　　　　　　　4　　　　　　　　“　　　”　

了我们““进攻””那些数据的方法。就如第44章所讨论的，““进攻””取决于：结果集的数据量、必须　

　　　　　　　　　　　　　　　“　　　”　

访问的数据量、可动用的““部队””（过滤条件）。　



任何大型的、复杂的查询，都可以被分成一连串较简单的步骤，其中一些步骤可以并行执行，　

就像综合战役通常要面对敌军的不同部队。每次战斗的结果差异可能很大，但关键是最后的综　

合结果。　

当我们分析查询的每个步骤时可能不会深入执行细节，但这些步骤可能的组合数量跟国际象棋　

不相上下，可以非常复杂。　

本章讨论存取经过适当规范化的数据时，经常遇到的情况。虽然本章主要讨论查询，但也适用　

　　　　　　　　　　　　　　　　　　　　　where　

于更新和删除操作，只要它们也有wwhheerree　子句，毕竟要先读取数据才能修改数据。无论是单纯　

为了查询、还是更新或删除记录，过滤数据会遇到的最典型情况有九种：　

小结果集，源表较少，查询条件直接针对源表　

小结果集，查询条件涉及源表之外的表　

小结果集，多个宽泛条件，结果取交集　

小结果集，一个源表，查询条件宽泛且涉及多个源表之外的表　

大结果集　

结果集来自基于一个表的自连接　

结果集以聚合函数为基础获得　

结果集通过简单搜索或基于日期的范围搜索获得　

结果集和别的数据存在与否有关　



本章将依次讨论上述各种情况。至于例子，有的简单明了，有的较为复杂（来自实际案例）。　

虽然案例大小存在差异，但解决问题的模式是相通的。　



通常，在执行查询时，应过滤掉所有不属于结果集的数据，这意味着应尽量采用最高效的搜索　

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　4　

条件。决定先执行哪个条件，通常是优化器的工作。但是，正如第44章所述，优化器必须考虑　

　　　　　　　　——　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　“　　　　　”　

大量不同情况————例如表的物理结构、查询编写方式等，所以优化器未必总能““理解正确””。因　

此，提高性能还有很多事情可做，下面对九种模式的讨论中，每种模式均是如此。　



小结果集，直接条件　



Small　Result　Set；　Direct　SpecificCriteria　



对于典型的在线交易处理，多为返回小结果集的查询，源表数量较少，查询条件也是“直接”针　

对源表的。当我们要通过一组条件查询出少许记录时，首先要注意的就是索引。　



一般而言，通过一个表或通过两个表的连接查询较少记录，只要确保查询有适当的索引支持即　


…………………………………………………………Page　50……………………………………………………………

可。然而，当很多表连接在一起，并且查询条件要参照不同的表时（例如　TA　和　TB），会面临　

连接顺序的问题。连接顺序的选择，取决于如何更快地过滤不想要的记录。如果统计数据足够　

精确地反映了表的内容，优化器有可能对连接顺序做出适当选择。　

当查询仅返回少量记录，且过滤条件直接针对源表时，我们必须保证这些过滤条件高效；对于　

非常重要的条件，必须事先为相应字段加上索引，以便查询时使用。　



索引可用性　



Index　Usability　



如第3章所述，对某字段使用函数时，则该字段上的索引并不能起作用。当然，你可以建立函数　

索引（functional　index），这意味着要对函数的结果加索引，而不是为字段加索引。　



注意，“函数调用”不光是指“显式函数调用”。如果你将某类型的字段与一个不同类型的字段或　

常量进行比较，则DBMS会执行“隐式类型转换”（隐式调用一个转换函数），如你所料，这会对　

性能造成影响。　



一旦确定重要的搜索条件上有索引，而查询编写方式也的确能因索引而提高性能，我们还须进　

一步区别如下两种情况：　



使用唯一性索引（unique　index）检索单条记录　



非唯一性索引（non…unique　index）或基于唯一性索引的范围扫描（range　scan）　



查询的效率与索引的使用　



QueryEfficiency　andIndex　Usage　



需要连接（join）表时，唯一性索引非常有用。然而，当程序获得的原始输入（primitive　input）　

不是查询语句需要的主键值时，必须通过编程来解决转换问题。　



这里的“原始输入”指程序接受的数据，可能由使用者输入，也可能从文件中读入。如果查询语　

句需要的主键值本身，就是根据原始输入利用另一个查询所获得的结果，则说明设计不合理。　

因为这意味着一个查询的输出被用作另一个查询的输入，应该考虑合并这两个查询。　

总结：优秀的查询未必来自优秀的程序。　



数据散布　



Data　Dispersion　



当条件是“非唯一性”的，或者条件以唯一性索引上的范围来表达时，DBMS　就必须执行范围扫　

描。例如：　



where　customer_id　between　。。。and。。。　

或：　

where　supplier_name　like　'SOMENAME％'　


…………………………………………………………Page　51……………………………………………………………

键对应的记录很可能散布在整个表中，而基于成本的优化器知道这一点。所以，索引范围扫描　

会使　DBMS　核心逐一读取表的存储页，此时，优化器会决定　DBMS　核心忽略索引对表进行　

扫描。　

如第5章所述，许多数据库系统提供了诸如分区（partition）和聚集索引（clustered　index）等功　

能，直接将可能一并读取的数据存储在一起。其实，数据插入处理也常造成数据丛聚（clumping）　

保存的现象：如果每条记录插入表时都要加时间戳（timestamp），则相继插入的记录会彼此紧　

邻（除非我们采取特殊手段避免资源竞争，见第9章的讨论）。这其实没有必要，而且关系理论　

中也没有“顺序”的概念，但在实际中却很可能发生。　



因此，当我们在时间戳字段的索引上执行范围扫描、查询时间上接近的索引项时，这些记录可　

能彼此紧邻——如果特意为此设置了存储选项参数，就更是如此了。　

现在做一个假定：键值与特定插入环境无关、与存储设置无关，与键值（或键值范围）对应的　

记录可能存储在磁盘的任何位置。索引仅以特定顺序来存储键值，而对应的记录随机散落在表　

中。此时，若既不分区、也不采用聚集索引，则需访问的存储区会更多。于是，可能出现下列　

情况：同一个表上有两个可选择性完全相同的索引，但一个索引性能好、一个索引性能差。这　

种情况在第3章已提到过，下面来分析一下。　

为了说明上述情况，先创建一个具有　1000000条记录的表，这个表有　c1、c2和　c3　三个字段，　

c1　保存序号（1　到　1　000000），c2　保存从　1　到　2　000000　不等的随机数，c3　保存可重复、　

且经常重复的随机值。表面看来，c1　和　c2　都具唯一性，因此具有完全相同的可选择性。索引　

建在c1上，则表中字段的顺序，与索引中的顺序相符——当然，实际上，对表的删除操作会留　

下“空洞”，随后又有新的插入记录填入，所以记录顺序会被打乱。相比之下，索引建在c2上，　

则表中记录顺序与索引中的顺序无关。　



下面读取c3　，使用如下范围条件：　



where　column_name　between　some_value　andsome_value　＋10　

如图6…1所示，使用c1索引（有序索引，索引中键的顺序与表中记录顺序相同）和c2索引（随机　

索引）的性能差异很大。别忘了造成这种差异的原因：为了读取c3的值，除了访问索引，还要　

访问表。如果我们有两个复合索引，分别在　（c1；c3）　和　（c2；c3）　上，就不会有上述差异了，因　

为这时不必访问表，从索引中即可获得要返回的内容。　

图6…1说明的这种性能差异，也解释了下述情况的原因：有时性能会随时间而降低，尤其是在新　

系统刚投入生产环境并导入旧系统的大量数据时。最初加载的数据的物理排序，可能是有利于　

特定查询的；但随后几个月的各种活动破坏了这种顺序，于是性能“神秘”降低　30％～40％。　


…………………………………………………………Page　52……………………………………………………………

图6…1：“索引项顺序与表中记录顺序是否一致”对性能的影响　



现在很清楚了，“DBA可以随时重新组织数据库”其实是错误的。数据库的重新组织曾一度流行；　

但不断增加的数据量及999999％　正常运行等要求，使得重新组织数据库变得不再适合。如果　

物理存储方式很重要，则应考虑第5章讨论过的“自组织结构（self…organizing　structure）”之一，　

例如聚集索引（clustered　indexe）或索引组织表（index…organized　table）。但要记住，对某种类　

型的查询有利，可能对另一种类型的查询不利，鱼与熊掌不可得兼。　



总结：类似的索引，性能却不同，这可能是物理数据的散布引起的。　



　　　　　　“　　　　　　　　”　

条件的““可索引性””　



Criterion　Indexability　



对“小结果集，直接条件”的情况而言，适当的索引非常重要。但是，其中也有不适合加索引的　

例外情况：以下案例，用来判断会计账目是否存在“金额不平”的情况，虽然可选择性很高，但　

不适合加索引。　



此例中，有个表glreport，该表包含一个应为0的字段amount_diff。此查询的目的是要追踪会计错　

误，并找出amount_diff不是0的记录。既然使用了现代的DBMS，直接把账目对应成表，并应用　

从前“纸笔记账”的逻辑，实在有点问题；但很不幸，我们经常遇到这种有问题的数据库。无论　

设计的质量如何，像amount_diff这样的字段　



通常不应加索引，因为在理想情况下每条记录的amount_diff字段都是　0。此外，amount_diff字　

段明显是“非规范化”设计的结果，大量计算要操作该字段。维护一个计算字段上的索引，代价　

要高于静态字段上的索引，因为被修改的键会在索引内“移动”，于是索引要承受的开销比简单　

节点增／删要高。　

总结：并非所有明确的条件都适合加索引。特别是，频繁更新的字段会增加索引维护的成本。　

回到例子。开发者有天来找我，说他已最佳化了以下　Oracle　查询，并询问过专家建议：　

select　

total。deptnum；　

total。accounting_period；　

total。ledger；　

total。cnt；　

error。err_cnt；　

cpt_error。bad_acct_count　

from　

……Firstin…line　view　

（select　

deptnum；　

accounting_period；　

ledger；　


…………………………………………………………Page　53……………………………………………………………

count（account）　cnt　

from　

glreport　

groupby　

deptnum；　

ledger；　

accounting_period）　total；　

……Second　in…line　view　

（select　

deptnum；　

accounting_period；　

ledger；　

count（account）　err_cnt　

from　

glreport　

where　

amount_diff0　



groupby　

deptnum；　

ledger；　

accounting_period）　error；　

……Third　in…line　view　

（select　

deptnum；　

accounting_period；　

ledger；　

count（distinct　account）　bad_acct_count　

from　

glreport　

where　

amount_diff0　

groupby　

deptnum；　

ledger；　

accounting_period　

）cpt_error　

where　

total。deptnum　=error。deptnum（＋）　and　

total。accounting_period　=error。accounting_period（＋）　and　

total。ledger　=error。ledger（＋）and　


…………………………………………………………Page　54……………………………………………………………

total。deptnum　=cpt_error。deptnum（＋）　and　

total。accounting_period　=cpt_error。accounting_period（＋）　and　

total。ledger　=cpt_error。ledger（＋）　

order　by　

total。deptnum；　

total。accounting_period；　

total。ledger　

外层查询where子句中的“（＋）”是Oracle　特有的语法，代表外连接（outerjoin）。换言之：　

select　whatever　

from　ta；　

tb　

where　ta。id=tb。id　（＋）　

相当于：　

select　whatever　

from　ta　

outerjoin　tb　

on　tb。id=ta。id　

下列SQL*Plus输出显示了该查询的执行计划：　

10：16：57SQL》　set　autotracetraceonly　

10：17：02SQL》　/　



37rows　selected。　



Elapsed：　00：30：00。06　



Execution　Plan　

………………………………………………………………………………………………………………………………………………………

0　　　　　SELECTSTATEMENTOptimizer=CHOOSE　

（Cost=1779554　Card=154Bytes=16170）　

1　　0　MERGEJOIN（OUTER）