2.5 索引维护

索引对于性能保障的重要性是不言而喻的,一个优质的索引是性能的润滑剂,相反,劣质的索引将是性能的“绞肉机”。通过2.4节的介绍,我们了解到了一个设计优良的索引,在经过日常业务应用,特别是OLTP的高并发“摧残”之后,将变得满目疮痍,原本优质的索引也可能转变为劣质的。

这就需要DBA的介入,找到劣质的索引,并恢复其优质的本相。索引的后期维护可能是DBA们日常维护工作中非常重要的一部分,同时也可能是最费时费力的一部分。有人可能会简单地概括一下:“就是索引的定期重建嘛。”不错,但是面对成百上千甚至上万的索引,会不会有种束手无策的感觉呢?感觉哪都是问题,却无从下手。另外一方面,索引是否也有它的生命周期呢?当然有,索引和业务是息息相关的,业务下线了,索引的生命周期也应该相应进入回收期。

本节将围绕索引重建展开介绍索引后期维护的方法,包括:为何重建索引,何时重建索引,如何重建索引,以及索引回收和相关影响分析。

 

2.5.1    为何重建索引

1.  劣质索引的代价

随着业务应用DML操作的进行,索引的结构也将变得“松散”,索引块碎片过多、索引空间利用率低下、索引树太高,这些问题都会导致索引的使用效率变差。

下面我们来看一个劣质索引的例子。idx_alex_t05_id经过一系列的高并发DML操作后,变得非常“松散”,结构分析的结果如下:

SQL> analyze index idx_alex_t05_id validate structure;
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

此时如果进行一些DML操作将会产生一些额外的开销。示例如下:

SQL> delete from alex_t05 where id between 466001 and 467000;
-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |                 |  1007 |  5035 |    12   (0)| 00:00:01 |
|   1 |  DELETE           | ALEX_T05        |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_ALEX_T05_ID |  1007 |  5035 |    12   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Statistics
-----------------------------------
1088  db block gets
211  consistent gets
33  physical reads
317448  redo size
26  sorts (memory)
0  sorts (disk)
1000  rows processed

如果重建索引idx_alex_t05_id后,一切将变得不一样了。执行计划中可以看到,“松散”结构的索引在处理同样的删除操作时,付出更大的COST开销,这部分开销从统计信息能找到原因,因为劣质索引会造成较多的逻辑读和物理读,甚至产生更多的REDO日志量。示例如下:

-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |                 |  1001 |  5005 |     4   (0)| 00:00:01 |
|   1 |  DELETE           | ALEX_T05        |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_ALEX_T05_ID |  1001 |  5005 |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Statistics
-----------------------------------
1067  db block gets
207  consistent gets
25  physical reads
317016  redo size
26  sorts (memory)
0  sorts (disk)
1000  rows processed

同样,在查询操作的时候,劣质的索引也会因为额外的开销造成性能问题,及时让“松散”的索引变得“结实”是很有必要的,也维持索引高效使用的必需行为。

2.  碎片的由来

索引结构不同与表的结构,其维护行为自然也不同于表的维护行为。对于索引来说,它是没有UPDATE操作的,它只有INSERT和DELETE操作。

q  INSERT操作:当有新值进入索引,其会判断是否有废弃的键值可以重用,如果有即重用,否则在相应节点上开辟一个新键值。

q  DELETE操作:索引键值的节点位置标示为删除,但并不实际删除该键值,空间不回收。这是由于索引树形结构的特点决定的,实际删除一个键值的开销是非常大的。

q  UPDATE操作:实质上是先做DELETE,再做INSERT。

索引的碎片就是由DELETE操作产生的。因为键值已经标志为删除状态,但并未实际删除掉,这样的废弃键值被认为是索引的碎片,如果废弃键值被重用了,则视为碎片的再利用。

索引结构中只有一个层级,一个叶节点,其包含5个索引键值,此时因为是新插入的数据,索引结构很整齐,没有碎片。示例如下:

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

HEIGHT  RATIO
-------- ---------
1  0%

—– begin tree dump

leaf: 0x10267a4 16934820 (0: nrow: 5 rrow: 5)

—– end tree dump

将5条记录都删除掉,索引上也发生DELETE操作,此时索引5个键值都被标志为废弃,没有有效键值,故碎片率为100%,而树形结构的DUMP告诉我们该5个键值并没有删除,空间也没有释放掉。验证如下所示:

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

HEIGHT  RATIO
-------- ---------
1  100%

—– begin tree dump

leaf: 0x10267a4 16934820 (0: nrow: 5 rrow: 0)

—– end tree dump

一个索引中碎片多了,和索引空间利用率低下是一样的,都是其“松散”的一种表现,都是会影响到应用的性能的。

提示

索引维护的重要任务就是及时消除掉不必要的碎片和提高索引空间利用率。

Trackback

no comment untill now

Add your comment now

切换到手机版