数据库索引详解-优快云博客

本文链接：https://blog.youkuaiyun.com/BIGPLANS/article/details/103048669

索引

什么是索引

1）索引是数据库对象之一，合理的使用索引可以大大降低 i/o 次数用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。

2）索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率

3）索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表；

4）索引一旦建立，在表上进行 DML 操作时（例如在执行插入、修改或者删除相关操作时），oracle 会自动管理索引，索引删除，不会对表产生影响

5）索引对用户是透明的，无论表上是否有索引，sql 语句的用法不变

6）oracle 创建主键时会自动在该列上创建索引

但是，对表进行 insert,update,delete 处理时，由于要表的存放位置记录到索引项中而会降低一些速度。

注意： 一个基表不能建太多的索引；
空值不能被索引
只有唯一索引才真正提高速度,一般的索引只能提高 30%左右。

建立索引

CREATE [UNIQUE] | [BITMAP] INDEX index_name --unique 表示唯一索引
ON table_name([column1 [ASC|DESC],column2 --bitmap，创建位图索引
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name] --指定索引的表空间
[PCTFREE n1] --指定索引在数据块中空闲空间
[STORAGE (INITIAL n2)]
[NOLOGGING] --表示创建和重建索引时允许对表做 DML 操作，默认情况下不应该使用
[NOLINE]
[NOSORT]; --创建索引时不进行排序，默认不适用，如果数据已经是按照该索引顺序排列的可以使用

索引分类

B 树索引

说明：
1.oracle 中最常用的索引；B 树索引就是一颗二叉树；叶子节点（双向链表）包含索引列和指向表中每个匹配行的 ROWID 值

2.所有叶子节点具有相同的深度，因而不管查询条件怎样，查询速度基本相同

3.能够适应精确查询、模糊查询和比较查询

创建例子
craete index index_sno on student(‘sno’);

适合使用场景
列基数（列不重复值的个数）大时适合使用 B 数索引

位图索引

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

创建例子
create bitmap index index_sno on student(sno);

适合场景：
对于基数小的列适合简历位图索引（例如性别等）

函数索引

说明：

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

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

单列索引和复合索引

注意：
即如果索引建立在多个列上，只有它的第一个列被 where 子句引用时，优化器才会使用该索引，即至少要包含组合索引的第一列
1．单列索引
单列索引是基于单个列所建立的索引，比如:
CREATE index 索引名 on 表名(列名)
2．复合索引
复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同,如：

Create index emp_idx1 on emp(ename,job); 
Create index emp_idx1 on emp(job,ename); 
-- 范例：给 person 表的 name 建立索引
create index pname_index on person(name);
-- 范例：给 person 表创建一个 name 和 gender 的索引
create index pname_gender_index on person(name, gender);

修改索引

查看索引

语法：

select index_name,index-type, tablespace_name, uniqueness
from all_indexes 
where table_name ='tablename';

示例：

create index index_sno on student('name'); 
select * from all_indexes where table_name='student';

重命名索引

alter index index_sno rename to bitmap_index;

删除索引

drop index to bitmap_index;

合并索引

表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者
合并索引,合并索引方式更好些，无需额外存储空间，代价较低）

alter index index_sno coalesce;

重建索引

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

索引建立原则总结

经常进行连接查询的列应该创建索引
数据比较少的表不要建立索引
对于基数大的列适合建立 B 树索引，对于基数小的列适合简历位图索引
如果有两个或者以上的索引，其中有一个唯一性索引，而其他是非唯一，这种情况下 oracle 将使用唯一性索引而完全忽略非唯一性索引
至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被 where 子句引用时，优化器才会使用该索引）
列中有很多空值，但经常查询该列上非空记录时应该建立索引
经常进行连接查询的列应该创建索引
使用 create index 时要将最常查询的列放在最前面
限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会
占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的
维护，降低了数据的维护速度）
LONG（可变长字符串数据，最长 2G）和 LONG RAW（可变长二进制数据，最长 2G）
列不能创建索引

注意事项

通配符在搜索词首出现时，oracle 不能使用索引，eg：
–我们在 name 上创建索引；
create index index_name on student(‘name’);
–下面的方式 oracle 不适用 name 索引
select * from student where name like ‘%wish%’;
–如果通配符出现在字符串的其他位置时，优化器能够利用索引；如下：
select * from student where name like ‘wish%’;
不要在索引列上使用 not，可以采用其他方式代替如下：（oracle 碰到 not 会停止
使用索引，而采用全表扫描）
select * from student where not (score=100);
select * from student where score <> 100;
–替换为
select * from student where score>100 or score <100
索引上使用空值比较将停止使用索引， eg：
select * from student where score is not null;