SqlServer数据库的索引
用户对数据库最频繁的操作是进行数据查询。一般情况下，数据库在进行查询操作时需要对整个表进行数据搜索。当表中的数据很多时，搜索数据就需要很长的时间，这就造成了服务器的资源浪费。为了提高检索数据的能力，数据库引入了索引机制。本章将介绍索引的概念及其创建与管理。
8.1.1 索引的概念
索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引是依赖于表建立的，它提供了数据库中编排表中数据的内部方法。一个表的存储是由两部分组成的，一部分用来存放表的数据页面，另一部分存放索引页面。索引就存放在索引页面上，通常，索引页面相对于数据页面来说小得多。当进行数据检索时，系统先搜索索引页面，从中找到所需数据的指针，再直接通过指针从数据页面中读取数据。从某种程度上，可以把数据库看作一本书，把索引看作书的目录，通过目录查找书中的信息，显然较没有目录的书方便、快捷。
索引和关键字及约束有较大的联系。关键字可以分为两类：一种是逻辑关键字，即在 “数据库基础章”节中介绍的；另一种是物理关键字，它用来定义索引的列，也即索引。
8.1.2 索引的结构
（1） 索引的B-树结构
SQL Server 中的索引是以B-树结构来维护的，如图8-1 所示。B-树是一个多层次、自维护的结构。一个B-树包括一个顶层，称为根节点（Root Node）；0 到多个中间层（Intermediate）；一个底层（Level 0），底层中包括若干叶子节点（Leaf Node）。在图 8-1 中，每个方框代表一个索引页，索引列的宽度越大，B-树的深度越深，即层次越多，
读取记录所要访问的索引页就越多。也就是说，数据查询的性能将随索引列层次数目的增加而降低。 图8-1 索引的B-树结构
在SQL Server 的数据库中按存储结构的不同将索引分为两类：簇索引（Clustered Index）和非簇索引（Nonclustered Index）。
（2） 簇索引簇索引对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上，即簇索引与数据是混为一体，的它的叶节点中存储的是实际的数据。由于簇索引对表中的数据一一进行了排序，因此用簇索引查找数据很快。但由于簇索引将表的所有数据完全重新排列了，它所需要的空间也就特别大，大概相当于表中数据所占空间的120% 。表的数据行只能以一种排序方式存储在磁盘上，所以一个表只能有一个簇索引。
（3） 非簇索引非簇索引具有与表的数据完全分离的结构，使用非簇索引不用将物理数据页中的数据按列排序。非簇索引的叶节点中存储了组成非簇索引的关键字的值和行定位器。行定位器的结构和存储内容取决于数据的存储方式。如果数据是以簇索引方式存储的，则行定位器中存储的是簇索引的索引键；如果数据不是以簇索引方式存储的，这种方式又称为堆存储方式（Heap Structure），则行定位器存储的是指向数据行的指针。非簇索引将行定位器按关键字的值用一定的方式排序，这个顺序与表的行在数据页中的排序是不匹配的。由于非簇索引使用索引页存储因此它比簇索引需要更多的存储空间且检索效率较低但一个表只能建一个簇索引，当用户需要建立多个索引时就需要使用非簇索引了。从理论上讲，一个表最多可以建249 个非簇索引。
8.2.1 用CREATE INDEX 命令创建索引
CREATE INDEX 既可以创建一个可改变表的物理顺序的簇索引，也可以创建提高查询性能的非簇索引。其语法如下：
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX index_name ON {table | view } column [ ASC | DESC ] [,...n])
[WITH
[PAD_INDEX]
[ [, ] FILLFACTOR = fillfactor]
[ [, ] IGNORE_DUP_KEY]
[ [, ] DROP_EXISTING]
[ [, ] STATISTICS_NORECOMPUTE]
[ [, ] SORT_IN_TEMPDB ]
]
[ON filegroup]
各参数说明如下：
- UNIQUE
创建一个惟一索引，即索引的键值不重复。在列包含重复值时，不能建惟一索引。如要使用此选项，则应确定索引所包含的列均不允许NULL 值，否则在使用时会经常出错。 - CLUSTERED
指明创建的索引为簇索引。如果此选项缺省，则创建的索引为非簇索引。 - NONCLUSTERED
指明创建的索引为非簇索引其索引。数据页中包含了指向数据库中实际的表数据页的指针。 - index_name
指定所创建的索引的名称。索引名称在一个表中应是惟一的，但在同一数据库或不同数据库中可以重复。 - table
指定创建索引的表的名称。必要时还应指明数据库名称和所有者名称。 - view
指定创建索引的视图的名称。视图必须是使用SCHEMABINDING 选项定义过的，其具体信息请参见“视图创建”章节。 - ASC | DESC
指定特定的索引列的排序方式。默认值是升序（ASC）。 - column
指定被索引的列。如果使用两个或两个以上的列组成一个索引，则称为复合索引。一个索引中最多可以指定16 个列，但列的数据类型的长度和不能超过900 个字节。 - PAD_INDEX
指定填充索引的内部节点的行数，至少应大于等于两行。PAD_INDEX 选项只有在FILLFACTOR 选项指定后才起作用。因为PAD_INDEX 使用与FILLFACTOR 相同的百分比。缺省时，SQL Server 确保每个索引页至少有能容纳一条最大索引行数据的空闲空间。如果FILLFACTOR 指定的百分比不够容纳一行数据S，QL Server 会自动内部更改百分比。 - FILLFACTOR = fillfactor
FILLFACTOR 称为填充因子，它指定创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor 的值为1 到100。它其实同时指出了索引页保留的自由空间占索引页大小的百分比。即100 - fillfactor。 对于那些频繁进行大量数据插入或删除的表在建索引时应该为将来生成的索引数据预留较大的空间，即将fillfactor 设得较小，否则，索引页会因数据的插入而很快填满，并产生分页，而分页会大大增加系统的开销。但如果设得过小，又会浪费大量的磁盘空间，降低查询性能。因此，对于此类表通常设一个大约为10 的fillfactor。 而对于数据不更改的、高并发的、只读的表，fillfactor 可以设到95 以上乃至100。
如果没有指定此选项，SQL Server 默认其值为0 。0 是个特殊值，与其它小FILLFACTOR
值（如：1，2）的意义不同，其叶节点页被完全填满，而在索引页中还有一些空间。可以
用存储过程Sp_configure 来改变默认的FILLFACTOR 值。 - IGNORE_DUP_KEY
此选项控制了当往包含于一个惟一约束中的列中插入重复数据时SQL Server 所作的反应。当选择此选项时，SQL Server 返回一个错误信息，跳过此行数据的插入，继续执行下面的插入数据的操作：当没选择此选项时，SQL Server 不仅会返回一个错误信息，还会回滚（Rolls Back）整个INSERT 语句（关于回滚，请参见“数据库更新”章节中的“事务”一节）。 - DROP_EXISTING
指定要删除并重新创建簇索引。删除簇索引会导致所有的非簇索引被重建，因为需要用行指针来替换簇索引键。如果再重建簇索引，那么非簇索引又会再重建一次，以便用簇索引键来替换行指针。使用DROP_EXISTING 选项可以使非簇索引只重建一次。 - STATISTICS_NORECOMPUTE
指定分布统计不自动更新。需要手动执行不带NORECOMPUTE 子句的UPDATESTATISTICS 命令。 - SORT_IN_TEMPDB
指定用于创建索引的分类排序结果将被存储到Tempdb 数据库中。如果Tempdb 数据库和用户数据库位于不同的磁盘设备上，那么使用这一选项可以减少创建索引的时间，但它会增加创建索引所需的磁盘空间。 - ON filegroup
指定存放索引的文件组。
注意：数据类型为TEXT、NTEXT、IMAGE或BIT的列不能作为索引的列。
由于索引的宽度不能超过900个字节，因此数据类型为CHAR、VARCHAR、BINARY和VARBINARY的列的列宽 度超过了900字节，或数据类型为NCHAR、NVARCHAR的列的列宽度超过了450个字节时也不能作为索引的列。
在使用索引创建向导创建索引时，不能将计算列包含在索引中，但在直接创建或使用CREATE INDEX命令创建索引时，则可以对计算机列创建索引，这在SQL Server2000以前的版本中是不允许的，算得上是一大改进。
例8-1： 为表products 创建一个簇索引。
create unique clustered index pk_p_id
on products(p_id)
with
pad_index,
fillfactor = 10,
ignore_dup_key,
drop_existing,
statistics_norecompute
on [primary]
例8-2 为表products 创建一个复合索引
create index pk_p_main
on products(p_id, p_name, sumvalue) --其中sumvalue 是一个计算列表达式为price*quantity
with
pad_index,
fillfactor = 50
on [primary]
例8-3 创建一个视图并为它建一个索引
create view dbo.work_years
with
schemabinding
as
select top 100 percent emp_id,e_name, birthday, hire_date, year(getdate())
- year(hire_date) as work_years
from dbo.employee
order by work_years desc
create unique clustered
index emp_id_view on dbo.work_years (emp_id)
8.2.2 用Enterprise Manager 创建索引。
在Enterprise Manager 中创建索引有两种方法
1 用索引创建向导创建索引
创建方法如下：
（1） 在目录树中选择要创建索引的表所在的数据库，点击任务板中的向导页面
“Wizards”，出现如图8-2 所示的选择向导界面。
（2） 单击任务板中的“Create an Index” 链接，出现如图8-3 所示的索引创建向导界面。也可以从“Tools ”菜单中选择“izards” 菜单项，则出现如图5-17 所示的选择SQL Server 向导对话框，再从树型目录中选择“Database”下的“Create Index Wizard”选项，也会出现如图8-3 所示的索引创建向导界面。
（3） 单击“下一步”按钮，出现如图8-4 所示的界面，从中选择要创建索引的表，及其所属的数据库。
（4） 单击“下一步”按钮，出现如图8-5 所示的界面，它显示了所选择的表中已经存在的索引的信息。如果表还没有创建过索引，则不会出现此界面，而直接出现如图8-6 所示的界面。
（5） 单击“下一步”按钮，出现如图8-6 所示的界面，它列出了表中的所有列的信息，从中选择创建索引的列。
（6） 单击“下一步”按钮，出现如图8-7 所示的界面，它列出了可供选择的索引设置选项（有关索引选项的设置，请参考前面讲述的CREATE INDEX 命令）。
（7） 单击“下一步”按钮，出现如图8-8 所示的完成索引创建界面，在此可以指定所创建索引的名称，还可以调整组成索引的列的顺序。单击“完成”按钮，结束索引创建过程。系统会弹出一个创建索引成功信息对话框。
2 直接创建索引
选择要创建索引的表，单击右键，从快捷菜单中选择“所有任务（All Tasks）”子菜单中的“Manage Indexes”选项，将会出现如图8-9 所示的索引管理对话框，其中列出了表中已经存在的索引。选择“New” 按钮，进入如图8-10 所示的创建索引对话框。在图8-10中，输入要创建的索引的名称，再选择用于创建索引的列，并设置索引的各种选项（有关索引选项的设置，请参考前面讲述的CREATE INDEX 命令）。单击“OK” 按钮，完成索引的创建。
8.3.1 用Enterprise Manager 查看、修改索引
在Enterprise Manager 中选择要查看的数据库，然后在任务板中选择“Tables & Indexes”页框，则会在任务板中显示此数据库的所有用户表的索引信息，如图8-11 所示。
要查看并修改索引的详细信息，需要在所要查看的表上单击右键，从快捷菜单中选择 “All Tasks”子菜单中的“Manage Indexes” 选项，将出现如图8-9 所示的索引管理对话框。选择要查看或修改的索引，单击“Edit” 按钮，出现如图8-12 所示的修改索引对话框。
在图8-12 所示的修改索引对话框中，可以修改索引的大部分设置，还可以直接修改其SQL 脚本，只需按下“Edit SQL… ”按钮，即可显示如图8-13 所示的SQL 脚本编辑框。可以在其中编辑、测试和运行索引的SQL 脚本。
但在图8-13 的索引的SQL 脚本编辑对话框中不能修改索引的名称，程序方式的索引名称修改需要使用系统存储过程Sp_rename 。要在企业管理器中修改索引的名称，改变其所属文件组等其它信息，则需要在表的属性对话框中进行，如图8-14 所示。应注意，图 8-14 所示的属性对话框是从图7-4 所示的修改表结构对话框中调用的，而不是直接通过快捷菜单的“属性”菜单项调用。
8.3.2 用存储过程Sp_helpindex 查看索引
Sp_helpindex 存储过程可以返回表的所有索引的信息。其语法如下：
sp_helpindex [@objname =] 'name'
其中[@objname =] 'name'子句指定当前数据库中的表的名称。
例8-4： 查看表orders 的索引。
exec sp_helpindex orders
运行结果如下
8.3.3：用存储过程Sp_rename 更改索引名称
例8-5 更改orders 表中的索引orders_quan 名称为orders_quantity。
exec sp_rename 'orders.[orders_quan]', 'orders_quantity', 'index'
运行结果如下：
------------------------------------------------------------------------------------------------------------------------
Caution: Changing any part of an object name could break scripts and stored procedures.
The index was renamed to 'orders_quantity'.
8.4.1 用Enterprise Manager 删除索引
在Enterprise Manager 中可以从如图8-9 所示的索引管理对话框或如图8-14 所示的表的属性对话框中选择要删除的索引，再选“Delete” 按钮来删除索引。
8.4.2 用DROP INDEX 命令删除索引
DROP INDEX 命令可以删除一个或多个当前数据库中的索引。其语法如下：
DROP INDEX 'tablename.indexname' [,...n]
DROP INDEX 命令不能删除由CREATE TABLE 或ALTER TABLE 命令创建的PRIMARY KEY 或UNIQUE 约束索引。也不能删除系统表中的索引。
例8-6： 删除表products 中的索引p_quantity。
drop index products1.p_quantity
8.5.1 索引与系统性能
索引可以加快数据检索的速度，但它会使数据的插入、删除和更新变慢。尤其是簇索引，数据是按照逻辑顺序存放在一定的物理位置，当变更数据时，根据新的数据顺序，需要将许多数据进行物理位置的移动，这将增加系统的负担。对非簇索引，数据更新时也需要更新索引页，这也需要占用系统时间。因此在一个表中使用太多的索引，会影响数据库的性能。对于一个经常会改变的表，应该尽量限制表只使用一个簇索引和不超过3~4 个非簇索引。对事务处理特别繁重的表，其索引应尽量不超过3 个。
8.5.2 索引调整向导（Index Tuning Wizard）
索引调整向导可以帮助选择并创建一个最优化的索引集合，以提高数据库的性能。
要使用索引调整向导需要一个工作负荷记录（Workload）。 工作负荷记录由SQL 脚本或SQL Server Profiler 创建的存储在文件或表中的跟踪组成。如果没有现存的针对要进行索引调整的数据库或表的工作负荷记录，可以通过SQL Server Profiler 来创建一个（其具体方法请参见第19 章中SQL Server Profiler 的用法）。可以用Sample 1 – TSQL 跟踪定义来创建或新建一个跟踪。索引调整向导可以使用查询优化器根据工作负荷记录分析索引的性能，并提出相应的调整建议。可以立即让系统根据建议修改索引，也可以将任务列入计划以后再创建。
8.5.3 使用索引调整向导：
使用索引调整向导的步骤如下
（1） 从“Tools” 菜单中选择“Wizards” 选项，出现如图8-15 所示的选择向导界面。
（2） 从树型目录中选择“Management” 下的“Index Tuning Wizard” 选项，出现如图8-16 所示的索引调整向导界面。
（3） 单击“下一步”按钮，出现如图8-17 所示的选择服务器和数据库对话框。在此选择要进行索引调整的数据库。各选项含义如下：
Keep all existing indexes
保留已经存在的索引。如果不选择此选项，在进行索引优化时可能将删除一些索引。
Perform thorough analysis
对工作负荷记录进行彻底地分析。
（4） 单击“下一步”按钮，出现如图8-18 所示的指定工作负荷记录文件对话框。在此选择要进行索引调整的数据库的工作负荷记录文件。单击“Advanced Options…”按钮，出现如图8-19 所示的修改缺省调整参数对话框，可以查看或修改调整索引的参数设置；索引中列的最大数目，提供给推荐索引的最大空间、最大的调整查询测试次数。
（5） 单击“下一步”按钮，出现如图8-20 所示的指定进行索引调整的表对话框。在此选择要进行索引调整的数据库中的一个或多个表
（6） 单击“下一步”按钮，出现如图8-21 所示的推荐索引调整对话框，在此选择系统推荐的要进行索引调整的索引，将其列入调整计划。
（7） 单击“下一步”按钮，出现如图8-22 所示的结束索引调整对话框。至此，索引调整向导执行完毕。单击“完成”按钮结束向导。
