Oracle 中的索引

一、前言

在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。 对于数据库来说，索引是一个必选项，但对于现在的各种大型数据库来说，索引可以大大提高数据库的性能，以至于它变成了数据库不可缺少的一部分。

1.1、索引的特点

1）索引是表的一部分，是可选的，表可以没有索引，就像书可以没有目录一样，数据库不做强制要求。

2）合理的索引可以提高数据查找的效率，减少磁盘I/O。

3）唯一索引可以约束表数据的完整性。

4）索引也是数据，需要存储空间。

5）数据库自动维护索引的内容，当对表的记录进行了增、删、改的操作时，数据库会自动更新索引。

6）索引虽然可以提高数据查找的效率，但对表进行增、删、改操作时，数据库除了维护表中的数据，还要维护索引，所以，索引会让增、删、改操作的效率下降。

7）索引提升了数据查找的效率，降低了数据操作的效率，在设计表和索引的时候，要根据实际情况在两者之间权衡。

1.2、索引的分类

索引可分为 普通索引、唯一索引、反向键索引、位图索引和基于函数的索引，常用的是普通索引和唯一索引，其它三种极少使用，我也不建议使用。

二、创建索引

先来创建超女基本信息表T_GIRL。

create table T_GIRL
(
  id        char(4)         not null,   -- 编号
  name      varchar2(30)    not null,   -- 姓名
  yz        varchar2(20)        null,   -- 颜值
  sc        varchar2(20)        null,   -- 身材
  weight    number(4,1)     not null,   -- 体重
  height    number(3)       not null,   -- 身高
  birthday  date            not null,   -- 出生时间
  memo      varchar2(1000)      null,   -- 备注
  keyid     number(8)       not null   -- 记录编号,从序列SEQ_GIRL中获取。
);

alter table T_GIRL add constraint PK_GIRL primary key(id);

2.1、普通索引

创建普通索引的语法如下：

create index 索引名 on 表名(字段名1,字段名2,......,字段名n);

索引名是标识符，从语法上来说，索引名只要符合Oracle的规定就行了，但是，在实际开发中，我会以IDX_打头，加表名和索引编号。

例如超女基本信息表T_GIRL的索引，命名将会是IDX_GIRL_1、IDX_GIRL_2、IDX_GIRL_3、IDX_GIRL_n。

例如为超女信息表T_GIRL的姓名字段创建索引：

create index IDX_GIRL_1 on T_GIRL(name);

例如为超女信息表T_GIRL的颜值和身材两个字段创建索引：

create index IDX_GIRL_2 on T_GIRL(yz,sc);

2.2、唯一索引

如果表中的列没有重复的值，是唯一的，就可以创建唯一索引，唯一索引的效率比普通索引要高很多。

创建普通索引的语法如下：

create unique index 索引名 on 表名(字段名1,字段名2,......,字段名n);

例如超女基本信息表T_GIRL的keyid字段，其值是从序列SEQ_GIRL中获取的，肯定是个唯一的值，可以创建唯一索引。

create unique index IDX_GIRL_3 on T_GIRL(keyid);

三、修改索引

3.1、重建索引

重建索引可以减少硬盘碎片和提高数据库系统的性能。

语法：

alter index 索引名 rebuild;

示例：

alter index IDX_GIRL_1 rebuild;

3.2、整理碎片

对索引的无用空间进行合并，减少硬盘碎片和提高数据库系统的性能。

语法：

alter index 索引名 coalesce;

示例：

alter index IDX_GIRL_1 coalesce;

3.3、修改索引名

语法：

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

示例：

alter index IDX_GIRL_1 rename to IDX_GIRL_NAME;

3.4、禁用索引

语法：

alter index 索引名 unusable;

示例：

alter index IDX_GIRL_1 unusable;

索引被禁用后，如果要启用，需要用rebuild重建。

四、索引的优化

4.1、查看索引

在PL/SQL Developer软件中可以查看表的索引。

4.2、SQL语句的执行计划

利用PL/SQL Developer软件可以查看SQL语句详细的执行计划（快捷键F5）。

4.3、SQL语句的执行步骤

SQL语句的执行顺序是从未级节点往根级节点看的，上图中的SQL语句执行的顺序如下：

INDEX RANGE SCAN->TABLE ACCESS BY INDEX ROWID->SELECT STATEMENT,GOAL=ALL_ROWS

4.4、执行计划的含义

在执行计划中，我们重点关注访问表（TABLE ACCESS BY …… ）的方式，常见的有三种：

1、TABLE ACCESS BY USER ROWID（通过ROWID的表存取）

rowid的值是行的物理位置，通过rowid可以快速定位到目标数据上，这也是Oracle中存取单行数据最快的方法。

2、TABLE ACCESS FULL（全表扫描）

Oracle读取表中全部的行，并检查每一行是否满足SQL语句中where的条件。

数据量太大的表不能使用全表扫描，除非本身需要取出的数据较多，占到表数据总量的5%~10% 或以上。

3、TABLE ACCESS BY INDEX SCAN（索引扫描）

在索引中，存储了每个索引的键值和行的rowid，所以索引扫描其实是先扫描索引得到对应的rowid，然后再通过rowid定位到具体的行。

索引扫描又分五种：

a）INDEX UNIQUE SCAN，索引唯一扫描，效率最高。

b）INDEX RANGE SCAN，索引范围扫描，效率较高。

c）INDEX FULL SCAN，索引全扫描，效率较低。

d）INDEX FAST FULL SCAN，索引快速扫描，效率一般。

e）INDEX SKIP SCAN，索引跳跃扫描，效率还行。

在Object name中显示了SQL语句使用的索引名，是索引优化的重要依据。