Sql Server2005索引内部构造

Sql Server 的两类关系型索引：聚集索引和非聚集索引

一、索引的构造

聚集索引：数据实际上是按顺序存储的，就好像参考手册将所有主题按序编制一样。一旦找到了所要搜索的数据，就完成了这次搜索。
非聚集索引：索引完全独立于数据本身结构的。一旦在索引中找到了要寻找的数据，就必须跟随其指针定位到实际的数据。

索引是由具有如下特征的一棵树所组成的，唯一的、作为遍历起点的根分页、可能存在的中间索引层以及底层的叶子分页。使用索引可以找到正确的叶子分页。索引的中间层次数是根据表的行数以及索引行的大小而变化的。

聚集索引和非聚集索引之间的最大区别在于除了索引键以外叶级别还存放了什么。

A、聚集索引：
聚集索引的叶级别不仅包含了索引键，还包含数据页。也就是说数据本身也是聚集索引的一部分。聚集索引基于键值保存表中的数据有序。表中的数据是通过一个被称作页链的双向链接表来维护的。页链中的页顺序以及数据页上的记录顺序就是索引键的顺序。

由于实际的数据页的页链只能按一种方式排序，因此一张表只能拥有一个聚集索引。在许多情况下，查询优化器非常倾向于采用聚集索引，因为聚集索引能够让我们直接在叶级别找到数据。

许多介绍Sql Server索引的文档会这样告诉读者：聚集索引按照 排序顺序物理地存储数据。如果读者认为物理存储就是磁盘本省的话就会产生误解。试想如果聚集索引必须按照特定顺序在实际的磁盘上维护数据的话，那么任何修改操作都将产生产生相当高昂的代价。当一个分页变得很忙并且必须一份为二时，所有后续页面上的数据就都必须向后移动。
聚集索引的排序顺序仅仅表示数据页链在逻辑上是有序的。如果Sql Server跟谁页链的话，就能以聚集索引键的顺序访问到每一行，而当添加新的行的分页时只需在页链中调整链接

Sql Server 2005中，所有的聚集索引都是唯一的。如果在创建聚集索引时没有指定unique关键字，Sql Server会在需要时通过往记录中添加一个唯一标识符在内部保证索引的唯一性。该标识符是一个4字节长的值，作为附加的聚集索引键的字段添加到数据库中。只有那些声明为索引键字段并拥有重复值的行才会被添加。

B、非聚集索引：
对于非聚集索引，叶级别不包含全部的数据。除了键值以外，每个叶级别中的索引行包含了一个书签，告诉Sql Server可以在哪里去找到与索引键相应的数据行。一个书签可能有两种格式：
1)、如果表上有聚集索引，,书签就是相应的数据行的聚集索引键
2)、如果表示堆结构(也就是没有聚集索引的情况)，书签就是一个标识符，以”文件号：页号：槽号”的格式来定位实际的行。

二、创建索引
Create [unique] [clustered | nonclustered] Index index_name on table_name(column_name[ASC|DESC])

通过关键字unique可以指定Sql Server必须强制规定索引键值的唯一性。如果不指定UNIQUE,重复的键值就是允许的。还可以指定索引是聚集的或者非聚集的。非聚集索引是默认值

包含性列：
Sql Server2005的索引键字段数量限制是16个，总共900个字节大小，然而sql server2005还允许用户创建带有包含性列的索引
Create [unique] [clustered | nonclustered] Index index_name on table_name(column_name[ASC|DESC])
[include (column_name[,…n]]
包含性列只在页级别中出现而且不以任何方式控制索引行的排序。它们的目的是使叶级别能够包含更多信息从而更大地发挥覆盖索引的索引调优能力。覆盖索引是一种非聚集索引，在其叶级别就可以找到满足查询的全部信息

约束和索引：

在声明主键或者唯一约束时，在表的一个或多个字段上会创建一个唯一性索引，就好像是用了create index命令一样。这些被创建出来以支持约束的索引名称与约束的名称相同。就索引的内部存储及其维护而言，是用create index命令创建的唯一性索引与用来支持约束而自动生成的索引之间没有区别。查询优化器是根据唯一性索引是否存在而不是根据一个字段上是否声明过主键来做决策的。

使用create index命令创建的索引和支持约束所创建的索引之间最大的区别在于该如何删除这个索引。Drop
Index命令只允许用户删除那些通过create index命令所建立的索引。另外，要删除由外键约束引用着的主键或唯一性约束的话，必须先删除外键约束。

关于是否应该使用唯一性约束或主键约束来定义唯一性是一个普遍关注的问题，且经常令人产生混淆
这里需要指明的是：约束是一个逻辑的概念，而索引是一个物理的概念。在建立索引时，实际上是请求Sql Server 创建一个占用存储空间并且在数据修改操作中必须得到维护的物理结构。在定义约束时，实际上是在定义数据的属性并且期望Sql Server强制限制该属性，而不是告诉它该如何强制。Sql Server当前的版本支持通过创建唯一性索引来支持主键和唯一性约束，但这不是产生唯一性索引的必要要求。Sql Server可能会在未来的版本中除了建立索引以外的某些其他方法来强制唯一性，但对于Sql Server20005来说是行不通的。

三、索引的分页结构
索引分页为三个基本类型：非聚集索引的叶级别、聚集索引的节点(非页级)级以及非聚集索引的节点级。对于聚集索引的叶级别，实际上并不存在一种独立的结构，因为那些就是数据分页。

聚集索引的节点行:
聚集索引的节点级包含了指向索引下一级的指针。分页指针的长度为6个字节：文件号占了2个字节，文件中的分页号占了4个字节。

非聚集索引的叶级行:
非聚集索引的叶级别行包含了每个键值以及一个书签。

非聚集索引的节点行：
非聚集索引的非叶级别只能在向较低级别遍历分页时起到作用。如果非聚集索引是唯一性的，那么节点行就只需非聚集索引键以及指向下层分页的指针。如果索引没有定义成唯一性的，即使所有的数值都是唯一的，非叶级索引行仍然包含书签


四、特殊索引

Sql Server 2005允许创建两种特殊类型的索引：1）、在计算列上建索引  2）、在视图上建索引

先决条件：给定相同的基准表数据，任何计算列或者视图中的任何行每次的返回值都是一致的。
1、必须为几个会话级的选项设置特定的数值
Set concat_null_yields_null on
Set quoted_identifier on
Set ansi_nulls on
Set ansi_padding on
Set ansi_warnings on
Set numeric_roundabort off
在创建这类特殊索引之前可以利用属性函数sessionproperty来测试当前连接的设定。返回值为1表示设定值为ON，而0就表示设定值为OFF
Select sessionproperty(‘numeric_roundabort’)

2、列和视图所使用的函数必须为确定性函数
当全部set选项都具有要求的设定值时，如果一个函数总是对相同的输入值返回相同的结果，那么次函数九被认为是确定性函数

3、架构绑定
创建索引视图要求数据表本身的任何基准对象的架构不能改变。为了防止架构定义的改变，create view语句允许使用with schemabinding选项，当指定了with schemabinding以后，定义次试图的select语句必须包含所有引用表的两段式名字


基于计算列的索引
Sql Server 2005允许你在确定的、精确的计算列上创建索引
在计算列上创建索引之前，可以使用IsDeterministic字段属性来判断字段是否具有确定性。如果指定了此属性返回1，否则返回0

索引视图

Sql Server中的索引视图类似于其他产品中的物化视图。索引视图最大的好处之一是具有对大表的累加聚合进行物化的能力。

附加要求：视图的定义也不能包含任何下列元素
1、 top
2、 text、ntext或者image字段
3、 distinct
4、 min、max、count(*)、stdev、variance、avg
5、 可空类型的表达式进行sum
6、 派生表
7、 Rowset函数
8、 其他视图
9、 Union
10、 子查询、outer连接或者自连接
11、 全文索引谓词
12、 Compute 、compute by
13、 Order by
可以使用objectproperty函数的IsIndexable属性来检验是否已经满足了所有需求
Select objectproperty(object_id(‘product_totals’),’IsIndexable’)

创建索引视图

Use Adventureworks
Go
Create view vdiscount1 with schemabinding
As select sum(UnitPrice*OrderQty) as SumPrice,
Sum(UnitPrice*OrderQty*(1.00-UnitProceDiscount)) as SumDiscountPrice,
Count_big(*) as count,
productID
from sales.saleorderdetail
group by productid;
注意上面例子中的with schemabinding字句以及表中指明的架构名称(dbo)

要创建索引视图，必须创建索引。在视图上创建的第一个索引必须是唯一的聚集索引。
组成索引视图的数据是持久化的，因为索引视图将数据保存在聚集索引的叶级别中，Sql Server会自动维护索引视图，只要有任何人修改数据影响到视图，Sql Server就会更新存储在聚集索引中的信息

建立唯一性聚集索引以后，可以在视图上创建多个非聚集索引。用户可以利用objectproperty函数的IsIndexed属性来判断一个 视图是否已经被索引化
Select objectproperty(object_id(‘vdiscount1’,’IsIndexed’))