Sqlserver 索引介绍及操作方法

索引的功能#

当表中有大量记录时，若要基于某些过滤条件对表进行查询，就需要遍历整张表和查询条件进行对比，返回满足条件的记录。使用暴力搜索的时间复杂度是 O(n)，同时频繁访问磁盘，当数据量达到一定的规模时(一般是 10 万级别以上)效率很低。

索引是对数据表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息，提高检索速度。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。如果 SELECT 的条件所在的列具有索引，则可以直接在索引中找到符合查询条件的索引值，进而快速找到表中对应的记录。索引是某个表中一列或若干列值的集合，和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引提供指向存储在表的指定列中的数据值的指针，然后根据指定的排序顺序对这些指针排序。

索引的优点和缺点#

创建索引有以下 4 个优点：

1. 大大加快数据的检索速度；
2. 保证表中每一行数据的唯一性；
3. 加速表和表之间的连接；
4. 在使用分组和排序子句进行数据检索时，显著减少时间开销。

创建索引有以下 2 个缺点：

1. 创建索引需要占物理空间；
2. 表中的数据产生变动的时候，索引也要动态地维护。

索引的分类#

按照存储结构，索引分为普通索引和非聚簇索引。聚集索引是按照数据存放的物理位置为顺序的索引，在聚集索引上的查询速度最快。每一个数据表只能有一个聚集索引，这是因为物理数据只能有一种排序规则。非聚集索引建立在专门的索引列和索引文件，按照设定的顺序进行排列后的结果数据保存到数据表中，对于单行记录的查询效率很高。
如何理解这 2 种索引？例如查字典有 3 种方法，分别是拼音查字法、部首查字法和笔画查字法。聚集索引可以类比为拼音查字法，因为字典中的所有的字都按照拼音来排列的，是唯一确定的排列方式。非聚集索引可以类比为部首查字法或笔画查字法，这 2 种查字法分别对应了部首和笔画，当我们使用笔画或者部首进行检索时使用这 2 种方法查询效率高。

SQL 语法#

创建索引#

创建索引时，首先需要确定表的索引列。如果表没有主键约束，则可以创建聚集索引，但如果有主键约束就只能创建非聚集索引。建立索引的命令格式是：

Copy Highlighter-hljs

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX 索引名
ON 表名 | 视图名 (列 [ASC|DESC][,…n])

关键字	功能
UNIQUE	表示创建的索引具有唯一性
CLUSTERED	指明创建的索引为聚集索引
NONCLUSTERED	指明创建的索引为非聚集索引
ASC、DESC	指定特定的索引列的排序方式为升序(默认)或降序

删除索引#

删除索引同样是用 DROP 命令实现，格式是：

Copy Highlighter-hljs

DROP INDEX 表名.索引名[,…]

样例#

假设 Student 表中有如下一些数据：

创建简单索引#

为 Student 表的 Sname 字段进行创建。

Copy Highlighter-hljs

CREATE INDEX sname_ind ON student (Sname)

创建唯一索引#

为 Student 表的 Sname 字段进行创建。

Copy Highlighter-hljs

CREATE UNIQUE INDEX sname_UNI_ind ON student (Sname)

此时对表使用如下 UPDATE 语句时会插入失败，因为创建索引时再 Sname 字段创建了唯一索引，也就是说 Sname 应该是要唯一的.然而插入的数据中重复插入了 Sname 数据“曾华庆”，产生了重复所以不行。

Copy Highlighter-hljs

INSERT Student VALUES('110', '曾华庆', '男', '1999-09-01', '95033') 

创建唯一聚集索引#

为 Student 表的 Sname 字段进行创建，注意如果表中已经存在了聚集索引，则不能创建多个聚集索引。

Copy Highlighter-hljs

CREATE UNIQUE CLUSTERED INDEX sno_cl_ind ON student (sno)

创建组合索引#

同时指定 class 和 sbirthday 作为索引列。

Copy Highlighter-hljs

CREATE INDEX classs_bith_ind ON student (class, sbirthday)

创建视图索引#

创建索引视图的时候需要指定表所属的架构，需要在创建视图语句中加上 WITH SCHEMABINDING。对视图创建索引时，表的表达式必须使用两段式 dbo.mytable 否则会报错。

Copy Highlighter-hljs

CREATE VIEW V WITH SCHEMABINDING 
AS
SELECT s.Sno, Sname, Class, Sbirthday FROM dbo.student s 

删除索引#

删除上述建的全部索引。

Copy Highlighter-hljs

DROP INDEX Student.sname_ind
DROP INDEX Student.sname_UNI_ind
DROP INDEX Student.classs_bith_ind