Oracle之索引和索引碎片问题解决

反正死锁问题你看到刚才那个结果就够了,就是他已经把死锁问题给你解决了,毕竟你只要出现死锁,毕竟有一方用户就弹出来了,

退出了,直接就弹出来了,这是ORACLE系统自己去解决了,咱们继续往下看

下面咱们要讲一个概念,这个概念其实也是一样的,除了死锁这个事,咱们讲索引这个事,那索引我还是以ORACLE为例,MYSQL

其实都是一样的,其实都是想通的,其实上面的这些也都是想通的,ORACLE有自己的处理方案,MYSQL有自己的处理方案而已,

然后现在就是索引,一般都是Balanced Tree,就是B树索引,Balance Tree,B树索引,平衡树索引,一般来讲这个结构就是这样,

首先它分无数个叶块,一般来讲我就讲成叶块,就是根节点块,比如说你这个索引是从0开始,0到500,然后到1000,咱们就分为B1,

B2,B3,然后下面这块又分支了,B1这块一定是0到小于500,就是499,然后B2这块就是500到999,然后B3就是1000+,这块就是最上层的,

然后分一块,这块就两块,三块,然后再往下,他又区分,从0到200又给我分一个块,然后从200到400又给我分一个块,一直到399,

然后到400一直到499,再分一个块,同理500到699,分一个块,然后700到799分一个块,就是分无数个块,把一个很大范围的分成无数个

小叶块,那么我查询的时候这个小东西,相当于去做定位了,比如我想去查第450个块,一下子我就检索到了这块了,这两块我都可以

不查了,从这里我又往下走,又很快找到这块了,450肯定包含在这里面,然后在这里又直接把450这条记录直接就找到了,一跳,两跳,

三跳,四跳就直接找到了,就是索引内部的设计原理,当然咱们的索引,咱们建一个表,建完索引之后,他肯定有另外的一块空间,

做一个维护的Balanced Tree,所以你一般给一个表建一个索引,都会牺牲你的空间的,你表里存数据牺牲空间,建索引也会牺牲空间,

它会维护Balance Tree里面的结构,他肯定是需要去维护的,所以我们去查询的时候,会定位的很快,维护的这块空间,这没问题吧,

这个东西是一个很简单的一件事,然后咱们继续往下看,B树索引其实还有其他的索引,什么位图索引啊,咱们其实就是研究B树就行了

其他的无所谓了

SQL的概念,就是咱们去优化SQL,优化SQL语句,无非就是4种方案吗,在这里写了

1. 建索引

2. 建分区

3. 物化视图

4. 并行查询

就这四块

索引的概念这块可以看一下

分为两大类在ORACLE里面

1. Balance Tree平衡树索引

2. 位图索引,在离散度非常低的时候可以使用位图索引,你可以上网去搜搜位图索引,这个咱们开发来说还是用的不多的,除非

你用到适合位图索引的需求,一般都是属于Balance Tree,是B树索引

然后往下看索引的说明和目的

索引的说明,基本上就是说,我只是针对于ORACLE,索引是和表相关联的一个可选结构,也就是你可以建也可以不建,在逻辑上和

物理上都有独立于表的数据,索引能优化查询,这个说的是毋庸置疑的,但不能优化DML操作,什么意思呢,就是这个东西就是两者

之间永远是不可调和的事情,就好像你设计一张表,你建了一个索引,那么你去insert的时候,你除了把这条记录插入到表里以外,

你还得把这条记录对应的索引给进行维护一下,这东西是永远都不可调和的,建两个索引你就得多维护一块,你建三个索引你就还得

再多维护一块空间,那就是索引建多了,你进行写操作,那效率性能上,IO性能上就会降低,但是你的查询效率就会变快了,这是肯定的,

你永远都没法去权衡,我们其实软件开发,大多数最多的情况下,其实都是两种方案取一个中间值,根据你自己的业务去做一个平衡,

咱们软件在做设计的时候,用的最多的是用空间换时间,就是为了提高性能,牺牲一些空间,然后去换取时间,包括你做这个中间库,

做这个缓存表,包括我们去做一个Lunece,Sorl,或者是ES,这些东西,大体上都是用空间换时间,很多设计其实都是从这个角度去考虑的,

不同的维度怎么去做,后期再一点一点去讲吧,这个是ORACLE里面的一个概念了,如果SQL语句仅访问被索引的列,就是你访问的是索引的列

比如ID就是索引,你只是查一下ID,那数据库仅从索引中读取数据,而不读取表,如果你只访问索引列之外,还访问列其他的数据,

你要查一条记录的话,那他这个时候会根据rowid,来查找对应的行,ORACLE里面有rowid的概念,除了有rownum隐式的行号之外,

还有rowid这个东西,这个东西其实很好找的,我还是拿emp1吧,SELECT ROWID FROM EMP1,其实咱们的ORACLE表里有这么一行,

这一行就是ROWID,其实他就通过ROWID去检索哪个位置

咱们继续往下走,数据库会使用rowid来查询表中的行,通常怎么怎么说,这个没什么可说的了,索引的目的是为了干什么,

主要是为了减少IO,其实所有的东西都是为了减少IO,查询的效率,读写,这样才可以体现索引的效率,后面有一些建索引的规则,

1. 大表,一个大表1000万条数据,你要返回5%的记录,你就要考虑建索引了

2. 经常使用where字句查询的

3. 离散度很高的列

4. 更新代价比较低的,不是频繁更新的,频繁更新的你建完索引之后,你的频繁的去维护,这个东西很麻烦,浪费性能

5. 还有and和or效率高,逻辑and

6. 查看索引在那一列,这个东西其实很简单,除了写存储过程的时候,用SQL Window,正常情况我都用Command Window,

这个东西你要查在什么地方,有这个索引,还有具体在哪一列,可以用这种SQL

基本上就是我当前scott用户,EMP这张表里面,EMPNO会是一个索引列,包括DEPT表里的DEPTNO是一个索引列,包括其他的表里

都有索引,都能看到,通过一个查询就能看到

其他的再往下看,索引的使用

ORACLE里面一般分7种索引:

1. 正常我们一般使用唯一索引,你主键会unique index,create unique index emp_idx on emp1(empno)

2. 一般索引,create index empno_idx on emp1(empno),这个语句就非常简单了,create index,给索引取个名,随便起个名字,

但是一般要见名知意,你叫empno_idx一看我就知道是emp表中的empno的字段它是一个索引,on是这个索引建在哪个表上,建在emp1

这个表上,然后建在emp1这个表哪一列上,就是这块有个大括号,建在empno这个列上,这个语句其实是你要记住的,

3. 组合索引像其他的没有特殊需求,我一直用ORACLE这么多年,这些都没怎么用过,组合索引,也就是联合索引,这个有的时候还用一点,

我一张表里两个字段, create index job_deptno_idx on emp1(job,deptno);做这个索引

4. reverse反向索引

5. 函数索引

6. 压缩索引

7. 升序降序索引,这个用的都很少

索引的一些问题:

就是它有一个索引碎片的问题,应该是一个很经典的一个案例,ORACLE其实是可以解决索引碎片的问题,我不知道MYSQL能不能解决,

你们有用过MYSQL的吗,那你可能都不知道索引碎片的概念,在这里我简单的说一下,举个例子,比如说你数据库里有一张表,这个系统

上线了好久了,比如说上线了几年了,可能最初建索引维护都还好,你上线几年了,这张表被频繁的被update,delete,都会insert操作,

这个时候你这个索引会干什么啊,这块维护的区域就会不断地去更新啊,修改啊,删除啊等等,等等这种操作,那么你必然会对着操作

产生一个碎片的问题,就是你这张表频繁的去insert,频繁的去update,比如你这个id是一个索引列,之前是1,现在一下子给他改成3了,

这相当于你改索引列了,或者你把它delete了,你把ID这条记录给delete了,你这个索引维护的时候是不是需要重新维护一下,包括

insert,等等一些操作的时候,你这个索引碎片就会越来越大,当然是上线很久了,可能最开始的时候,表刚建出来的时候,可能1000万

条数据,你要走索引查一条数据的时候,可能一两秒钟你就直接检索出来你想要的数据,那么两三年之后,频繁的删除,修改,删除,产生

索引碎片的问题,那你这个时候又执行相同语句的时候,你查询出来的效率可能就不是一两秒了,三四秒,七八秒都会,逐渐的变慢,这个

就是索引碎片的问题产生的,这个问题怎么去解决呢,其实在ORACLE里面是可以去解决的,我这里又一个描述,就是对于基表,原始表做

DML操作,会导致索引块自动的更改操作,因为你做DML操作,尤其是基表的DELETE操作,就是删除索引列,删除索引字段,删除这个数据,

会引起index表,相当于我们的emp表,他的emp表里有一个id,id肯定有一个index表,index表就是维护id的,可能这是一个index表,会

导致index表的index_entries,逻辑删除,然后注意只有一个索引块,全部的index_entry被删除了,才会把这个索引删除了,索引对于

基表的delete,就会产生索引碎片的问题,只有当一个索引块全部index_entry全部都被删除了,才会把这个索引块删除,就我这句话的

意思,这块已经明白了,举个例子,这个就是我们的索引块,你看这块,这块有0这个位置,后面就是29了,维护的索引可能就是28个,

0一直到28,这个什么概念呢,索引块是有大小的,你比如我现在,我把数据库里面第一条记录,给他删掉了,那就相当于他没有第0个了,

再删再删删了特别多了,那可能是从第一条到第28条,整个索引块可能就是,块的大小肯定是不会变的,原先存的28个索引,整个这个

块空间,其实就没啥用了,他说的是什么意思呢,就是很浪费空间吗,存28个id的维护,现在可能就存1个,频繁的去修改和删除,做

一个delete操作,索引给破坏了,这个时候,可能就没啥用了,你索引再加上相同数据的时候,你得从新去建立块,然后再去维护,

问题是索引的物理的结构,会膨胀,你只有把index_entry全部都删除了,从1到28里所有的数据,你都给他删除了以后,你才会把

索引块删除,会有这个问题,会产生一个索引碎片的问题,导致咱们性能下降

在ORACLE里面没有很清晰的给出索引碎片的量化标准,然后ORACLE建立通过一个手段来解决索引碎片的问题,如果你自行去解决的话,

你可以去查看一个视图,叫index_status视图,里面可能会有一个索引碎片的一个整理,一个参考,通过这三个指标

1. 一个是HEIGHT要大于等于4,这个高度指的是什么意思呢,这个高度指的是层次,就是1楼,2楼,3楼,4楼,这个高度一共四层,要不然

还有一些指标

2. PCT_USED,这个应该是小于50%的时候

3. 还有一个指标就是这个DEL_LF_ROWS/LF_ROWS,这个比值是大于等于0.2的时候


你只要有一个指标,超过这个值了,证明你这个数据库,证明你这张表,应该进行索引碎片的整理了,要不然你这个性能就慢了,我这里有

一个简单的例子

说明了索引碎片的问题了,咱们来看一下,这里是一个很简单的例子,creat table t(id int),这里只有一个字段id,等于int类型的,

它是一个字段,然后我去把他建立一个索引,creat index ind_1 on t(id),就是把这个id当成一个索引,给他起个名字叫ind_1,

就是建立一个索引名字,咱们来看一下,这个其实很简单

我当前的table中就有一个t,就有t这张表了,这个t表是空的,是我刚建立起来的,然后t表有一个索引,就是ind_1这个索引,

那咱们可以查看一下,刚才查看索引的这个SQL

你看到我当前有一个索引了,然后它是存在一个T表的,它是在T表的ID这一列上的,T表就一个字段ID,当然T表是没有

任何数据的,刚把索引建完,表和索引都建完,然后呢这个时候,咱们要做的另外一件事情,就做插入,就做插入一堆数据,

这堆语句是做什么事情的,一般来讲你建表的时候,要求建表的时候,直接把应该有的索引都建上,这个性能是比较高的,

你不能等表已经插入100条数据的时候,然后再建索引,那就不太好了,性能就比较低了,你设计一张数据库表的时候,

一定要想好了,这个业务哪个字段会反复的查询,直接把索引建好,你最好是这么去做,这是多少次啊,这是一个简单的块,

for in 100万条数据,然后往里去insert,就是往t表里添加100万条数据,然后去取模,取模做什么事啊,if mod(i,100)取模

就是i这个值取100,等于0的话,那我就commit提交了,end if,就是往里插数据,如果那什么的时候,去模等于0的时候,

去做这个事,咱们看一下,用Command Window,我就执行一下这个,我就回车

这个过程可能很慢,你看一下这个语句,他就是insert into 这么多条数据,就往这张表里插数据,只要你这个取模的时候,

取100等于0的时候,就commit,提交,然后其他情况就不提交,就跳着来的,咱们的数据可能是1,3,6,...,就少了很多,

就咱们不按照顺序去走,稍等一下,他这个过程可能比较长,100万条数据吗,我这么做的目的其实就是让他产生这个索引碎片的

问题,一会我们查一下这个表,这个数据一定是很多的,然后这样,它是做这个事情,分析ind_1,然后这个节奏,就是去分析一下,

现在肯定是属于一个正常的,这个表里的数据都是非常正确的

咱们SELECT COUNT(*) FROM T这张表

里边一共有这么多条数据,这块为了啥啊,就是为了100条提交一次,相当于批量处理,这也是100万条数据,那我当前T表中,

已经有100万条数据了,因为这块就是取模,一旦你取模等于100条的时候,200条的时候我也提交一次,300条的时候我也提交一次,

400条的时候我也提交一次,事务分批去提交,让他一次性去提交100万,这个相当于性能损耗太多了,就这个意思,当前我们这个表里

已经插入100万条数据了,这个表里是一个最完美的一个状态,就是什么啊,刚才我们已经遵守了条约了,我在表里没有数据的时候,

去建立的一个index索引,然后往里插数据的,我插了100万条,就这个意思,然后现在咱们去做一件事,分析,做这个语句分析一下

这个索引,咱们去test

我分析完索引以后,我开始得去分析一下,不进行分析的话,就是index_stat这张表,如果你不进行分析这里面是没数据的,

只有你分析了以后,才会有数据,刚才我应该不分析,先查一下这个表,这个表里面肯定是没数据的,但是刚才我执行完分析以后,

他就相当于把数据插入到index_stats表里了,我们要取的值不就是这几个值吗,高度,一个PCT_USED,还有一个就是LF_ROWS的取值,

select name,HEIGHT,PCT_USED,DEL_LF_ROWS/LF_ROWS from index_stats

咱们这个情况是属于最正常的情况,这个高度是正常的,咱们看看这个条件,这个条件如果是大于等于4,或者小于50%,或者是大于0.2

的时候,无论哪个指标成立满足了,就是你张表的索引是有问题,你应该进行索引碎片的整理,就这三个阀值,那我现在是什么情况啊,

我现在这个HEIGHT高度是几,我这个高度是3,属于一个理想的状态,PCT_USED是100%,百分之百就是相当于使用率很高,因为我基本上

没有做其他的操作,你只有小于50%了,你这个使用率小于50%了,你得碎片整理,如果你大于50%,甚至60%,70%,100%,非常完美的情况下,

你都不用做,这个查询速率是很高的,或者是这个值大于0.2,我这个值是多少啊,我这个值是0,那肯定是小于0.2的,目前来讲,刚才我做了

这个操作,我查的时候肯定是走索引的,他这个性能一定是最佳的,肯定是跟咱们的索引是没关系的,但是接下来我要做的事是破坏他,

我怎么破坏呢,我这样,我去批量的DELETE,delete t where rownum < 700000,这是多少,rownum只要小于70万的,我一下子删除了

70%的数据,一共100万条,我删除了70%的数据,你说他肯定会对这个索引的碎片,造成很大的影响,咱们来做一下这个事情吧,那这个

删除操作稍微等一下,就是已经删除69万9999条数据

然后commit提交,提交事务,然后我们再次去SELECT COUNT(*) FROM T这张表

这里面就是30万01条了,那么这个时候,我再去做这个事情,我再去查还是一样的

他没有变化是为什么呢,说明了一件事情,这三个指标为什么没有变,原因是你必须去分析一下,之后才会把分析之后的

结果放到index_stats这张表里,把那个覆盖掉了,所以你还是的Copy一下,再分析一下,分析完了之后再查

这回你会发现,比如这个指标高度没有什么变化,或者使用率也没有什么变化,但是最后一个指标变得非常高,

他肯定是大于0.2的,刚才我说的索引碎片量化的指标,是这样的,是这三个条件只要满足一个,那就是你这个表的索引就必须得进行

碎片的整理了,他会有索引碎片的问题,他查询的效率就比较低了,那肯定满足DEL_LF_ROWS/LF_ROWS>0.2了,现在是0.69999了,

那肯定是需要整理的,除了DELETE还有其他的操作,会影响其它两个参数,比如UPDATE,比如insert,可能会影响前面两个参数,

或者你把原先索引ID等于1,你直接把ID改成几了,那这个都是会有影响,可能会影响上面两个指标,接下来是如何整理索引碎片的

问题,我记得是ORACLE 10g以后,官方里面出的时候就说,我们的ORACLE能在线进行索引碎片整理,通过这个语句,

alter index ind_1 rebuild [online] [tablespace name],alter index,ind_1这个index,ind_1是你自己的索引名,然后

rebuild重新重构,重新整理一下这个索引,然后有几个参数可选的,你可以选表空间,这里有一个rebuild [online],在线的整理,

什么意思呢之前92的时候没有这个功能,什么功能,你要整理索引碎片的问题,之前这个表跑了好几年了,4,5年了,发现跑的性能

越来越慢,然后我要重新整理,我该怎么办啊,只有把服务停了,把表锁住了,把另外一张新表进行整理,怎么怎么办,他现在

有一个这样的功能,你这边不影响你表的查询,然后我还可以给你进行整理索引,就是rebuild online,我们加上这个参数

看一下,我可以直接test,然后后面加上一个online,可以指定表空间的无所谓啊,这个无所谓了,我在这里就不测试表空间了

然后瞬间就整理完了,整理完了之后,然后我们再去看stat这块,他就会把之前的清空了

你重新rebuild online了,再次去分析一下,分析完了以后,整理完了以后

刚才说了一个事,什么时候会产生索引碎片的问题,里面有量化指标,当HEIGHT这个东西大于等于4,或者PCT_USED小于50%,

或者DEL_LF_ROWS/LF_ROWS>0.2的时候,我现在整理,2是不是小于4,小于4说明高度是没有问题,通过了我这个指标,PCT_USED小于

50%的时候说明你索引碎片是有问题了,那这个90依然是没有问题,DEL_LF_ROWS/LF_ROWS直接降到0了,那这样的话就会把我这个表,

变得很完美,很完整,就是你可以做一个整理表的这个事,当然在真正的工作中,一般都是这么去用的,都是在数据库表跑一段时间

以后,举个例子吧,其实还有一种方案,我把一张表的数据导到一张新表里,然后再去做一个索引,重新放在一个新表里,把这个索引

的表都干掉,放在第一个新的表里,一个非常完美的表里,没有任何索引碎片的问题,但是你想想,你要整理索引碎片的这张表,

数据量一定是很大,肯定是几亿,几十亿,那你导的过程肯定是耗时的,你需要几个小时,甚至更久,那我倒不如怎么办,rebuild online

先把这个索引碎片问题解决掉,查询效率高一点,然后我这边自己慢慢地去做一些操作,比如把数据重新导入到一张新表里,当这个导完了

之后,所有的都建好了以后,然后大不了把这张表干掉呗,然后再切换回来,切换到各个指标都非常完美的这张表里,再慢慢的去使用,

这就是相当于ORACLE做索引碎片整理的问题,你们用MYSQL的时候,有碰到这个问题是怎么解决的,MYSQL应该是没有这个功能的,

可能你们用MYSQL用的比较熟,大体上就是说一下索引碎片的问题,总之你一张表用的时间久了,你肯定需要频繁的修改删除,那肯定

会造成你查询的效率会越来越慢,索引也就越来越慢,那肯定是需要整理