Oracle索引个人总结

概述

为什么要建立索引，几乎每个人的第一反应就是能够提升查询速度。但是建立索引和不建立索引有什么区别，索引为什么能够提升查询速度呢。

当数据库表中存在数据量比较大时，对表执行指定条件的查询的时候。常规的查询方法会将所有的记录都读取出来，然后再把读取的每一条记录与查询条件进行对比，最后返回满足条件的记录。这样进行操作的时间开销和I/O开销都很大。对于这种情况，就可以考虑通过建立索引来减小系统开销。

有了索引之后，只需要在索引中找到符合查询条件的索引字段值，就可以通过保存在索引中的ROWID快速找到表中对应的记录。举个例子索引就像是一本书的目录，通过目录可以迅速找到相应的页码，进而可以阅读相应页码的内容，而没必要去翻看整本书去一页一页的查找。

索引的基本概念

• 索引是建立在表上的可选对象
• 索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率
• 索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表
• 索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引，索引删除，不会对表产生影响
• 索引对用户是透明的，无论表上是否有索引，sql语句的用法不变
• oracle创建主键时会自动在该列上创建索引

索引操作

创建

create or replace unique|bitmap index <schema>.<index_name> on <schema>.<table_name>
  (<column_name>|<expression> asc|desc ,
   <column_name>|<expression> asc|desc ...)

–eg:

create index index_sno on student('name');

其实上面这几个属性就差不多够用了，其他还有很多属性。

create or replace unique|bitmap index <schema>.<index_name> on <schema>.<table_name>
  (<column_name>|<expression> asc|desc ,
   <column_name>|<expression> asc|desc ...)
    tablespace <tablespace_name>
    storage <storage_settings>
    logging|nologging
    compute statistics
    nocompress|compress<nn>
    nosort|reverse
    partition|global partition<partition_setting>

• unique | bitmap : unique表示唯一值索引，bitmap表示位图索引，为空则默认为B-tree索引
• column_name | expression asc | desc , … :可以单列索引，也可以多列进行联合索引
• tablespace : 制定存放索引的表空间（当表和索引在不同的表空间的时候，效率更高）
• storage : 可以设置表空间的存储参数
• logging | nologging : 是否对索引产生redolog（对于大表来说，可以设置为nologging从而来减少空间占用，提高效率）
• compute statistics : 设置为创建索引时，收集统计信息
• nocompress | compressnn : 是否使用“键压缩”（使用键压缩可以删除一个键列中出现的重复值）
• nosort | reverse : nosort表示与表中相同的顺序进行创建索引，reverse表示使用与表中相反的顺序进行创建索引
• partition | nopartition | global partition : 可以在分区表上和未分区表上对创建的索引进行分区

修改

重命名索引

alter index [index_name1] rename to [index_name2];

合并索引

表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低（涉及到索引的结构，细讲可以单独写篇博客了）。

alter index [index_name] coalesce;

重建索引

• 方式一：删除原来的索引，重新建立索引
• 方式二：alter index [index_name] rebuild;

删除索引

drop index [index\_name];

查看索引

select index_name, index_type, tablespace_name, uniqueness from all_indexes where table_name ='table_name';

create index index_sno on student('name');

select * from all_indexes where table_name='student';

索引的优缺点

优点

• 大大加快检索数据的速度
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
• 加速表与表之间的连接，所以一般有外键就要建索引（底层原因有待探究，或许是因为外键经常涉及到查询，不过现在建表很少有建外键的了）
• 查询语句汇总含有分组或者排序的语句时，速度更快（因为Oracle维护的索引是有序的）
• 查询的过程中，使用索引，可以提高系统的性能

缺点

• 创建索引，占一定的物理空间
• 在对表进行增删改的时候，索引相应的也需要进行动态的更新，耗费时间随着数据量的增大而增大（所以频繁修改的列最好不要建索引）

什么时候适合建立索引

适合建立索引的情况

• 经常需要搜索的列上
• 主键，一般建立唯一性索引，保持数据的唯一性（Oracle会自动创建）
• 外键，提高表与表之间连接的速度
• 需要排序的列上
• where子句后边经常出现的字段
• 经常需要根据范围进行搜索的列上，比如日期

不适合建立索引的情况

• 很少进行搜索的列上
• 列取值比较少的列上
• blob类型的列上
• 修改频率比较高的列上

以上原因对比一下索引的优缺点就会明白。

索引的分类

B树索引

（默认索引，保存排序过的索引列和对应的rowid值）

• 说明：

1. oracle中最常用的索引；B树索引就是一颗二叉树；叶子节点（双向链表）包含索引列和指向表中每个匹配行的ROWID值
2. 所有叶子节点具有相同的深度，因而不管查询条件怎样，查询速度基本相同

• 分类：
  UNIQUE,NON-UNIQUE(默认),REVERSE KEY（数据列中的数据是反向存储的）
• 创建例子：
  create index index_sno on student('sno');
• 适合使用场景：
  列基数（列不重复值的个数）大时适合使用B数索引

位图索引

• 说明：

1. 创建位图索引时，oracle会扫描整张表，并为索引列的每个取值建立一个位图（位图中，对表中每一行使用一位（bit，0或者1）来标识该行是否包含该位图的索引列的取值，如果为1，表示对应的rowid所在的记录包含该位图索引列值），最后通过位图索引中的映射函数完成位到行的ROWID的转换

• 创建例子：
  create bitmap index index_sno on student(sno);
• 适合场景：
  对于基数小的列适合建立位图索引（例如性别等）

单列索引和复合索引（基于多个列创建）

注意：如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引，即至少要包含组合索引的第一列。（复合索引的顺序要求记不太清了，只记得要将最常查询的列放在最前面，后续补上）

Create Index i_deptno_job on emp(deptno,job); —>在emp表的deptno、job列建立索引。
select * from emp where deptno=66 and job='sals' ->走索引。
select * from emp where deptno=66 OR job='sals' ->将进行全表扫描。不走索引
select * from emp where deptno=66 ->走索引。
select * from emp where job='sals' ->进行全表扫描、不走索引。

函数索引

• 说明：

1. 当经常要访问一些函数或者表达式时，可以将其存储在索引中，这样下次访问时，该值已经计算出来了，可以加快查询速度
2. 函数索引既可以使用B树索引，也可以使用位图索引；当函数结果不确定时采用B树索引，结果是固定的某几个值时使用位图索引
3. 函数索引中可以使用len、trim、substr、upper（每行返回独立结果），不能使用如sum、max、min、avg等

create index fbi on student (upper(name));
select * from student where upper(name) ='WISH';

限制索引/索引失效（建立了索引，但是无法使用）

使用 IS NULL 和 IS NOT NULL

SELECT ... FROM emp WHERE comm IS NULL; comm 列的索引会失效

WHERE 子句中使用函数

如果没有使用基于函数的索引，那么 where 子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。例如：select * from staff where trunc(birthdate) = '01-MAY-82';

但是把函数应用在条件上，索引是可以生效的，把上面的语句改成下面的语句，就可以通过索引进行查找。select * from staff where birthdate < to_date('01-MAY-82');
注意：对于 MIN, MAX 函数，Oracle 仍然使用索引。

使用 LIKE ‘%T%’ 进行模糊查询

之前有看到说’T%'的话索引是会生效的，有待验证。

WHERE 子句中使用不等于操作

（ 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中.）
不等于操作包括：<>, !=, NOT colum >= ?, NOT colum <= ?

对于这个限制条件可以通过 OR 替代，例如： colum <> 0 ===> colum>0 OR colum<0

等于和范围索引不会被合并使用

SELECT emp_id, emp_m, salary_q ... FROM emp WHERE job='manager' AND dept_no>10;

job 和 dept_no 都是非唯一索引，这种条件下 oracle 不会合并索引，它只会使用第一个索引。

比较不匹配数据类型

dept_no是一个varchar2型的字段，在这个字段上有索引，但是下面的语句会执行全表扫描。select * from dept where dept_no = 900198;
这是因为 oracle 会自动把 where 子句转换成 to_number(dept_no)=900198，相当于使用函数，这样就限制了索引的使用。

正确写法如下：select * from dept where dept_no = '900198';

这篇文章主要是对着以前做的笔记进行回忆和总结，现在工作中除了写写SQL基本上与Oracle数据库没太大关系了，感觉以前学的很多东西都快要忘记了。也包括markdown~||，为了写这边博客又特地复习了一下。由于博主的阅历有限，时间又有点晚了，写的可能有点仓促，文中不足和错误的地方欢迎大家指正^^，后续我会修改。文中还缺乏很多例子，后续有时间也会补上。

参考链接：

https://blog.csdn.net/sinat_27933301/article/details/79968971

https://blog.csdn.net/runrabbit/article/details/52151990

https://www.cnblogs.com/wishyouhappy/p/3681771.html

https://www.cnblogs.com/wang-junxi/archive/2017/12/25/8111663.html