SQL Server 2022的索引

《SQL Server 2022从入门到精通（视频教学超值版）》图书介绍-CSDN博客

《SQL Server 2022从入门到精通（视频教学超值版）（数据库技术丛书）》(王英英)【摘要 书评 试读】- 京东图书 (jd.com)

10.1 索引的含义和特点

索引是一个单独的、存储在磁盘上的数据库结构，它包含着对数据表中所有记录的引用指针。使用索引用于快速找出在某个或多个列中有某一特定值的行，对相关列使用索引是减少查询操作时间的最佳途径。索引包含由表或视图中的一列或多列生成的键。

例如，数据库中有两万条记录，现在要执行这样一个查询：SELECT * FROM table WHERE num=10000。如果没有索引，必须遍历整个表，直到num等于10000的这一行被找到为止；如果在num列上创建索引，SQL Server不需要任何扫描，直接在索引中找10000，就可以得知这一行的位置。可见，索引的建立可以加快数据的查询速度。

索引主要有以下优点：

（1）通过创建唯一索引可以保证数据库表中每一行数据的唯一性。

（2）可以大大加快数据的查询速度，这也是创建索引最主要的原因。

（3）实现数据的参照完整性，可以加快表和表之间的连接。

（4）在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间。

增加索引也有许多不利的方面，主要表现在如下几个方面：

（1）创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。

（2）索引需要占用磁盘空间，除数据表占用数据空间外，每一个索引还要占用一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。

（3）当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

10.2 索引的分类

不同数据库中提供了不同的索引类型，SQL Server 2022中的索引有两种：聚集索引和非聚集索引。它们的区别是在物理数据的存储方式上。

1. 聚集索引

聚集索引基于数据行的键值，在表内排序和存储这些数据行。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。

创建聚集索引时应该考虑以下几个因素：

（1）每个表只能有一个聚集索引。

（2）表中的物理顺序和索引中行的物理顺序是相同的，创建任何非聚集索引之前要先创建聚集索引，这是因为非聚集索引改变了表中行的物理顺序。

（3）关键值的唯一性使用UNIQUE关键字或者由内部的唯一标识符明确维护。

（4）在创建索引的过程中，SQL Server临时使用当前数据库的磁盘空间，所以要保证有足够的空间创建聚集索引。

2. 非聚集索引

非聚集索引具有完全独立于数据行的结构，使用非聚集索引不用将物理数据页中的数据按列排序。非聚集索引包含索引键值和指向表数据存储位置的行定位器。

可以对表或索引视图创建多个非聚集索引。通常，设计非聚集索引是为了改善经常使用的、没有建立聚集索引的查询的性能。

查询优化器在搜索数据值时，先搜索非聚集索引以找到数据值在表中的位置，然后直接从该位置检索数据。这使得非聚集索引成为完全匹配查询的最佳选择，因为索引中包含所搜索的数据值在表中的精确位置的项。

具有以下特点的查询可以考虑使用非聚集索引：

（1）使用JOIN或GROUP BY子句。应为连接和分组操作中所涉及的列创建多个非聚集索引，为任何外键列创建一个聚集索引。

（2）包含大量唯一值的字段。

（3）不返回大型结果集的查询。创建筛选索引以覆盖从大型表中返回的定义完善的行子集的查询。

（4）经常包含在查询的搜索条件（如返回完全匹配的WHERE子句）中的列。

3. 其他索引

除聚集索引和非聚集索引外，SQL Server 2022还提供了其他的索引类型。

1. 唯一索引：确保索引键不包含重复的值，因此，表或视图中的每一行在某种程度上是唯一的。聚集索引和非聚集索引都可以是唯一索引。这种唯一性与前面讲过的主键约束是相关联的，在某种程度上，主键约束等于唯一性的聚集索引。
2. 包含列索引：一种非聚集索引，它扩展后不仅包含键列，还包含非键列。
3. 索引视图：在视图上添加索引后能提高视图的查询效率。视图的索引将具体化视图，并将结果集永久存储在唯一的聚集索引中，而且其存储方法与带聚集索引的表的存储方法相同。创建聚集索引后，可以为视图添加非聚集索引。
4. 全文索引：一种特殊类型的基于标记的功能性索引，由 Microsoft SQL Server 全文引擎生成和维护，用于帮助用户在字符串数据中搜索复杂的词。这种索引的结构与数据库引擎使用的聚集索引或非聚集索引的B树结构是不同的。
5. 空间索引：一种针对geometry数据类型的列上建立的索引，这样可以更高效地对列中的空间对象执行某些操作。空间索引可以减少需要应用开销相对较大的空间操作的对象数。
6. 筛选索引：一种经过优化的非聚集索引，尤其适用于涵盖从定义完善的数据子集中选择数据的查询。筛选索引使用筛选谓词对表中的部分行进行索引。与全表索引相比，设计良好的筛选索引可以提高查询性能、减少索引维护开销并可降低索引存储开销。

XML索引：是与XML数据关联的索引形式，是XML二进制大对象（Binary Large Object，BLOB）的已拆分持久表示形式，XML索引又可以分为主索引和辅助索引。

使用Transact-SQL语句创建索引

CREATE INDEX命令既可以创建一个可改变表的物理顺序的聚集索引，也可以创建提高查询性能的非聚集索引，语法如下：

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX index_name ON {table | view}(column[ASC | DESC][,..n])
[ INCLUDE ( column_name [ ,...n ] ) ]
[with
(
  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
) [...n]

1. UNIQUE：表示在表或视图上创建唯一索引。唯一索引不允许两行具有相同的索引键值。视图的聚集索引必须唯一。
2. CLUSTERED：表示创建聚集索引。在创建任何非聚集索引之前创建聚集索引。创建聚集索引时会重新生成表中现有的非聚集索引。如果没有指定CLUSTERED，则创建非聚集索引。
3. NONCLUSTERED：表示创建一个非聚集索引，非聚集索引数据行的物理排序独立于索引排序。每个表都最多可包含999个非聚集索引。NONCLUSTERED是CREATE INDEX语句的默认值。
4. index_name：指定索引的名称。索引名称在表或视图中必须唯一，但在数据库中不必唯一。
5. ON {table| view}：指定索引所属的表或视图。
6. Column：指定索引基于的一列或多列。指定两个或多个列名，可为指定列的组合值创建组合索引。{table| view}后的括号中，按排序优先级列出组合索引中要包括的列。一个组合索引键中最多可组合16列。组合索引键中的所有列必须在同一个表或视图中。
7. [ ASC | DESC ]：指定特定索引列的升序或降序排序方向。默认值为ASC。
8. INCLUDE ( column [ ,...n ] )：指定要添加到非聚集索引的叶级别的非键列。
9. PAD_INDEX：表示指定索引填充。默认值为OFF。ON值表示fillfactor指定的可用空间百分比应用于索引的中间级页。
10. FILLFACTOR = fillfactor：指定一个百分比，表示在索引创建或重新生成的过程中数据库引擎应使每个索引页的叶级别达到的填充程度。fillfactor必须为介于1和100之间的整数值，默认值为0。
11. SORT_IN_TEMPDB：指定是否在tempdb中存储临时排序结果。默认值为OFF。ON值表示在tempdb中存储用于生成索引的中间排序结果。OFF表示中间排序结果与索引存储在同一数据库中。
12. IGNORE_DUP_KEY：指定对唯一聚集索引或唯一非聚集索引执行多行插入操作时，出现重复键值的错误响应。默认值为OFF。ON表示发出一条警告信息，但只有违反了唯一索引的行才会失败。OFF表示发出错误消息，并回滚整个INSERT事务。
13. STATISTICS_NORECOMPUTE：指定是否重新计算分发统计信息。默认值为OFF。ON表示不会自动重新计算过时的统计信息。OFF表示启用统计信息自动更新功能。
14. DROP_EXISTING：指定应删除并重新生成已命名的先前存在的聚集或非聚集索引。默认值为OFF。ON表示删除并重新生成现有索引。指定的索引名称必须与当前的现有索引相同，但可以修改索引定义。例如，可以指定不同的列、排序顺序、分区方案或索引选项。OFF表示如果指定的索引名已存在，则会显示一条错误。
15. ONLINE = { ON | OFF }：指定在索引操作期间，基础表和关联的索引是否可用于查询和数据修改操作。默认值为OFF。
16. ALLOW_ROW_LOCKS：指定是否允许行锁。默认值为ON。ON表示在访问索引时允许行锁。数据库引擎确定何时使用行锁。OFF表示未使用行锁。
17. ALLOW_PAGE_LOCKS：指定是否允许页锁。默认值为ON。ON表示在访问索引时允许页锁。数据库引擎确定何时使用页锁。OFF表示未使用页锁。
18. MAXDOP：指定在索引操作期间，覆盖【最大并行度】配置选项。使用MAXDOP可以限制在执行并行计划的过程中使用的处理器数量。最大数量为64个。

为了演示创建索引的方法，下面在test_db数据库中创建数据表authors，输入语句如下：

CREATE TABLE authors(
	auth_id int IDENTITY(1,1) NOT NULL,
	auth_name   varchar(20) NOT NULL,
	auth_gender tinyint NOT NULL,
	auth_phone   varchar(15) NULL,
	auth_note   varchar(100) NULL
) ;

【例10.1】在authors表中的auth_phone列上创建一个名称为Idx_phone的唯一聚集索引，以降序排列，填充因子为30%，输入语句如下：

CREATE UNIQUE CLUSTERED INDEX Idx_phone 
ON authors(auth_phone DESC)
WITH
FILLFACTOR=30;

【例10.2】在authors表中的auth_name和auth_gender列上创建一个名称为Idx_nameAndgender的唯一非聚集组合索引，以升序排列，填充因子为10%，输入语句如下：

CREATE UNIQUE NONCLUSTERED INDEX Idx_nameAndgender
ON authors(auth_name, auth_gender)
WITH
FILLFACTOR=10;