oracle数据库索引

[什么是索引]

在关系型数据库中，索引是一种和表有关的数据结构，它可以使查询的sql语句执行的更快。其实索引就如同图书的目录，我们可以根据目录中的页码快速的找到所需的内容。

编写一本书，只有章节内容定好之后再设置目录；数据库索引也是一样，只有先插入好数据，再建立索引。

[为何索引可提高查询效率]

因为DB在执行一条sql语句的时候，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件就加入搜索结果集合。如果我们对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。

[索引的类型]

[索引的类型]

1.全表扫描

全表扫描(FULL TABLE SCANS) 时所有行、所有数据快均会被读到，是效率最低的一种，一般会在表缺少索引、读取大量数据、访问小表或高并发时发生

2. ROWID扫描

ROWID扫描是通过rowid中数据文件和块位置访问数据行。一般作为访问索引后的第二步，如果访问的列全部包含在索引中，则不会执行rowid扫描

3. 索引扫描

索引扫描包括全索引扫描(full index scan   FIS)、快速全索引扫描(fast full index scan   FFIS)、索引范围扫描(index range scan)、索引唯一扫描(index unique scan)、索引跳跃式扫描(index skip scan)、位图索引扫描(bitmap index scan)

[如何创建索引]

创建表:

create table person (id int, sex char(1), name char(10));

创建索引:

create index person_index on person (id);

查看创建的表与索引:

select object_name, object_type from user_objects;

除了可以根据单个字段创建索引，也可以根据多列创建索引:

create index person_index2 on person (sex, name);

删除索引：

drop index person_index;

[创建索引的注意事项]

1. 建立索引的目的是为了提高查询效率，但建立的索引过多会影响插入和删除数据的速度，这是因为我们修改表数据时，索引也要跟着修改，所以创建索引的时候我们需要权衡是查询多还是修改多。

2. 应该创建索引的字段：

① 经常作为查询条件的字段

② 经常用在多表连接的列，例如外键

③ 经常需要排序的字段

3. 应该少建或者不建索引的情况：

① 表中数据太少，增加索引基本不会带来查询速度的提升，反而浪费了存储空间。

② 经常需要插入、修改、删除操作的表

③ 表中数据重复且分布平均的字段(如“性别”)

④ 查询中很少用到不应该创建索引

⑤ 定义为text、image、bit数据类型的列不应该加索引

4. 一些sql的写法会限制索引的使用：

where子句中如果使用in、or、like、!= 、<>均会导致索引不能正常使用，将<>换成>and <,将is not null换成>=chr(0)

[where语句中索引的使用]

1. 索引列上不要使用is null 或 is not null

错误：select column from table where column is not null

正确：select column from table where column >= chr(0)

2. 索引列上不要使用函数：

错误：select column from table where substr(name, 1, 3) = 'ABC'

正确：select column from table where name like 'ABC%' // 避免通配符在词首出现

3. 索引列上不要使用NOT(!=、<>)

错误：select column from table where column != 10

正确：select column from table where column > 10 or column < 10

4. 索引列上不要进行计算

错误：select column from table where column / 10 > 10

正确：select column from table where column > 10 * 10