索引的特性与优化

索引的概念

在使用 oracle 的过程中 , 我们就不能不考虑性能和 SQL 优化 , 而正确的使用索引在优化过程中是很关键的 .

索引是建立在表的一列或多列上的辅助对象 , 它有助于快速访问该表中的数据 . 索引由于其内在的结构 , 具有某些内在的开销 , 这些开销依赖于为了检索由索引中 ROWID 指定的行所访问的表中的块数 , 需要特别注意的是 : 这个开销可能会超过进行顺序全表扫描的成本 .

Oracle 使用 B* 树存储索引 ( 包括位图索引 ). 索引的顶点称为根节点 , 第二级节点称为分支节点 , 最低级的节点是叶节点 . 上级索引块 ( 分支节点 ) 包含了指向下级索引块的索引数据 . 最低级索引块 ( 叶节点 ) 包含每个值的索引数据和一个相对应的用来确定该实际行位置的 ROWID. 叶节点本身使用双向链表连接 , 允许叶节点双向切换 .

二 , 索引的文件存储

　 索引文件在存储器上分为两个区：索引区和数据区。索引区存放索引表，数据区存放主文件。建立索引文件的过程：
　　（ 1 ） 按输入记录的先后次序建立数据区和索引表。其中索引表中关键字是无序的
　　（ 2 ） 待全部记录输入完毕后对索引表进行排序，排序后的索引表和主文件一起就形成了索引文件。
　　【例】对于表 10.2 的数据文件，主关键字是职工号，排序前的索引表如表 10.3 所示，排序后的索引表见表 10.4 ，表 10.2 和表 10.4 一起形成了一个索引文件。

drop table t_index_test;

create table T_index_test(
f1 integer ,
f2 integer ,
f3 varchar2 ( 400 )
);

insert into t_index_test

select rownum , mod ( rownum , 100 ),lpad( rownum , 300 , '-' )
from dba_objects, dba_tab_cols
where rownum <= 10000 ;

commit ;

create index ind_index_test_1 on t_index_test(f1);

analyze index ind_index_test_1 validate structure ;

select * from index_stats where name = upper( 'ind_index_test_1' );

字段名称	字段描述	字段内容
HEIGHT	索引树高度	2
BLOCKS	分配给索引的块数	32
NAME		IND_INDEX_TEST_1
PARTITION_NAME
LF_ROWS	索引叶子节点个数	10000
LF_BLKS	叶子节点块数	21
LF_ROWS_LEN	叶子节点总长度	149801
LF_BLK_LEN	平均每个叶子块的大小	7980
BR_ROWS	根节点指针个数, 就是说根节点中有20 个指针指向叶子节点	20
BR_BLKS	根节点个数	1
BR_ROWS_LEN	根节点总长度	220
BR_BLK_LEN		8012
DEL_LF_ROWS	删除的叶子节点行数	0
DEL_LF_ROWS_LEN		0
DISTINCT_KEYS	不同值总数	10000
MOST_REPEATED_KEY		1
BTREE_SPACE	分配给索引的字节数	175592
USED_SPACE	索引已经使用的字节数	150021
PCT_USED		86
ROWS_PER_KEY	每个字段的平均个数	1
BLKS_GETS_PER_ACCESS		3
PRE_ROWS		0
PRE_ROWS_LEN		0
OPT_CMPR_COUNT		0
OPT_CMPR_PCTSAVE		0

可以看到，该所引高度为 2 ，只有 1 个 branch 块，同时也是 root 根节点，同时有 21 个 leaf 块。

select extent_id,file_id,block_id,blocks from dba_extents where segment_name=upper( 'ind_index_test_1' )

EXTENT_ID	FILE_ID	BLOCK_ID	BLOCKS
0	33	12073	8
1	33	12081	8
2	33	12089	8
3	33	12097	8

如何建立最佳索引

何时使用索引

假定索引的唯一目的是减少 IO 操作 , 如果一个查询使用索引时相对于全表扫描执行了更多的 IO 操作 , 则使用索引的意义会明显降低 .

例如 , 假设有一个拥有 1000000 行的表存储在 5000 个块中 , 某个给定的查询需要的结果分布在其中 4000 个数据块中 , 这种情况下 , 建立和使用这一列上的索引肯定不是最佳的 .

如果一个拥有 1000 行的表经历了大量的重复插入和删除操作后 , 表的高水位标记线将升高 , 因为 delete 操作不能收回已经使用的数据块 . 如果高水位标记线为 1000, 而实际记录存储在其中 100 个数据块中 , 这时使用索引是有意义的 . 因为被访问的数据块的数量和执行 IO 操作的数量明显少于执行全表扫描的数量 .

什么是最佳索引

较好的索引 ( 数据按照索引组织 , 在索引中顺序的内容在表中也相邻存储 . 这样之需要读取较少的数据块就可以完成检索任务 )

A---------7

A---------8

B---------8

B---------8

C----------8

C----------9

较差的索引 ( 索引中相邻的数据在表中存储位置相隔较远 , 导致每次读取了多余的重复数据块 )

A---------1357

A---------2

B---------9878

B---------38

C----------1008

C----------9

最佳索引的参数 (CF)

什么是 ClusteringFactor

什么是 Clustering Factor 呢？ Clustering Factor 是的含义是如果通过一个索引扫描一张表，需要访问的表的数据块的数量。 Clustering Factor 计算的方法如下：

1 、扫描一个索引

2 、比较某行的 rowid 和前一行的 rowid ，如果这两个 rowid 不属于同一个数据块，那么 cluster factor 增加 1

3 、整个索引扫描完毕后，就得到了该索引的 cluster factor 。

如果 Clustering Factor 接近于表存储的块数，说明这张表是按照索引字段的顺序存储的。如果 Clustering Factor 接近于行的数量，那说明这张表不是按索引字段顺序存储的。在计算索引访问成本的时候，这个值十分有用。 Clustering Factor 乘以选择性参数（ selectivity ）就是访问索引的开销。

如果这个统计数据不能真实反映出索引的真实情况，那么可能会造成优化器错误的选择执行计划。另外如果某张表上的大多数访问是按照某个索引做索引扫描，那么将该表的数据按照索引字段的顺序重新组织，可以提高该表的访问性能。

问题和答案

1, 什么是 Index clustering Facotr(CF).

Index CF 是一个 CBO 的统计值 , 这个值标示表中两行记录的距离与索引中两行记录的距离的比值 . 可以大致理解为 ( rowid(row1) – rowid(row2))/(rowed(index1) – rowed(index2)).

2, 为什么 Index CF 值越小越好 .

根据上面的定义描述 , 我们知道 , 这个值越小 , 索引中两个相邻值在表中存储的位置越接近 , 这样 oracle 在根据根据索引范围得到存储记录的位置的范围越小 . 所需要读取的数据块数就越少 , 所以索引的性能就越高 .

3, 使用 exp/imp 或者 table/index move 可以帮助减少 Index CF 值吗 ?

答案是否定的 , 这两种方式都对 index CF 没有改变 .

Ok, 那么我们就可以理解为 , table/index move 虽然可以收回没有记录的数据块 , 但这个过程并不对数据记录排序后重新存储 , 而只是简单地将几个相邻的空闲块中的内容写入新块中 .

4, 怎么做才能减少 index CF

只有对结果记录排序后重新 reload 到表中才能减少这个值 .

5, 如果表中的索引不止一个 , 怎么办 ?

如果表的索引不止一个 , 我们不可能同时让所有的 index CF 值减少 , 而只能通过排序 reload 减少某一个或者几个索引的 index CF 值 .

6, 有没有什么办法可以避免产生高的 index CF values?

可以将表放在 keep pool 中 .

…

7, 减少 index CF values 的方式 .

使用外部排序特性 , 对表数据按照索引排序后重新读入 .

或者使用 create table as select from table order by 的方式 .

有效使用索引的几个问题

以下问题的答案有助于建立最佳索引 .

1, 与全表扫描相比 , 索引扫描需要执行多少块 IO 操作 .

如果知道这个问题的答案 , 就会立即知道建立和使用一个索引是否具有性能意义 .

2, 用于特定表中的数据访问的最常用列组合是什么 ?

研究应用程序代码 , 如果程序代码不容易看懂 , 则查看 V$SQLAREA 或 V$SQLTEXT, 并分析最常用的 SQL 语句 . 查找在 V$SQLAREA 中具有较高执行次数的语句 , 并查找它们的 where 子句的成分 .

3, 对打算在其上建立索引的一组给定的列 , 其选择性是什么 ?

如果一些列始终有值并且相对唯一 font-family: Times New Ro