揭开索引让SQL举步维艰的另一面
|
《揭开索引让SQL举步维艰的另一面》要点： 作者介绍 梁敬彬,福富研究院副理事长、公司唯一四星级内训师,国内一线知名数据库专家,在数据库优化和培训领域有着丰富的经验.多次应邀担任国内外数据库大会的演讲嘉宾,在业界有着广泛的影响力.著有多本畅销书籍,代表作有《收获,不止Oracle》. 任何事物都有它的两面性,索引也不例外,本文我们就来阐述一下索引的坏处. 大家可能没注意到,在查询语句中,如果更新语句出现,索引的缺点将很明显.因为索引本身是有序的,而更新数据的过程中也要更新索引,更新完后还要保持索引的有序性,这就需要付出很大的开销了,索引不好的一面就从这里开始显示出来了. 这里我们先简要综述一下索引的不足之处有哪些,而后详细阐述,随后一起和大家探索各种工作中的案例,最后为思考回顾. 总体学习思路如下图所示：
一、索引的不足之处关于索引的不足之处,我们可以从索引的开销和容易失效这两个方面来讨论,如下图所示：
索引的各种开销还记得前面关于索引结构的分析吗?通过系列步骤,我们明白了索引的结构,推导出索引的三大特性,并应用这些特性让SQL跑得更快. 这只是索引好的一面.真正有问题的一面被掩盖了.那到底都有什么问题 1.热块竞争 你看,索引最新的数据块一般是在最右边,而我们访问数据时正常来说也是访问比较新的数据,历史数据很少有人关注.然而问题来了,大家都一起访问最新的数据,不就都集中于同一个目标来访问了吗?这就很容易产生热块竞争. 2.回表开销 另外,大家都知道索引存储索引列的值和rowid,通过rowid来定位回到表中.其实这个回到表中的开销也是很大,具体情况我们随后可以了解到. 3.更新开销 索引的有序性是一个非常重要的特性,这个特性能够消除排序等开销,但是索引块要保持有序性,可不是一件容易的事.毕竟索引列的数据是随机插入的,比如你在原来的索引列中存储的是100、110、111等等时,现在要插入101,就应该在100和111之间插入,为了保证这个顺序索引需要做很多事,比如索引块分裂.而索引列的增删改的开销是很大的. 4.建立开销 还有千万别忽略了建立索引的开销,这也和索引的有序性有关.我们在建索引的过程中,首先把索引列的数据排序提取出来,再插入到块中形成索引块,这时如果数据不断地插入,排序提取这个动作什么时候能结束呢?所以还必须要锁表,这就是一个很大的开销(Online建索引是一种特殊的思路,这里不做描述).当然建索引过程中排序这个动作本身也是一个不小的开销. 索引使用失效索引的不足之处除了上述的几点外,从另一个维度看,还会有失效的可能.我们现在知道建索引对查询一般比较有利,对更新一般比较有害.不过有的时候,虽然建了索引,但其对查询毫无帮助,这种情况还是有的.比如索引失效了,这分为逻辑失效和物理失效两种. 1.逻辑失效 逻辑失效是索引本身并没有真正失效,只是由于写法的问题导致索引用不上,比如对SQL的条件列进行运算,类似select * from t where upper(name)=‘ABC’等,这时在name列上建了Btree索引是用不上的.再或者比如被人强制用了全表扫描的Hint等导致数据库被迫不用索引等. 2.物理失效 物理失效就是索引真的失效了,比如被人误设了unusable动作,或者是一些类似分区表的不规范操作导致的索引失效.对此后续有详细的例子说明. 二、感受美好索引另一面前面简要描述了索引的不足之处,接下来我们进行更加详细的展开说明,具体细节如下：
索引各种开销
而disorganized表的聚合因子比较大,回表的代价很高,如下,产生21360个buffer：
3.索引更新方面的开销比较大 环境搭建：
脚本1：无索引,表记录增加,插入不怎么变慢 由上面代码可以看出,虽然t_small是小表,t_big是大表.但是插入一般不会随着记录的增加越插越慢.什么时候会越插越慢,就是当表有索引的时候.因为索引需要维护,越大维护越困难.我们继续做一组试验. 环境准备(建3张结构和记录都一样的表,只是索引分别是6个、2个及无索引)：
分别往这三张表里插记录：
脚本2：有索引的表,记录越多,插入越慢 表记录越大,索引越多,插入越慢,从试验结果来看,这一点还是非常明显的. 4.建索引的过程开销也很大 (1)建索引过程会产生全表锁
可以通过如下方式查看被锁的情况：
脚本3：建索引产生锁 (2)建索引过程会产生全表排序未建索引前,观察一下数字字典中记录的系统排序情况,如下：
建索引后,继续观察,发现排序次数sorts (memory)增加了,如下：
脚本4：建索引产生排序
索引使用失效索引逻辑失效 (1)用索引反而代价变得更高这个道理比较简单,如果应用索引范围检索数据,返回大量记录且几乎是所有的记录,这时候用索引肯定有错,索引范围查询访问一般适合返回少量记录的情况,否则用全表扫描或者全索引扫描就可以. (2)发生索引列的类型转换在表字段设计的时候有一个非常重要的原则,什么类型的字段存什么类型的值,否则就会发生类型转化,具体请看如下例子：
实际上只有如下写法才可以用到索引,这个很不应该,如果什么类型的取值就设置什么样的字段,把ID字段类型改为Number,就顺畅了,如下：
脚本5：索引列的类型转换 (3)对索引进行了各种运算对索引列进行了各种运算,详见后面的案例部分. 索引物理失效(1)long列调整导致索引失效环境准备(建表,建long字段)：
接下来将long修改为clob,发现索引失效了,必须重建索引,如下：
脚本6：long列调整导致索引失效 (2)move操作会导致索引失效move是一个危险系数非常高的操作,虽然它可以收缩表降低高水平位,却会导致索引失效,因而需要重建索引,请看下面例子：
脚本7：move操作导致索引失效 (3)分区表导致索引失效的操作这在前面已经描述过了,这里就不再重复了,请读者自行回到前面的章节进行复习总结.归纳如下： truncate分区会导致全局索引失效,不会导致局部索引失效.如果对truncate 增加update global indexes,则全局索引不会失效. drop分区会导致全局索引失效,局部索引因为drop分区,所以也不存在该分区的局部索引了.如果对drop分区增加update global indexes,全局索引不会失效. split分区会导致全局索引失效,也会导致局部索引失效.如果对split分区增加update global indexes,则全局索引不会失效. add 分区不会导致全局索引失效,也不会导致局部索引失效. exchange会导致全局索引失效,不会导致局部索引失效.如果对exchange分区增加update global indexes,则全局索引不会失效. 重要结论： 所有的全局索引,只要用到update global indexes,都不会失效,其中add分区甚至不需要增加update global indexes都可以生效. 局部索引的操作都不会失效,除了split分区.切记split分区的时候,要将局部索引进行rebuild.
索引取舍控制
脚本8：组合索引前缀与单列索引 2.删除系统从未用到的索引 环境搭建,建表建索引并完成某列索引的监控：
接下来继续执行一个用索引的查询,然后再观察Used字段,发现索引被用过：
停止对索引的监控,观察v$object_usage状态变化,发现MONITORING的值为NO,且END_MONITORING记录了停止监控的时间,如下：
脚本9：删除系统从未用到的索引 3.组合列过多的索引很可疑 组合索引一般不宜过多,如果组合索引列达到4个以上,那这个索引本身就很大,就不一定高效.另外,索引更新也会出现比较大的性能问题. 文章来自微信公众号：DBAplus社群 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
