按键盘上方向键 ← 或 → 可快速上下翻页,按键盘上的 Enter 键可回到本书目录页,按键盘上方向键 ↑ 可回到本页顶部!
————未阅读完?加入书签已便下次继续阅读!
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