索引-定义、创建(CREATE INDEX)、删除（DROP INDEX）

一、概述

1、索引是SQL语言定义的一种数据对象，是大多数DBMS为数据库中基本表创建的一种辅助存取结构，用于响应特定查询条件进行查询时的查询速度，DBMS根据查询条件从数据库文件中，选择出一条或者多条数据记录以供检索，当多个数据记录均满足某一查询条件时，按数据记录的物理顺序，第一条记录最先被找到，并将其指定为当前记录，以后的查找操作就从这条记录开始，再定位满足条件的下一条记录，当查询只涉及数据库文件中少量数据记录时，如果磁盘上所有数据记录逐一读取检查，效率低下，最好系统能够直接定位那些满足查询条件的数据记录，这就是创建索引的目的

二、索引的概念

索引是建立在关系的某一属性（组）上的一种数据结构，针对某个属性建立索引，就是根据此属性值将记录进行逻辑排序

三、索引的作用

索引提供了在改属性（组）上快速查找具有某个特定值的元组的方法

1、索引文件所占磁盘空间通常比数据库文件所在磁盘空间要少

2、索引记录是有序的，可以使用二分查找法来查找索引键值所在记录

四、索引的分类

1、聚集索引（主索引）：数据文件中数据记录的物理顺序与索引记录的排列顺序在索引键上保持一致，只能有一个

2、非聚集索引（次索引/辅助索引）：数据库文件中数据记录不按索引键值物理排序,可以有多个

五、索引的创建

方式一：创建基本表的同时创建索引

CREATE TABLE <表名>

(<属性列名1> <数据类型> 【列级完整性约束条件】

【，<属性列名2><数据类型> 【列级完整性约束条件】，...】

【，<表级完整性约束条件>】

)

PRIMARY KEY 【CLUSTERED|NONCLUSTERED】

PRIMARY KEY 【CLUSTERED|NONCLUSTERED】（<列名组>）

1、一般DBMS默认基于表定义的主键创建聚集索引，使得所创建的表的数据文件是一个顺序文件

2、举例1：创建一个与表s逻辑结构相同的表的表s2的同时创建聚集索引

create table s2(
   sNo char(12) primary key clustered,
   sN varchar(6) not null,
   sex char(2) default'男',
   sB Date,
   sD varchar(16),
   check(sex in('男','女'))
);

3、举例2：创建表s3的同时创建非聚集索引

create table s3(
   sNo char(12) ,
   sN varchar(3) not null,
   sex char(1) default'男',
   sB Date,
   sD varchar(8),
   check(sex in('男','女')),
   primary key nonclustered (sNo)
);

方式二：单独创建索引

CREATE 【UNIQUE】【CLUSTERED】 INDEX <索引名>

ON <表名> (<属性列名1>【<次序>】【，<属性列名2>【次序】，...】)

【其他参数】

1、注：

（1）UNIQUE:索引键值唯一，即创建的索引中不包含索引键值相同的索引项

（2）CLUSTERED：聚集索引，在默认情况下，CREATE INDEX语句创建的是非聚集索引

（3）<表名>：创建索引的表

（4）<属性列名1>：索引键 

（5）<次序>：每个属性列名后面可以用次序来指定索引键值的排列顺序，可选ASC（升序）或DESC（降序），默认是ASC

（6）其他参数：是与索引文件物理存储相关的因素，可以不指定，由DBMS决定

2、举例1：在基本表s3上创建基于出生日期的聚集索引

create clustered index sY5
on s3(sB desc);

将表s中的元组全部复制到表s3中，则会发现，表s3中的元组都是按照索引键值降序排列的

3、举例2：在基本表s2上创建基于姓名的唯一键值索引

create unique index sY1
on s2(sN)

4、举例3：在基本表s2上创建基于出生日期的非聚集索引

create index sY2
on s2(sB)

创建索引后，相应的索引描述存储到数据字典中，当不在需要索引时应及时删除，避免系统为维护索引降低系统操作性能

六、索引的删除

1、删除索引的语句格式

DROP INDEX <索引名> ON <表名>

或    DROP INDEX <表名> .<索引名>

2、举例1：删除基本表s2中的索引sY4

drop index sY2 on s2

七、索引的创建原则

1、表中数据较少时一般不需要创建索引

2、数据库主要用于查询时，可根据需要多创建一些索引

3、数据库处于频繁更新时，不宜创建过多的索引

4、最好在创建任何非聚集索引之前创建聚集索引

5、索引键一般是经常出现在查询条件中的属性，一般基于主键和外键创建索引

6、在频繁更新的属性上尽量不创建聚集索引，一般在主键上创建聚集索引

7、可基于经常用于统计查询的属性创建索引

八、总结

1、索引是DBMS中常用的快速存取磁盘上数据库中数据的存取方法

2、索引由数据库管理员或者数据库拥有者管理，由DBMS动态维护

3、DBMS利用索引对查询进行优化处理，实现高效查询