在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

索引是以表列为基础的数据库对象 索引中保存著表中排序的索引列 并且纪录了索引列在数据库表中的物理存储位置 实现了表中数据的逻辑排序 通过索引 可以加快数据的查询速度和减少系统的响应时间可以使表和表之间的连接速度加快

但是 不是在任何时候使用索引都能够达到这种效果 若在不恰当的场合下 使用索引反而会事与愿违 所以 在SQL Server数据库中使用索引的话 还是需要遵守一定的规则 笔者觉得 主要是需要遵守六大铁律

铁律一 天下没有免费的午餐 使用索引是需要付出代价的

索引的优点有目共睹 但是 却很少有人关心过采用索引所需要付出的成本 若数据库管理员能够对索引所需要付出的代价有一个充分的认识 也就不会那么随意到处建立索引了

仔细数数 其实建立索引的代价还是蛮大的 如创建索引和维护索引都需要花费时间与精力 特别是在数据库设计的时候 数据库管理员为表中的哪些字段需要建立索引 要调研 要协调 如当建有索引的表中的纪录又增加 删除 修改 *** 作时 数据库要对索引进行重新调整 虽然这个工作数据库自动会完成 但是 需要消耗服务器的资源 当表中的数据越多 这个消耗的资源也就越多 如索引是数据库中实际存在的对象 所以 每个索引都会占用一定的物理空间 若索引多了 不但会占用大量的物理空间 而且 也会影响到整个数据库的运行性能

可见 数据库管理员若要采用索引来提高系统的性能 自身仍然需要付出不少的代价 数据库管理员现在要考虑的就是如何在这两个之间取得一个均衡 或者说 找到一个回报与投入的临界点

铁律二 对于查询中很少涉及的列或者重复值比较多的列 不要建立索引

在查询的时候 如果我们不按某个字段去查询 则在这个字段上建立索引也是浪费 如现在有一张员工信息表 我们可能按员工编号 员工姓名 或者出身地去查询员工信息 但是 我们往往不会按照身份z号码去查询 虽然这个身份z号码是唯一的 此时 即使在这个字段上建立索引 也不能够提高查询的速度 相反 增加了系统维护时间和占用了系统空间 这简直就是搬起石头砸自己的脚呀

另外 如上面的员工信息表 有些字段重复值比较多 如性别字段主要就是“男” “女”职位字段中也是有限的几个内容 此时 在这些字段上添加索引也不会显著的增加查询速度 减少用户响应时间 相反 因为需要占用空间 反而会降低数据库的整体性能

数据库索引管理中的第二条铁律就是 对于查询中很少涉及的列或者重复值比较多的列 不要建立索引

铁律三 对于按范围查询的列 最好建立索引

在信息化管理系统中 很多时候需要按范围来查询某些交易记录 如在ERP系统中 经常需要查询当月的销售订单与销售出货情况 这就需要按日期范围来查询交易记录 如有时候发现库存不对时 也需要某段时期的库存进出情况 如 月 日到 月 日的库存交易情况等等 此时 也是根据日期来进行查询

对于这些需要在指定范围内快速或者频繁查询的数据列 需要为其建立索引 因为索引已经排序 其保存的时候指定的范围是连续的 查询可以利用索引的排序 加快查询时间 减少用户等待时间

不过 若虽然可能需要按范围来进行查询 但是 若这个范围查询条件利用的不多的情况下 最好不好采用索引 如在员工信息表中 可能需要查询 年 月份以前入职的员工明细 要为他们增加福利 但是 由于表中记录不多 而且 也很少进行类似的查询 若维这个字段建立索引 虽然无伤大雅 但是很明显 索引所获得的收益要低于其成本支出 对数据库管理员来说 是得不偿失的

再者 若采用范围查询的话 最好能利用TOP关键字来限制一次查询的结果 如第一次按顺序只显示前面的 条记录等等 把TOP关键字跟范围一起使用 可以大大的提高查询的效率

铁律四 表中若有主键或者外键 一定要为其建立索引

定义有主键的索引列 一定要为其建立索引 因为主键可以加速定位到表中的某一行 结合索引的作用 可以使得查询的速度加倍 如在员工信息表中 我们往往把员工编号设置为主键 因为这不但可以提高查询的速度 而且因为主键要求记录的唯一 还可以保证员工编号的唯一性 此时 若再把这个员工编号字段设置为索引 则通过员工编号来查询员工信息 其效率要比没有建立索引高出许多

另外 若要使得某个字段的值唯一 可以通过两种索引方式实现 一种就是上面所讲的主键索引 还有一种就是唯一索引 利用UNIQUE关键字指定字段内容的唯一性 这两种方式都会在表中的指定列上自动创建唯一索引 这两种方式的结果没有明显的区别 查询优化器不会区分到底是哪种方式建立的唯一性索引 而且他们进行数据查询的方式也是相同的

若某张表中的数据列定义有外键 则最好也要为这个字段建立索引 因为外键的主要作用就在于表与表之间的连接查询 若在外键上建立索引 可以加速表与表之间的连接查询 如在员工基本信息表中 有一个字段为员工职位 由于员工职位经常在变化 在这里 存储的其实只是一个员工职位的代码 在另外一张职位信息表中详细记录著该职位的相关信息 此时 这个员工职位字段就是外键 若在这个字段上建立外键 则可以显著提高两张表的连接速度 而且 记录越多 其效果越加明显

所以 当表中有外键或者主键的时候 就最好为其建立索引 通过索引 可以强化主键与外键的作用 提高数据库的性能

铁律五 对于一些特殊的数据类型 不要建立索引

在表中 有些字段比较特殊 如文本字段(TXT) 图像类型字段(IMAGE)等等 如果表中的字段属于这些数据类型 则最好不要为其建立索引 因为这些字段有一些共同的特点 如长度不确定 要么很长 几个字符要么就是空字符串 如文本数据类型常在应用系统的数据库表中用来做备注的数据类型 有时候备注很长 但有时候又没有数据 若这种类型的字段上建立索引 那根本起不了作用 相反 还增加了系统的负担

所以 在一些比较特殊的数据类型上 建立索引要谨慎 在通常情况下 没有必要为其建立索引 但是 也有特殊的情况 如有时候 在ERP系统中 有产品信息这个表 其中有个产品规格这个字段 有时候 其长度可能长达 个字符 此时 只有文本型的数据类型可以容纳这么大的数据量 而且 在查询的时候 用户又喜欢通过规格这个参数来查询产品信息 此时 若不为这个字段建立索引的话 则查询的速度会很慢 遇到这种情况时 数据库管理员只有牺牲一点系统资源 为其建立索引

从这里也可以看出 虽然以上几条说的时铁律 但是 是否需要遵循 还是需要数据库管理员根据企业的实际情况 做出合理的选择

铁律六 索引可以跟Where语句的集合融为一体

用户在查询信息的时候 有时会经常会用到一些限制语句 如在查询销售订单的时候 经常会用到客户以及下单日期的条件集合如在查询某个产品的库存交易情况时 就会利用产品编号与交易日期起止日期的条件集合

对于这些经常用在Where子句中的数据列 将索引建立在 Where子句的集合过程中 对于需要加速或者频繁检索的数据列 可以让这些经常参与查询的数据列按照索引的排序进行查询 以加快查询的时间

lishixinzhi/Article/program/SQLServer/201311/22311

三种索引类型分别是：

1、主键索引：不允许具有索引值相同的行，从而禁止重复的索引或键值。系统在创建该索引时检查是否有重复的键值，并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查。

2、聚集索引：指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一个聚集索引，因为一个表的物理顺序只有一种情况。

3、非聚集索引：索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。非聚集索引的叶层不包含数据页。 相反，叶节点包含索引行。

扩展资料

聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。

例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。

频繁更改的列 这将导致整行移动，因为 SQL Server 必须按物理顺序保留行中的数据值。这一点要特别注意，因为在大数据量事务处理系统中数据是易失的。来自聚集索引的键值由所有非聚集索引作为查找键使用，因此存储在每个非聚集索引的叶条目内。

参考资料来源：百度百科-非聚集索引

参考资料来源：百度百科-聚集索引

参考资料来源：百度百科-唯一索引

---