七种索引简述，索引原则及索引失效

索引

起名规则：索引名_表名_列名

1) 索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引（数据库对象有6个（用户，表，视图，索引，序列，触发器）

2) 索引是建在表上的可选对象

索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率

3) 索引在逻辑上和物理上都有相关的表和数据无关

当创建或者删除一个索引时，不会影响基本的表

4) 索引一旦建立，在表上进行DML操作时(例如在执行插入，修改或者删除相关操作时)

oracle会自动管理索引（大部分情况都会降低效率）;

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

6) oracle 创建主键时会自动在读到上创建索引（B-TREE）索引

1）全表扫描：将表的每一条数据都挨个扫一遍，默认的扫描方式

2）索引扫描：在表中的列上建立索引，先对数据进行排序，将该数据以及对应的rowid存在一条索引条目（数据+rowid）

3）检索数据时，会先对索引进行检索，然后直接定位到数据所在的rowid

--冷知识 rowid对我们没有什么太大作用，但是检索速度很快

索引的分类

1.按照存储形式

1）B树索引 2）位图索引 3）反向键索引 4）基于函数索引

2.按照唯一性

1）唯一索引 非唯一索引

3.按照列个数

单列索引 复合索引（联合索引） --所列索引

一.B树索引（B_tree 索引）

--默认索引类型，最常见，最常见的索引类型

根块--索引中顶级块（包含了指向下一个节点的信息）

分支快--指向下一个节点的信息（指向分支块或者叶块）

叶块--索引的入口（能够适应精确查找，模糊查询，比较查询）

1.语法 create index 索引名 on 表名（需要添加索引的字段......);

create index b_class_1_cno on class_1(cno);

--创建索引

select * from class_1 where cno=1;

--sql语句先看有没有符合条件的索引，有就使用索引，没有就全表扫描

特点：1.适用于大部分情况 2.不适用于包含or的语句

注意点：1.经常出现在where子句中的字段，建议添加索引

2.经常用于表连接的字段，建议添加索引

3.主键自带索引，oracle会自动维护索引，但会降低DML语言的效率

4.表中数据量过少时，不建议创建索引

二.位图索引

--主要用在值的范围相对固定的列中，或只有少数几个值的列中

--例如性别列，数据量再大也只有男女两种值

就适合添加位图索引，适用于包含or的语句

语法：create bitmap index 索引名 on 表名（需要添加索引的字段......）;

三.反向键索引

--将数据反过来储存，相对于b树索引，提高了索引分配的随机性

--不常见，部分时候有奇效

create index 索引名 on 表名（需要添加索引的字段reverse;

--（reverse 颠倒）

四.基于函数的索引 --不包含分组函数

--有一些列会经常以函数的形式出现再筛选条件中

create index 索引名 on 表名 （function 需要添加索引的字段......）;

用于经常对某个字段做查询时带函数操作的

按照唯一性分类的索引

五.唯一索引 --索引列中不能出现重复值

create unique index 索引名 on 表名 （需要添加索引的字段...）;

注意点：b树索引可以是唯一索引，也可以不是，位图索引一定不是唯一索引（主键）

六.单列索引--只覆一个列的索引（只引用一个列会生效）

语法： create index 索引名 on 表名（需要添加索引的字段）

按照片索引覆盖列的各位分类

单列索引 --只覆盖一个列的索引

--只引用一个列时才会生效

七.复合索引 --覆盖多个列的索引

--引用第一个列时才会生效

语法：create index 索引名 on 表名 （需要添加索引的字段1，字段2,...）;

create unique index 索引名 on 表名 （需要添加索引的字段1，需要添加索引的字段2）;

删除索引 drop index 索引名

修改索引

1.索引重命名

alter index 旧索引名 rename to 新索引名;

2.合并索引 --将更新，删除等操作产生的空闲空间进行合并

alter index 索引名 coalesce deallocate unused;

3.重建索引 --相当于把原来的索引删除，并创建一个新的索引，可以顺便修改参数

alter index 索引名 rebuild;

4.禁止索引 alter index 索引名 unsable;

5.启用索引 alter index 索引名 usable;

索引建立原则总结：

1.如果有两个或者两个以上的序列，其中一个是唯一索引，其他索引是非唯一的，这时Oracle会自动使用唯一索引，忽略调其他索引

2.如果索引建立在多个列上，只有他的第一个列被引用时，优化器才会使用该索引

3.数据量较少的表不建议创建索引

4.对于值种类比较多的列，适合船舰B树索引；对于值比较少的列适合创建位图索引

5.列中有很多空值，不建议创建索引，因为索引容易失效哦啊，但如果该列的非空值经常被查询，还是应当创建索引

6.列中有很多空值，但是经常查询该列的非空值，应当未该列创建索引

7.经常用于where或表连接的列，应当建立索引

8.long和long row 列不能建立索引

（--long可变长，字符串数据，最长2G，long row 可变长 二进制数据，最长2G）

9.要限制表中索引的数量

--不太重要的列不用建立索引，一个表不要建立太多索引

1）创建索引耗费时间，并且索引随着数据量增大而增大

2）索引会占用物理空间

3）当对表中的数据进行增删改时，索引页会进行动态维护

索引失效

1.通配符出现在搜索词首位时，索引会失效 --通配符'_'与'%'

select * from emp where name like '%A'; --失效

select * from emp where name like '_%A'; --失效

select * from emp where name like 'A%'; --失效

2.在索引列上使用not或者不等于时，索引会失效 --不等于 != 或 <>

select * from emp where sal <> 3000; --失效

select * from emp where sal not in (3000); --失效

select * from emp where sal >=3000; --不失效

3.索引列判断空值时，索引会失效

select * from emp where comm is null; --失效

select * from emp where comm is not null; --失效

select * from emp where comm >0; --不失效

4.未引用复合索引的第一个列时，索引会失效，emp表复合索引，覆盖empno,ename

select * from emp where ename='SMITH'; --失效

select * from emp where empno=1234 and ename='SMITH'; --不失效

select * from emp where empno=1234; --不失效