sql server 索引总结三

一、非聚集索引维护

非聚集索引的行定位器值保持相同的聚集索引值，即使该聚集索引列物理上重新定位后，也是如此。

为了优化这个维护开销，SQL Server添加一个指向旧数据页的指针，以在页面分割之后指向新的数据页面，而不是更新所有相关非聚集索引的行定位器。这样，虽然降低了非聚集索引的维护开销，但是增加了从非聚集索引行到数据行的导航开销，因为添加了一个旧数据页面和信数据页面之间的连接。因此，将聚集索引作为行定位器降低了非聚集索引相关的开销。

二、定义书签查找

当一个查询请求不是优化器选择的非聚集索引一部分的列时，需要一个查找。这对一个聚集索引来说是一个关键字查找，堆堆表来说是一个RID查找。这些查找的统称来自于旧的定义名 - 书签查找。这种查找根据非聚集索引的行定位器值，从表中读取对应的数据行，除了索引页面上的逻辑读操作意外，还需要一个数据页面上的逻辑读。但是，如果查询需要的列在索引中，那么就不需要访问数据页面。这被称为覆盖索引。

这些书签查找是大结果集最好使用聚集索引的原因。聚集索引不需要书签查找，因为叶子页面就是数据页面。

三、非聚集索引的建议

因为表只能有一个聚集索引，所以可以使用多个非聚集索引的灵活性来帮助改进性能。下面将说明非聚集索引使用的决定因素。

1、何时使用非聚集索引

非聚集索引在需要从一个大表上读取少量的行的时候最有用，随着需要检索的行数量的增加，书签查找的开销成比例增加。为了从表中检索少量的行，索引列应该有很高的选择性。

适合使用非聚集索引的情况：

• 列具有高选择性；
• 根据列获取少量数据；
• 窄列；
• 列经常被分组排序；

下面给出不适合建立聚集索引，但也能够使用聚集索引的情况

• 频繁更新的列；
• 宽类型列；

聚集索引频繁更新是非常消耗资源的，因为会影响表的顺序，同时也影响到其他索引，在频繁更新的列上的非聚集索引的开销不像聚集索引那么大。在非聚集索引上的更新操作被限定在基本表和非聚集索引上，它不影响表上的其他非聚集索引。

宽类型列也类似，非聚集索引列上使用宽类型，虽影响比如聚集索引那么大，但是也要小心使用。

2、何时不使用聚集索引

非聚集索引不适合于大量行的查询。这样的查询使用聚集索引更好，聚集索引不需要大量的书签查找，而非聚集索除了在索引列上检索的逻辑读外，书签查找也需要消耗太多资源。SQL Server查询优化器在检索大结果集时会考虑这一开销，并相应地放弃该非聚集索引。

不适合建非聚集索引的情况： 获取大量数据； 低选择性；

聚集索引、非聚集索引、非聚集唯一索引

我们都知道建立适当的索引能够提高查询速度，优化查询。先说明一下，无论是聚集索引还是非聚集索引都是B树结构。

• 聚集索引默认与主键相匹配，在设置主键时，SQL Server会默认在主键列创建聚集索引。但是可以手动更改为在任意一个列创建聚集索引，然后在另一个字段或多个字段上定义主键。这时主键将会被作为一个唯一的非聚集索引(唯一索引)被创建。通过指定NONCLUSTERED关键字就可以做到。

  CREATE TABLE MyTableKeyExample
  {
  Column1 int IDENTITY KEY NONCLUSTERED,
  Column2 int
  }

• 聚集索引实际上装载了SQL Server的数据记录行，聚集索引的叶级就是数据行，所以到达了聚集索引的叶级就到达了数据。
• 为表声明主键或唯一约束时，SQL Server会自动创建与之对应的唯一索引。
• 聚集索引的列最好就是自增的，因为根据区段-页的理论，如果聚集索引列是自增的，那么添加数据的时候，所见是放在索引的最后，不会发生由中间插入的情况，这样就不会引起页拆分。而如果索引列不是自增的，添加数据的时候，还要按顺序找到该条记录的位置，并且插入，如果插入比较频繁，还可能会经常引起页拆分。
• 聚集索引的最佳数据类型，smallint、int、bigint、datetme。
• 最好避免组合聚集索引。

索引(index)是除了表之外的另一个重要的、用户定义的存储在数据库里的数据结构。当根据索引码的值搜索数据时，索引提供了对数据的快速访问。事实上，没有索引数据库也能够根据SELECT语句通过表扫描成功地检索到结果，但是随着表变得越来越大，使用“适当”的索引的效果就越来越明显。但如果使用索引时不认真考虑其实现过程，索引反而有可能会降低数据库的工作性能。创建主键时会自动创建聚集索引，除非当前表中已经含有了聚集索引或是创建主键时指定了NONCLUSTERED关键字。

聚集索引、非聚集索引、非聚集唯一索引：

SqlServer提供了两种索引：聚集索引和非聚集索引。聚集的作用就是将某一列（或是多列）的物理顺序改变为和逻辑顺序相一致。

聚集索引(CLUSTERED)与非聚集索引(NONCLUSTERED)的区别：

其实，我们的汉语字典的正文本身就是一个聚集索引(按照拼音的英文字母排序) 比如，我们要查“安”字 ，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字 母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的 字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。 这也是为什么一张表只能 够有一个聚集索引的原因。因为一张表只能够按一种方式排序。 其中聚集索引的叶级节点就是数据。你可以理解为有很多列火车，按照火车头索引(排序)，火车头后面跟着的就是数据。 如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张” 的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是 “弩”字，页面390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩” 三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。

实际上如果表上有聚集索引，则非聚集索引中存储着聚集索引的引用，然后通过利用聚集索引来获取数据。因此这就是为什么返回少量数据的时候使用非聚集索引的性能较好，而返回大量的数据的时候使用非聚集索引还不如直接全表扫描来得快。而如果表上没有聚集索引的时候，则引用行号。

非聚集索引需要额外的空间进行存储，按照被索引列进行聚集索引，并在B树的叶子节点包含指向非聚集索引所在表的指针。非聚集索引也是B树结构，另外一个单独的B树。

唯一索引：

当主键创建时如果不设置为聚集索引，那么就一定是唯一的非聚集索引。实际上，唯一索引，故名思议就是它要求该列上的值是唯一的。

聚集索引列可以重复，非聚集索引列也可以重复。这就是为什么要有唯一这个东东了。声明唯一索引的语法很简单，只是多了个UNIQUE关键字。

唯一索引有很多限制和特性。下面详细学习下唯一索引。

为表声明主键或唯一约束时，SQL Server会自动创建与之对应的唯一索引。定义一个唯一约束时，SQL Server会自动创建一个与之同名的唯一索引，要删除索引必须先删除约束。但删除约束，删除约束也会导致与之关联的索引被删除，也就是说，不能删除唯一索引，要删除索引只有删除唯一约束这个办法。

唯一索引依赖于唯一约束，删除唯一索引必须删除唯一约束。另外SQL Server又在建立唯一约束时又默认建立唯一索引。

总结起来就是：唯一索引与唯一约束始终一同存在。

因为定义一个主键或是定义约束会导致索引被创建，所以你必须在约束定义时就给出必要的索引信息，因此上面ALTER TABLE语句中包含了”CLUSTERED”关键字。

 如果唯一索引或约束所约束的列在当前的表中已经含有了重复值，那么创建索引会失败。而当唯一索引创建成功后，所有违反这个约束的INSERT、UPDATE语句都会失败。

唯一约束和唯一索引并没有显著的区别。创建独立的唯一索引和使用唯一约束对于数据的验证方式并无区别。查询优化器也不会区分唯一索引是由约束创建还是手工创建。然而以数据完整性为目标的话，最好创建约束，这使得对应的索引的目标一目了然。

过滤唯一索引，当我们需要既允许多个NULL值，又不允许重复的时候，可以使用这个：

对于用以上语法创建的唯一索引，插入时，只有当唯一索引列不为NULL的时候才检测重复。换句话说，以上表的索引列允许多个NULL值。

一、索引视图基本概念

索引视图实际上是一种将一组唯一值“物化”为群集索引形式的视图，所为物化就是几乎和表一样，其数据也是会存储一份的(会占用硬盘空间，但是查询速度快，例如可以将count(),sum()等值设在索引视图中)。其优点是它在提取视图背后的信息方面提供了一个非常快的查找方法。在第一个索引(必须是针对一组唯一值的聚集索引)之后，通过使用来自第一个索引的聚集键作为参考点，SQL Server还能在视图上建立额外的索引。其限制如下：

1. 视图必须使用SCHEMABINDING选项；
2. 如果视图引用了任何用户自定义函数，那么这些函数也必须是模式绑定的；
3. 视图不可以引用任何其他的视图-只能引用表和UDF；
4. 在视图中引用的所有表和UDF必须采用两部分的命名约定(例如：dbo..Customers)，并且也必须具有和视图相同的所有者；
5. 视图和视图引用的所有对象必须在相同的数据库中；
6. 在创建视图和所有底层表时，必须打开ANSI_NULLS以及QUOTED_IDENTIFIER选项；
7. 视图引用的任何函数必须是确定的；

示例：

当创建索引时，在视图上创建的第一个索引必须是聚集的和唯一的：

一旦执行该命令，就有了视图的群集索引。索引基本和表的一样，也需要维护成本。

二、索引视图作用示例

PersonTenMillion是一张一千万记录的表，下面我们来执行如下SQL语句：

对一张1千万记录的表进行分组计算每个年龄的认输，你可以想象到需要花费的时间了。

1分31秒，这种查询语句如果在网页上面，页面已经显示页面无法响应了。

下面我们来优化上面这个查询，我们创建一个索引视图如下：

这次我们从索引视图上获取数据：

这次是瞬间出来的，因为只是相当于从一个81行的表中使用聚集索引分那会81行数据：

查询速度快了好多好多，但这以为这索引视图是好的选择吗？不是的，这只意味着它可能是。和任何索引一样，需要记住索引的维护成本。维护该索引将会使对底层表的INSERT、UPDATE和DELETE语句的执行速度减慢多少？这必须考虑进去，这是个平衡问题，要视每个表和每个索引而定。尽管如此，索引视图还是一种较强大的工具，因此作仔细地权衡。

一、索引压缩

数据和索引压缩在SQL Server2008被引入。压缩一个索引意味着将在一个页面中获得更多的关键字信息。这可以造成重大的性能改进，因为存储索引需要的页面和索引级别更少。因为索引中的键值被压缩和解压缩，也将造成CPU和内存的开销，所以这并不是适合所有索引的方案。

默认情况下，索引将不会被压缩。必须明确地在创建索引时要求索引被压缩。有两种压缩类型：行级压缩和页面级压缩。索引中的非叶子页面不接受页面类型压缩。

创建压缩索引的语法如下：

下面以一个示例来看看压缩索引的作用：

正常索引：

行级压缩索引：

页面级压缩索引：

我们看到对于100万索引的Name列索引，索引数据页面分别是：

普通索引	行索引	页面索引

从上面的例子我们可以得出结论，的确压缩能够能够大幅减少索引的页面量。但是举个很简单的例子，如果一台数据库服务器上，内存和CPU已是瓶颈，那么压缩数据作用实际上并不大。

至于更具体的哪个更好，这个有时间了再慢慢测试。现在只是知道了索引可以压缩已减少索引数据页面数量。

附上一个查看索引消息的SQL语句：

二、 附加特性

1、不同的列排序顺序

SQL Server支持使用不同的排序顺序为索引的不同列创建一个复杂的索引。如果希望一个索引的第一列按照升序排列二第二列按照降序排列，可以用如下语句完成：

2、BIT数据类型列上的索引

SQL Server允许创建在BIT数据类型列上的索引。创建BIT数据类型列上的索引的能力本身不是一个大的优点，因为这样的列只能有两个不同的值。这么低的选择性的列通常不是好的索引后选择。但是，这个功能在考虑覆盖索引时非常有用。因为覆盖索引需要包含所有搜索中的返回列，而在索引中添加BIT数据类型列将使得覆盖索引在需要时包含这样的列。

3、CREATE INDEX语句也会使用索引提升速度

CREATE INDEX操作被集成到查询处理器。优化器可能使用已有的索引来减少扫描开销并在创建索引时排序。

在第一个索引中由于已经包含了Name列，而第二个索引也要使用Name列的时候，创建索引SQL Server直接使用索引扫描的方式来创建索引。

4、并行索引创建

SQL Server支持CREATE INDEX语句的并行计划，正如在其他SQL查询中一样。在一个多处理器的机器上，索引创建不限于单个处理器而是从多个处理器中获益。可以使用SQL Server的max degree of parallelism配置参数来控制用于CREATE INDEX语句中的处理器数量。这个参数的默认值为0，0表示可以使用任意的CPU数量。

这个配置设置立即生效，不需要重启服务器。 查询提示MAXDOP可以用于CREATE INDEX语句。而且，CREATE INDEX特性只可以用于SQL Server 2005和2008企业版。

一、书签查找的概念

书签可以帮助SQL Server快速从非聚集索引条目导向到对应的行，其实这东西几句话我就能说明白。

如果表有聚集索引(区段结构)，那么书签就是从非聚集索引找到聚集索引后，利用聚集索引定位到数据。此处的书签就是聚集索引。如果表没有聚集索引(堆结构)。那么扫描非聚集索引后，通过RID定位到数据，那么此处书签就是RID。

所谓的书签查找，就是通过聚集索引，然后利用聚集索引或RID定位到数据。

不论表示堆结构还是区段结构，数据的存放都是数据库文件的某文件->某页->某行，因此定位数据的文件组合起来就是 文件号:页号:行号。这三个数字就是RID。如文件1的第77页的第12行的RID就是1:77:12。

堆结构与区段结构不同，通常堆上的行不会改变位置，一旦他们被插入某个页中，他们就会一直在那个位置。在堆上的行很少移动，如果行被移动的话，他们会在原来的位置留下指向其移动到的新位置的指针。而区段结构的行，是可以移动的，在添加数据或整理索引时，都可以会被移动位置。

因为在堆上的行很少移动，所以RID就可以唯一标识某一行，RID的值不仅仅不变，RID所表示的行的物理位置也不会变，这使得RID的值更适宜作为书签。这也是为什么SQL Server在堆上建立的非聚集索引的书签都使用RID。

1、堆上的非聚集索引：基于RID的书签

部分数据顺序：

注意到以上数据是无序的。

上面建立的非聚集索引因为使用了RID作为书签，直接指向对应行所在的物理位置，因此效率不错。虽然RID值用于键查找非常高效，但书签中包含的值与具体的用户数据无关。

2、在聚集索引上的非聚集索引：基于聚集键的书签

如果表示基于聚集索引的，则表内数据可以在表移动。因此，对于聚集索引来说，RID并不能一直不变的定位一个相同的行。因此必须用另外的方法定位行，这个方法就是使用聚集索引的索引键。 使用聚集索引键作为书签可以使得当数据在页中的行改变时，不需要非聚集索引的书签的值进行变动，因此非聚集索引的键就可以用于去找底层表的数据，即根据书签取数据不再基于物理位置，而是基于聚集索引查找。

以聚集索引键作为非聚集索引的书签最好要聚集索引键满足如下标准： 索引应该具有唯一性：每一个索引条目书签都应该使得书签可以通过聚集索引的键值唯一的确认表中的一行，如果你创建的聚集索引键值不唯一，SQL Server将会为有重复键值的每一行自动加上一个叫uniquifier的东西使得每一行唯一。这个uniquifier对客户端是透明的。对于是否可以允许聚集索引键重复，要考虑以下两点：

• 生成uniquifier增加SQL Server插入操作的额外负担，在插入时SQL Server还需要判断插入的值在表中是否唯一，如果不唯一生成uniquifier值再进行插入。
• uniquifier本身对业务数据来说是没有意义的，但是这个uniquifier本身不仅仅需要占用聚集索引键的空间，还同时占用非聚集索引书签的空间

索引键应该短：索引键所占的字节数应该短.因为这个键还会占用非聚集索引书签的空间。比如Contact表中以Last name / first name / middle name / street组合作为索引键看上去不错，但如果表中存在多个非聚集索引的话情况就有些微妙了。n个非聚集索引使得Last name / first name / middle name / street这些字段被存储在n+1个位置。

索引键最好不要变动：也就是索引键的值最好不要变动。对于聚集索引键的修改会使得基于这个聚集索引的所有非聚集索引同样进行修改。所以对于聚集索引的一次update会造成n个非聚集索引书签的update+1个聚集索引键值本身的update。

下面以一个示例来帮助理解书签查找:

假设数据库有一张表如下：

我们再Name列建一个非聚集索引，然后执行下面的语句：

从执行计划我们可以看到，因为Age列并不在非聚集索引中，所以SQL Server通过“键查找”引导到聚集表获取数据，这就是书签查找。

书签查找的目的，就是为了从非聚集索引导航到基本表获取非聚集索引中并未包含的信息。

二、书签查找的缺点

书签查找要求访问索引页面之外的数据页面，访问两组页面增加了查询逻辑读操作次数。而且，如果页面不在内存中，书签查找可能需要在磁盘上一个随机I/O操作来从索引页面跳转到数据页面，还需要必要的CPU能力来汇集这一数据并执行必要的操作。这是因为对于大的表，索引页面和对应的数据页面通常在磁盘上并不临近。

如果需要增加逻辑读操作或者开销较大的物理读操作使书签查找的数据检索操作开销相当大，这个开销因素是非聚集索引更适合于返回较小的数据行数的原因。随着查询检索的行数增加，书签查找的开销将变得无法接受。

为了理解书签查找随着检索行数增加而使feu聚集索引无效，下面来看一个实例:

还是那张Person表,一万数据。这次，我把索引建在Id列，Id列的唯一性是1，因为原来Id列是做主键+聚集索引的，但被我删掉了。

我们来看看下面两个查询的执行计划，

返回100条：

返回300条：

我们看到，当要求返回300条数据的时候，SQL Server就不在使用Id列上的非聚集索引，而是直接进行表扫描了。因为SQL Server认为执行300次书签查找还不如直接对一张1万条记录的表进行全表扫描。

由上面的实例可以得出结论，返回大的结果集将增加书签查找的开销，甚至低于表扫描。因此在返回较大结果集的情况下，必须考虑避免书签查找的可能性。

三、书签查找的起因

书签查找可能是一个开销较大的操作，所以应该分析查询计划，在执行计划中选择一个关键字查找步骤的原因。可能发现可以通过在非聚集索引键中包含丢失的行，或者作为索引页面级别上的包含列来避免书签查找，从而避免与书签查找相关的开销。

从上面的实例，我们可以提出观点：如果查询的各部分(不只是选择列表)中引用的列不都包含在使用的非聚集索引中，就会发生书签查找操作。

下面介绍一个技巧，我们点击某一个执行计划的图标之后，就能在右侧的属性信息栏里获取到相关的执行信息。例如，输出列表就是本执行计划的要返回的列。

四、避免书签查找的方法

因为书签查找的相对开销可能非常高，所以应该尽可能尝试摆脱书签查找操作。下面给出一下方案。

1、使用聚集索引

对于聚集索引，索引的叶子页面和表的数据页面相同。因此，当读取聚集索引键列的值时，数据引擎可以读取其他列的值而不需要任何导航。例如前面的区间数据查询的操作，SQL Server通过B树结构进行查找是非常快速的。

把非聚集索引转换为一个聚集索引说起来很简单。但是，这个例子和大部分可能遇到的情况下，这不可能做到，因为表已经有了一个聚集索引。这个表的聚集索引恰好是主键。必须卸载掉所有的外键约束，卸载并且重建为一个非聚集索引。这不仅要考虑所涉及的工作，还可能严重地影响依赖于现有聚集索引的其他查询。

2、使用覆盖索引

为了理解覆盖索引是如何避免书签查找，我们还是对于Person来执行如下两个查询：

下面修改索引增加Name列。

由于非聚集索引上已经有了需要查询的Id和Name列的数据，所以不在需要书签查找定位到基本表。

3、使用索引连接

如果覆盖索引变得非常宽，那么可能要考虑索引连接技术。索引连接技术使用两个或更多索引之间的一个索引交叉来完全覆盖一个查询。因为索引连接技术需要访问多余一个索引，它必须在所有索引连接中使用的索引上执行逻辑读。因此，索引连接需要比覆盖索引更高的逻辑读数量。但是，因为索引连接所用的多个窄索引能够比宽的覆盖索引服务更多的查询。所以索引连接也可以作为避免书签查找的一种技术来考虑。

我们来看下面的实例：

留意到，上面的例子我们创建了两个非聚集索引，一个在 Id列，一个在Name列。但是我们的查询需要同时返回Id列和Name列。而这两个非聚集索引都不完全包含要返回列。这个时候，哈希匹配目的就是通过定位到索引，而不用定位到基本表就能够获得我们所需要的全部数据，这样索引连接就避免了书签查找。

一、索引的图形界面操作

SQL Server非常强大的就是图形界面操作。关于索引方面也一样那么强大，很多操作比如说重建索引啊，查看各种统计信息啊，都能够通过图形界面快速查看和操作，下面来看看SQL Server索引方面的GUI操作。

二、索引统计信息的图形界面操作

在有大量事务的数据库中，表和索引随着时间的推移而碎片化。因此，为了增进性能，应该定期检查表和索引的碎片，并对具有大量碎片的进行整理。

1、确定当前数据库中所有需要分析碎片的表。

2、确定所有表和索引的碎片。

3、考虑一下因素以确定需要进行碎片整理的表和索引。

• 高的碎片水平-avg_fragmentation_in_percent大于20%；
• 不是非常小的表或索引-也就是page_count大于8的；

4、整理具有大量碎片的表和索引；

这里给出一个样板SQL存储过程，它执行以下操作；

• 遍历系统上的所有数据库并确认符合碎片条件的每个数据库中表上的索引，并将它们保存到一个临时表中；
• 根据碎片水平，重新整理碎片较少的索引并重建碎片很多的索引。

为了自动化碎片分析过程，可以从SQL Server企业管理器中用以下简单的步骤创建一个SQL Server任务。

1、开启SQL Server代理;

2、打开Management Studio，右键单击，选择新建=》任务；

3、在新建任务对话框的“常规”页面中，输入任务名称和其他细节：

4、在新建任务对话框的“步骤”页面中，单击“新建”并输入用户数据库的SQL命令。

5、在新建任务步骤对话框“高级”页面上，输入报告碎片分析结果的输出文件名称：

6、单击“确定”按钮，返回新建作业对话框；

7、在新建任务对话框“计划”页面，单击“新建计划”，并输入运行SQL Server任务的合适计划：

安排这个存储过程在非高峰执行。为了确定数据库的数据库模式，记录整天的SQL Server：SQL Statistics\Batch Requests/sec性能计数器，它将展示数据库负载的波动。

8、单击“确定”按钮，返回新建任务对话框。

9、输入所有信息后，单击新建任务对话框中的“确定”按钮创建SQL Server任务。创建计划在一个固定时间间隔(每周)运行sp_indexDefrag存储过程的SQL Server任务。

10、确保SQL Server代理运行，这样SQL Server任务将自动根据设置的计划运行。

这个SQL任务将在每个星期天的凌晨1点分析每个数据库并且进行碎片整理。