常用索引简介

引言

   提到sql性能优化，索引是最常用的手段之一，我们经常会看到create index on 表名（列名...）之类的脚本，这就是最为常用的索引，它具有加快查询速度的作用，当数据库数据量大的时候，效用将会比较明显。笔者想将索引总结分类，分类一定是按照某一种分类逻辑去分的，经查阅大量资料，发现网上很多文章基本都是按逻辑（单行索引，多行索引，函数索引等等）或者按物理空间（分区索引，非分区索引等）或者按数据结构（B树索引，HASH索引，全文索引等）去进行分类，种类五花八门而且很多基本不常用，建立索引的目的就像建立书本的目录一样，查找内容的时候先去查找目录再定位内容就能提高效率，而不是对书本全局扫描，笔者在这里总结几种常用索引。

索引分类

主键索引 

   不需要手动创建，我们在创建主键的时候数据库会自动加上索引

   eg:我们执行  alter table 表名 add constraint 主键名 primary key(列名)  后，当我们使用plsql查看该表的创建语句时会发现创建主键的语句是  

我们发现数据库确实自动给主键对应的列加上了索引

普通索引 

     创建语句: create index 索引名 on 表名(列1,列2...列n)

     可以是单列索引，也可以是联合索引，是B树结构的一种

唯一索引 

     创建语句: create unique index 索引名 on 表名(列1,列2...列n)

     可以单列可以联合，主要作用是确保单列数据或者联合数据的唯一性，是B树结构的一种

位图索引

     创建语句: create bitmap index 索引名 on 表名(列名)

     适用范围：基数较小，且基数值相对稳定（没有频繁的update）

     example: select * from table where gender=‘男’ //对gender建立位图索引

     原理这篇博客讲的很清楚: https://www.cnblogs.com/LBSer/p/3322630.html，引用里面的一句话：位图索引的关键是缩小了存储空间，以使得内存遍历成为可能，

函数索引

     创建语句: create  index 索引名 on 表名(F(列名))

     F是数据库中的函数，可以是系统函数，eg:to_char(), 也可以是自定义函数，自定义函数必须是deterministic修饰的

       deterministic意为确定性的，即对于某一函数，当输入参数确定后，输出参数在任何时间一定是个确定的值，不会随着时间发生变化，例如某个函数的功能是返回输入值和当前系统时间组成的字符串，这种函数就不是确定性函数，不满足要求

     example: select.....from a where to_char(a.age)='15' //对to_char(age)建索引

     select.....from a where  decrypt_des(a.cert_id,'123456')='stop' //对decrypt_des(a.cert_id,'123456')建索引

  但这种函数索引较少使用，因为通常对值转换是更好的方式，除非是一些特殊情况，例如对列做md5运算后判断是否相等，因为非对称加密的关系无法对值进行解密

注意事项

以上介绍的索引优化的前提条件是where 后面使用"=", 如果不使用"="而是使用一些其它的符合有可能还是会使用全局扫描从而导致索引失效，以下列出的有一部分前者为索引失效的用法，后者为替换用法

<>  n  或者!=n   都会导致全表扫描，改为 <n or >n ;

is null,is not null  会导致全表扫描，无替换方法，最好不这样用 ;

dept_id(varchar2,列有索引) =5 ，系统会自动对dept_id加to_number()使索引失效，改为

dept_id ='5'  ;

使用like也会导致全表扫描 ;

in,not in也会导致全表扫描，如果连续，可改为between...and...  ;

尽量不对字段进行表达式操作，会导致全表扫描，

索引使用的大概规律

数据量较小时不需要创建索引

频繁作为查询条件的字段或者作为order by字段应该创建索引

查询中与其他表有关联的字段

频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件

where条件里用不到的字段，不创建索引

查询中排序的字段创建索引将提高排序速度

查询中统计或者分组的字段