Oracle索引之Btree索引
索引介绍
日常开发中,对于数据的查询如果需要优化,常听说要加个索引。但是为什么加了索引,数据的查询就快了呢?那是不是加了索引就一定会是有效或者有利的呢?
Oracle中常见有BTREE索引,位图索引和函数索引。
我们今天就先介绍一下这个BTREE索引吧。既然叫BTREE索引,那就从它的树结构说起:
建BTREE索引其实是先拿出所有数据排序,将有序的索引列的值和rowid存进Oracle的各个数据块中,形成索引块,存在内存中。这些数据块以树结构的形式组织起来,父节点只记录子节点的键值位置信息,不存具体数据,所以,只有叶子块存具体数据(索引列数据和rowid)。其他非叶子节点只记录位置信息,占用空间非常小,所以索引块即使存了很多数据,这颗树的高度其实并不高。如下图:
索引查询
查询的时候,根据树的结构去查询,产生的逻辑读的次数也就是树的高度,走索引的逻辑读不会很多,产生的IO少,所以肯定比全表扫描要效率高。
注意:如果索引列包含空值,是不会走索引的。所以,在一些查询中,要想走索引,需要加上条件“索引列 is not null”或者修改表字段属性为非空。
例如:没建索引之前,对于统计函数肯定是全表扫描:
建索引之后,如果我们不对空值进行筛选,是不走索引的:
对于空值进行排除后,可以看到走索引了:
在最大最小值的查询中,索引也是优化的一个手段。没建索引之前:
建了索引之后,因为索引的有序特性,直接去索引树的最右或最左叶子节点找一遍就可以了:
再看select具体字段和select *的区别。
比较下面两个查询:
上面我们说过,索引储存了索引列和rowid的数据,如果我们只取索引列的数据,则,访问到索引块即取到我们需要的数据了,如果还需要取其他字段的数据,在索引中找不到,则会存在一个去表中取数据的操作,即“TABLE ACCESS BY INDEX ROWID ”,多产生的读取操作必然会增加消耗,所以如果有些字段必须展现,又数据量很小,可以考虑建联合索引。
既然索引是有序的,那我们的order by操作是不是也可以用索引来优化呢?答案是肯定的:
建完索引之后:
可见走索引的排序要比全表扫描的排序消耗小很多。
上面我们见到了执行计划中各种走索引的方式,那他们有什么区别呢?感兴趣的童鞋可以讨论讨论呀~
联合索引
上面说到,为了避免回表,我们可以建联合索引,但组合列最好不要超过3列,返回的组合列越少越高效;
问题也随之而来,组合列中哪个列在前哪个列在后呢?
一般,在等值查询情况下,谁在前谁在后没关系;如果一列是范围查询,一列是等值查询,等值查询列在前效率高;联合索引中,如果针对其中一列的查询单列比较多的时候,单列查询在前。比如在某表中,想对其code和pcode字段建联合索引,但是业务中有大量的查询pcode的单列查询操作,则联合索引应该将pcode放在前面比较合适。
索引的危害
上面说了这么多索引的优点,那是不是建索引一定是有利的呢?
因为索引的有序特性,索引索引越多,为了维持索引有序,更新数据受到影响就会越大:
insert:每插入一条数据,就要维持索引有序,因此,索引对于插入操作有弊无利;
delete:删除数据,如果数据量很大,对于定位要删除的少量数据,条件列是索引列是有利的。如果删除大量记录或者索引列过多,对于删除操作是有弊端的;
update:如果更新非索引列,则无影响;如果更新索引列,定位和更新索引列则和delete操作类似。
因为是将数据拿出来排序并且保存到数据块中,建索引时会锁表,以免建索引过程中数据更新造成影响,所以,建索引尤其是大数据表,不要在使用高峰期建。
以上便是BTREE索引的一些简单介绍啦,还请多多指正啊~
我是一个呆头呆脑的程序员,一直想做一只有趣的代码狗,关注公众号codinggogo,了解更有趣的爱恨情仇啊~