创建索引及撤销

概述：
    用户对数据库最频繁的操作是数据查询。一般情况下，数据库进行查询操作时，需要对整个表进行搜索。当表中的数据很多时，搜索数据就需要很长的时间，为了提高检索数据的能力，数据库引入了索引机制。
定义：
    索引是一个单独的、热处理的数据库结构，是数据库的一个表中所包含的值的列表，其中注明了表的各个值所在的存储位置。索引是依赖于表建立的，提供了编排表中数据的方法。
 实际上一个表的存储是由两部分组成的，一部分用来存放表的数据页面，另一部分用来存放索引页面，索引就存放在索引页面上。通常，索引页面相对于数据页面来说小得多。当进行数据检索时，系统先搜索索引页面，从中找到所需数据的指针，再通过指针从数据布面中读取数据。
    打个比喻，把数据库看作一本书，把索引就可看作书的目录。
索引的分类：
按存储结构的不同分为两类：簇索引(ClusteredIndex)和非簇索引(Nonclustered Index)。
  1 簇索引
    簇索引对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上，即簇索引与数据是混为一体的，它的叶节点中存储的是实际的数据。
 由于簇索引对表中的数据一一进行了排序，因此用簇索引查找数据很快。但由于簇索引将表的所有数据完全重新排列了，所需要的空间也就特别大，大概相当于表中数据所占空间的120%。
 注意：表的数据行只能以一种排序方式存储在磁盘上，所以一个表只能有一个簇索引。
  2 非簇索引
    非簇索引具有与表的数据完全分离的结构，使用非簇索引不必将物理数据页中的数据按列排序。非簇索引的叶节点中存储了关键字值和行定位器。行定位器的结构和存储内容取决于数据的存储方式。如果数据是以簇索引方式存储的，则行定位器中存储的是簇索引的索引键;如果不是以簇索引方式存储的，则行定位器存储的是指向数据行的指针，这种方式又称堆存储方式(Heap Structure)。非簇索引将行定位器按关键字进行排序，这个顺序与表的行所在数据页中的排序是不匹配的。
 由于非簇索引使用索引页存储，因此此簇索引需要更多的存储空间，且检索效率较低。理论上，一个表最多可以建249个非簇索引。
索引的创建语法：

Create Relational Index 
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,n ] ) 
    [ INCLUDE ( column_name [ ,n ] ) ]
    [ WITH ( <relational_index_option> [ ,n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
        table_or_view_name
}

<relational_index_option> ::=
{
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

Create XML Index 
CREATE [ PRIMARY ] XML INDEX index_name 
    ON <object> ( xml_column_name )
    [ USING XML INDEX xml_index_name 
        [ FOR { VALUE | PATH | PROPERTY } ]
    [ WITH ( <xml_index_option> [ ,n ] ) ]
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
        table_name
}

<xml_index_option> ::=
{ 
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

Backward Compatible Relational Index
Important   The backward compatible relational index syntax structure will be removed in a future version of SQL Server. Avoid using this syntax structure in new development work, and plan to modify applications that currently use the feature. Use the syntax structure specified in <relational_index_option> instead.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column_name [ ASC | DESC ] [ ,n ] ) 
    [ WITH <backward_compatible_index_option> [ ,n ] ]
    [ ON { filegroup_name | "default" } ]

<object> ::=
{
    [ database_name. [ owner_name ] . | owner_name. ] 
        table_or_view_name
}

<backward_compatible_index_option> ::=
{ 
    PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE 
  | DROP_EXISTING 
}

常用关键字的含义：
    UNIQUE (DISTINCT):惟一性索引，不允许表中的不同的行在索引列画饼充饥相同的值。若巳有相同的值有存在，则系统给出相关信息，不建此索引。
    CLUSTERED/NONCLUSTERED:聚集和非聚集索引，若为CLUSTERED，则为聚集索引，即表中元组按索引项的值排序，并聚集在一起。一个基本表只能建一个聚集索引。NONCLUSTERED表示创建的索引为非聚集索引。缺少时，创建为非聚集索引。
    ASC/DESC: 索引表中的值的排序次序，缺少为ASC（正序排列)。

 

实例：
假定有如下表结构

create table studentstudentInfo 
(
 sno char(3) not null,
sname char(8) not null,
sex char(2) not null,
address char(8) not null,
dno int not null
)
插入三条记录
insert into studentInfo values('001','张平','女','湖南',2)
insert into studentInfo values('002','李山','男','北京',4)
insert into studentInfo values('002','陈明','男','北京',3)

(1) 创建简单的非簇索引
create index Name_Index on studentInfo(sname)
执行 select sname from studentinfo 语句
运行结果如下：
        
在非簇索引中，DBMS只对创建索引的列的键进行排序(默认升序)，而索引的表行不排序。
查询创建索引后studentinfo表中的所有数据
select * from studentinfo
运行结果如下：
        
由上图可见，创建非簇索引并不改变表中数据存放的物理位置。
当在表上创建一个或多个索引后，SQL Server的查询优化器会自动决定在查询执行期间使用哪个索引。如果强制使用某种索引，可以用如下语法：

select column1,column2,.
from table_name
with (index(index_name))
where condition

例如：
select * from studentinfo with (Name_Index))
运行结果如下：
        
(说明：对于查询表中的所有记录信息，使用索引是毫无意义的。)
使用order by关键字同样可以对查找结果进行排序。
select * from studentinfo order by sname
运行结果如下：
        
二者的区别：
order by关键词在每次查询数据时，都要对数据进行排序;
创建索引后，数据库系统实际上创建了一个索引结构休，用户每次使用查询数据时，都使用相同的索引结构，从而节约了时间。
当数据库表被删除时，和它相关的所有索引都将被删除。
(2) 创建多字段非簇索引
例如：创建sex和sname的索引
create index SexName on Studentinfo(Sex,sname)
select * from studentinfo with (SexName))
运行结果如下：
        
由上可见，创建多字段簇索引中，sex字段优先级高于sname字段。在创建多字段索引时，各字段的排列顺序决定了其优先级，排列在前，优先级越高。

(3) 使用unique关键字创建惟一索引
惟一索引是指不允许在两行中存在相同的索引值。当用户试图用insert和update语句，在拥有惟一索引的数据中生成一个重复的值，会提示错误。当在有重复的数据上创建惟一索引时，也提示错误。如下：
由于sno里有相同的002，我们先把dno为3的记录的sno修改为003。
update studentinfo set sno =003 where dno=003
然后创建sno惟一索引
create unique index sno_Index on studentinfo(sno)
后，执行
insert into studentInfo values('001','刘梅','女','湖南',2)
会提示如下错误：
Msg 2601, Level 14, State 3, Line 1
不能在具有唯一索引 'Sno_Index' 的对象 'StudentInfo' 中插入重复键的行。
语句已终止。

(0 row(s) affected)

创建如下索引：
create unique index address_Index on studentinfo(address)
会提示如下错误：
Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX 终止，因为发现了索引 ID 7 的重复键。最重要的主键为 '北京    男'。
语句已终止。

(4) 使用clustered关键字创建簇索引
创建簇索引会改变数据存放的物理位置。在带有簇索引的表中，行是以索引顺序存放的。即簇索引不仅对索引中的键字值进行排序，而且对表中的行排序，以便使其与索引的排序匹配。
使用簇索引的优点：
1、使用簇索引的表将占用最小的磁盘空间。因为DBMS在插入新行时，会自动地重用以前分配给删除行的空间。
2、对基于簇索引的列进行查询时，会有更快的执行速度，因为所有值在物理磁盘上相互靠近。
3、基于簇索引的列以升序显示数据查询，不再需要order by子句，因为表的数据本身已经以所要求的输出顺序排列。
例如：
create clustered index Snamecluster_index on studentinfo(sname)
执行 select * from studentinfo 语句
执行结果如下：
        

可见，创建簇索引后，表中数据(行)存储的物理位置发生了变化。

(5) 创建多个字段的簇索引。

create clustered index SexAddressCluster_index on studentinfo(sex,address)
如果没有执行删除簇索引，则用出现如下错误：
Msg 1902, Level 16, State 3, Line 1
Cannot create more than one clustered index on table 'studentinfo'. Drop the existing clustered index 'Snamecluster_index' before creating another.
出现此错误的原因是因为一个表只能建立一个簇索引。
因此我们需要删除以前创建的簇索引。
drop index studentinfo.Snamecluster_index 然后再执行
create clustered index SexAddressCluster_index on studentinfo(sex,address)
执行 select * from studentinfo 语句
执行结果如下：
        
由此可见，在创建多字段簇索引时，优先级别同样取决于字段的排列顺序。

(6) 索引的销毁：
语法如下：

drop index table_name.index_name

例如：
drop index studentinfo.Snamecluster_index
(7)使用索引的几点原则:
1、对小的数据表，使用索引并不能提高任何检索性能，因此不需对其创建索引。
2、当用户要检索的字段的数据包含有很多数值或很多空值(null)时，为该字段创建索引，会大大提高检索效率。
3、当用户查询表中的数据时，如果查询结果包含的数据(行)较少，一般少于数据总数的25%时，使用索引会显著提高查询效率。反之，如果用户的查询操作，返回结果总是包含大量数据，那么索引的用处不大。
4、索引列在WHERE子句中应频繁使用。例如，在学生姓名字段上建了索引，但实际查询中并不是经常用姓名作为查询条件，该索引就没有发生作用。
5、先装数据，后建索引。对于大多数的表，总有一批初始数据需要装入。该原则是说，建立表后，先将这些初始数据装入表，然后再建索引，这样可以加快初始数据的录入。如果建表后就建索引，那么在输入初始数据时，每插入一个记录都要维护一次索引。当然，对于索引来说，早建和晚建都是允许的。
6、索引提交了数据检索的速度，但也降低了数据更新的速度。如果要对表中的数据进行大量更新时，最好先销毁索引，等数据更新完毕再创建索引，这样会提高效率。
7、索引要占用数据库空间。在设计数据库时，要把需要的索引空间考虑在内。
8、尽量把表和它的索引存放在不同的磁盘上，这样会提高查询速度。
怎么会