oracle 索引的创建与使用

什么是数据库索引

在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行得更快。

索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。 

对于数据库来说，索引是一个必选项，但对于现在的各种大型数据库来说，索引可以大大提高数据库的性能，以至于它变成了数据库不可缺少的一部分。

---

索引原理

1.  若没有索引，搜索某个记录时（例如查找name='wish'）需要搜索所有的记录，因为不能保证只有一个wish，必须全部搜索一遍

2. 若在name上建立索引，oracle会对全表进行一次搜索，将每条记录的name值哪找升序排列，然后构建索引条目（name和rowid），存储到索引段中，查询name为wish时即可直接查找对应地方

3.创建了索引并不一定就会使用，oracle自动统计表的信息后，决定是否使用索引，表中数据很少时使用全表扫描速度已经很快，没有必要使用索引

---

索引使用

创建一个测试表

#创建一个测试表
create table cstable (id int,sex char(1),name char(10));

#插入5000万行测试数据
begin
for i in 1..50000000
loop
insert into cstable values(i,'M','ljy');
end loop;
commit;
end;
/

（1）单一索引

单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上。

#创建一个单一索引
create index cs_index1 on cstable(id);
注：对表的第一列（id）创建索引。

#查看某一行的数据
select id,sex,name from cstable where id=8888888;
#创建索引前大约16.77秒，创建索引后查找大约0.366秒

（2）复合索引：基于多个列的索引

索引分离于表，作为一个单独的个体存在，除了可以根据单个字段创建索引，也可以根据多列创建索引。

用户可以在多个列上建立索引，这种索引叫做复合索引(组合索引)。复合索引的创建方法与创建单一索引的方法完全一样。

Oracle要求创建索引最多不可超过32列。

create index cs_index2 on cstable(id,sex);
注：对表的列（sex,name）创建索引。

select id,sex,name from cstable order by id,sex;
#创建索引前大约16.77秒，创建索引后查找大约39.366秒（不知道为啥）

（3）创建基于函数的索引

--在cstable表的name列上执行大小写无关的搜索
CREATE INDEX upper_index3 ON cstable(UPPER(name))

SELECT * FROM cstable WHERE UPPER(name)='LJY'

（4）删除和修改索引

a.重建索引：如果经常在索引列上执行DML操作，需要定期重建索引
ALTER INDEX cs_index1 REBUILD;

b.删除索引
DROP INDEX cs_index2;