在SQL Server中使用索引的技巧

在SQL Server中，为了查询性能的优化，有时我们就需要对数据表通过建立索引的方式，目的主要是根据查询要求，迅速缩小查询范围，避免全表扫描。

索引有两种类型，分别是聚集索引(clustered index，也称聚类索引、簇集索引)和非聚集索引(nonclustered index，也称非聚类索引、非簇集索引)。

聚集索引在一个表中只能有一个，默认情况下在主键建立的时候创建，它是规定数据在表中的物理存储顺序，我们也可以取消主键的聚集索引，所以必须考虑数据库可能用到的查询类型以及使用的最为频繁的查询类型，对其最常用的一个字段或者多个字段建立聚集索引或者组合的聚集索引，它就是SQL Server会在物理上按升序(默认)或者降序重排数据列，这样就可以迅速的找到被查询的数据。

非聚集索主要是数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储。可以在一个表格中使用高达249个非聚集的索引，在查询的过程中先对非聚集索引进行搜索，找到数据值在表中的位置，然后从该位置直接检索数据。这使非聚集索引成为精确匹配查询的最佳方法，因为索引包含描述查询所搜索的数据值在表中的精确位置的条目。

所以我们在选择创建聚集索引的时候要注意以下几个方面：

1) 对表建立主键时，就会为主键自动添加了聚集索引，如自动编号字段，而我们没有必要把聚集索引浪费在主键上，除非你只按主键查询，所以会把聚集索引设置在按条件查询频率最高的那个字段或者组合的字段。

2) 索引的建立要根据实际应用的需求来进行，并非是在任何字段上建立索引就能提高查询速度。聚集索引建立遵循下面几个原则：

包含大量非重复值的列。

使用下列运算符返回一个范围值的查询：BETWEEN、>、>=、< 和 <=。

被连续访问的列。

返回大型结果集的查询。

经常被使用联接或 GROUP BY 子句的查询访问的列;一般来说，这些是外键列。对ORDER BY 或 GROUP BY 子句中指定的列进行索引，可以使 SQL Server 不必对数据进行排序，因为这些行已经排序。这样可以提高查询性能。

OLTP 类型的应用程序，这些程序要求进行非常快速的单行查找(一般通过主键)。应在主键上创建聚集索引。

举例来说，银行交易日志中对交易日期建立聚合索引，数据物理上按顺序存于数据页上，重复值也排列在一起，因而在范围查找时，可以先找到这个范围的起末点，且只在这个范围内扫描数据页，避免了大范围扫描，提高了查询速度。而如果我们对员工的基本信息表中性别的字段列上建立聚集索引，就完全没有必要，因为内容里只涉及到 &ldquo;男&rdquo;与&ldquo;女&rdquo;两个不同值。

3) 在聚集索引中按常用的组合字段建立索引，形成复合索引，一般在为表建立多个主键的时候就会产生，如果一个表中的数据在查询时有多个字段总是同时出现则这些字段就可以作为复合索引,这样能形成索引覆盖,提高where语句的查询效率。

4)索引对查询有一这的优化，但由于改变一个表的内容，将会引起索引的变化。频繁的对数据操作如insert,update,delete语句将导致系统花费较大的代价进行索引更新，引起整体性能的下降。一般来讲，在对查询性能的要求高于对数据维护性能要求时，应该尽量使用索引，有时在这种操作数据库比较频繁的某些极端情况下，可先删除索引，再对数据库表更新大量数据，最后再重建索引，新建立的索引总是比较好用。

索引在使用了长久的时候，就会产生很多的碎片，查询的性能就会受到影响，这时候有两种方法解决，一是利用DBCC INDEXDEFRAG整理索引碎片，还有就是利用DBCC DBREINDEX重建索引。

DBCC INDEXDEFRAG 命令是联机操作，所以索引只有在该命令正在运行时才可用。而且可以在不丢失已完成工作的情况下中断该操作。这种方法的缺点是在重新组织数据方面没有聚集索引的除去/重新创建操作有效。

重新创建聚集索引将对数据进行重新组织，其结果是使数据页填满。填满程度可以使用 FILLFACTOR 选项进行配置。这种方法的缺点是索引在除去/重新创建周期内为脱机状态，并且操作属原子级。如果中断索引创建，则不会重新创建该索引。

我们来看看索引重建使用的方法：

语法 DBCC DBREINDEX ( [ &lsquo;TableName&rsquo; [ , index_name [ , fillfactor ] ] ] )

参数 &lsquo;TableName&rsquo;

是要重建其指定的索引的表名。数据库、所有者和表名必须符合标识符的规则。有关更多信息，请参见使用标识符。如果提供 database 或 owner 部分，则必须使用单引号 (&lsquo;)将整个 database.owner.table_name 括起来。如果只指定 table_name，则不需要单引号。

index_name 是要重建的索引名。索引名必须符合标识符的规则。如果未指定 index_name 或指定为 &lsquo; &lsquo;，就要对表的所有索引进行重建。

fillfactor 是创建索引时每个索引页上要用于存储数据的空间百分比。fillfactor替换起始填充因子以作为索引或任何其它重建的非聚集索引(因为已重建聚集索引)的新默认值。如果 fillfactor 为 0，DBCC DBREINDEX 在创建索引时将使用指定的起始fillfactor。

我们在查询分析器中输入如下的命令：

DBCC DBREINDEX (&lsquo;MyTable,&rsquo;&rsquo;,80)

这样就会索引重建了。