Oracle性能分析6:数据访问方式之索引扫描

这节将介绍各种索引扫描方式,在了解了各种索引扫描方式的特点后,你就可以判断你的执行计划中使用的扫描方式是否正确,并可以针对获取的信息作出改进。

索引唯一扫描

在下面的场景中使用相等条件时,数据库使用索引唯一扫描。
 1)查询条件中包含唯一索引中的所有列时;
 2)查询条件使用主键约束列时。
下面是一个实际的例子,在表historyalarm中创建如下唯一索引:

create unique index idx_historyalarm$queryid on historyalarm(queryid) tablespace uep4x_fm_index

然后在表上执行查询:

select * from historyalarm where queryid = 3

该查询符合上面的第一种情况,会使用索引唯一扫描,该查询的执行计划如下:

	DESCRIPTION							OBJECT NAME
-----------------------------------------------------------------------
SELECT STATEMENT, GOAL = ALL_ROWS
 TABLE ACCESS BY GLOBAL INDEX ROWID		HISTORYALARM
  INDEX UNIQUE SCAN						IDX_HISTORYALARM$QUERYID

这里Oracle首先通过唯一索引扫描找到索引节点,然后使用索引节点中包含的rowid来访问表中的数据。

索引范围扫描

当查询条件可能会返回一定范围的数据时就会选用索引范围扫描,索引可以是唯一索引或者不唯一索引,但如果查询条件包含的数据范围太大,也有可能导致全表扫描。查询条件中使用<、>、LIKE、BETWEEN、=等都可能使用索引范围扫描,需要注意单个=条件在唯一索引或者主键上将导致索引唯一扫描。
下面是一个索引范围扫描的例子,在上面的histroyalarm中执行一个范围查询:

select * from caffm4x.historyalarm where queryid < 10

这里查询的数据是一个范围,且使用了queryid列,在queryid列上有唯一索引,但任然会导致索引范围扫描:

	DESCRIPTION							OBJECT NAME
-----------------------------------------------------------------------
SELECT STATEMENT, GOAL = ALL_ROWS
 TABLE ACCESS BY GLOBAL INDEX ROWID		HISTORYALARM
  INDEX RANGE SCAN						IDX_HISTORYALARM$QUERYID

索引范围扫描从索引的根节点出发,找到第一个匹配的条目所在的叶子数据块开始遍历索引结构,首先从索引条目中取出rowid然后取出对应的表数据块(通过rowid访问数据表),接下来叶子索引块会被再次访问并读取下一个索引条目并获取rowid,这样反复直到整个叶子索引块被的索引条目全部被读出。因此排除索引根节点和中间节点,每行数据读取需要读取两个数据块,我们可以通过blevel来得到索引高度,通过索引高度和获取的数据行数就能得到需要读取的数据块数,例如:如果blevel为3,读取5行数据,则总的需要访问的数据块次数将是(5*2) + 3 = 13(注意只有根节点时blevel为0)。
如果在读取了整个叶子索引块之后,还需要访问下一个叶子索引块,在当前的叶子索引块中有指向下一个叶子索引块的指针(也含有指向上一个叶子索引块的指针)。
使用索引范围扫描的另一个优势就在于排序,由于索引的节点是有序的,因此如果查询的结果需要按照索引列排序(升序或者降序),那么使用索引范围扫描则可以很好的避免排序操作,例如:

select * from historyalarm where queryid > 10

由于queryid大于10的数据量占总数据量的99%,因此Oracle的优化器选择了全表扫描:

	DESCRIPTION							OBJECT NAME
-----------------------------------------------------------------------
SELECT STATEMENT, GOAL = ALL_ROWS		
 PARTITION RANGE ALL			
  TABLE ACCESS FULL						HISTORYALARM

如果我们在查询时对数据指定排序,如下:

select * from historyalarm where queryid > 10 order by queryid

执行计划如下:

	DESCRIPTION							OBJECT NAME
-----------------------------------------------------------------------
SELECT STATEMENT, GOAL = ALL_ROWS			
 TABLE ACCESS BY GLOBAL INDEX ROWID		HISTORYALARM	
  INDEX RANGE SCAN						IDX_HISTORYALARM$QUERYID

优化器改为使用了索引范围扫描。由于当数据量很大时,排序的代价是很大的(可能导致物理排序),这时使用索引范围扫描将是一个很好的选择,特别是当你排序后选择部分数据的情况下(rownum < n)。

索引全扫描

索引全扫描会读取索引上的所有条目,下面几种情况可能导致索引全扫描:

1)没有条件但是所需获取列的列表可以通过其中一列的索引来获得;

select id from t3

	DESCRIPTION							OBJECT NAME
-----------------------------------------------------------------------
SELECT STATEMENT, GOAL = ALL_ROWS		
 INDEX FULL SCAN						IDX_T3_ID

由于id列带有索引,因此这里优化器选择了索引全扫描。

2)查询条件中包含排序操作

select * from historyalarm order by queryid

	DESCRIPTION							OBJECT NAME
-----------------------------------------------------------------------
SELECT STATEMENT, GOAL = ALL_ROWS
 INDEX FULL SCAN						IDX_HISTORYALARM$QUERYID

索引全扫描读取单个数据块,读取每个条目的rowid,再通过rowid取出数据行,由于索引已经排序,所以不必执行排序操作。如果查询只请求了索引列,数据库将跳过表访问,只通过访问索引得到数据。
索引全扫描的另一个优势在计算最大、最小值时:

select min(queryid) from historyalarm 

	DESCRIPTION							OBJECT NAME
-----------------------------------------------------------------------
SELECT STATEMENT, GOAL = ALL_ROWS	
 SORT AGGREGATE		
  INDEX FULL SCAN (MIN/MAX)				IDX_HISTORYALARM$QUERYID

由于索引本身已经排序,因此在计算最大最小值时只需要很小的代价。

索引跳跃扫描

当查询条件中带有符合索引中的列,但是不包含前导列时,就可能导致索引跳跃扫描。数据库将一个复合索引拆分为多个逻辑子索引,符合索引前导列的不同值决定逻辑子索引的数量,即前导列的不同值越少,索引跳跃式扫描的性能就越好。

select value from t3 where value = 'test'

	DESCRIPTION							OBJECT NAME
-----------------------------------------------------------------------
SELECT STATEMENT, GOAL = ALL_ROWS
 INDEX SKIP SCAN						IDX_T3_COMBINE

索引快速全扫描

当索引本身包含查询中指定的所有列时,Oracle执行索引快速全扫描。索引快速全扫描和索引全扫描的区别在于:索引全扫描使用单块读操作,而索引快速全扫描使用多块读。这种扫描不能用于避免排序,因为数据块是通过无序的多块读取来读取的。

select queryid from historyalarm

	DESCRIPTION							OBJECT NAME
-----------------------------------------------------------------------
SELECT STATEMENT, GOAL = ALL_ROWS
 INDEX FAST FULL SCAN					IDX_HISTORYALARM$QUERYID

 

 

Oracle性能分析6:数据访问方式之索引扫描

上一篇:数据库-编译原理


下一篇:SQL中MINUS的用法