规划和管理索引

参数	说明
UNIQUE	为表或视图创建唯一索引（不允许存在索引值相同的两行）。
CLUSTERED	创建聚集索引。每个表中只能允许有一个聚集索引。如果没有指定CLUSTERED，则创建非聚集索引。
NONCLUSTERED	创建非聚集索引，非聚集索引最多可以创建249个。
index_name	索引名。索引名在表中必须唯一，但在数据库中不必唯一。
table_name	包含要创建索引的列的表。只有表的所有者可以在表中创建索引
view_name	要建立索引的视图的名称。视图索引的类型必须是唯一聚集索引。必须使用SCHEMABINDING定义视图才能在视图上创建索引。而且，索引中所有键列必须是精确的，不能在视图的float列上或者使用非确定性函数表达式的列上创建索引。
column_name	应用索引的列。可以指定一个或多个列名，当指定多个列名时，将使用这些列的组合值创建组合索引。在table_name后的圆括号中列出组合索引中要包含的列（按排序优先级排列）。
ASC \| DESC	确定具体某个索引列的升序或降序排序方向，默认设置为ASC。
FILLFACTOR	为索引指定填充因子，用来提高表的更新性能。
IGNORE_DUP_KEY	如果为索引指定了IGNORE_DUP_KEY，当INSERT语句向唯一索引列插入重复的键值时，SQL Server将发出警告消息并忽略重复的行。
DROP_EXISTING	指定应除去并重建已命名的先前存在的聚集索引或非聚集索引。指定的索引名必须与现有的索引名相同。

● 示例代码

--在Student表的ID上创建非聚集索引

IF EXISTS(SELECT * FROM sysindexes s WHERE s.[name]='IX_Student_ID')

DROP INDEX IX_Student_ID ON Student

CREATE INDEX IX_Student_ID

ON Student(ID)

--在Student表的Name上创建唯一的非聚集索引

IF EXISTS(SELECT * FROM sysindexes s WHERE s.[name]='IX_Student_Name')

DROP INDEX Student.IX_Student_Name

CREATE UNIQUE INDEX IX_Student_Name

ON Student([Name])

--在Student表的firstName和lastName上创建组合索引

IF EXISTS(SELECT * FROM sysindexes s WHERE s.[name]='IX_Student_FirstName_LastName')

DROP INDEX IX_Student_FirstName_LastName ON Student

CREATE INDEX IX_Student_FirstName_LastName

ON Student(firstName,lastName)

--使用DROP_EXISTING选项在Province表的[National]列上删除并重新创建现有索引。

CREATE NONCLUSTERED INDEX IX_Province_National

ON Province([National])

WITH (DROP_EXISTING=ON,FILLFACTOR=80)

（3）、注意事项

● 在创建索引前考虑表中已经创建的索引数量，最好不要在一个表中创建大量的索引。

● 检查表中已经创建的索引定义，避免对同一列重叠创建索引。

● 选择在唯一值较高的列上创建索引。检查列中唯一数据值的数量，并与表中的行数进行比较，比较的结果就是该列的可选性。

● 一个索引包含的所有列的宽度之和不能超过900字节。例如：不能在宽度为char(300)、char(300)和char(301)的3个列上创建组合索引，因为总宽度超过了900字节。

2、组合索引

组合索引是指使一个索引中包含了一个以上的列，最多可以有16列组合到一个索引中。

3、唯一索引

● 唯一索引可以确保索引列中不包含重复的值。只有当唯一性是数据本身的特性时，指定唯一索引才有意义。

● 当创建或修改唯一索引时，可以设置选项（IGNORE_DUP_KEY）以忽略重复的键。如果已设置该选项，并且试图通过添加或更新将影响多行的数据来创建重复键（使用INSERT或UPDATE语句），则不能添加导致重复的行，或在更新时放弃这样的行。

例如：在已经含有“Jones”的表中试图用“Jones”更新“Smith”，则在结果表中只有一个“Jones”，而没有“Smith”。原有的“Smith”行丢失，因为UPDATE语句实际上是DELETE语句后跟一个INSERT语句。“Smith”已删除，而试图插入列一个“Jones”的操作失败。整个事务不能回滚，因为该选项的目的是允许事务，而不管是否存在重复项。

4、主键索引

在SQL Server中为表定义主键时将自动创建主键索引，主键索引是唯一索引的特殊类型。如果该表尚未创建聚集索引，并且在创建PRIMARY KEY约束时未指定索引的类型，则PRIMARY KEY约束会自动创建聚集索引。

5、填充因子FILLFACTOR

● 页拆分：如果向已满的索引页添加新行，数据库引擎将把大约一半的行移到新页中，一遍为该新行腾出空间。这种重组称为页拆分。页拆分可为新行腾出空间，但是执行页拆分可能需要花费一定的时间，此操作会消耗大量资源。此外，它还可能造成碎片，从而导致IO操作增加。

● 填充因子指定使用现有数据创建新索引时将每页填满到什么程度。页面不会维护在任何特定的填充水平上。

● 填充因子的值是从0到100的百分比数值，默认为0。值为0并不表示页面的填满程度为0%，而是类似于设置为100的情况，在索引页中预留很少量的空闲空间。

● 指定填充因子选项是用于微调性能。创建索引时，很少需要指定填充因子。

6、管理索引

（1）、查看索引

可以通过SQL Server Management Studio查看。

也可以使用系统存储过程sp_helpindex查看表上或视图上索引的信息。

示例：

EXEC sp_helpindex 'Student'

（2）、修改索引

修改索引的过程是首先删除表中准备修改的索引，然后使用新的索引定义重新创建该索引。

创建和修改索聚集索引时，SQL Server要在磁盘上对表的行进行重组，当表中存储了大量记录时，会产生很大的开销，可能要花很长时间。

（3）、删除索引

删除索引可以回收占用的磁盘空间。

删除聚集索引会导致重建同一个表上的所有非聚集索引。

可以使用SQL Server Management Studio删除索引。

也可以使用命令删除索引。

DROP INDEX Province.IX_Province_Name

若要删除为实现Primary Key或UNIQUE约束而创建的索引，必须首先删除约束。

五、维护索引

创建索引后，必须对索引进行维护，确保索引的统计信息是有效的，才能够提高查找速度。随着更新操作不断执行，数据会变的支离破碎，这些数据碎片会导致额外的页读取，妨碍数据的并行扫描。应该定期整理索引清除数据碎片，提高数据读取的性能。

1、维护索引的统计信息

● 使用DBCC SHOW_STATISTICS命令显示指定索引的当前分步统计

DBCC SHOW_STATISTICS ('Province',IX_Province_Name)

● 使用UPDATE STATISTICS命令更新表或者视图中的索引统计信息

UPDATE STATISTICS Province

● 使用sp_updatestats系统存储过程，对当前数据库中所有用户定义的表运行UPDATE STATISTICS

EXEC sp_updatestats

2、数据碎片

● 使用DBCC SHOWCONTIG命令显示指定的表或视图的数据和索引的碎片信息

DBCC SHOWCONTIG('Province',IX_Province_Name)

结果集如下图所示：

结果集内的信息如下表：

统计信息	说明
扫描页数	表或索引中的页数
扫描区数	表或索引中的区数
区切换次数	遍历表或索引的页时，DBCC语句从一个区移动到另一个区的次数
每个区的平均页数	页链中每个区的页数
扫描密度[最佳计数：实际计数]	百分比。这是“最佳计数”与“实际计数”的比率。如果所有内容都是连续的，则该值为100；如果该值小于100，则存在一些碎片。“最佳计数”是指在一切都连续链接的情况下，区更改的理想数目。“实际计数”是指区更改的实际次数。
逻辑扫描碎片	扫描索引的叶级页时返回的出错页的百分比。此数与堆无关。对于出错页，在IAM中所指示的下一页与叶级页中下一页指针所指向的页不同。
区扫描碎片	扫描索引的叶级页时出错区所占的百分比。此数与堆无关。对于出错区，包含当前索引页的区在物理上不是包含上一个索引页的区的下一个区。注意：如果索引跨越多个文件，则此数字无意义。
每页的平均可用字节数	扫描的页上平均可用字节数。此数字越大，则页的填充程度越低。如果索引不会有很多随机插入，则数字越小越好。此数字还受行大小影响，行越大，此数字就越大。
平均页密度（满）	页的平均密度，以百分比表示。该值会考虑行大小。因此，该值可以更准确地指示页的填充程度。百分比越大越好。