（五）索引

（五）索引

1.什么是索引

索引是用于加速数据存取的数据对象，可以理解为：一本书中的 ‘目录’。合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。

索引是需要占据存储空间的，也可以理解为是一种特殊的数据。形式类似于下图的一棵“树”，而树的节点存储的就是每条记录的物理地址，也就是我们提到的伪列（ROWID）

2.普通索引

语法

create index 索引名称 on 表名(列名)

需求：我们经常根据学生姓名搜索学生信息，所以我们饿基于学生表的sname字段来建立索引

create index index_stu_sid on STUDENT(SNAME)

索引性能测试：

创建一个两个字段的表

create table T_INDEXTEST (
ID NUMBER,
NAME VARCHAR2(30)
);

编写 PL/SQL 插入 100 万条记录（后续学到）

BEGIN
FOR i in 1..1000000
loop
INSERT INTO T_INDEXTEST VALUES(i,'AA'||i);
end loop;
commit;
END;

创建完数据后，根据 name 列创建索引

CREATE INDEX INDEX_TESTINDEX on T_INDEXTEST(name)

执行下面两句 SQL 执行

SELECT * from T_INDEXTEST where ID=765432;
SELECT * from T_INDEXTEST where NAME='AA765432';

我们会发现根据 name 查询所用的时间会比根据 id 查询所用的时间要短

3.唯一索引

如果我们需要在某个表某个列创建索引，而这列的值是不会重复的。这是我们可
以创建唯一索引

create unique index 索引名称 on 表名(列名);

需求：在业主表的水表编号一列创建唯一索引

create unique index index_stu_sid on
STUDENT(sid);

4.联合索引

我们经常要对某几列进行查询，比如，我们经常要根据学历和性别对学员进行搜索，如果我们对这两列建立两个索引，因为要查两棵树，查询性能不一定高。那如何建立索引呢？我们可以建立复合索引，也就是基于两个以上的列建立一个索引

经常对几列进行查询,所以用到了组合索引，组合索引的第一个字段必须出现在查询组句中，并且不能跳跃，否则不会用到

语法

create index 索引名称 on 表名(列名,列名.....);

需求：根据姓名和学号建立索引

create index stu_index_idname
on STUDENT(sid,sname);

5.反向键索引

应用场景：当某个字段的值为连续增长的值，如果构建标准索引，会形成歪脖子树。这样会增加查询的层数，性能会下降。建立反向键索引，可以使索引的值变得不规则，从而使索引树能够均匀分布

语法

create index 索引名称 on 表名(列名) reverse;

6.位图索引

使用场景：位图索引适合创建在低基数列上，即指指这一列出现的可能值较少
位图索引不直接存储 ROWID，而是存储字节位到 ROWID 的映射

优点：减少响应时间，节省空间占用

语法：

create bitmap index 索引名称 on 表名(列名);

需求：在STUDENT表的ssex列上建立位图索引

create bitmap index index_stu_ssex
on STUDENT(ssex)

7.优缺点

优点：

• 加快数据的 ‘检索速度’
• 可以保证列值的 ‘唯一性’（unique、主键）
• 实现表与表之间的 ‘参照完整性’（外键）
• 在使用 order by、group by 子句时，可以减少排序和分组的时间

缺点：

• 当对表数据进行 DML 操作的时候，索引自动维护，’降低 DML 操作的速度’
• 索引需要占 ‘物理空间’，同数据表的 ‘表空间’ tablespace 一样
• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

使用原则：

‘主键’ 和 ‘外键’ 会自动创建索引，无需人工操作

选择正确的表：经常检索包含大量数据的表中小于 15% 的行

选择正确的列：多个表之间的关联关系

合理安排索引列：（A，B，C） A 最常用，B 次之，C 最末

8.查询表中所有索引

select * from all_indexes where table_name= ‘表名’

删除索引

drop index 索引名 on 表名