在SQL Server中为了查询性能的优化有时我们就需要对数据表通过建立索引的方式目的主要是根据查询要求迅速缩小查询范围避免全表扫描
索引有两种类型分别是聚集索引(clustered index也称聚类索引簇集索引)和非聚集索引(nonclustered index也称非聚类索引非簇集索引)
聚集索引在一个表中只能有一个默认情况下在主键建立的时候创建它是规定数据在表中的物理存储顺序我们也可以取消主键的聚集索引所以必须考虑数据库可能用到的查询类型以及使用的最为频繁的查询类型对其最常用的一个字段或者多个字段建立聚集索引或者组合的聚集索引它就是SQL Server会在物理上按升序(默认)或者降序重排数据列这样就可以迅速的找到被查询的数据
非聚集索主要是数据存储在一个地方索引存储在另一个地方索引带有指针指向数据的存储位置索引中的项目按索引键值的顺序存储而表中的信息按另一种顺序存储可以在一个表格中使用高达个非聚集的索引在查询的过程中先对非聚集索引进行搜索找到数据值在表中的位置然后从该位置直接检索数据这使非聚集索引成为精确匹配查询的最佳方法因为索引包含描述查询所搜索的数据值在表中的精确位置的条目
所以我们在选择创建聚集索引的时候要注意以下几个方面
) 对表建立主键时就会为主键自动添加了聚集索引如自动编号字段而我们没有必要把聚集索引浪费在主键上除非你只按主键查询所以会把聚集索引设置在按条件查询频率最高的那个字段或者组合的字段
) 索引的建立要根据实际应用的需求来进行并非是在任何字段上建立索引就能提高查询速度聚集索引建立遵循下面几个原则
包含大量非重复值的列
使用下列运算符返回一个范围值的查询BETWEEN>>=< 和 <=
被连续访问的列
返回大型结果集的查询
经常被使用联接或 GROUP BY 子句的查询访问的列;一般来说这些是外键列对ORDER BY 或 GROUP BY 子句中指定的列进行索引可以使 SQL Server 不必对数据进行排序因为这些行已经排序这样可以提高查询性能
OLTP 类型的应用程序这些程序要求进行非常快速的单行查找(一般通过主键)应在主键上创建聚集索引
举例来说银行交易日志中对交易日期建立聚合索引数据物理上按顺序存于数据页上重复值也排列在一起因而在范围查找时可以先找到这个范围的起末点且只在这个范围内扫描数据页避免了大范围扫描提高了查询速度而如果我们对员工的基本信息表中性别的字段列上建立聚集索引就完全没有必要因为内容里只涉及到 男与女两个不同值
) 在聚集索引中按常用的组合字段建立索引形成复合索引一般在为表建立多个主键的时候就会产生如果一个表中的数据在查询时有多个字段总是同时出现则这些字段就可以作为复合索引这样能形成索引覆盖提高where语句的查询效率
)索引对查询有一这的优化但由于改变一个表的内容将会引起索引的变化频繁的对数据操作如insertupdatedelete语句将导致系统花费较大的代价进行索引更新引起整体性能的下降一般来讲在对查询性能的要求高于对数据维护性能要求时应该尽量使用索引有时在这种操作数据库比较频繁的某些极端情况下可先删除索引再对数据库表更新大量数据最后再重建索引新建立的索引总是比较好用
索引在使用了长久的时候就会产生很多的碎片查询的性能就会受到影响这时候有两种方法解决一是利用DBCC INDEXDEFRAG整理索引碎片还有就是利用DBCC DBREINDEX重建索引
DBCC INDEXDEFRAG 命令是联机操作所以索引只有在该命令正在运行时才可用而且可以在不丢失已完成工作的情况下中断该操作这种方法的缺点是在重新组织数据方面没有聚集索引的除去/重新创建操作有效
重新创建聚集索引将对数据进行重新组织其结果是使数据页填满填满程度可以使用 FILLFACTOR 选项进行配置这种方法的缺点是索引在除去/重新创建周期内为脱机状态并且操作属原子级如果中断索引创建则不会重新创建该索引
我们来看看索引重建使用的方法
语法 DBCC DBREINDEX ( [ TableName [ index_name [ fillfactor ] ] ] )
参数 TableName
是要重建其指定的索引的表名数据库所有者和表名必须符合标识符的规则有关更多信息请参见使用标识符如果提供 database 或 owner 部分则必须使用单引号 ()
将整个 databaseownertable_name 括起来如果只指定 table_name则不需要单引号
index_name 是要重建的索引名索引名必须符合标识符的规则如果未指定 index_name 或指定为 就要对表的所有索引进行重建
fillfactor 是创建索引时每个索引页上要用于存储数据的空间百分比fillfactor替换起始填充因子以作为索引或任何其它重建的非聚集索引(因为已重建聚集索引)的新默认值如果 fillfactor 为 DBCC DBREINDEX 在创建索引时将使用指定的起始fillfactor
我们在查询分析器中输入如下的命令
DBCC DBREINDEX (MyTable)
这样就会索引重建了
