数据库索引

索引是什么

数据库中索引(index)的概念与目录的概念非常类似，如果列出现在查询的条件中，而该列数据是无序的，查询时只能从第一行开始一行一行往下匹配。创建索引就是对某些特定列中的数据进行排序，生成独立的索引表，在某列上创建索引后，如果该列出现在查询条件中，oracle会比对全表扫描和索引扫描的代价，如果索引扫描代价少，那么oracle就会自动引用该索引，先从索引表中查出符合该条件记录的rowid。由于rowid保存的是物理地址，因此可以根据rowid快速定位到具体的记录。表中数据比较多时，引用索引带来的查询效率非常可观。

oracle数据库会为表的主键和包含唯一约束的列，自动创建唯一索引。索引可以提升效率但是在数据增删改时需要更新索引。

索引的类型

• 唯一索引：索引列的值必须是唯一的(emp.empno)
• 普通索引：索引列的值没有唯一限制（emp.deptno）
• 组合索引：多个字段创建索引（deptno+ename）
• 位图索引：使用位图的数据库索引针对大量相同的列而建立（sex）

什么时候使用索引

1. 如果表中的某些字段经常被查询，并且作为查询条件出现时，可以考虑为该列创建索引
2. 经常作为关联条件的字段，也可以考虑为这些字段创建索引

使用的基本原则

• 当任何单个查询（sql）要查询的行数小于或等于整张表行数的10%时，索引非常有效
• 经常分组排序的字段，适合建索引
• 经常用作过滤的字段时候建索引
• 经常表连接的字段适合建索引
• 数据分布，在不同值比较少的字段，不适合建立索引
• 小表不适合建立索引
• 经常dml操作不适合建立索引

如何创建索引

create [unique] index 索引名 on 表名(列名[,列名])
--unique 指定索引列上的值必须时唯一的，称为唯一索引
--列名 为列表中的某个列创建索引，当我们为多个列创建索引时，这种索引就是组合索引

--1、创建普通索引
create index in_dep on emp1(deptno);
--2、创建唯一索引
create unique index uni_emp on emp1(empno);
--3、创建组合索引
create index de_job on emp1(deptno,job);

--创建empindex表，数据和emp一样
create table empindex as select * from emp;
---给empindex表 添加主键约束
alter table empindex add primary key(empno);
--给empindex ename添加唯一索引
drop index un_name;--先删除已经存在的索引
create unique index un_name on empindex(ename);
--给empindex 的deptno和job添加组合索引
create index de_job on empindex(deptno,job);

索引失效情况

1. 隐式转换会导致索引失效，字段p_n为varchar2(50)

select * from test where p_n=134894789275; --索引失效，全表扫描
select * from test where p_n='12378472921';--索引生效，扫描索引 	

2. 对索引列进行运算会导致索引失效（+ - * / != > <）

select * from emp where sal-1000>2000;--索引失效，全表扫描
select * from emp where (sal) > (1000+2000);--索引生效，索引扫描

3. not in，not exists会导致索引失效

select * from emp deptno not in (10,20);--索引失效，全表扫描
select * from emp deptno in (10,20);--索引生效，索引扫描

4. 组合索引的时候，单独引用索引中非第一位置的索引，回导致索引失效。

--组合索引（job,deptno）
select * from emp_1010 where job='CLERK' and deptno=20;--索引生效，索引扫描
select * from emp1010 where deptno=10 and job='CLERK';--索引生效，索引扫描
select * from emp1010 where job='CLERK';--索引生效，索引扫描
select * from emp1010 where deptno=20;--索引失效，全表扫描

5. 使用oracle函数也会导致索引失效，此时应创建基于函数的索引

select * from emp1010 where round(empno)>10;--索引失效，全表扫描

正确示范
create index index_empno on emp1010(round(empno,2));--先创建基于函数的索引
select * from emp1010 where round(empno,2)>10;--再使用函数索引
函数索引的局限性
select * from emp1010 where round(empno)>10;--函数索引失效，全表扫描
select * from emp1010 where trunc(empno,2)>10;--函数索引失效，全表扫描

索引的优缺点

优点

1. 唯一的索引，保证数据的唯一性
2. 加快数据检索速度
3. 加快表与表的连接
4. 加快分组和排序的速度

缺点： 牺牲空间换时间，索引时保存在索引空间里