SQLserver 索引

6.1.1 索引基本概念在数据库中建立索引是为了加快数据的查询速度。数据库中的索引与书籍中的目录或书后的术语表类似。在一本书中，利用目录或术语表可以快速查找所需信息，而无须翻阅整本书。在数据库中，索引使对数据的查找不需要对整个表进行扫描，就可以在其中找到所需数据。书籍的索引表是一个词语列表，其中注明了包含各个词的页码。而数据库中的索引是一个表中所包含的列值的列表，其中注明了表中包含各个值的行数

网络架构师0001

3381人浏览 · 2022-03-31 12:55:07

网络架构师0001 · 2022-03-31 12:55:07 发布

索引基本概念

在数据库中建立索引是为了加快数据的查询速度。数据库中的索引与书籍中的目录或书后的术语表类似。在一本书中，利用目录或术语表可以快速查找所需信息，而无须翻阅整本书。在数据库中，索引使对数据的查找不需要对整个表进行扫描，就可以在其中找到所需数据。书籍的索引表是一个词语列表，其中注明了包含各个词的页码。而数据库中的索引是一个表中所包含的列值的列表，其中注明了表中包含各个值的行数据所在的存储位置。可以为表中的单个列建立索引，也可以为一组列建立索引。索引一般采用B树结构。索引由索引项组成，索引项由来自表中每一行的一个或多个列（称为搜索关键字或索引关键字）组成。B树按搜索关键字排序，可以对组成搜索关键字的任何子词条集合上进行高效搜索。例如，对于一个由A、B、C三个列组成的索引，可以在A以及A、B和A、B、C上对其进行高效搜索。
例如，假设在Student表的Sno列上建立了一个索引（Sno为索引项或索引关键字），则在索引部分就有指向每个学号所对应的学生的存储位置的信息，如图6-1所示。
在这里插入图片描述

图6-1 索引及数据间的对应关系示意图

当数据库管理系统执行一个在Student表上根据指定的Sno查找该学生信息的语句时，它能够识别该表上的索引列（Sno），并首先在索引部分（按学号有序存储）查找该学号，然后根据找到的学号所指向的数据的存储位置，直接检索出需要的信息。如果没有索引，则数据库管理系统需要从Student表的第一行开始，逐行检索指定的Sno值。从数据结构的算法知识我们知道有序数据的查找比无序数据的查找效率要高很多。
但索引为查找所带来的性能好处是有代价的，首先索引在数据库中会占用一定的存储空间来存储索引信息。其次，在对数据进行插入、更改和删除操作时，为了使索引与数据保持一致，还需要对索引进行相应维护。对索引的维护是需要花费时间的。
因此，利用索引提高查询效率是以占用空间和增加数据更改的时间为代价的。在设计和创建索引时，应确保对性能的提高程度大于在存储空间和处理资源方面的代价。
在数据库管理系统中，数据一般是按数据页存储的，数据页是一块固定大小的连续存储空间。不同的数据库管理系统数据页的大小不同，有的数据库管理系统数据页的大小是固定的，比如SQL Server的数据页就固定为8KB；有些数据库管理系统的数据页大小可由用户设定，比如DB2。在数据库管理系统中，索引项也按数据页存储，而且其数据页的大小与存放数据的数据页的大小相同。
存放数据的数据页与存放索引项的数据页采用的都是通过指针链接在一起的方式连接各数据页，而且在页头包含指向下一页及前面页的指针，这样就可以将表中的全部数据或者索引链在一起。数据页的组织方式示意图如图6-2所示。
在这里插入图片描述

图6-2 数据页的组织方式示意图

6.1.2 索引的存储结构及分类
索引分为两大类，一类是聚集索引（Clustered Index，也称为聚簇索引），另一类是非聚集索引（Non-Clustered Index，也称为非聚簇索引）。聚集索引对数据按索引关键字值进行物理排序，非聚集索引不对数据按索引关键字值进行物理排序，而只将索引关键字按值进行排序。图6-1所示的索引示意图即为非聚集索引。在SQL Server中聚集索引和非聚集索引都采用B树结构来存储索引项，而且都包含数据页和索引页，其中索引页用来存放索引项和指向下一层的指针，数据页用来存放数据。不同的数据库管理系统中索引的存储结构不尽相同，本章我们主要介绍SQL Server对索引采用的存储结构。
在介绍这两类索引之前，首先简单介绍一下B树结构。
1．B树结构
B树（Balanced Tree，平衡树）的最上层节点称为根节点（Root Node），最下层节点称为叶节点（Left Node）。在根节点所在层和叶节点所在层之间的层上的节点称为中间节点（Intermediate Node）。B树结构从根节点开始，以左右平衡的方式存放数据，中间可根据需要分成许多层，如图6-3所示。
在这里插入图片描述

2．聚集索引
聚集索引的B树是自下而上建立的，最下层的叶级节点存放的是数据，因此它即是索引页，同时也是数据页。多个数据页生成一个中间层节点的索引页，然后再由数个中间层节点的索引页合成更上层的索引页，如此上推，直到生成顶层的根节点的索引页。其示意图如图6-4所示。生成高一层节点的方法是：从叶级节点开始，高一层节点中每一行由索引关键字值和该值所在的数据页编号组成，其索引关键字值选取的是其下层节点中的最大或最小索引关键字的值。
在这里插入图片描述
除叶级节点之外的其他层节点，每一个索引行由索引项的值以及这个索引项在下层节点的数据页编号组成。
例如，设有职工（Employee）表，其包含的列有：职工号（Eno）、职工名（Ename）和所在部门（Dept），数据示例如表6-1所示。假设在Eno列上建有一个聚集索引（按升序排序），则其B树结构示意图如图6-5所示（注：每个节点左上方位置的数字代表数据页编号），其中的虚线代表数据页间的链接。

表6-1 Employee表的数据

Eno	Ename	Dept
E01	AB	CS
E02	AA	CS
E03	BB	IS
E04	BC	CS
E05	CB	IS
E06	AS	IS
E07	BB	IS
E08	AD	CS
E09	BD	IS
E10	BA	IS
E11	CC	CS
E12	CA	CS

在这里插入图片描述

图6-5 在Eno列上建有聚集索引的B-树在聚集索引的叶节点中，数据按聚集索引关键字的值进行物理排序。因此，聚集索引很类似于电话号码簿，在电话号码薄中数据是按姓氏排序的，这里姓氏就是聚集索引关键字。由于聚集索引关键字决定了数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以由多个列（组合索引）组成，就像电话号码簿按姓氏和名字进行组织一样。当在建有聚集索引的列上查找数据时，系统首先从聚集索引树的入口（根节点）开始逐层向下查找，直到达到B树索引的叶级，也就是达到了要找的数据所在的数据页，最后只在这个数据页中查找所需数据即可。例如，若执行语句：SELECT * FROM Employee WHERE Eno = 'E08' 则首先从根（310数据页）开始查找，用“E08”逐项与310页上的每个索引关键字的值进行比较。由于“E08”大于此页的最后一个索引项“E07”的值，因此，选“E07”所在的数据页203，再进入到203数据页中继续与该页上的各索引关键字进行比较。由于“E08”大于203数据页上的“E07”而小于“E10”，因此，选“E07”所在的数据页110，再进入到110数据页中进行逐项比较，这时可找到Eno等于“E08”的项，而且这个项包含了此职工的全部数据信息。至此查找完毕。当插入或删除数据时，除了会影响数据的排列顺序外，还会引起索引页中索引项的增加或减少，系统会对索引页进行分裂或合并，以保证B树的平衡性，因此B树的中间节点数量以及B树的高度都有可能会发生变化，但这些调整都是数据库管理系统自动完成的，因此，在对有索引的表进行插入、删除和更改操作时，有可能会降低这些操作的执行性能。聚集索引对于那些经常要搜索列在连续范围内的值的查询特别有效。使用聚集索引找到包含第一个列值的行后，由于后续要查找的数据值在物理上相邻而且有序，因此只要将数据值直接与查找的终止值进行比较即可。在创建聚集索引之前，应先了解数据是如何被访问的，因为数据的访问方式直接影响了对索引的使用。如果索引建立的不合适，则非但不能达到提高数据查询效率的目的，而且还会影响数据的插入、删除和更改操作的效率。因此，索引并不是建立的越多越好（建立索引需要占用空间，维护索引需要耗费时间），而是要有一些考虑因素。下列情况可考虑创建聚集索引： 包含大量非重复值的列。 使用下列运算符返回一个范围值的查询：BETWEEN AND、>、>=、< 和 <=。 经常被用作连接的列，一般来说，这些列是外键列。 对ORDER BY或GROUP BY子句中指定的列建立索引，可以使数据库管理系统在查询时不必对数据再进行排序，从而可以提高查询性能。对于频繁进行更改操作的列则不适合建立聚集索引。 2．非聚集索引非聚集索引与图书后边的术语表类似。书的内容（数据）存储在一个地方，术语表（索引）存储在另一个地方。而且书的内容（数据）并不按术语表（索引）的顺序存放，但术语表中的每个词在书中都有确切的位置。非聚集索引就类似于术语表，而数据就类似于一本书的内容。非聚集索引的存储示意图如图6-6所示。 ![在这里插入图片描述](https://img-blog.csdnimg.cn/c474f92402bd4c92b9022647699096b1.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA572R57uc5p625p6E5biIMDAwMQ==,size_10,color_FFFFFF,t_70,g_se,x_16) 非聚集索引与聚集索引一样用B树结构，但有两个重要差别： 数据不按非聚集索引关键字值的顺序排序和存储。 非聚集索引的叶级节点不是存放数据的数据页。非聚集索引B树的叶级节点是索引行。每个索引行包含非聚集索引关键字值以及一个或多个行定位器，这些行定位器指向该关键字值对应的数据行（如果索引不唯一，则可能是多行）。例如，假设在Employee表的Eno列上建有一个非聚集索引，则数据和其索引B树的形式如图6-7所示。从这个图可以观察到，数据页上的数据并不是按索引关键字Eno有序排序的，但根据Eno建立的索引B树是按Eno的值有序排序的，而且上一层节点中的每个索引关键字值取的是下一层节点上的最小索引键值。在建有非聚集索引的表上查找数据的过程与聚集索引类似，也是从根节点开始逐层向下查找，直到找到叶级节点，在叶级节点中找到匹配的索引关键字值之后，其所对应的行定位器所指位置即是查找数据的存储位置。由于非聚集索引并不改变数据的物理存储顺序，因此，可以在一个表上建立多个非聚集索引。就象一本书可以有多个术语表一样，如一本介绍园艺的书可能会包含一个植物通俗名称的术语表和一个植物学名称的术语表，因为这是读者查找信息的两种最常用的方法。

图6-7 在Eno列上建有非聚集索引的情
在创建非聚集索引之前，应先了解数据是如何被访问的，以使建立的索引科学合理。对于下述情况可考虑创建非聚集索引：
包含大量非重复值的列。如果某列只有很少的非重复值，比如只有1和0，则不对这些列建立非聚集索引。
经常作为查询条件使用的列。
经常作为连接和分组条件的列。

4．唯一索引
唯一索引用于确保索引列不包含重复的值，唯一索引可以只包含一个列（限制该列取值不重复），也可以由多个列共同构成（限制这些列的组合取值不重复）。例如，如果在LastName、FirstName和MiddleInitial三个列上创建了一个唯一索引FullName，则该表中任何两个人都不可以具有完全相同的名字（LastName、FirstName和MiddleInitial名字均相同）。
聚集索引和非聚集索引都可以是唯一索引。因此，只要列中的数据是唯一的，就可以在同一个表上创建一个唯一的聚集索引和多个唯一的非聚集索引。
说明：
只有当数据本身具有唯一性特征时，指定唯一索引才有意义。如果必须要实施唯一性来确保数据的完整性，则应在列上创建UNIQUE约束或PRIMARY KEY约束，而不是创建唯一索引。例如，如果想限制学生表（主键为Sno）中的身份证号列（sid）的取值不能有重复，则可在sid列上创建 UNIQUE约束，而不是在该列上创建唯一索引。实际上，当在表上创建PRIMARY KEY约束或UNIQUE约束时，系统会自动在这些约束的列上创建唯一索引。
6.1.3 创建和删除索引
1．创建索引
确定了索引关键字之后，就可以在数据库的表上创建索引。创建索引使用的是CREATE INDEX 语句，其一般语法格式为：
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX <索引名> ON <表名> ( <列名> [,…n] )
其中：
UNIQUE：表示要创建的索引是唯一索引。
CLUSTERED：表示要创建的索引是聚集索引。
NONCLUSTERED：表示要创建的索引是非聚集索引。
如果没有指定索引类型，则默认是创建非聚集索引。

例1．为Student表的Sname列创建非聚集索引。
CREATE INDEX Sname_ind
ON Student ( Sname )
例2．为Student表的Sid列创建唯一聚集索引。
CREATE UNIQUE CLUSTERED INDEX Sid_ind
ON Student ( Sid )
例3．为Employee表的FirstName和LastName列创建一个聚集索引。
CREATE CLUSTERED INDEX EName_ind
ON Employee ( FirstName, LastName )
2．删除索引
索引一经建立，就由数据库管理系统自动使用和维护，不需要用户干预。建立索引是为了加快数据的查询效率，但如果要频繁的对数据进行增、删、改操作，则数据库管理系统会花费很多时间来维护索引，这会降低数据的修改效率；另外，存储索引需要占用额外的空间，这增加了数据库的空间开销。因此，当不需要某个索引时，可将其删除。
在SQL语言中，删除索引使用的是DROP INDEX语句。其一般语法格式为：
DROP INDEX <表名>.<索引名>
例4．删除Student表中的Sname_ind索引。
DROP INDEX Student.Sname_ind