【Oracle】关于索引的那些事

【Oracle】关于索引的那些事

关于索引的理解，举一个不是很恰当的例子，如果要在英汉字典查询‘index’，可以从第一页开始往后翻，一页一页查，知道查询到‘index’，也可以根据字典目录快速找到‘index’所在页码，直接查到，相比之下，第二种方法更加快速，而索引起到的作用就跟第二种方式中的目录相似。索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率。当索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引，索引删除，不会对表产生影响，并且索引对用户是透明的，无论表上是否有索引，sql语句的用法不变。

一，索引分类

oracle的索引有很多种，可以按不同的角度进行分类，比如逻辑上的索引：Single column 单行索引、Concatenated 多行索引、Unique 唯一索引、NonUnique 非唯一索引、Function-based函数索引、Domain 域索引，物理上的索引：Partitioned 分区索引
、NonPartitioned 非分区索引。
不过，根据实现索引的原理：可以分为三类：B-TREE索引、位图索引(bitmap索引)、HASH索引。

1，B-TREE索引
Oracle数据库中最常见的索引类型是B-TREE索引，CREATE INDEX语句时，默认就是在创建B-TREE索引。
原理：Oracle的B-TREE索引呈树状，包含两种类型节点，一种是索引分支块（根节点块，分支节点块）一种是索引叶子块（叶子节点块），分节点用来搜索，叶子节点用来存储数据，根节点存储索引的低层分支节点的数据。检索时，根据检索条件，Oracle将树沿向下导航，经过不同的分支节点，直到找到需要的叶子节点。叶子节点具有两个字段，第一个字段表示索引的键值，对于单列索引来说是一个值；而对于多列索引来说则是多个值组合在一起的，第二个字段表示键值所对应的记录行的ROWID，该ROWID是记录行在表里的物理地址，这样就直接定位到记录。

分类：NON-UNIQUE(默认)，UNIQUE(默认为主键创建)，反向索引(索引的键值反转建立索引)，函数索引(为函数结果建立索引，每行返回独立结果，聚合函数不能使用)，还有降序索引，复合索引之类的。

使用场景：表的行数多；列的不同的值的个数很多；

2，位图索引(bitmap索引)
原理：位图索引的结果与B-TREE相似，呈树状结构，但是叶子节点中存储的内容不同。假设，表中有性别字段，它的值就1-男、0-女，那么位图索引的树状结构就简单些，一个根节点，两个分支节点，两个叶子节点。叶子节点中索引条目包含表里**第一条记录所对应的ROWID以及最后一条记录所对应的ROWID，索引条目的最后一部分则是由多个bit位所组成的bitmap，每个bit位就对应一条记录。**当发出where gender='1’这样的SQL语句时，oracle会去搜索’1’所在的索引条目，然后扫描该索引条目中的bitmap里所有的bit位。第一个bit位为1，则说明第一条记录上的gender值为1，于是返回第一条记录所在的ROWID（根据该索引条目里记录的start ROWID加上行号得到该记录所在的ROWID）。第二个bit位为0，则说明第二条记录上的gender值不为1，依此类推。另外，如果索引列为空，也会在位图索引里记录，也就是将对应的bit位设置为0即可。
除此之外，位图索引可以进行与或非的任意组合，再有一列婚姻状况，1-已婚，0-未婚，同样建立为位图索引，当where is_married = 1 and gender =1 , 两个位图索引&&，得到一组新的bitmap，然后按照之前那样找到ROWID，其他逻辑操作也是相似的操作。

使用场景： 列中包含的值为枚举类型，也就包含几个不同的值，表中的行数多列用于布尔代数运算

3，HASH索引
参考：https://blog.csdn.net/weixin_36634753/article/details/88703408

在实际工作中，我只遇到和使用过B-TREE索引，其他两个索引都没遇到过。

二，索引操作

创建单一索引
create index 索引名称 on 表名(列名);
创建复合索引
create index 索引名称 on 表名(列名1,列名2);
创建反向索引
create index 索引名称 on  表名(列名) reverse;
创建函数索引
create index 索引名称 on 表名(函数名(列名));
创建位图索引
reate bitmap index 索引名称 on 表名(列名) ;
创建唯一索引（oracle默认为表主键创建）
create unique index 索引名称 on 表名(列名) ;

查询索引，表名称大写
select * from all_indexes where table_name = '表名称';
select* from all_ind_columns where table_name = '表名称';

三，建索引的原则

一般需要创建索引的情况

在经常需要搜索的列上，可以加快搜索的速度；
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
在经常用在连接的列上，这些列主要是一些外键，可以加快表连接的速度；
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的，比如日期；
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度；

一般不创建索引的情况

对于那些在查询中很少使用或者参考的列不应该创建索引；
对于那些只有很少数据值的列也不应该增加索引；
对于那些定义为text, image和bit数据类型的列不应该增加索引；
当修改性能远远大于检索性能时，不应该创建索引，频繁修改，索引相应的也需要进行动态的更新；

四，索引失效的情况

1，使用不等于<> 、 != ，（不等于操作符一定会进行全表扫描）
2，使用is null 、 is not null ，因为数据库并没有定义null值。如果被索引的列中有很多null，就不会使用这个索引，所以建表时把需要索引的列最好定义为非空(not null)。
3，使用函数，如果需要，可以考虑建函数索引。
4，数据类型不匹配，比如class_id，在建表时为varchar2(10)，但是在查询中使用 where class_id = 123456，这是oracle就会先执行一个类型转换，to_number(class_id)，这样就限制了索引的使用。
5，使用like子句，Like 的字符串中第一个字符如果是‘%’则用不到索引，Column1 like ‘aaa%’ 是可以的。
6，对索引列进行运算，如果需要，可以考虑建函数索引。
7，如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引，即至少要包含复合引的第一列。
8，在基于CBO的优化器（花费）下，查询的数据量超过大表数量的30%，或很小时。也可能导致不使用索引，估计使用索引比全表扫描慢。
9，查询条件中使用or连接时，需要每个列都必须有索引才能走索引。
10，使用not in 会进行全表扫描，关于in、exists，可以参考这篇博文https://www.cnblogs.com/seasons1987/archive/2013/07/03/3169356.html
11，等于和范围索引不会被合并使用，比如：SELECT * FROM CARS WHERE COLOR = ‘yellow’ AND TYPE = ‘B’，COLOR和TYPE列上都创建了非唯一索引，在这种查询条件下Oracle并不会合并索引，而只会使用第一个索引，即只有COLOR列上索引会生效。

参考:
https://blog.csdn.net/wulex/article/details/79394072
https://blog.csdn.net/weixin_36634753/article/details/88703408
https://www.cnblogs.com/cxxjohnson/p/5836203.html
https://www.jianshu.com/p/05a4d2686613