索引的创建和销毁

1、索引的结构

索引分为聚集索引和非聚集索引。

（1）聚集索引对表中的数据进行排序，因此使用聚集索引查找数据很快。由于表的排序方式只能有一种，所以聚集索引只能有一个。

聚集索引结构如图：

 

（2）非聚集索引

非聚集索引和表的数据完全分离。使用非聚集索引不必将表的物理数据进行排序。由于非聚集索引使用索引页进行存储，所以比聚集索引需要更多的空间，且检索效率较低，但一个表可以有多个非聚集索引，理论上说一个表最多可以有149个非聚集索引。

非聚集索引的结果如图：

 

2、索引的创建

基本表studentinfo：

（1）为基本表studentinfo的sname字段创建非聚集索引：

 

create index name_index on studentinfo(sname) 

这时候查看索引字段：select sname from studentinfo

结果为：

再查看整个表的内容：

由此可见非聚集索引的索引部分，对索引字段进行了排序，而原表的内容顺序没变。当然也可以对查询的结果强制使用非聚集索引。

eg1:对查询结果强制使用非聚集索引

select *from studentinfo with (index(name_index)) 

结果：

这时候表的数据内容按照索引列升序排列。

注意：使用order也可以对表的内容进行排序，排序后查询结果同上（select *from studentinfo order by sname），但是使用索引和使用order by 有本质的区别，创建索引后数据库实际上创建了一个索引结构体，用户每次查询数据的时候，都是用的相同的索引结构， 从而节约了时间。但是使用order by 每次查询的时候都要对数据进行排序。

（2）为studentinfo表的sname字段创建聚集索引

create clustered index name_index on studentinfo(sname) select *from studentinfo 

结果：

由此可见建立聚集索引后，表中的数据行物理位置发生了变化。

使用聚集索引有很多优势：

• 占用最小的磁盘空间，因为DBMS在插入新行时会自动重用分配给删除行的空间
• 更快的执行速度。因为所有值在屋里上相互靠近
• 不需要order by子句，因为数据表已经以所要求的输出顺序排序。

当然使用聚集索引也有要注意的地方：在对表创建了一个聚集索引后，在插入新行或者是更新聚集索引一部分列值时，DBMS将自动重新排序，因此对于那些经常进行大量插入行或者更新索引列值的表，尽量不要使用聚集索引。

（3）创建多字段的（聚集/非聚集）索引

eg：

create index sexname_index on studentinfo (sex,sname) select *from studentinfo with (index(sexname_index)) 

 

（4）使用unique关键字创建唯一索引

create unique index sno_index on studentinfo(sno desc) select *from studentinfo with (index(sno_index))

使用唯一索引的列不能有重复值（如果有两个NULL值也被认为重复），否则创建索引失败；（例如：企图在studentinfo的地址字段创建唯一索引会失败。）当创建唯一索引时候如果插入的索引列和已有值相同 ，则插入失败。

 

3、索引的销毁

eg：

drop index studentinfo.name_index

注意：索引销毁时，一定要指明索引所在表的名称。

 

4、使用索引的几点原则

• 对小的数据表，使用索引并不能提高任何索引性能，因此不需对其创建索引
• 用户要检索的字段的数据包含有很多数值或者很多NULL时，为该字段创建索引会大大提高效率
• 用户查询的数据较少，一般少于总数据的25%时，使用索引会提高查询效率，反之查询数据较多时，返回大量数据，索引用处不大
• 索引的列应该在where子句中频繁使用，否则索引没有发挥作用
• 先装数据，后建索引。如果建表后就建索引，那么在输入数据时，每插入一次记录就要维护一次索引，效率较低
• 索引提高了数据检索的速度，但是也降低了数据更新的速度，如果要对表中的数据进行大量更新时，最好先销毁索引，等数据更新完毕后再创建索引，这样会提高效率。
• 最好把索引和表存放在不同的盘内，这样可以提高效率
• 索引要占用存储空间，在设计数据库时，索引的存储空间也要考虑进去。