《Microsoft Sql server 2008 Internals》读书笔记--第六章Indexes:Internals and Management(2)

 

《Microsoft Sql server 2008 Internals》读书笔记订阅地址：

http://www.cnblogs.com/downmoon/category/230397.html/rss

《Microsoft Sql server 2008 Internals》索引目录：

《Microsoft Sql server 2008 Internal》读书笔记--目录索引

 

 

上篇主要介绍了索引中的基本概念B-Tree及分析索引的工具，下面我们继续学习理解索引的存储结构。这一节太费时间了，整整看了两个晚上，将近六 个小时.累啊！

The Dependancy on the Clustering key

从上面的学习我们了解到：索引结构被分为两类索引组件：叶级(leaf level)和非叶级(non-leaf Level(s)) 。

一个聚集索引的叶级包含索引键和数据。“聚集索引的叶级除了键值还有什么？”答案是“其他的任何东东(everything else)”。也就是说，表中的所有行的列都在一个聚集索引的叶级中。换种说法是：当一个聚集索引被创建的时候，数据(data)变成了聚集索引的叶级， 同 时，表中的数据被聚集键复制和排序。一旦被创建，一个聚集索引被逻辑维护而不是物理维护，排序被通过一个双链列表（称为页链page Chain)维护。（注意：在一个堆(Heap)中页是不会有任何方式互相链接的。 ）在页链中的页的排序和数据页中行的排序，均是基于聚集 索引的定义。决定哪些列用于索引是一个重要的性能参考指标。

由于数据页的实际页链只能被一种方式排序，所以一个表只能有一个聚集索引。而且，通常情况下，大多数在使用聚集索引的时候性能更佳。然而，聚集键需 要被慎重选择，为了适当选择索引键，你必须理解索引是如何工作的。即聚集键的内部依赖，特别是非聚集索引。

位于聚集键上的非聚集索引的依赖(dependency)在SQL Server 7.0时代存储引擎被重新架构时就已经存在了，它最初是用于（当使用一个非聚集索引去引用表中相应的行时）行如何被标识。如果一个表有聚集索引，行被聚集 键标记(和查找）。如果没有聚集索引，则行被物理行标识(identifier RID)标记（和查找）。查找相应的数据行的处理就好像查找书的书签。

非聚集索引仅仅包含索引定义的数据 。当在一个非聚集索引中查找一行的时候，你通常不得不到实际的数据行以得到没有包含在非聚集索引部 分的数据。为了检索这部分附加数据，你必须深入表的内部。

首先 ，最重要的是，所有聚集索引必须是惟一的。为什么必须是惟一的首要原因是非聚集索引项能够准确地指向一个特定的行。考虑到如果一 个表被last name的不惟一的值聚集，问题来了。如果一个非聚集索引存在于一个惟一的值，比如社会安全号（对我们而言就是身份证号），一个查询社会安全号为 123-45-6789的索引，结果，聚集键是“Smith”,那么多个最后名字为Smith的行出现，到底是哪个？这个社会安全号为 123-45-6789的特定行，如何被有效定位？

为了聚集键能被有效使用，所有非聚集索引项必须准确地指向一行。因为SQL Server中的指针是聚集索引，所以，聚集索引必须是惟一的。如果你创建一个聚集索引时没有使用Unique关键字，系统在必要时会自动向这些行添加一 个惟一标志列以确保内部的惟一性。这个惟一标志(uniquifier)是一个4字节的整数，当行的聚集键不惟一时，自动被回到数据行，一旦被添加，它变 成了聚集键的一部分，意味着它在每一个非聚集索引中被复制。　

第二 ，如果一个聚集键(Clustering key)被用于从一个非聚集索引到一个聚集索引的（数据）内部查找相应的数据行，那么,这个聚集键是表中最被过多使用的数据。所有列都组织索引键，这些 键被包含于每一个非聚集索引附加于实际数据行中。结果，索引键的宽度变得重要了。考虑一个拥有12个非聚集索引和一百万数据的表的一个聚集索引拥有64字 节的聚集健。不计算内部和结构头部，仅仅用于存储在每一个非聚集索引键的索引键将是732MB,而如果聚集键只有8字节的话，这开销是92MB，而如果4 字节的话，将只有46MB。尽管这只是一个粗略的估计，它表明如果你使用了一个过宽的聚集键，将浪费大量的空间（以及潜在的缓 存池内存）,性能的损耗是比较可观的。因此，一个过宽的非聚集索引是应该避免的。

第三，因为聚集索引键是整个表中最冗余的数据，你应该确信你的聚集键是不易变的(not volatile）,如果一个聚集健改变，它有以下几个副作用：1、它会引起聚集索引内的记录的搬迁，造成页page的分离与碎片。2、它引起每一个非聚 集索引被修改，以便于所有相关的非聚集索引的行的索引键的值被纠正。这既浪费时间和空间，导致需要整理的碎片，增加了不必要的开销（每个列重组聚集键）。

这三个属性：惟一、窄和静态也被用一个好的主键（但不总是）。因为你只有一个主键（仅仅一个聚集键）。SQL Server使用一个惟一的聚集索引以强化主键约束。然而，并不是每一个表的创建者（唉，别睡着了，说你呢！）都知道这一点。于是，主键没有被坚持这些标 准(举例，当主键被选为数据的自然健(natural key)，即，如一个宽的7个列的100个字节组合)，那么使用一个聚集索引去强化惟一性，并在每一个非聚集索引复制完全的100字节的列组合将具有极强 的反作用。l因此，对一些不知情的（unsuspecting）数据库开发人员（具体就是刚才睡觉的那几位),一个非常宽的聚集健在创建表时就被系统不知 不觉的加上了，好消息是：你可以定义主键是非聚集索引，并且轻易地在一个不同的列上创建一个聚集索引 。当然，你必须知道什么时候和怎样做这 些。

最后，一个表的聚集键也应当以Insert时产生最小碎片为依据。尽管一旦聚集索引被创建后只有一个逻辑排序被维护，这个结构的维护确实有开销。如 果行需要持续地被插入在随机点（如插入一个以last name排序的表中），那么这个表的逻辑排序的维护成本比总是插入一个以自增长列排序的表（总是在表的末尾处）略微有些提高。

小结：表的聚集键应该基于表的用途，同时也应当基于SQL Server在聚集健的内部依赖。聚集键应该是惟一的、窄的、静态的，最好，自增长？（ ever-increasing)。

好的聚集键的例子如下：

■、单个列键被定义为一个不断增长的自增长列(如int 或bigint)

■、一个不断增长的日期列(date而不是datetime)，紧跟着一个惟一的行标识（如自增长列）组成一个复合键，这对基于日期分区的表非常有用。如SalesDate(8字 节）+SalesNumber(4字节）组合为12字节，注意在SQL Server 2008中，一个日期列(date)不包含时间。但是单独的一个日期列(date)并不是一个好的聚集键，因为它不惟一。

■、一个GUID能被成功地用作一个聚集键，因为它本身就是惟一的。相对的窄(12字节）,可能是静态的。然而，仅仅在不断增长的模式下，GUID 才适合被用作聚集健。在一些情况下，GUID是在SQL Server的外部被生成或在SQL Server内部用 NewID()生成，这种模式下产生的碎片抵消了这个列作为聚集健的作用。如果可能，请选择NEWSEQUENTIALID()函数或选择其他列。如果你 想用GUID作为一个主键，并且不是自增长的，你可以把这个列作为一个非聚集索引代替聚集索引。

总之，没有一个通用的最好的绝对有效的法则来决定一个聚集键的选择。然而，一个表如果只有一个索引，并且是非聚集索引，那么在聚集健上的非聚集索引 依赖将不再相关。如果是聚集索引，可以采取任何形式。幸运的是，大多数表有一个以上的非聚集索引，大多数的表有一个聚集索引性能更佳。因此，第一步：精心 选择聚集键，第二步，查找一个正确的平衡，选择适当的，最小数量的非聚集索引。

非聚集索引 （ＮonClustering Indexes)

正如前面所述，所有索引中有两类主要组件：叶级(leaf level)和非叶级(non-leaf Level(s)) 。一个聚集索引的叶级就是数据。一个非聚集索引叶级是一个独立的、额外的结构（一些数据的copy),特别地，一个非聚集索引取决于它的叶级形式的定义， 非聚集索引由索引键，任何包含性列、数据行的书签(bookmark)值，一个非聚集索引的数量跟表中的数据行一样多，除非索引定义时使用了过滤谓词 (Filter predicate)。过滤谓词在SQl Server 2008是新增的。

非聚集索引的工作方式有两种：

１、辅助指向数据或直接回答查询。当一个非聚集索引拥有查询中请求的全部数据时，这就是所谓的“查询覆盖(query covering)”，这时索引被称为覆盖索引，此时，非聚集索引被用于直接回答查询而避免了书签查询(bookmark lookup)，一种对于非聚集索引异常昂贵的查询。

２、当一个非聚集索引没有包含查询中请求的全部数据时，这个查询被一个索引能找到的谓词驱动，此时，书签查询发生。如果一个表有一个聚集索引，非聚 集索引被用于驱动查询去通过聚集键找到相应的数据行。如果是Heap(没有聚集索引），查询值是一个8字节的RID，实际行位置如下格式 FileID:PageID:SlotNumber。这个8字节值：2字节:4字节:2字节。后面还有深入了解。

非聚集索引的设置与否并不会影响数据页被组织。SQL Server　2008中，一个表中可以有999个非聚集索引。SQL Server 2005中是249个。实际使用环境中，你尽可能少数量的使用（因为一些例外(Exception)，比如Filtered Indexes）。

总而言之，非聚集索引不会影响基表，相反，基表的结构会影响非聚集索引的结构。如果你想减少开销和架构最佳性能，你最好理解这些。

约束和索引(Constaits and Indexes)

有关Primary Key约束和UNIQUE 约束，请查看MSDN:

http://msdn.microsoft.com/zh-cn/library/ms191236.aspx

http://msdn.microsoft.com/zh-cn/library/ms191166.aspx

一个基本的区别是后者可以是NUll,前者不允许NUll,两者都不允许重复。

Indexes Creation Options

CREATE INDEX命令的更多选项，请查看MSDN:

http://msdn.microsoft.com/zh-cn/library/ms188783.aspx

后面一节将学习物理的索引结构。