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

按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！



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

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

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

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

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

种情况在第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）（Cost=1779554　Card=154Bytes=16170）　

2　　1　　MERGEJOIN（OUTER）（Cost=1185645　Card=154　Bytes=10780）　

3　　2　　　　　　　VIEW（Cost=591736　Card=154Bytes=5390）　

4　　3　　　　　　　　SORT（GROUPBY）（Cost=591736　Card=154Bytes=3388）　

5　　4　　　　　　　　　TABLEACCESS（FULL）　OF'GLREPORT'　

（Cost=582346　Card=4370894Bytes=96159668）　

6　　2　　　　　　　SORT（JOIN）（Cost=593910　Card=154Bytes=5390）　

7　　6　　　　　　　　VIEW　（Cost=593908Card=154Bytes=5390）　

8　　7　　　　　　　　　SORT（GROUP　BY）（Cost=593908　Card=154Bytes=4004）　

9　　8　　　　　　　　　　　TABLEACCESS（FULL）　OF'GLREPORT'　

（Cost=584519　Card=4370885Bytes=113643010）　

10　　1　　SORT（JOIN）（Cost=593910　Card=154Bytes=5390）　

11　10　　　　　　　　VIEW（Cost=593908　Card=154Bytes=5390）　


…………………………………………………………Page　55……………………………………………………………

12　11　　　　　　SORT（GROUPBY）（Cost=593908Card=154　Bytes=5698）　

13　12　　　　　　　TABLEACCESS（FULL）　OF'GLREPORT'　

（Cost=584519　Card=4370885Bytes=161722745）　



Statistics　

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

193　recursive　calls　

0　db　block　gets　

3803355consistent　gets　

3794172　physical　reads　

1620　redo　size　

2219　bytes　sentvia　SQL*Net　toclient　

677bytes　received　via　SQL*Net　from　client　

4　SQL*Net　roundtrips　to/from　client　

17　sorts（memory）　

0　sorts　（disk）　

37　rows　processed　

在此说明，我没有浪费太多时间在执行计划上，因为查询本身的文字描述已显示了查询的最大　

特点：只有四~五百万条记录的glreport表，被访问了三次；每个子查询存取一次，而且每次都　

是完全扫描。　

编写复杂查询时，嵌套查询通常很有用，尤其是你计划将查询划分为多个步骤，每个步骤对应　

一个子查询。但是，嵌套查询不是银弹，上述例子就属于“滥用嵌套查询”。　

查询中的第一个内嵌视图，计算每个部门的账目数、会计期、分类账，这不可避免地要进行全　

表扫描。面对现实吧！我们必须完整扫描glreport表，因为检查有多少个账目涉及所有记录。但　

是，有必要扫描第二次甚至第三次吗？　



总结：如果必须进行全表扫描，表上的索引就没用了。　

不要单从“分析（analytic）”的观点看待处理，还要退一步，从整体角度考虑。除了在　amount_diff　

值上的条件之外，第二个内嵌视图所做的计算，与第一个视图完全相同。我们没有必要使用　

count（）计算总数，可以在amount_diif不是　0　时加　1，否则加0，通过　Oracle　特有的　decode（u；　v　

w；　x）　函数，或使用标准语法case　when　u=vthen　welsexend，即可轻松实现这项计算。　

第三个内嵌视图所过滤的记录与第一个视图相同，但要计算不同账目数。把这个计数合并到第　

一个子查询中并不难：用chr（1）代表amount_diff　为　0　时的“账户编号（account　number）”，就很　

容易统计有多少个不同的账户编号了，当然，记住减1去掉chr（1）这个虚拟的账户编号。其中，　

账户编号字段的类型为varchar2（注1），而chr（1）在　Oracle　中代表ASCII码值为　1　的字符——　

在使用　Oracle　这类用　C　语言编写的系统时，我总是不敢安心使用chr（0），因为　C语言　以　

chr（0）作为字符串终止符。　

Sothis　is　thesuggestion　thatI　returnedtothe　developer：　

select　deptnum；　

accounting_period；　

ledger；　


…………………………………………………………Page　56……………………………………………………………

count（account）　nb；　

sum（decode（amount_diff；0；0；1））err_cnt；　

count（distinct　decode（amount_diff；0；chr（1）；account））…1　

bad_acct_count　

from　

glreport　

groupby　

deptnum；　

ledger；　

accounting_period　

这个新的查询，执行速度是原先的四倍。这丝毫不令人意外，因为三次的完整扫描变成了一次。　

注意，查询中不再有where子句：amount_diff上的条件已被“迁移”到了select列表中decode（）函数　

执行的逻辑，以及由groupby子句执行的聚合（aggregation）中。　



使用聚合代替过滤条件有点特殊，这正是我们要说明的“九种典型情况”中的另一种——　以聚合　

函数为基础获得结果集。　

总结：内嵌查询可以简化查询，但若使用不慎，可能造成重复处理。　



小结果集，间接条件　



Small　Result　Set；　Indirect　Criteria　



与上一节类似，这一节也是要获取小结果集，只是查询条件不再针对源表，而是针对其他表。　

我们想要的数据来自一个表，但查询条件是针对其他表的，且不需要从这些表返回任何数据。　

典型的例子是在第4章讨论过的“哪些客户订购了特定商品”问题。如第4章所述，这类查询可用　

两种方法表达：　



使用连接，加上　distinct　去除结果中的重复记录，因为有的客户会多次订购相同商品　

使用关联或非关联子查询　



如果可以使用作用于源表的条件，请参考前一节“小结果集，直接条件”中的方法。但如果找不　

到这样的条件，就必须多加小心了。　



取用第4章中例子的简化版本，找出订购蝙蝠车的客户，典型实现如下：　

select　distinct　orders。custid　

from　orders　

join　orderdetail　

on　（orderdetail。ordid　=orders。ordid）　

join　articles　

on　（articles。artid=orderdetail。artid）　

where　articles。artname　='BATMOBILE'　

依我看，明确使用子查询来检查客户订单是否包含某项商品，才是较好的方式，而且也比较容　

易理解。但应该采用“关联子查询”还是“非关联子查询”呢？由于我们没有其他条件，所以答案　


…………………………………………………………Page　57……………………………………………………………

应该很清楚：非关联子查询。否则，就必须扫描orders表，并针对每条记录执行子查询——当orders　

表规模小时通常不会查觉其中问题，但随着orders表越来越大，它的性能就逐渐让我们如坐针毡　

了。　



非关联子查询可以用如下的经典风格编写：　

select　distinct　orders。custid　

from　orders　

where　ordid　in（select　orderdetails。ordid　

from　orderdetail　

join　articles　

on　（articles。artid=orderdetail。artid）　

where　articles。artname　='BATMOBILE'）　

或