索引的特性与优化

winzenghua

                    

索引的概念

在使用oracle的过程中,我们就不能不考虑性能和SQL优化, 而正确的使用索引在优化过程中是很关键的.
索引是建立在表的一列或多列上的辅助对象, 它有助于快速访问该表中的数据. 索引由于其内在的结构, 具有某些内在的开销, 这些开销依赖于为了检索由索引中ROWID指定的行所访问的表中的块数, 需要特别注意的是: 这个开销可能会超过进行顺序全表扫描的成本.
Oracle使用B*树存储索引(包括位图索引). 索引的顶点称为根节点, 第二级节点称为分支节点, 最低级的节点是叶节点. 上级索引块(分支节点)包含了指向下级索引块的索引数据. 最低级索引块(叶节点)包含每个值的索引数据和一个相对应的用来确定该实际行位置的ROWID. 叶节点本身使用双向链表连接, 允许叶节点双向切换.
　 索引文件在存储器上分为两个区：索引区和数据区。索引区存放索引表，数据区存放主文件。建立索引文件的过程：
　　（1） 按输入记录的先后次序建立数据区和索引表。其中索引表中关键字是无序的
　　（2） 待全部记录输入完毕后对索引表进行排序，排序后的索引表和主文件一起就形成了索引文件。
　　【例】对于表10.2的数据文件，主关键字是职工号，排序前的索引表如表10.3所示，排序后的索引表见表10.4，表10.2和表10.4一起形成了一个索引文件。 </p>
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

如何建立最佳索引

<a name="_Toc231974789">何时使用索引

假定索引的唯一目的是减少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, 对打算在其上建立索引的一组给定的列, 其选择性是什么?

<span style="font-size: small;">如果一些列始终有值并且相对唯一<span style="">font-family: Times New Ro