sql 索引笔记2

以下资料都来于MSDN。

聚集索引指南：

一。此列和列值供内部使用，用户不能查看或访问。

在创建聚集索引之前，应先了解数据是如何被访问的。考虑对具有以下特点的查询使用聚集索引：

  • 使用运算符（如 BETWEEN、>、>=、< 和 <=）返回一系列值。
    使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行物理相邻。例如，如果某个查询在一系列销售订单号间检索记录，SalesOrderNumber 列的聚集索引可快速定位包含起始销售订单号的行，然后检索表中所有连续的行，直到检索到最后的销售订单号。
  • 返回大型结果集。
  • 使用 JOIN 子句；一般情况下，使用该子句的是外键列。
  • 使用 ORDER BY 或 GROUP BY 子句。
    在 ORDER BY 或 GROUP BY 子句中指定的列的索引，可以使数据库引擎 不必对数据进行排序，因为这些行已经排序。这样可以提高查询性能。

一般情况下，定义聚集索引键时使用的列越少越好。考虑具有下列一个或多个属性的列：

  • 唯一或包含许多不重复的值
    例如，雇员 ID 唯一地标识雇员。EmployeeID 列的聚集索引或 PRIMARY KEY 约束将改善基于雇员 ID 号搜索雇员信息的查询的性能。另外，可对 LastName、FirstName、MiddleName 列创建聚集索引，因为经常以这种方式分组和查询雇员记录，而且这些列的组合还可提供高区分度。
  • 按顺序被访问
    例如，产品 ID 唯一地标识 AdventureWorks 数据库的 Production.Product 表中的产品。在其中指定顺序搜索的查询（如 WHERE ProductID BETWEEN 980 and 999）将从 ProductID 的聚集索引受益。这是因为行将按该键列的排序顺序存储。
  • 由于保证了列在表中是唯一的，所以定义为 IDENTITY。
  • 经常用于对表中检索到的数据进行排序。
    按该列对表进行聚集（即物理排序）是一个好方法，它可以在每次查询该列时节省排序操作的成本。

聚集索引不适用于具有下列属性的列：

  • 频繁更改的列
    这将导致整行移动，因为数据库引擎 必须按物理顺序保留行中的数据值。这一点要特别注意，因为在大容量事务处理系统中数据通常是可变的。
  • 宽键
    宽键是若干列或若干大型列的组合。所有非聚集索引将聚集索引中的键值用作查找键。为同一表定义的任何非聚集索引都将增大许多，这是因为非聚集索引项包含聚集键，同时也包含为此非聚集索引定义的键列。

创建聚集索引时，可指定若干索引选项。因为聚集索引通常都很大，所以应特别注意下列选项：

  • SORT_IN_TEMPDB
  • DROP_EXISTING
  • FILLFACTOR
  • ONLINE

有关详细信息，请参阅设置索引选项。

非聚集索引包含索引键值和指向表数据存储位置的行定位器。有关非聚集索引体系结构的详细信息，请参阅非聚集索引结构。

可以对表或索引视图创建多个非聚集索引。通常，设计非聚集索引是为改善经常使用的、没有建立聚集索引的查询的性能。

与使用书中索引的方式相似，查询优化器在搜索数据值时，先搜索非聚集索引以找到数据值在表中的位置，然后直接从该位置检索数据。这使非聚集索引成为完全匹配查询的最佳选择，因为索引包含说明查询所搜索的数据值在表中的精确位置的项。例如，为了从 HumanResources.Employee 表中查询向特定经理报告的所有雇员，查询优化器可能使用非聚集索引 IX_Employee_ManagerID；它以 ManagerID 作为其键列。查询优化器能快速找出索引中与指定 ManagerID 匹配的所有项。每个索引项都指向表或聚集索引中准确的页和行，其中可以找到相应的数据。在查询优化器在索引中找到所有项之后，它可以直接转到准确的页和行进行数据检索。

设计非聚集索引时需要注意数据库的特征。

  • 更新要求较低但包含大量数据的数据库或表可以从许多非聚集索引中获益从而改善查询性能。
    决策支持系统应用程序和主要包含只读数据的数据库可以从许多非聚集索引中获益。查询优化器具有更多可供选择的索引用来确定最快的访问方法，并且数据库的低更新特征意味着索引维护不会降低性能。
  • 联机事务处理应用程序和包含大量更新表的数据库应避免使用过多的索引。此外，索引应该是窄的，即列越少越好。
    一个表如果建有大量索引会影响 INSERT、UPDATE 和 DELETE 语句的性能，因为所有索引都必须随表中数据的更改进行相应的调整。

在创建非聚集索引之前，应先了解访问数据的方式。考虑对具有以下属性的查询使用非聚集索引：

  • 使用 JOIN 或 GROUP BY 子句。
    应为联接和分组操作中所涉及的列创建多个非聚集索引，为任何外键列创建一个聚集索引。
  • 不返回大型结果集的查询。
  • 包含经常包含在查询的搜索条件（例如返回完全匹配的 WHERE 子句）中的列。

考虑具有以下一个或多个属性的列：

  • 覆盖查询。
    当索引包含查询中的所有列时，性能可以提升。查询优化器可以找到索引内的所有列值；不会访问表或聚集索引数据，这样就减少了磁盘 I/O 操作。使用具有包含列的索引来添加覆盖列，而不是创建宽索引键。有关详细信息，请参阅 具有包含性列的索引。
    如果表有聚集索引，则该聚集索引中定义的列将自动追加到表上每个非聚集索引的末端。这可以生成覆盖查询，而不用在非聚集索引定义中指定聚集索引列。例如，如果一个表在 C 列上有聚集索引，则 B 和 A 列的非聚集索引将具有其自己的键值列 B、A 和 C。
  • 大量非重复值，如姓氏和名字的组合（前提是聚集索引被用于其他列）。
    如果只有很少的非重复值，例如仅有 1 和 0，则大多数查询将不使用索引，因为此时表扫描通常更有效。

在创建非聚集索引时，可以指定若干索引选项。要尤其注意以下选项：

  • FILLFACTOR
  • ONLINE

有关详细信息，请参阅设置索引选项。

创建索引：

要考虑的一个重要因素是对空表还是对包含数据的表创建索引。对空表创建索引在创建索引时不会对性能产生任何影响，而向表中添加数据时，会对性能产生影响。
对大型表创建索引时应仔细计划，这样才不会影响数据库性能。对大型表创建索引的首选方法是先创建聚集索引，然后创建任何非聚集索引。在对现有表创建索引 时，请考虑将 ONLINE 选项设置为 ON。该选项设置为 ON 时，将不持有长期表锁以继续对基础表的查询或更新。有关详细信息，请参阅联机执行索引操作。

脱机（默认设置）创建索引时，直到创建索引事务完成后，才释放基础表的排他锁。在创建索引时，用户不可以访问表。
在 SQL Server 2005 中，可以指定联机创建索引。联机选项设置为 ON 时，在创建索引的过程中，将不持有长期表锁以继续对基础表的查询或更新。虽然建议联机执行索引操作，但您应该对环境和特定要求进行评估。脱机运行索引操作 可能比较好。这样做，用户在操作过程中对数据具有有限的访问权限，但操作会完成得更快且使用的资源更少。有关详细信息，请参阅联机执行索引操作。

posted @ 2014-04-06 19:44  英雄饶命啊  阅读(157)  评论(0)  编辑  收藏  举报