本节书摘来自华章出版社《高并发Oracle数据库系统的架构与设计》一书中的第2章，第2.5节，作者 侯松，更多章节内容可以访问云栖社区“华章计算机”公众号查看
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操作。
INSERT操作：当有新值进入索引，其会判断是否有废弃的键值可以重用，如果有即重用，否则在相应节点上开辟一个新键值。
DELETE操作：索引键值的节点位置标示为删除，但并不实际删除该键值，空间不回收。这是由索引树形结构的特点决定的，实际删除一个键值的开销是非常大的。
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
一个索引中碎片多了，和索引空间利用率低下是一样的，都是其“松散”的一种表现，都是会影响到应用的性能的。
索引维护的重要任务就是及时消除掉不必要的碎片和提高索引空间利用率。
2.5.2 何时重建索引
索引需要定期重建来保证其使用的高效性，但是这个时间点不是那么容易把握的。不是什么时候心血来潮来就重建一下，也不是性能报警了才去考虑重建，它应该是有一些标准和阈值来控制的。下面我们将介绍一下如何判断一个索引是否到了需要考虑重建的时间点。
1.?判断标准
要判断也是需要有一个判断标准的。关于标准也是各有各的说法，不能说谁对谁错，只能说各有各的适应环境。这里我们也说一个被大家普遍认同的标准，以下条件满足其一即需要考虑索引重建：
索引树高度过高，如：height>=4；
叶节点碎片率过多，如： DEL_LF_ROWS/LF_ROWS>20%；
叶节点使用率低下，如：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-16所示为一个建在普通表上的索引条目存储结构，其中索引条目头为2～3个字节，索引列的长度定义为1个字节，索引键值的字节数取决于该列的实际长度，ROWID长度定义为1个字节，ROWID为6个字节（若是建在分区表上的全局索引，其ROWID为10个字节）。这样我们可以估算出单个索引条目的存储字节数INDEX_ROW_LEN＝10＋COL_LEN。
接下来，我们要估算一下一个索引叶节点数据块中能存储多少个索引条目。一个数据块的空间不是全部都能用存储索引条目的，先要去除数据块保留部分（index_stats中“丢失”的叶节点块空间）的192个字节，再考虑PCTFREE参数预留的比例。那么单块能存储的索引条目数可以估算为（BLOCK_SIZE-192）*（1-PCTFREE/100）/INDEX_ROW_LEN。
现在要将索引的全部条目存储起来，我们还需要知道索引的条目数（NUM_INDEX_ROWS），这个条目数不一定等于表的记录行数。那存储全部索引条目需要多少个叶节点数据块呢？
有了这个估算的叶节点数据块数，再将其与实际的叶节点数据块数做个对比，就可以得到索引空间的有效利用率了。用100%减去这个有效利用率得到的是索引的碎片率（EST_FRAG_RATIO），这个碎片率和通过索引分析得到的碎片率是不一样的，它是索引分析碎片率和索引空洞（叶块中没有使用的部分）率的一个并集，可以说它同时反映了叶节点碎片率和叶节点使用率两个指标。
整理一下就可以得到如下计算公式：
我们通过一个例子实际操作一下看看吧，先通过表和索引的统计信息数据获取公式中各个变量的值，再进行计算。
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
随着表中数据量的增加，索引中条目达到相当规模，此估算方法得到的碎片率也将更加准确。如果数据量不是很大或者很小，此方法可能不是太适用，当然数据量不大的索引也没有太多考虑索引重建的必要。
2.5.3 如何重建索引
对于重建索引来说，似乎没有太多的好办法，大致有索引重建和索引重组两种。而重组和重建都可分为在线操作和离线操作。
离线重组：alter index idx_alex_t05_id shrink space
在线重组：alter index idx_alex_t05_id coalesce
离线重建：alter index idx_alex_t05_id rebuild
在线重建：alter index idx_alex_t05_id rebuild online
对于索引重组来说，很多说法都是大为肯定的，快捷且影响小。事实情况并非如此，coalesce与shrink space命令相比于重建索引有一个显著的缺点：不会导致索引降级，且coalesce不回收索引上的空闲空间，shrink space空间回收也不是很彻底，这对高并发的OLTP应用来说是没有太大意义的。为什么说coalesce是在线，而shrink space是离线呢？主要是从资源消耗上来说的。
对应高并发的OLTP系统，我们宁愿选择在空闲时段进行索引重建，以换取高效的索引性能。对比两种重建方式，离线重建索引实质上是对现有索引结构的扫描和重建操作，其过程中索引本身是不可用的。对于一个高并发的OLTP系统来说，任何时间段都是不可能接受的，更多时候我们会选择在线重建。在线重建实质上是对表进行扫描，再新建一个同样的索引，然后进行切换和旧索引的清除。
2.5.4 废旧索引清理
索引这东西和表不一样，对于表来说往往可以很明确什么时候新建，什么时候下线。索引则不然，新建索引可以很明确，但在表没有下线的时候，我们往往会非常纠结索引是不是可以下线。如果将一个尚有业务使用的索引下线了，那将意味着一场灾难，由此而导致DBA们都不敢删除索引，结果就成了一个表上的索引越来越多。
那如何解决这个问题呢？简单来说就是严进严出，不轻易新建更不轻易删除。如前面章节介绍的一样，新建的时候要做好性能影响分析，删除的时候更要严格做好性能影响分析。那废旧索引清理的时候需要如何来做性能分析呢？Oracle给了我们一个不错的选择，就是索引的使用情况监控（INDEX USAGE MONITOR）。
我们可以开启索引idx_alex_t05_id的使用监控，然后可以通过数据字段动态性能视图v$object_usage来进行查询监控情况，其中看到USED栏位显示该索引的使用状态为“NO”，那在此次监控期间内该索引未被使用到，可以考虑删除。示例如下所示：
SQL> alter index idx_alex_t05_id monitoring usage;
Index altered
SQL> select index_name, monitoring, used from v$object_usage;
INDEX_NAME MONITORING USED
-------------------------- ----------- ----
IDX_ALEX_T05_ID YES NO
仔细思考一下，这个方法算不算很靠谱呢？当然不算，这仅是一种治标不治本的方法。如果监控期间本身就有性能问题，SQL的执行计划“跑偏”了，导致索引没有用到，此时若以此监控情况而删除了索引，那就是不理智的。或者说，在监控期间内，会使用到该索引的SQL很幸运的都没有跑，那很不幸的就又会出现索引误删的情况。为了减少以上情况的出现，我们可以这样改善：
选择数据库高峰期实施索引监控，以及尽可能使用较长的监控周期；
可以对特定时间段实施多次监控采样。
我们展开一下想象，是谁在使用索引？SQL语句嘛，而此方法是在SQL语句执行的末期进行监控，是一种被动的把关，故不为推荐，但可以作为一种辅助手段。那比较靠谱的做法是什么呢？当然是在SQL语句执行的初期就进行把关，或者说在SQL语句还没有执行的时候就进行把关，再结合索引监控进行前追后堵，基本上可以做到万无一失了。要达到这个目的，我们就需要：
先扫描程序代码，主动地去发现可能用到该索引的SQL语句；
同时通过Oracle 11g引进的SPA等工具进行必要SQL语句的抓取；
最后根据以上SQL语句进行性能影响分析（具体的方法之后的章节将会介绍）。
不要说这会带来多大的工作量，DBA的工作本身就不容易，对待索引新建和删除这样敏感的操作更加需要十二分的精神，尽可能地把工作做在前面，主动预防潜在的数据库问题，才不会出现本可以不出现的生产故障。
