定义 SQL Server 2005 中的索引

简介  

大家好，在这篇文章中，我将尝试解释“如何在 SQL Server 中定义索引”。 对于本文，我将使用 Northwind 数据库的 Products 表。

本文内容包括：

• 查询优化器 
• 创建索引
• 创建唯一索引
• 创建聚集索引
• 创建全文索引
• 更改索引属性
• 重命名索引
• 删除索引
• 指定索引的填充因子
• 创建 XML 索引
• 删除 XML 索引
• 索引的优点
• 索引的缺点
• 索引指南

解释

每个组织都有自己的数据库，随着数据量的日益增加，这些组织每天都要面对数据检索和访问相关的问题。需要一个系统来提高数据访问速度。索引（简单来说，就像任何书的索引一样，例如，在书中搜索一个词时，我们使用书后面的索引来查找该词的出现及其相关的页码），这使得我们能够更轻松地检索和呈现数据。索引是一个系统，可提供对行的更快访问并强制执行约束。

如果我们不创建任何索引，SQL 引擎将搜索表中的每一行（也称为表扫描）。当表数据增长到数千、数百万行甚至更多时，不使用索引的搜索将变得非常缓慢且昂贵。

例如。以下查询从 Northwind 数据库的 Customers 表中检索国家为 USA 的客户信息。

SELECT CustomerID,ContactName,CompanyName,City
FROM Customers
WHERE Country ='USA'

由于该表没有索引，数据库引擎会执行表扫描，并读取每一行以检查 Country 是否为“USA”。查询结果如下所示。数据库引擎扫描了 91 行，找到了 13 行。

索引支持数据库引擎。适当的索引始终能显著提高应用程序的性能并节省时间，反之亦然。当 SQL Server 处理查询时，它会使用索引来查找数据。索引可以创建在一个或多个列上，也可以创建在 XML 列上。我们可以通过选择表中将被搜索的一个或多个列来创建索引。索引创建与使用一个或多个列创建的表/视图和约束相关的模型。它更像一个平衡二叉树。这有助于 SQL Server 查找具有指定键的行。

索引可以是聚集索引或非聚集索引。

聚集索引 

每个表只能有一个聚集索引，因为索引是基于唯一键列构建的，并且数据行中的键值是唯一的。它根据键值存储表中的数据行。拥有聚集索引的表也称为聚集表。

非聚集索引

它的结构与数据行不同。非聚集索引的键值用于指向包含键值的行数据。这个值称为行定位符。数据页的存储类型决定了该行定位符的结构。如果数据页存储为堆，则行定位符会成为指针。同样，如果数据页存储在聚集表中，则行定位符会成为聚集索引键。

这两者都可以是唯一的。无论何时更改数据表，索引的管理都会自动完成。

SQL Server 允许我们通过传递当前索引键限制在非聚集索引的叶节点添加非键列，并执行完全覆盖的索引查询。

每当我们为主键、唯一键约束创建表时，都会自动创建索引。

查询优化器

查询优化器使用索引来减少我们在查询数据时产生的磁盘输入/输出操作和系统资源使用。数据操作查询语句（如 SELECT、DELETE 或 UPDATE）需要索引来最大化性能。当查询执行时，会在可用方法中评估检索数据的最高效方法。它使用表扫描或索引扫描。

表扫描需要许多输入/输出操作，它也使用大量资源，因为要扫描表中的所有行。

索引扫描用于搜索索引键列以查找存储位置。
包含较少列的索引可以加快查询执行速度，反之亦然。

创建索引

• 从对象资源管理器连接到 Northwind 数据库，右键单击 **Customers** 表以创建索引，然后单击 **Modify**（修改）。

• 在顶部菜单的“**表设计器**”中单击“**索引/键**”，或者右键单击任何列并单击“**索引/键**”。

• 在“**索引/键**”对话框中单击“**添加**”。

• 在“选定的**主键/唯一键或索引列表**”中，选择新索引，并在右侧的网格中设置索引的属性。

• 现在，如果需要，请指定索引的其他设置，然后单击“**关闭**”。
• 当我们保存表时，索引会在数据库中创建。

我们也可以使用查询来创建此索引。此命令提到了索引的名称（Country）、表名（Customers）以及要索引的列（Country）。

 CREATE INDEX Country ON Customers (Country) 

创建唯一索引

SQL Server 允许我们为唯一标识列（如员工的参考 ID、电子邮件 ID 等）创建唯一索引。我们使用一组列来创建唯一索引。

• 在“对象资源管理器”中右键单击 Customers，然后单击 **Modify**（修改）。
• 现在，在“**表设计器**”菜单中单击“**索引/键**”，然后单击“**添加**”。
• “选定的主键/唯一键或索引列表”会显示新索引的自动生成名称。
• 在网格中，单击“**类型**”，然后从下拉列表中选择“**索引**”。
• 在“列名”下，我们可以选择要索引的列，然后单击“确定”。最多可以设置 16 列。为了获得最佳性能，建议每个索引使用一到两列。对于每一列，这些列的值都按升序或降序排列。
• 在网格中，单击“Is **Unique**”（是否唯一），然后选择“**Yes**”（是）。

• Null 被视为重复值。因此，如果一个列包含多个 Null 值，则无法在该列上创建唯一索引。同样，如果多列在同一行中包含 Null，则无法在这些列上创建索引。
• 现在选择“忽略重复键”选项。如果需要忽略会导致在新索引中创建重复键的新数据或更新数据（通过 INSERT 或 UPDATE 语句）。
• 当我们保存表时，索引会在数据库中创建。

我们也可以使用查询来创建此索引。此命令提到了索引的名称（ContactName）、表名（Customers）以及要索引的列（CompanyName,ContactName）。

CREATE UNIQUE INDEX ContactName ON Customers (CompanyName,ContactName) 

创建聚集索引

表只能有一个聚集索引。在聚集索引中，索引键值的逻辑顺序和物理顺序与表中的行相同。

• 在“**对象资源管理器**”中，单击 Northwind 数据库，右键单击 Customers 以创建索引，然后单击 **Modify**（修改）。
• 现在，我们有了该表的“**表设计器**”。
• 在“表设计器”菜单中，单击“**索引/键**”，然后在“索引/键”对话框中单击“添加”。
• 现在，在“选定的**主键/唯一键或索引列表**”中，选择新索引。
• 在网格中，选择“创建为**聚集**”，然后从属性右侧的下拉列表中选择“是”。

• 当我们保存表时，索引会在数据库中创建。

我们也可以使用查询来创建此索引。此命令提到了索引的名称（PK_Customers）、表名（Customers）以及要索引的列（CustomerID）。

CREATE CLUSTERED INDEX PK_Customers on Customers(CustomerID)

创建全文搜索 

对于基于文本的列，在许多情况下都需要执行全文搜索。在这种情况下，将使用全文索引。在创建全文索引之前，需要准备一个常规索引，因为后者依赖于前者。常规索引在非空单列上创建。建议在具有小值的列上创建常规索引。在许多情况下，SQL Server Management Studio 也用于创建目录。

• 在“**对象资源管理器**”中，单击 Northwind 数据库，右键单击 Customers 以创建索引，然后单击 **Modify**（修改）。
• 现在，我们有了 Customers 表的“**表设计器**”，然后从“表设计器”菜单中单击“**全文索引**”。

• “**全文索引**”对话框将打开。（有时数据库未启用全文索引。在这种情况下，“添加”按钮将禁用。要启用它，请右键单击数据库检查数据库的属性。并勾选“全文索引”复选框）
• 现在，我们需要右键单击“**存储**”>“**新建全文目录**”来创建一个“**目录**”。在对话框中输入一些所需信息。

• 现在，从“**表设计器**”菜单中，打开“**全文索引属性**”对话框，然后单击“**添加**”。
• 现在，从“选定的全文索引列表”中选择新索引，并在网格中为索引分配属性。

• 当我们保存表时，索引会自动保存在数据库中，并且此索引可用于修改。

更改索引属性

• 连接到 SQL Server 2005，在对象资源管理器中单击 Northwind 数据库。
• 在表设计器菜单中单击“索引/键”。
• 现在，从“选定的主键/唯一键或索引列表”中选择索引。并更改属性。
• 当我们保存表时，索引会自动保存在数据库中。

重命名索引

• 在对象资源管理器中，右键单击要重命名索引的表，然后单击 Modify（修改）。
• 在“表设计器”菜单中单击“索引/键”。
• 现在，从“选定的主键/唯一键或索引列表”中选择索引。
• 在网格中，单击“名称”，然后在文本框中键入新名称。
• 当我们保存表时，索引会自动保存在数据库中。

我们也可以使用 sp_rename 存储过程重命名索引。sp_rename 过程至少需要对象的当前名称和对象的新名称。重命名索引时，当前名称必须包括表名、一个点分隔符和索引名，如下所示。

EXEC sp_rename 'Customers.Country', 'Countries'

删除索引 

• 在对象资源管理器中，右键单击要删除索引的表，然后单击 **Modify**（修改）。
• 在“**表设计器**”菜单中单击“**索引/键**”。 
• 在“**索引/键**”对话框中选择要删除的索引，然后单击“**删除**”。
• 当我们保存表时，索引将从数据库中删除。

我们可以遵循相同的过程删除全文索引。在表设计器中，选择全文索引，然后选择索引名称，并单击删除。

从数据库中移除不那么有价值的索引是非常明智的。例如，如果我们知道查询不再搜索特定列的记录，我们可以删除该索引。不必要的索引只会占用存储空间并降低 SQL 命令的显示效果，如下所示。

DROP Index Customers.Country  

指定填充因子

填充因子由 SQL Server 用于指定每个索引页的填充程度。填充因子是分配给索引的可用空间的百分比。我们可以指定要填充的空间量。这一点非常重要，因为选择不当可能会降低性能。

• 在对象资源管理器中，右键单击要指定**填充因子**的索引所在的表，然后单击 **Modify**（修改）。
• 在“**表设计器**”菜单中单击“**索引/键**”。
• 在“选定的**主键/唯一键或索引列表**”中，选择索引。
• 在“填充因子”框中键入一个 0 到 100 之间的数字。值 100 表示索引将完全填充，存储空间需求将最小，这在数据变化的可能性很小的情况下是推荐的。如果数据有常规修改和添加，则将此值设置得更低。存储空间与设定的值成正比。

创建 XML 索引

创建 XML 索引的方式有所不同，我们无法使用“索引/键”对话框创建 XML。我们从基于主 XML 索引的 XML 数据类型列创建 XML 索引。当我们删除主 XML 索引时，所有 XML 索引都会被删除。

• 在“**对象资源管理器**”中，右键单击 customers 表以创建 XML 索引，然后单击 **Modify**（修改）。

• 在“**表设计器**”中打开的表中，选择要为索引创建的** XML 列**。
• 在“表设计器”菜单中，单击“XML 索引”，
• 在“XML 索引”对话框中单击“**添加**”，

删除 XML 索引 

• 在对象资源管理器中，右键单击要删除** XML 索引**的 customers 表，然后单击 **Modify**（修改）。
• 在“**表设计器**”菜单中单击“**XML 索引**”。
• 从“选定的** XML 索引**列”中，单击要删除的索引。然后单击“**删除**”。

查看现有索引

我们可以在用于创建索引的对话框中查看表中所有索引的列表。只需单击“选定的索引”下拉控件，然后滚动浏览可用索引。

我们可以使用一个名为 sp_helpindex 的存储过程。此存储过程提供了一个表中所有索引及其所有相关属性。该过程唯一的输入参数是表名，如下所示。

EXEC sp_helpindex Customers

索引的工作原理 

CREATE INDEX 命令中指定的列由数据库引擎获取，并在平衡二叉树（B-Tree）数据结构中进行排序。B-Tree 结构支持以最少的磁盘读取进行快速搜索，并允许数据库引擎为指定的查询找到快速的起始点和结束点。

数据库获取 CREATE INDEX 命令中指定的列，并将值排序到一个称为 B 树的特殊数据结构中。B 树结构支持快速搜索，只需最少的磁盘读取，从而使数据库引擎能够快速找到我们正在使用的查询的起始和结束点。

概念上，每个索引条目都有一个索引键。每个条目还包含一个对具有该特定值的表行的引用，从中我们可以检索所需信息。

这与书的背面非常相似，可以帮助我们快速查找关键词，因此数据库可以使用存储在索引中的已排序键值列表来快速将它必须检查的记录数量缩小到最少。这样，我们就避免了表扫描来获取查询结果。以下是一些索引提供优势的场景。索引的优点

搜索记录  

索引最重要的用途是在查找与 WHERE 子句匹配的单个记录或一组记录。索引可以帮助具有特定范围的查询，以及查找特定值的查询。例如，以下查询都可以受益于 UnitPrice 上的索引

DELETE FROM Customers WHERE Country = "USA"
 UPDATE Customers SET Region = "Pacific" WHERE Country = "USA" 
 SELECT * FROM Customers WHERE Country="USA" or "Brasil"

索引在 DELETE 和 UPDATE 命令中搜索记录时效果很好，就像它们在 SELECT 语句中一样。

排序记录

当我们要求排序结果时，数据库会尝试查找索引，并在查询执行时避免排序结果。我们通过在 ORDER BY 子句中指定字段或字段，以及升序 (ASC) 或降序 (DESC) 的排序顺序来控制数据集的排序。例如，下面的查询返回所有按国家排序的客户

SELECT * FROM Customers ORDER BY Country ASC

当没有索引时，数据库将扫描 Customers 表，然后对行进行排序以处理查询。但是，我们之前在 Country（Country）上创建的索引将为数据库提供一个已排序的国家列表。数据库可以简单地从第一个记录扫描到最后一个记录，并按排序顺序检索行。相同的索引对以下查询也有效，它只需反向扫描索引。

SELECT * FROM Customers ORDER BY Country DESC

;

分组记录

我们可以使用 GROUP BY 子句对记录进行分组并聚合值，例如，计算一个国家/地区的客户数量。为了处理带有 GROUP BY 子句的查询，数据库通常会按 GROUP BY 中包含的列对结果进行排序。下面的查询计算每个国家/地区拥有相同 UnitPrice 值的客户数量。

SELECT Count(*) FROM Products GROUP BY UnitPrice

索引的缺点

索引存在一些缺点。虽然索引为搜索提供了显著的性能优势，但索引也有其缺点。

索引和磁盘空间

索引存储在磁盘上，所需的空间量取决于表的大小，以及索引中使用的列的数量和类型。磁盘空间通常足够便宜，可以用来换取应用程序性能，尤其是在数据库为大量用户服务时。要查看表所需的空间，请在查询窗口中使用 sp_spaceused 系统存储过程。 EXEC sp_spaceused Customers  

结果

name                rows        reserved           data               index_size         unused
---------        ----------- ------------------ ------------------ ------------------ 
Customers        91          200 KB             24 KB              176 KB             0 KB

从上面的输出可以看出，表数据使用了 24 kb，而表索引使用了大约 18 倍，即 176 kb。索引大小与表大小的比率可能因列、数据类型和表上的索引数量而有很大差异。

索引和数据修改

如果数据频繁更改或修改，数据库引擎需要更新所有索引，因此过多的索引会降低性能。因此，用于事务处理的数据库应使用较少的索引，以允许更高的插入和更新吞吐量。而在 DSS（决策支持系统）和数据仓库中，数据是静态的，查询主要用于报告目的而不是修改目的，那么就需要大量的索引来优化性能。

使用索引的另一个缺点是对数据修改语句的性能影响。任何时候查询修改表中的数据（INSERT、UPDATE 或 DELETE），数据库都需要更新所有数据已更改的索引。如前所述，索引可以通过让数据库快速定位要修改的记录来帮助数据库在数据修改语句期间，但是，现在我们应该注意，提供过多的索引进行更新实际上会损害数据修改的性能。这就需要在调整数据库性能时进行精妙的权衡。

其他索引指南

为了创建有效的索引，选择正确的列和类型非常重要。

保持索引键简短

数据库引擎处理更长的索引键会更困难。例如，整数键的存储空间比存储 100 个字符的字符字段小。尽量使聚集索引保持尽可能短。

我们应尽量避免在索引中使用字符列，尤其是主键索引。在提高查询性能方面，整数列始终比字符字段具有优势。

不同的索引键

重复值百分比较小的索引总是有效的。

具有高百分比唯一值的索引是选择性索引。显然，唯一索引是最具选择性的索引，因为没有重复值。SQL Server 会跟踪索引的统计信息，并知道每个索引的选择性。查询优化器在选择最佳索引用于查询时会利用这些统计信息。

结论 

在这篇文章中，我试图解释如何为 SQL Server 表创建、管理和选择索引。我所涵盖的大部分内容对于任何关系数据库引擎都是适用的。适当的索引在大型数据库的良好性能中起着至关重要的作用。很难在索引较差的情况下写出最好的查询，同时，我们也可以用良好的索引弥补一个写得不好的查询……