第九章 索引优化

索引优化以下几点

一 索引膨胀

二 索引因子

三 索引分裂

索引不是银弹,也不是祖传中药秘方,包治百病! 不是数据库一慢就添加索引,不过对外可以这么玩! 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只能用于等于、不等于判断。其他如>、

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20181112G0SQLN00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

同媒体快讯

扫码关注云+社区

领取腾讯云代金券