为了便于研究数据访问,可以从以下两方面来分类:
1. 弱选择性
2. 强选择性
所谓弱选择性,通俗的讲就是选择条件不是很明确,返回数据量较大,通常会采取全表扫描等方式
所谓强选择性,通常指选择条件很明确,返回数据量较少,通常会采取索引扫描方式。
先看弱选择性下的数据访问:
这种访问有很多种,全表扫描,全索引扫描,全分区扫描都算。
全表扫描中要注意的性能问题是高水位线的问题。如果高水位线以下有很多空数据块,则会造成很多不必要的开销,这时候要做的就是重构表。
全索引扫描要注意的问题是快速索引扫描。我们可以通过hint来使用快速索引扫描,这种方式扫描索引的时候不读取索引结构,所以获取的数据不是按照索引顺序排序的,这时就有一个排序的问题,如果要求数据结果是按索引排序的,那么用索引扫描就正好可以得到完美的结果,这时如果用快速索引扫描,就需要额外的order by操作的开销。
再看看强选择性下的数据访问:
强选择性下数据访问最常见的是通过索引访问,而最常见的索引是B-树索引。B树索引最常见的性能问题是clustering_factor过高的问题,所以在处理索引性能问题的时候要着重观察这一点。
下面是关于索引访问中性能问题的几点:(如未做特别说明,讨论的是B树索引)
1. 组合索引
组合索引是由若干列组成的,如:
CREATE INDEX ind1 ON tab1(col1 , col2);
在组合索引中,在where条件中只要有先导列,就可以使用组合索引,也就是说where的谓词组合中有col1就可以使用组合索引。即使是整个where条件中只有一个col1=? 这样的谓词。 但如果where条件中只有一个col1=?这样谓词存在的话,这时候要求这个谓词不可以是col1=NULL。因为B树索引不存储NULL。 那么如果我们要求where条件中有col1=NULL 并且还想查询走索引扫描怎么办?很简单,因为是组合索引,所以只要把组合索引中的另外一个键 col2加上就可以。
2. INDEX UNIQUE SCAN && INDEX RANGE SCAN
这两种扫描乍一看不存在什么区别,不过实际上他们在性能上还是有一点区别的。可以做如下测试,
a. 创建一个很大的表,至少要包含2个数据块。。
b. 创建一个该表的副本
c. 在第一个表的 unique列上创建一个普通索引,第二个表的同一个unique列上创建一个unique索引
d. 在where条件中通过谓词 唯一列= 来访问表
这时候,通过查看执行计划的运行时统计信息会发现,非UNIQUE的索引 逻辑读比UNIQUE的索引扫描要多些。这也符合我们的理解。因为在UNIQUE索引扫描中,查找到了目标应该就可以返回了,但是在非UNIQUE中,查找的了目标之后还要继续扫描,因为后面可能还有相同值的目标。
3. IN 操作
IN 操作在执行计划中叫做 inlist iterator
基本上IN 操作就是若干个等于操作,比如说,where id in (1,2,3,4)
这种情况下就相当于是4个等于操作,也意味着前面的相应操作要执行4次。所以IN的开销通常较大。
4.max/min函数
正常情况下,oracle会对这两个函数做一个优化,在执行计划中的体现是下面这两个操作
index full scan (min/max)
index range scan (min/max)
这两个操作其实是只返回索引最左端或最右端的数据,以此来保证返回的是最大值或者最小值,显而易见,这样的优化保证了开销的最小化。通过执行计划的buffer字段,也就是逻辑读字段,可以很明显的看出。但有些时候,oracle会无法使用这一优化机制,比如说 MAX MIN放在一起的时候。这种情况下,我们应该尽量的设计,优化,让oracle采取前面的两种执行方式之一,因为那样真的会提高很多性能。必要的时候,甚至可以把max,min这两个字段单独提出来放到两个SQL语句中,保证ORACLE能够优化。