2.5.2 何时重建索引

索引需要定期重建来保证其使用的高效性,但是这个时间点不是那么容易把握的。不是什么时候心血来潮来就重建一下,也不是性能报警了才去考虑重建,它应该是有一些标准和阀值来控制的。下面我们将介绍一下如何判断一个索引是否到了需要考虑重建的时间点。

1.  判断标准

要判断也是需要有一个判断标准的。关于标准也是各有各的说法,不能说谁对谁错,只能说各有各的适应环境。这里我们也说一个普遍被大家认同的标准,以下条件满足其一即需要考虑索引重建:

q  索引树高端过高,如:height>=4;

q  叶节点碎片率过多,如: DEL_LF_ROWS/LF_ROWS>20%;

q  叶节点使用率低下,如:PCT_USAGE<20%。

2.  通常的判断方法

有了判断标准的阀值,我们可以进一步看一下判断的方法。说到方法,大部分读者第一反应应该都是做索引结构分析吧?不错,这是一个常规性的做法,当然也是最准确的做法。上面我们也已经介绍到了,它大致有两个步骤。

步骤1  分析待重建索引的结构:

SQL> analyze index idx_alex_t05_id validate structure;

实质上这个步骤和DUMP索引的树形结构是差不多的,只不过将分析的工作交给了数据库来做。完成分析后,其会将结果记录到一个名叫index_stats的视图中。

步骤2  接下来就是从index_stats中获取我们需要的信息了:

SQL> select height,
2  round((del_lf_rows_len/lf_rows_len)*100,2)||'%' ratio,
3  pct_used from index_stats where name= 'IDX_ALEX_T05_ID';

HEIGHT  RATIO       PCT_USED
-------- --------- ----------
3  49.33%            62

树高、碎片率、空间利用率都有了,而且步骤只有两步,一切看上去很美妙,不是吗?很遗憾,回答是否定的。步骤2固然很快捷方便,但是步骤1的分析过程将要付出高昂的代价。不仅执行起来很慢,而且会锁表,对于OLTP在线系统来说,这样的分析是不可能完成的任务,除非你愿意付出高额性能损耗作为代价。

3.  灵巧的判断方法

做不做索引结构分析?这往往成了DBA们比较纠结的问题,做的话,代价太大,不做迟早要会付出代价。面对这种两难的局面,相信大家都会选择找个比较空的时间段去做,付出点代价至少比迎接性能崩溃的“洪水猛兽”要好一些。

其实,还是有比较灵巧的方法的,仅需付出很小的代价就能完成这个判断。这里我们可以解放一下思路,开辟一个新的思考方式。

问题1我们真正需要的是什么呢,是索引结构分析过程吗?不是。我们真正需要的是那三个判断指标而已。

问题2我们需要的是非常准确的碎片率和空间利用率指标值吗?不是。我们需要一个比较接近真实值的大致估算就可以了。

问题3我们需要关注整个索引结构吗?不需要。我们更关注的是叶节点,如果叶节点“松散”,分支节点也不会好到哪里去,反之,就不必考虑索引重建的问题了。

基于以上三点,我们似乎可以不要再麻烦地去分析索引结构了,可以变通地进行大致的估算就可以了,而估算的基础就是表和索引的统计信息的数据。不要说这个也没有,对于CBO优化器来说,如果没有较为准确的统计信息也没有必要考虑什么索引维护的问题了。

如何去做估算呢?我们还是需要从索引叶节点结构入手的,来做一次“庖丁解牛”吧。图2-15所示为一个建在普通表上的索引条目存储结构,其中索引条目头为2~3个字节,索引列的长度定义为1个字节,索引键值的字节数取决于该列的实际长度,ROWID长度定义为1个字节,ROWID为6个字节(若是建在分区表上的全局索引,其ROWID为10个字节)。这样我们可以估算出单个索引条目的存储字节数INDEX_ROW_LEN=10+COL_LEN。

book_ch02_15

图2-15  索引条目存储结构

接下来,我们要估算一下一个索引叶节点数据块中能存储多少个索引条目。一个数据块的空间不是全部都能用存储索引条目的,先要去除数据块保留部分(index_stats中“丢失”的叶节点块空间)的192个字节,再考虑PCTFREE参数预留的比例。那么单块能存储的索引条目数可以估算为(BLOCK_SIZE – 192) * (1 – PCTFREE / 100) / INDEX_ROW_LEN。

现在要将索引的全部条目存储起来,我们还需要知道索引的条目数(NUM_INDEX_ROWS),这个条目数不一定等于表的记录行数。那存储全部索引条目需要多少个叶节点数据块呢?

book_ch02_16

有了这个估算的叶节点数据块数,再将其与实际的叶节点数据块数做个对比,就可以得到索引空间的有效利用率了。用100%减去这个有效利用率得到的是索引的碎片率(EST_FRAG_RATIO),这个碎片率和通过索引分析得到的碎片率是不一样的,它是索引分析碎片率和索引空洞(叶块中没有使用的部分)率的一个并集,可以说它同时反映了叶节点碎片率和叶节点使用率两个指标。

book_ch02_17

整理一下就可以得到如下计算公式:

book_ch02_18

我们通过一个例子实际操作一下看看吧,先通过表和索引的统计信息数据获取公式中各个变量的值,再进行计算。

LEAF_BLOCKS、NUM_INDEX_ROWS和PCTFREE的值可以通过查询dba_indexes数据字段视图获取,查询如下所示:

SQL> select leaf_blocks, num_rows, pct_free from dba_indexes
2  where index_name='IDX_ALEX_T05_ID';

LEAF_BLOCKS   NUM_ROWS   PCT_FREE
----------- ---------- ---------
92        10000         10

COL_LEN为索引列的实际长度,我们没办法去计算每个记录行的实际长度,可以使用统计信息中平均行长(AVG_COL_LEN)来替代,可以通过查询dba_tab_cols视图获取其变量值。查询如下所示:

SQL> select avg_col_len from dba_tab_cols
2  where table_name = 'ALEX_T05' and column_name='ID';

COLUMN_NAME   AVG_COL_LEN
------------- -----------
ID                         5

BLOCK_SIZE就是数据库的数据块大小,这里我们取的是默认值8192字节(Bytes)。

SQL> select value from v$parameter where name='db_block_size';

VALUE
----------
8192

可以计算出估算索引的碎片率(EST_FRAG_RATIO)约为77%。对比一下进行索引结构分析的结果来看一下吧。此时叶节点块的使用率是51%,碎片率为49.18%,将空洞也视为碎片换算一下,得到:碎片率 = (1 – 92 * 51% * (1-49.18%) / 92) * 100% = 74%,其和估算的77%相差不大,估算的碎片率基本上可以反映索引的碎片情况。索引结构分析查询结果如下:

SQL> select height,
2  round((del_lf_rows_len/lf_rows_len)*100,2)||'%' ratio,
3  pct_used from index_stats where name= 'IDX_ALEX_T05_ID';

HEIGHT  RATIO       PCT_USED
-------- --------- ----------
3  49.18%            51

随着表中数据量的增加,索引中条目达到相当规模,此估算方法得到的碎片率也将更加准确。如果数据量不是很大或者很小,此方法可能不是太适应,当然数据量不大的索引也没有太多考虑索引重建的必要。

Trackback

no comment untill now

Add your comment now

切换到手机版