第 18 章 索引

1. 第18章 索引 学习导读 • 索引其实是一个类似于目录的概念，就像书本的目录是用来导引书本的主要内容一样，是用来帮助检索数据库中表的记录信息的。书的目录有详细与不详细之分：不详细的目录可能将书的内容定位到某些页面内，而详细的目录可以将要查找的内容定位在某一页内。显然，目录太详尽也会产生许多问题。索引与此类似，不过索引的颗粒度更小，索引必须要检索到相应的记录。

2. 索引介绍 • 用术语来说，索引是一项用于提高数据库查询性能的技术。索引可以减小生成的结果集的规模，减少对数据库的读取量和消耗时间；另一方面，索引也能作为一种强制数据完整性约束，保证表的行具有惟一性，如UNIQUE索引。当然，设计不当的索引也会成为性能提高的障碍。显然，正确地了解索引及其结构是设计良好索引的基础。本节将主要介绍这些内容。

3. 索引的类型 • 在SQL Server中，索引可以分为聚集索引、非聚集索引、惟一性索引、包含列索引、索引视图、全文索引、空间索引、筛选索引、XML索引等多种类型。这里仅介绍常用的聚集索引、非聚集索引以及惟一性索引。 1．聚集索引 聚集索引是对聚集索引列进行排序，进而实现了对记录进行排序。也就是说，在聚集索引中，不但索引是有序的，而且索引中的数据也是有序的

4. 索引的类型 2．非聚集索引 在非聚集索引中，每个索引并不是包含行记录的数据，而是数据行的一个指针。也就是说，在非聚集索引中，索引是顺序排序和存储，但是索引指向的数据行并不一定是按顺序存储的，这取决于创建非聚集索引时所用的方法。 创建非聚集索引的方法大致分为基于聚集索引和基于堆。其中，基于聚集索引创建非聚集索引时，数据行是有序的；而基于堆创建非聚集索引时，数据行是无序的。

5. 索引的类型 3．惟一性索引 一个索引的唯一性体现在该索引能够唯一地确定表的一个记录。因为唯一性索引不允许索引键中包含重复的值，因此在具有唯一性索引的表或视图中，每一个记录在某种程度上都是唯一的。 其实，用户早已接触和使用了唯一性索引。在创建PRIMARY KEY约束时，SQL Server自动创建了一个聚集类型的唯一性索引。

6. 索引的要求和注意事项 • 上面介绍了索引的分类，这里将介绍设计索引时的一些注意事项。这些注意事项在设计索引时将起到一定的指导意义。 • 由于SQL Server在维护索引时需要消耗资源，所以不要对较小的表使用索引。一般情况下，如果表的记录规模在几千行记录以内，那么该表就可以看作一个小表。对于小表，SQL Server在没有索引的情况下也可以快速地生成结果集。相反地，小表中的索引将成小表的拖累。

7. 索引的要求和注意事项 • 如果一个列或一些列需要频繁地进行INSERT操作，那么最好不要对这些列创建索引。索引比较适合于SELECT语句、UPDATE语句以及DELETE语句等用到检索功能的语句，如DELETE语句需要先查到记录才能删除。 • 索引要包含尽量少的列，特别是在经常使用INSERT语句和UPDATE语句的表中。

8. 创建索引 • 在SQL Server中，支持使用SQL Server Management Studio（即CREATE INDEX）和SQL语句创建索引。这里以SQL Server Management Studio创建索引为主要介绍对象，并在其操作后给出相同功能的SQL语句。

9. 使用SQL Server Management Studio创建索引 • 在SQL Server Management Studio中，创建索引操作时可以几乎不需要输入SQL语句。 (1)打开一个查询窗口输入以下SQL语句并执行： IF OBJECT_ID(‘database_demo.dbo.student’) IS NOT NULL DROP TABLE database_demo.dbo.student GO CREATE TABLE database_demo.dbo.student(id INT,name NCHAR(10),salary INT)

10. 使用SQL Server Management Studio创建索引 (2)在SSMS中打开student表的设计窗口。

11. 使用SQL Server Management Studio创建索引 (3)在表设计器中单击右键选择“索引/键”命令，打开“索引/键”窗口。

12. 使用SQL Server Management Studio创建索引 (4)在“索引/键”窗口中，单击“添加”按钮为当前表添加索引。单击“列”为索引添加所需的列。

13. 使用SQL Server Management Studio创建索引 (5)用“保存”按钮将索引的设计保存到数据库中。 (6)创建的索引会列在“对象资源管理器”中。在创建了非聚集索引IX_student的同时，还自动创建了统计信息对象IX_student。

14. CREATE INDEX创建索引 • 前面提到，在SQL Server中，可以使用SQL语句创建索引。其实，创建索引的语句就是 CREATE INDEX语句。 CREATE [UNIQUE][CLUSTERED|NONCLUSTERED] INDEX index_name ON table_or_view_name (column [ASC|DESC][,…n]) [WITH (<relational_index_option>[,…n])] []

15. CREATE INDEX创建索引 <relational_index_option>::= { 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} }

16. CREATE INDEX创建索引 • 各组成元素的意义： • UNIQUE指示该索引是唯一索引 • CLUSTERED指示该索引为聚集索引。一个表或视图只允许同时有一个聚集索引 • NONCLUSTERED指示该索引为非聚集索引，是默认值。每个表可以最多包含999个非聚集索引 • index_name是索引的名称。索引名称在表或视图中必须唯一，但在数据库中不必唯一，索引名称必须满足标识符规则 • table_or_view_name是表或视图的名称，最好将其表示为database.dbo.table_name格式

17. CREATE INDEX创建索引 • column指定创建索引的列 • PAD_INDEX指定索引填充，其值可以为ON或OFF（默认）。当指定为ON时，将使用FILLFACTOR属性指定的可用空间百分比作为索引的二级索引；当指定为OFF时，二级索引将被填充到其容量的程度，以留出足够的空间 • FILLFACTOR指定一个百分比，表示在索引创建或重新生成过程中索引页的填充程度，其值必须是介于1～100之间的整数值，默认值为0（与100相同） • SORT_IN_TEMPDB指定是否在tempdb中存储临时排序结果，指定为ON时，在tempdb中存储用于生成索引的中间结果。如果tempdb与数据库不在同一组磁盘上，就可以缩短创建索引所需的时间。但这样会增加索引生成期间所使用的磁盘空间。当指定为OFF时，中间排序结果与索引存储在同一个数据库中

18. CREATE INDEX创建索引 • IGNORE_DUP_KEY指定对唯一索引进行多行插入操作时出现重复键值的错误响应。指定为ON时，这种情况会发出一条警告信息，但只有违反了唯一索引的行才会失败；当指定为OFF时，发出错误消息，并回滚整个INSERT事务。IGNORE_DUP_KEY设置仅适用于创建或重新生成索引后发生的插入操作，该设置在索引创建操作期间无效。如果是对视图创建的索引，那么IGNORE_DUP_KEY不能设置为ON • STATISTICS_NORECOMPUTE指定是否重新计算分发统计信息。指定为ON时，不会自动重新计算以前的统计信息；当指定为OFF时，启用统计信息，自动更新以前的统计信息

19. CREATE INDEX创建索引 • DROP_EXISTING指定应删除并重新生成已命名的先前存在的索引。指定为ON时，删除并重新生成现有索引，且指定的索引名称必须与现有的索引相同；当指定为OFF时，如果指定的索引已经存在，则会报错，即不能改变索引类型 • ONLINE指定在索引操作期间，基础表和关联的索引是否能用于查询和数据修改操作。指定为ON时，在索引操作期间不持有长期表锁；指定为OFF时，在索引操作期间应用表锁 • ALLOW_ROW_LOCKS指定是否允许行锁。当指定为ON时，在访问索引时允许行锁，数据库引擎确定何时使用行锁；指定为OFF时，不使用行锁 • ALLOW_PAGE_LOCKS指定是否允许页锁，使用同上

20. CREATE INDEX创建索引 USE database_demo GO CREATE NONCLUSTERED INDEX IX_student ON dbo.student (id ASC) WITH (PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF, SORT_IN_TEMPDB=OFF, IGNORE_DUP_KEY=OFF DROP_EXISTING=OFF, ONLINE=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON)

21. 管理索引 • 虽然索引在设计和使用之后，SQL Server将会在执行SELECT等语句时自动使用索引，但是用户仍需要在索引设计后进行管理和维护。其中，管理索引的操作主要包括查看索引的信息、修改索引以及禁止/启用索引等。

22. 查看索引的信息 • 查看表或视图中索引的信息可以使用系统试图sys.indexes、object_id函数以及INDEX_COL函数。为了便于查看数据库中表或视图的索引，这里将其封装为一个存储过程。该存储过程可以查看表或视图的索引名，以及索引的列。 USE database_demo GO IF OBJECT_ID(‘indexs_in_table’) IS NOT NULL DROP PROCEDURE indexs_in_table GO

23. 查看索引的信息 CREATE PROCEDURE indexs_in_table @table_name NCHAR(100) AS SELECT @table_name AS ‘表或视图’,i.name,INDEX_COL(‘database_demo.dbo.student’,sysic.index_id,sysic.index_column_id) AS ‘列名’ FROM sys.indexs AS I LEFT JOIN sys.index_columns AS sysic ON sysic.object_id=i.object_id WHERE i.object_id=OBJECT_ID(@table_name) AND (type_desc=‘NONCLUSTERED’ or type_desc=‘CLUSTERED’) GO

24. 查看索引的信息 • 如果要查看数据库database_demo中表student的索引信息，执行一下SQL语句即可： EXECUTE indexs_in_table ‘database_demo.dbo.student’

25. 修改索引 • 修改索引的操作包括重命名索引的名称，以及禁止/启用索引。其中，重新命名索引的名称要使用sp_rename存储过程。不过，最好将索引删除后重建，而不要使用该存储过程。这里仅介绍禁止/启用索引，主要用到ALTER INDEX语句。 ALTER INDEX index_name ON table_or_view_name DISABLE|REBUILD

26. 修改索引 • 组成元素的意义： • index_name是索引的名称 • table_or_view_name是索引所依赖的表或视图的名称 • DISABLE代表禁用该索引 • REBUILD代表启用该索引 如下语句禁用IX_student索引 USE database_demo GO ALTER INDEX IX_student ON dbo.student DISABLE

27. 删除索引 • 删除索引主要用到DROP INDEX语句。 DROP INDEX index_name ON table_or_view_name 如下SQL语句将删除数据库database_demo中表student的索引IX_student： USE database_demo GO DROP INDEX IX_student ON dbo.student

28. 小结 • 本章主要介绍索引。索引主要是为了提高查询速度，当新建索引后，SQL Server 2008会为索引建立一个单独的区块，在没数据操纵时，同时会更新这个区块。在查询时，先查询这个区块，再通过对应表去查询实体数据。有人问我把一个表所有的字段都加上索引是否会增加系统速度，这样是不行的，索引也是双刃剑，太多会增加系统的开销，影响系统速度。