第九章 索引优化

索引优化以下几点

一 索引膨胀

二 索引因子

三 索引分裂

索引不是银弹,也不是祖传中药秘方,包治百病! 不是数据库一慢就添加索引,不过对外可以这么玩! DBA界就要谦虚些了....

索引的创建

不是所有的字段都可以建索引的,有些字段不适合创建索引,一个表有最大索引个数的限制！

好像小仙我说错了。比如SEX字段也可以创建索引，一个表可以有255个字段，自然也可以有255个索引。

这要讲应用场景了，如果你的数据库都是混合型的，那就没有办法了，GOD也拯救不了数据库，优化只好去见GOD了。

当然要读写分离啦！ 不仅要读写分离，还要读还要分离，根据SQL请求特性来分，比如说OLTP,OLQP,OLAP,OLHP。这里谈前三个OLTP,OLQP,OLAP。不太明白可以看

前几章。

那么在OLTP 应用场景中 该交易表就有索引数量的限制了，并且对字段有很高的要求。

1 索引不可以超过5个，因为插入数据的时候需要同时更新5个索引哦！持有TX锁，锁定索引叶。

2 该字段的不同值的数量，要求越多越好。比如SEX只有两个选值，男和女。而AGE字段取值范围1-120岁的，共有120个不同值可供选择。

3 字段的值不能倾斜太厉害，最好是均匀的。比如上面的AGE字段，大部分人都在80岁死了，而80-120岁的人数很少，从概率分布来说1-30岁的人很多。假如中国13亿人口一个表，给AGE字段创建索引，你要查20岁的，比查100岁的人慢得多了。

4 WHERE ,JOIN字段 才可以建索引。 只有WHERE和JOIN的字段建的索引才有用，否则符合上面3点很好的字段建索引，也是摆设花瓶的角色。

创建索引有先次序是 4，2,3,1。

一 索引膨胀

索引有个毛病，就是数据被删除了，它丫的只是打个标记，不真删除呢！天长日久，索引就越来越大了，有可能超过表的大小。

索引太肥了对索引扫描带来更高的成本，比如索引范围扫描，全索引扫描，快速全索引扫描。需要读取更多的叶块，而且这些叶块空闲率很高，因为里面很多索引键值被删除了，还霸个位置在那。

很显然索引膨胀对OLAP影响比较大。

那怎么办，自然是重建索引啦！ alter index index rebuilder online

二 索引因子

这个因子是指聚族因子,意思是说 从硬读取一个表的数据块到内存的利用程度。比如说你一个SQL读取10笔数据，通过索引找到这10笔数据，这10笔数据分布在10个块和与分布在两个块的IO量。所以这个因子越大越不好，越接近表的数量越不好，越接近表的块数据就越好。

通过查询dba_indexes视图、user_indexes视图以及all_indexes视图的CLUSTERING_FACTOR列，可以了解当前索引的聚簇因子值。

为什么会导致因子存在呢？ 因为ORACLE的表组织形式是堆，也就是说乱序存放的，数据都是随机插入到不同的块中。

那怎么办？ 怎么优化呢？

这就要看你这个索引重不重要，使用频不频繁了。

如果是， 那么就得重新组织表，CREATE TABLE XXX AS SELECT * FROM OLD_TABLE ORDER BY 索引字段 ASC；

不过这方式只对旧数据进行重组，新数据依旧是乱序插入的。或者把表改成索引组织表。

如果 NO 话，那就无视它的因子。或者废掉这个索引，重新找个比较好的字段做索引。

三 索引分裂

当一次DML 事务操作修改了索引块上的数据，但是旧有的索引块没有足够的空间去容纳新修改的数据，那么将分裂出一个新的索引块，旧有块的部分数据放到新开辟的索引块上去.

分裂的类型：根节点分裂，分支节点分裂，叶节点分裂（最频繁发生，对性能影响最直接）

按照数据迁移量的比例，将索引分裂分为两种类型：9-1分裂和5-5分裂.

9-1分裂：绝大部分数据还保留在旧有节点上，仅有非常少的一部分数据迁移到新节点上。

5-5分裂：旧节点和新节点上的数据比例几乎是持平的。

--可通过此语句来查询其情况

SELECT Names.Name, Stats.Value, Stats.Statistic#

FROM V$sesstat Stats, V$statname Names

WHERE stats.sid = (SELECT sid FROM v$mystat WHERE rownum = 1)

ANDNames.Statistic# = Stats.Statistic#

AND Names.Name LIKE '%split%'

ORDER BY Stats.Statistic#;

leaf node 90-10splits:

插入到索引leaf block叶子块中的索引键是该块中最大的键值(包括块中已删除的索引键值)。在此种情况下实施90-10split，原叶子块仍保持99%的full，

而到另一个空的叶子块中插入该条新的最大键值记录。

leaf node 50-50splits:

当插入到索引叶子块中的索引键值不是该块中的最大值时(包括块中已删除的索引键值)，将发生 50/50split分裂，这意味着有一半索引记录仍存在当前块，

而另一半数据移动到新的叶子块中。

Branch Block 50-50 Split:

由于不断的索引叶块分裂需要将新的leaf block的信息加入到branch block中，当branch block没有足够空间容纳新的记录时，又会引发branch block的 Split。即将一半记录移动到新的branch block中。

Root Block 50-50 Split :

root block 根块实际是一种特殊的branch block, 当root block 50-50split分裂发生时将分配2个新的数据块， 分裂将一半的数据移动到一个新块中，另一半数据移动到另一个新块中。并更新原来的root block，使之指向那2个新的数据块，实际上是2个branch block了 如下图会增高索引的高度

优化方案：

1.将索引重新创建为反键索引，反键索引可能会导致不必要的问题,对索引范围扫描带来高成本.

2.Hash（散列）分区索引

3.如果索引关键字是从序列（sequence）生成的，则增加序列的高速缓存大小，在应用程序支持时，使用“无序”序列

4.定期rebuild索引来减轻这样现象

建立反键索引：

SQL> alter index i5 rebuild reverse;

反转键索引（reverse key index），这是一种十分著名的索引，反转键索引是在存储键值的时候，先将键值进行翻转。比如'1234'存储在索引中的键值是'4321'。设计反转键索引的目的是解决索引的热块冲突问题。索引块出现热块冲突是在性能优化时经常会碰到的问题，比如一个主键是通过sequence生成的，那么主键索引就可能成为热块。这种情况下，如果我们确定针对主键的查询不存在或者很少有索引范围扫描，那么我们可以考虑使用反转键索引来解决主键的热块冲突问题。反转键索引解决索引热块冲突的原理很简单，就是通过键值的反转，打乱索引数据块中的数据组织，从而将热点数据分散到不同的索引数据块中

Reverse Key Index

属于B树索引。

RKI：首先反向每个列键值的字节（如123，就被反为321，新数值在范围上，比原来那些列值会分布的更均匀），然后在反向后的数据上进行索引。

非常适用于含有序数的列。（因传统的B树，此时往往会产生很多级，而超过4级性能就会很低）

注意：RKI只能用于等于、不等于判断。其他如>、