在SQLServer中使用索引的技巧

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

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

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

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

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

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

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

包含大量非重复值的列

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

被连续访问的列

返回大型结果集的查询

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

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

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

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

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

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

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

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

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

语法 DBCC DBREINDEX ( [ TableName [ index_name [ fillfactor ] ] ] )

参数 TableName

是要重建其指定的索引的表名 数据库 所有者和表名必须符合标识符的规则 有关更多信息 请参见使用标识符 如果提供 database 或 owner 部分 则必须使用单引号 ( )

将整个 database owner table_name 括起来 如果只指定 table_name 则不需要单引号

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

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

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

DBCC DBREINDEX ( MyTable )

lishixinzhi/Article/program/SQLServer/201311/22210

--创建索引

CREATE NONCLUSTERED INDEX IX_TEST_TNAME --创建一个非聚集索引

ON TEST(TNAME) --为TEST表的TNAME字段创建索引

WITH FILLFACTOR = 30 --填充因子为30%

GO

SELECT * FROM TEST(INDEX = IX_TEST_TNAME) WHERE TNAME = 'A' --指定按‘IX_TE

什么是索引

拿汉语字典的目录页（索引）打比方：正如汉语字典中的汉字按页存放一样，SQL Server中的数据记录也是按页存放的，每页容量一般为4K 。为了加快查找的速度，汉语字（词）典一般都有按拼音、笔画、偏旁部首等排序的目录（索引），我们可以选择按拼音或笔画查找方式，快速查找到需要的字（词）。

同理，SQL Server允许用户在表中创建索引，指定按某列预先排序，从而大大提高查询速度。

• SQL Server中的数据也是按页（ 4KB ）存放

• 索引：是SQL Server编排数据的内部方法。它为SQL Server提供一种方法来编排查询数据。

• 索引页：数据库中存储索引的数据页；索引页类似于汉语字（词）典中按拼音或笔画排序的目录页。

• 索引的作用：通过使用索引，可以大大提高数据库的检索速度，改善数据库性能。

索引类型

• 唯一索引：唯一索引不允许两行具有相同的索引值

• 主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空

• 聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个

• 非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个

索引类型：再次用汉语字典打比方，希望大家能够明白聚集索引和非聚集索引这两个概念。

唯一索引：

唯一索引不允许两行具有相同的索引值。

如果现有数据中存在重复的键值，则大多数数据库都不允许将新创建的唯一索引与表一起保存。当新数据将使表中的键值重复时，数据库也拒绝接受此数据。例如，如果在stuInfo表中的学员员身份z号(stuID) 列上创建了唯一索引，则所有学员的身份z号不能重复。

提示：创建了唯一约束，将自动创建唯一索引。尽管唯一索引有助于找到信息，但为了获得最佳性能，建议使用主键约束或唯一约束。

主键索引：

在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时，它还允许快速访问数据。

聚集索引（clustered index）

在聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。表只能包含一个聚集索引。例如：汉语字（词）典默认按拼音排序编排字典中的每页页码。拼音字母a，b，c，d……x，y，z就是索引的逻辑顺序，而页码1，2，3……就是物理顺序。默认按拼音排序的字典，其索引顺序和逻辑顺序是一致的。即拼音顺序较后的字（词）对应的页码也较大。如拼音“ha”对应的字(词)页码就比拼音“ba” 对应的字(词)页码靠后。

非聚集索引(Non-clustered)

如果不是聚集索引，表中各行的物理顺序与键值的逻辑顺序不匹配。聚集索引比非聚集索引（nonclustered index）有更快的数据访问速度。例如，按笔画排序的索引就是非聚集索引，“1”画的字（词）对应的页码可能比“3”画的字（词）对应的页码大（靠后）。

提示：SQL Server中，一个表只能创建1个聚集索引，多个非聚集索引。设置某列为主键，该列就默认为聚集索引

如何创建索引

使用T-SQL语句创建索引的语法：

CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]

INDEX index_name

ON table_name (column_name…)

[WITH FILLFACTOR=x]

q UNIQUE表示唯一索引，可选

q CLUSTERED、NONCLUSTERED表示聚集索引还是非聚集索引，可选

q FILLFACTOR表示填充因子，指定一个0到100之间的值，该值指示索引页填满的空间所占的百分比

在stuMarks表的writtenExam列创建索引：

USE stuDB

GO

IF EXISTS (SELECT name FROM sysindexes

WHERE name = 'IX_writtenExam')

DROP INDEX stuMarks.IX_writtenExam

/*--笔试列创建非聚集索引：填充因子为30％--*/

CREATE NONCLUSTERED INDEX IX_writtenExam

ON stuMarks(writtenExam)

WITH FILLFACTOR= 30

GO

/*-----指定按索引 IX_writtenExam 查询----*/

SELECT * FROM stuMarks (INDEX=IX_writtenExam)

WHERE writtenExam BETWEEN 60 AND 90

虽然我们可以指定SQL Server按哪个索引进行数据查询，但一般不需要我们人工指定。SQL Server将会根据我们创建的索引，自动优化查询。

索引的优缺点

• 优点

– 加快访问速度

– 加强行的唯一性

• 缺点

– 带索引的表在数据库中需要更多的存储空间

– *** 纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新

创建索引的指导原则

• 请按照下列标准选择建立索引的列。

– 该列用于频繁搜索

– 该列用于对数据进行排序

• 请不要使用下面的列创建索引：

– 列中仅包含几个不同的值。

– 表中仅包含几行。为小型表创建索引可能不太划算，因为SQL Server在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长

---