SQL Server教程 - T-SQL-索引（INDEX）

更新记录
转载请注明出处：
2022年8月3日 发布。
2022年7月2日 从笔记迁移到博客。

索引说明（INDEX）

索引是什么

索引是对数据表中的一个或多个列的值进行排序的结构，用于加快数据查询
索引是一种在磁盘上（不是内存）上的数据结构，建立在表或者视图之上
使用索引可以使数据的获取更加快速更加高效
相反，加载一张带索引的表或视图同样会影响其他操作的性能，比如插入或者更新
索引可以建立在一列或者多列上

数据库的索引与书籍的目录类似
在进行数据查询SQL Server 2016先访问索引
再根据索引信息在数据表中找到相关记录
避免扫描整个数据表，从而有效提升数据检索的速度

通过指定列的值快速寻找记录
索引是一种针对表建立的逻辑结构
索引指向实际数据的物理地址

索引作用（优点）

加快检索速度，提高系统性能
创建唯一性索引可以保证每一行数据的唯一性.
加速表和表之间的连接，特别是在实现数据的参照完整性方面特别有意义.
在对数据进行分组或排序时，可以减少查询中分组和排序的时间
通过索引，可以在查询的过程中使用优化隐藏器提高系统的性能
如果表上的多个字段经常进行排序或分组，考虑使用组合索引

索引缺点

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而培加
对表数据进行增删改将减慢速度
索引占用存储空间

如何选择多列索引和单列索引

Should there be only one column used, a single-column index should be the choice. Should there be two or more columns that are frequently used in the WHERE clause as filters, the composite index would be the best choice
不适合使用索引的场景
Indexes should not be used on small tables
Tables that have frequent, large batch update or insert operations should not be indexed
Indexes should not be used on columns that contain a high number of NULL values
Columns that are frequently manipulated should not be indexed

索引分类

说明

根据数据在表中的存储结构的不同，分为：聚集索引和非聚集索引
数据库索引类型：
聚集索引、非聚集索引、全文索引、XML索引和空间索引、唯一索引、包含列索引、索引视图和筛选索引

聚集索引

聚集索引基于数据行的键值，在数据表内排序和存储这些数据行。每个数据表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。如果表具有聚集索引，该表就称为聚集表，表中的物理顺序和索引的顺序是相同的。
创建聚集索引时应该考虑以下几个因素：
（1)每个数据表只能有一个聚集索引。
（2)数据表中的物理顺序和索引中行的物理顺序是相同的，创建任何非聚集索引之前要先创建聚集索引，这是因为非聚集索引改变了数据表中行的物理顺序。
（3)关键值的唯一性是使用UNIQUE关键字或者由内部的唯一标识符明确维护。
（4)在索引的创建过程中，SQL Server临时使用当前数据库的磁盘空间，所以要保证有足够的磁盘空间来创建聚集索引。

注意：
当创建PRIMARY KEY时，如果不存在该表的聚集索引且未指定唯一非聚集索引， 将自动生成唯一聚集索引。
创建 Unique 约束时，默认情况下将创建唯一非聚集索引，若该表不存在聚集索引， 则可以指定唯一聚集索引。

以下情况考虑使用聚集索引：
查询包含外键，创建聚集索引
含有大量非重复值的列
使用BETWEEN,>,<,=,<,<=范围筛选的情况
返回大量结果集的查询
经常使用JOIN和GRIP BY的查询

非聚集索引

非聚集索引具有完全独立于数据行的结构，使用非聚集索引不用将物理数据页中的数据按字段排序。非聚集索引包含索引键值和指向数据表中数据存储位置的行定位器。可以对数据表或索引视图创建多个非聚集索引。通常，设计非聚集索引是为了改善经常使用的、没有建立聚集索引的查询的性能。查询优化器在搜索数据时，先搜索非聚集索引以找到数据在数据表中的位置，然后直接从该位置检索数据。这使得非聚集索引成为完全匹配查询的最佳选择，因为索引中包含所搜索的数据在数据表中的精确位置。
具有以下特点的查询可以考虑使用非聚集引：
（1)使用JOIN或GROUPBY子句。应为连接和分组操作中所涉及的字段创建多个非聚集索引，为任何外键字段创建一个聚集索引。
（2)包含大量唯一值的字段。
（3)不返回大型结果集的查询。创建筛选索引以覆盖从大型数据表中返回定义完善的行子集的查询。
（4)经常包含在查询的搜索条件（如返回完全匹配的WHERE子句）中的字段。

非聚集索引注意：
使用非聚集索引，不需要表中的物理数据页按列排序
非聚集索引包含索引键值和指向数据存储位置的行定位器
可以对表创建多个非聚集索引
允许最多有249个非聚集索引
索引页只包含索引关键字，不包含数据
需要以多种方式检索数据时，通常创建非聚集索引

唯一索引（Unique Indexes）

确保索引键不包含重复的值，因此，数据表或视图中的每一行在某种程度上是唯一的。聚集索引和非聚集索引都可以是唯一索引。这种唯一性与前面讲过的主键约束是相关联的，在某种程度上，主键约束等于唯一性的聚集索引。

包含性列索引

一种非聚集索引，它扩展后不仅包含键字段，还包含非键字段。

索引视图

在视图上添加索引后能提高视图的查询效率。视图的索引将具体化视图，并将结果集永久存储在唯一的聚集索引中，而且其存储方法与带聚集索引的数据表的存储方法相同。创建聚集索引后，可以为视图添加非聚集索引。

单列索引实（Single-Column Indexes）

CREATE INDEX index_name

ON table_name (column_name)

复合索引（Composite Indexes）

CREATE INDEX index_name on table_name (column1, column2)

隐式索引（Implicit Indexes）

当创建primary key constraints and unique constraints.时会自动创建隐士索引

全文索引

一种特殊类型的基于标记的功能性索引，由Microsoft SQL Server全文引擎生成和维护。用于帮助在字符串数据中搜索复杂的词。这种索引的结构与数据库引擎使用的聚集索引或非聚集索引的B树结构是不同的。

XML索引

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

筛选索引

一种经过优化的非聚集索引，尤其适用于定义完善的数据子集中选择数据的查询。筛选索引使用筛选谓词对数据表中的部分行进行索引。与全表索引相比，设计良好的筛选索引可以提高查询性能、减少索引维护开销并可降低索引存储开销。

空间索引

一种针对 geometry 数据类型的字段建立的索引，这样可以更高效地对字段中的空间对象执行某些操作。空间索引可以减少需要应用开销相对较大的空间操作的对象数。

索引设计和使用原则

索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好的性能非常重要。设计索引时，应该考虑以下准则：

（1)索引并非越多越好，一个数据表中如果有大量的索引，不仅占用大量的磁盘空间，而且会影响 INSERT、DELETE、UPDATE等语句的性能。因为当表中数据更改的同时，索引也会进行调整和更新。
（2)避免对经常更新的数据表进行过多的索引，并且索引中的字段尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。
（3)数据量小的数据表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，因此索引可能不会产生优化效果。
（4)在条件表达式中经常用到的、不同值较多的字段上建立索引，而在不同值少的字段上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低更新速度。
（5)当唯一性是某种数据本身的特征时，指定为唯一索引。使用一索引能够确保定义的字段的数据完整性，提高查询速度。
（6)在频繁进行排序或分组（即进行 GROUP BY或ORDER BY操作）的字段上建立索引，如果待排序的字段有多个，可以在这些字段上建立组合索引。

创建索引

创建索引-使用SSMS

进入要创建索引的表

填写索引参数

创建索引-使用T-SQL

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

注意：
如果指定聚集索引（CLUSTERED），则必须指定唯一UNIQUE，非聚集索引不用
如果不指定索引的类型，则默认为非聚集索引（NONCLUSTERED）
如果不指定排序，则默认为ASC
Indexs_options可以是：
DROP_EXISTING = ON| OFF 已经存在索引则删除，没有存在则新建

实例：创建唯一索引

CREATE UNIQUE INDEX index_name
on table_name (column_name)

创建全文索引-使用SSMS

创建全文索引-使用T-SQL

CREATE FULLTEXT INDEX ON table_name
KEY INDEX index_name

查看索引

查看索引-使用SSMS

查看索引-使用T-SQL

查看指定表上存在的索引

EXEC sp_helpindex 表名;

重命名索引

EXEC sp_rename '旧名','新名','index';

修改索引

重新组织索引说明

适合碎片较少的情况，当碎片足够多(超过30%)应考虑使用重新生成索引

重新生成索引说明

本质是旧的索引完全被删除，然后再新建索引

如果数据量大，这是一项非常耗时的过程

重新组织索引-使用SSMS

进入从新组织索引的页面

可以查看到索引是否有碎片，点击确定即可从新组织索引

重新组织索引-使用T-SQL

ALTER INDEX [索引名]
ON [表名]
REORGANIZE;

实例：

ALTER INDEX [PandaIndex]
ON [PandaTable]
REORGANIZE;

重新生成索引-使用SSMS

进入重新生成索引的界面

查看碎片情况，点击确定

重新生成索引-使用T-SQL

ALTER INDEX [索引名] 
ON [表名]
REBUILD WITH (ONLINE = ON)

实例：

ALTER INDEX [PandaIndex] 
ON [PandaTable]
REBUILD WITH (ONLINE = ON)

删除索引

删除索引-使用SSMS

删除索引-使用T-SQL

DROP INDEX 表名.索引名;

或者

DROP INDEX [索引名称] ON 表名;

删除全文索引-使用T-SQL

DROP FULLTEXT INDEX ON table_name