友情提示：如果本网页打开太慢或显示不完整，请尝试鼠标右键“刷新”本网页！
SQL语言艺术(PDF格式)-第10部分
快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部! 如果本书没有阅读完，想下次继续接着阅读，可使用上方 "收藏到我的浏览器" 功能 和 "加入书签" 功能！
总结：优秀的查询未必来自优秀的程序。
数据散布
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）（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'）
�
快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部!
温馨提示： 温看小说的同时发表评论，说出自己的看法和其它小伙伴们分享也不错哦！发表书评还可以获得积分和经验奖励，认真写原创书评 被采纳为精评可以获得大量金币、积分和经验奖励哦！