设计有用的索引是改善查询性能最重要的方面之一，它要求理解索引的结构及理解数据是如何使用的。

数据

在创建索引前，必须对数据有全面的了解，包括以下内容：
·逻辑和物理设计
·数据特征
·如何使用数据
为了设计有用和有效索引，必须依赖于对用户所发出查询的分析。对用户如何访问数据的不准确分析会导致查询响应慢或不必要的表锁定。通过以下观察应该知道用户如何访问数据：
·所执行的查询类型
·通常执行的查询频率
全面了解用户的数据要求有助于确定对哪些列进行索引和创建哪一类的索引。可能需要牺牲某个查询的速度以使另一个查询获得较好的查询性能。

使用索引的指导原则

事务环境、数据特征和数据的使用决定了要指定创建索引的列。索引的有用性直接与查询返回行的比例有关，低比例或高选择性是比较有效的。

(1).要索引的列

在经常要搜索的列上创建索引，例如：
·主键所在的列
·外键或在联接表中经常使用的列
·搜索键值范围的列
·以排序次序访问的列
·在聚合过程中被划分为一组的列

(2).不需要索引的列

在以下情况不需要索引列：
·在查询中很少引用的列
·包含很少惟一值的列。例如，在具有两个值-男和女的列上的索引，返回行在所有行中占了较高的百分比。
·由text、ntext或image数据类型定义的列，具有这些数据类型的列不能进行索引。

选择合适的聚集索引

在为每个表选择聚集索引时，需考虑表是如何使用的。

(1).经常要更新的表

当优化向经常使用的表插入数据这一操作的性能时，可以考虑在主键标识列上创建聚集索引。通过强制将数据插入表末尾处的一小组页面中，可以加快速度。频繁地访问可以使这些页保存在内存中。

(2).排序

时常因报表而排序的表、因聚合而分组的表或搜索特定范围数据的表，在排序的列上建立聚集索引是很有好处的。当需要返回表的　很多列并且非聚集索引又不起作用时，使用聚集索引很有用处。例如，邮件列表以邮编建立聚集索引就很有用，因为必须打印并以指定的顺序应用邮件的标签。

(3).列的长度和数据类型

SQL Server使用聚集索引值作为每个非聚集索引中的行标识符。聚集索引值可以在表结构中重复使用很多次。
为了避免因为大的聚集索引而使与它们相关的非聚集索引变得更大或更慢，可以执行以下操作：
·限制在聚集索引中列的数目
·通过使用varchar数据类型来代替char数据类型，以减少字符的平均数
·尽可能地使用最小的数据类型，例如用tinyint代替int

使用索引以支持查询

查询的性能取决于你的索引设计的好坏。另外，编写带有能有效地利用索引列的搜索参数的查询也很重要。

(1).使用搜索参数

搜索参数可以限制精确匹配的搜索、范围值的搜索，也可以限制通过AND运算符连接起来的两个或多个条目的组合的搜索。搜索参数包括一个常数表达式，该表达式通过使用操作符作用于列。在编写包括有搜索参数的查询时，可以增加查询优化器使用索引的机会。

(2).编写较好的搜索参数

如果表达式没有限制搜索，就可以认为该表达式没有搜索参数。很多情况下，可以重新编写查询，把没有搜索参数的表达式变为带有搜索参数的表达式。
为了限制搜索，必须执行以下操作：
·在查询中指定Where子句
·检验Where子句是否限制了行的数目
·检验在每个引用该查询的表中是否都存在表达式
·避免使用放在前面的通配符
下边显示了有效的搜索参数：


决定选择性

在讨论索引时，经常使用的概念和术语是选择性。在确定对哪些列进行索引和选择要创建的索引类型时，必须考虑数据值的可选择性如何。

(1).定义选择性

选择性是从通过查询访问或返回的行数所占从表中的行的百分比衍生而来的。查询优化器能够确定Select、Update和Delete语句的选择性。创建索引时，应该把它们创建在：
·联接操作或Where子句经常引用的列上
·具有高选择性的数据上
在高选择性中，搜索标准限制了返回的行数在所有行中占有比较低的百分比。返回一条记录可获得最高的选择性。
在低选择性中，按搜索标准将返回的行在表中所有行中占有比较高的百分比。

(2).评估选择性

通过评估某个表中的特定查询所返回的行的数目与表中行的总数目的关系，可以确定查询的选择性。

创建组合索引

组合索引指定多个列作为键值。在下面两种情况下，可以创建组合索引：
·当两列或多列最适合作为一个键进行搜索时
·如果查询仅引用索引中的列时
例如，电话号码目录是使用组合索引的最好例子，该目录是按姓氏组织的，在姓氏中，又是按名组织的，因为经常存在具有相同姓氏的记录。
在创建组合索引时，需要考虑以下事项和原则：
·最多可以有16个列组合到一个组合索引中。构成组合索引的列的总长度 不能超过900字节
·在组合索引中的所有列都必须来自于同一个表，除非该索引在视图上创建
·先定义惟一性最高的列。在Create INDEX语句中定义的第一列称作最高顺序
·为了让查询优化器使用组合索引，必须在查询的Where子句中引用组合索引的第一列
·在(column1，column2)上的索引与在(column2，column1)上的索引不同-每一个索引都有不同的列序。有些列包含较高选择性数据或返回行的百分比最低，这样的列通常决定了列的顺序。
·对具有多个列作为键的表来说，组合索引是很有用的
·使用组合索引可以增强查询性能，并减少在表上创建的索引数目
·在同一列上的多个索引通常没有多大用处

文章出处

[1].《数据库程序设计-SQL Server2000 数据库程序设计》微软公司著,高等教育出版社,P326-P329,P340

该文章在 2011/3/13 0:19:40 编辑过