SqlServer——索引

索引是根据表中一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表。在数据库系统中建立索引主要有以下作用：

l快速存取数据；

l保证数据记录的唯一性；

l实现表与表之间的参照完整性；

l在使用ORDER BY、GROUP BY子句进行数据检索时，利用索引可以减少排序和分组的时间。

 

1．聚集索引

 聚集索引将数据行的键值在表内排序并存储对应的数据记录，使得数据表物理顺序与索引顺序一致。SQL Server 是按B树（BTREE）方式组织聚集索引的，B树方式构建为包含了多个节点的一棵树。顶部的节点构成了索引的开始点，叫做根。每个节点中含有索引列的几个值，一个节点中的每个值又都指向另一个节点或者指向表中的一行，一个节点中的值必须是有序排列的。指向一行的一个节点叫做叶子页。叶子页本身也是相互连接的，一个叶子页有一个指针指向下一组。这样，表中的每一行都会在索引中有一个对应值。查询的时候就可以根据索引值直接找到所在的行。

•聚集索引中B树的叶节点存放数据页信息。聚集索引在索引的叶级保存数据。这意味着不论聚集索引里有表的哪个（或哪些）字段，这些字段都会按顺序保存在表中。由于存在这种排序，所以每个表只会有一个聚集索引。

•由于数据记录按聚集索引键的次序存储，故聚集索引对查找记录很有效。 

 

2．非聚集索引

非聚集索引完全独立于数据行的结构。SQL Server 也是按B树方式组织非聚集索引的，与聚集索引的不同之处在于：非聚集索引B树的叶节点不存放数据页信息，而是存放非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行。

在非聚集索引内，从索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页的存储方式是堆集还是聚集。对于堆集，行定位器是指向行的指针。对于有聚集索引的表，行定位器是聚集索引键。只有在表上创建聚集索引时，表内的行才按特定顺序存储，这些行按聚集索引键顺序存储。如果一个表只有非聚集索引，则它的数据行将按无序的堆集方式存储。

在PXSCJ数据库中，经常要对XSB、KCB、CJB三个表进行查询和更新。为了提高查询和更新速度，可以考虑对三个表建立如下索引：

（1）对于XSB表，按学号建立唯一索引组织方式为聚集索引。

（2）对于KCB表，按课程号建立主键索引，组织方式为聚集索引。

（3）对于KCB表，按课程名建立唯一索引（UNIQUE约束），组织方式为非聚集索引。

（4）对于CJB表，按学号+课程号建立唯一索引，组织方式为聚集索引。

在SQL Server Management Studio中，既可利用界面方式创建上述索引，也可以利用T-SQL命令通过查询分析器建立索引。

 

1．界面方式创建索引

启动“SQL Server Management Studio”，在对象资源管理器中展开“数据库”，选择“表”中的“dbo.XSB”，右击其中的“索引”项，在弹出的快捷菜单上选择“新建索引(N)…”菜单项。

这时，用户可以在弹出的“新建索引”窗口中输入索引名称（索引名在表中必须唯一），如PX_XSB，选择索引类型为“聚集”，勾选“唯一”复选框，单击新建索引窗口的“添加”按钮，在弹出的“选择要添加到索引键的表列”窗口中选择要添加的列，添加完毕后，单击“确定”按钮，在主界面中为索引键列设置相关的属性，单击“确定”按钮，即完成索引的创建工作。

 

在“表设计器”窗口创建索引的方法如下。

第1步：右击PXSCJ数据库中的“dbo.XSB”表，在弹出的快捷菜单中选择“设计”菜单项，打开“表设计器”窗口。

第2步：在“表设计器”窗口中，选择“学号”属性列，右击鼠标，在弹出的快捷菜单中选择“索引/键”菜单项。在打开的“索引/键”窗口中单击“添加”按钮，并在右边的“标识”属性区域的“名称”一栏中确定新索引的名称（用系统默认的名或重新取名）。在右边的常规属性区域中的“列”一栏后面单击“ ”按钮，可以修改要创建索引的列。如果将“是唯一的”一栏设定为“是”则表示索引是唯一索引。在“表设计器”栏下的“创建为聚集的”选项中，可以设置是否创建为聚集索引，由于XSB表中已经存在聚集索引，所以这里的这个选项不可修改。

第3步：最后关闭该窗口，单击面板上的“保存”按钮，在弹出的对话框中单击“是”按钮，索引创建即完成。

索引创建完后，只需返回SSMS主窗口，在对象资源管理器中展开“dbo.XSB”表中的“索引”项，就可以查看已建立的索引。其他索引的创建方法与之类似。

 

2．利用SQL命令建立索引

使用CREATE INDEX语句可以为表创建索引。

语法格式：

CREATE [ UNIQUE ]   /*指定索引是否唯一*/

  [ CLUSTERED | NONCLUSTERED ]   /*索引的组织方式*/

  INDEX index_name   /*索引名称*/

    ON {[ database_name. [ schema_name ] . | schema_name. ] table_or_view_name}

   ( column [ ASC | DESC ] [ ,...n ] )   /*索引定义的依据*/

    eg:

/*为KCB表的“课程名”列创建索引*/
create index kc_name_ind
    on KC(Cname)
go

Result:picture2

eg:

/*根据KCB表的“课程号”列创建唯一聚集索引，因为指定了CLUSTERED，所以该索引将对磁盘上的数据进行物理排序。*/
create unique clustered index xs_id_ind
    on XS(Sname)

Result:

picture3

eg:

/*创建复合索引。*/
create index cj_ind
    on XS(Sage,Sno1)

Result:

p4

eg:

/*根据XSB表中的“总学分”列创建索引*/
CREATE NONCLUSTERED INDEX score_ind 
    ON XS(Sno1)

eg:

/*根据XSB表中“学号”列创建唯一聚集索引。如果输入了重复的键，将忽略该INSERT或UPDATE语句。*/
CREATE UNIQUE CLUSTERED INDEX xs_ind 
    ON XS(Sno)

创建索引有如下几点要说明：

（1）在计算列上创建索引。对于UNIQUE或PRIMARY KEY索引，只要满足索引条件，就可以包含计算列，但计算列必须具有确定性，必须精确。若计算列中带有函数，则使用该函数时有相同的参数输入，输出的结果也一定相同时，该计算列是确定的。而有些函数，如getdate()，每次调用时都输出不同的结果，这时就不能在计算列上定义索引。

计算列为text、ntext或image列时也不能在该列上创建索引。

（2）在视图上创建索引。可以在视图上定义索引。索引视图是一种在数据库中存储视图结果集的方法，可减少动态生成结果集的开销。索引视图还能自动反映出创建索引后对基表数据所做的修改。

 eg:

/*定义视图，在以下例子中使用了WITH  SCHEMABINDING子句，因此定义视图时，SELECT子句中表名必须为“架构名.表名”的形式*/
CREATE VIEW View_stu WITH SCHEMABINDING 
    AS 
    SELECT Sno,Sname 
        FROM   dbo.XS
GO

/*在视图上创建索引*/
CREATE UNIQUE CLUSTERED INDEX Inx1 
    ON View_stu(Sno)
GO

Result:

p5.1,p5.2

1．通过图形界面方式删除索引

通过图形界面方式删除索引的主要步骤如下：

启动SQL Server Management Studio，在对象资源管理器中展开数据库“PXSCJ→表→dbo.XSB→索引”，选择其中要删除的索引，单击鼠标右键，在弹出的快捷菜单上选择“删除”菜单项。在打开的“删除对象”窗口中单击“确定”按钮即可。

2．通过SQL命令删除索引

从当前数据库中删除一个或多个索引。

语法格式：

DROP INDEX

{    index_name ON  table_or_view_name [ ,...n ]

  | table_or_view_name.index_name [ ,...n ]

}

 　　

DROP INDEX语句可以一次删除一个或多个索引。这个语句不适合删除通过定义PRIMARY KEY或UNIQUE约束创建的索引。若要删除PRIMARY KEY或UNIQUE约束创建的索引，必须通过删除约束实现。

另外，在系统表的索引上不能进行DROP INDEX操作。

eg:

/*删除PXSCJ数据库中表KCB的一个索引名为kc_name_ind的索引。*/
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'kc_name_ind')
    DROP INDEX KC.kc_name_ind