MS SQL 索引(一)

港控/mmm° 2022-07-16 16:57 195阅读 0赞

**SQL SERVER 2005索引**

**（一）基本概念：**

索引是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键。这些键存储在一个结构(B 树)中，使 SQL Server 可以快速有效地查找与键值关联的行。

表或视图可以包含以下类型的索引：

**      聚集索引**

聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。

只有当表包含聚集索引时，表中的数据行才按排序顺序存储。如果表具有聚集索引，则该表称为聚集表。如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。

聚集索引不适用于具有下列属性的列的情况：

(1):频繁更改的列:这将导致整行移动，因为数据库引擎 必须按物理顺序保留行中的数据值。这一点要特别注意，因为在大容量事务处理系统中数据通常是可变的。

(2):宽键是若干列或若干大型列的组合。所有非聚集索引将聚集索引中的键值用作查找键。

聚簇索引的适用的侯选列有：

1、主键列,该列在where子句中使用并且插入是随机的。

2、按范围存取的列，如pri\_order > 100 and pri\_order < 200。

3、在group by或order by中使用的列。

4、不经常修改的列。

5、在连接操作中使用的列。

**      非聚集索引**

非聚集索引具有独立于数据行的结构。非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。从非聚集索引中的索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页是存储在堆中还是聚集表中。对于堆，行定位器是指向行的指针。对于聚集表，行定位器是聚集索引键。

在 SQL Server 2005 中，可以向非聚集索引的叶级别添加非键列以跳过现有的索引键限制(900 字节和 16 键列)，并执行完整范围内的索引查询。在建立非聚簇索引时，要权衡索引对查询速度的加快与降低修改速度之间的利弊。另外，还要考虑这些问题：

1、索引需要使用多少空间。

2、合适的列是否稳定。

3、索引键是如何选择的，扫描效果是否更佳。

4、是否有许多重复值。

对更新频繁的表来说，表上的非聚簇索引比聚簇索引和根本没有索引需要更多的额外开销。所以，建立非聚簇索引要非常慎重。非聚簇索引常被用在以下情况:

1、某列常用于集合函数(如Sum,....)。

2、某列常用于join,order by,group by。

3、查寻出的数据不超过表中数据量的20%。

**非聚集索引与聚集索比较**

相同的是：引具有相同的 B 树结构

显著差别在于以下两点：

1、基础表的数据行不按非聚集键的顺序排序和存储。

2、非聚集索引的叶层是由索引页而不是由数据页组成。

**      唯一索引**

唯一索引确保索引键不包含重复的值，因此，表或视图中的每一行在某种程度上是唯一的。

聚集索引和非聚集索引都可以是唯一索引。

例：以下示例为 Production.UnitMeasure 表的 Name 列创建唯一的非聚集索引。该索引将强制插入 Name 列的数据有唯一性。

<table style="table-layout: fixed; width: 95%; border: #cccccc 1px dotted;"> 
 <tbody> 
  <tr> 
   <td style="WORD-WRAP: break-word">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; USE&nbsp;AdventureWorks; <br>　　GO <br>　　CREATE&nbsp;UNIQUE&nbsp;INDEX&nbsp;AK_UnitMeasure_Name <br>　　ON&nbsp;Production.UnitMeasure(Name); <br>　　GO</td> 
  </tr> 
 </tbody> 
</table>

**包含性列索引**

一种非聚集索引，它扩展后不仅包含键列，还包含非键列。

**　索引涵盖**

指查询中的SELECT与WHERE子句的所用列同时也属于非聚集索引的情况。这样就可以更快检索数据，因为所有信息都可以直接来自于索引页，从而SQL Server可以避免访问数据页。加上独立的索引文件组，可以用最快速度访问数据。

覆盖索引是指那些索引项中包含查寻所需要的全部信息的非聚簇索引，这种索引之所以比较快也正是因为索引页中包含了查寻所必须的数据,不需去访问数据页。如果非聚簇索引中包含结果数据,那么它的查询速度将快于聚簇索引。

但是由于覆盖索引的索引项比较多,要占用比较大的空间。而且update操作会引起索引值改变。所以如果潜在的覆盖查询并不常用或不太关键，则覆盖索引的增加反而会降低性能。

**总结：**一般来说建立索引的思路是：

(1)、主键时常作为where子句的条件，应在表的主键列上建立聚簇索引，尤其当经常用它作为连接的时候。

(2)、有大量重复值且经常有范围查询和排序、分组发生的列，或者非常频繁地被访问的列，可考虑建立聚簇索引。

(3)、经常同时存取多列，且每列都含有重复值可考虑建立复合索引来覆盖一个或一组查询，并把查询引用最频繁的列作为前导列，如果可能尽量使关键查询形成覆盖查询。

(4)、如果知道索引键的所有值都是唯一的，那么确保把索引定义成唯一索引。

(5)、在一个经常做插入操作的表上建索引时，使用fillfactor(填充因子)来减少页分裂，同时提高并发度降低死锁的发生。如果在只读表上建索引，则可以把fillfactor置为100。