二叉树 索引

http://blog.sina.com.cn/s/blog_4fd842b201009x22.html　

　没有什么比添加一条记录而等待大半天更痛苦的事了，而事实我发现公司所用的数据库设计就出现了很多的不明智选择，用了很多外键及触发器，至于存储过程更是无数，里边的加密存储过程我也偷着解密看了下，确实很伟大，里边有很多“大事务”，对于一个几十万条记录的表来说，这是个很惊人的举动，难怪客户数据出现报错的大小都超过1G以上的，而在机器配置不到2G内存的机器往往会有数据错误发生，前阵子就有一个事务死锁的现像，很明显，就是由于共享锁在转变为排它锁后，独占了资源，而后面的列新锁将和此前的锁进行无休止的斗争。更为奇怪的是老有触发器不能触发，至今也不能知道是什么原因造成这个局面的。

　　其实可以学习一下B树，了解一下索引，这是个不错的选择。

B树

　　对于一维升序或降序数据序列（假设其个数为N）来说，可以采用两分检索的方法来迅速地找到需要插入或删除元素的位置。但是当采用顺序存储的方式时，为插入一个元素，需要将其以下的数据均进行后移；为删除一个元素，需要将以下的数据进行前移。为避免大量的数据移动，提高插入和删除的工作效率，研究者提出了多种解决方法，B树就是其中较好的一种方案。

B树是由一系列节点（SQL Server数据库采结构进行数据存储）所构成，它的每一个节点均由2m个数据域和2m+1个指针域所构成，每个节点的数据从左向右成升序排列。一般情况下，B树的每个节点中的数据域不一定存放满数据，但基本上每个节点存放的数据数大于用B树m个。如下图所示：

　　B树中父节点与子节点中的数据之间具有如下关系：父节点中每一数据域中存放的数据，均大于该数据域左侧指针指向的子节点中的所有数据，也小于该数据域右侧指针指向子节点中的所有数据。以图2－2所示的B树来看，节点A中的数据0.25，其左侧的指针指向节点B，B中的数据均小于0.25，其右侧的指针指向C，C中的数据也均大于0.25。

　　为建立一棵B树，需要将一个一个的数据插入其中。

　　当需要在上面所示的B树中插入一个数据，例如是0.65，首先需要查询其应插入的位置。首先将根节点的数据与带插入数据向比较，其结果发现应插入在0.40与0.72之间。而后，根据这两个数据之间的指针所值的位置，查到所指向的子节点D。比较之后确认应插入在数据0.60之后，当检查0.60右侧的指针后发现该指针为空，由此确认应插入在节点D中数据0.60之右侧，恰巧在这个位置是空的，因此插入数据0.65后即完成了所需的插入工作。这是存在的另一种可能性，是在0.60右侧有另外的数据，但节点D中还有空间允许填入新的数据，这是需要将0.60后面的数据进行右移，空出位置来插入0.65这一数据。

　　当查询到插入位置，却发现该节点已填满数据时，我们需要进行节点的分割。仍以上述B树为例，设需要插入的数据是0.10。采用相同的方法，确认需要插入的位置在节点B的数据0.12的左侧，但由于节点B已填入了四个数据，必须建立新的节点存放数据。为此，我们将原节点中存放的数据和待插入的数据一起，找寻其中间数据，根据中间数据将这2m+1个数据分为两部分：小于中间数据的m个数据存入新的节点B1，大于中间数据的m个数据存入节点B2中，将中间数据存入节点B的父节点A中，同时对中间数据两侧的指针加以处理，使其指向节点B1和B2。当出现父节点同样数据存满的情况时，采用类似的方法将父节点进行相应的分割。

索引
　　SQL Server 数据页和索引页都是 8K 字节大。SQL Server 数据页包含除了文本和图像数据以外所有与表的某一行相关的数据。对于文本和图像数据，包含与文本/图像列有关的行的 SQL Server 数据页将包含一个指针，该指针指向一个包含一个或多个 8 KB 页的 B 树结构，文本/图像数据便包含在在该 B 树结构中。

　　SQL Server 索引页仅包含组成特定索引的列中的数据。这意味着与 8 KB 数据页相比，索引页可以有效地将与更多行相关的信息压缩到一个 8 KB 页。可以想象索引的 I/O 性能可由此获得改进。如果所提取的列（这些列构成了索引的一部分）占表的行大小的百分比相对较低，这种推想是对的。当 SQL 查询要求某个表中的一个行集，这些行的某些值与查询中的列相匹配，SQL Server 可以节省 I/O 操作和时间，因为可以只读取索引页来查找这些值，然后只访问表中满足查询的所需行，而无须执行 I/O 操作以扫描表中所有行来找到所需行。如果定义索引时选择得很好，那么实际情况就是这样。

　　有两种 SQL Server 索引，且这两种索引均建立在由 8 KB 索引页所组成的 B 树结构上。它们的不同在于 B 树结构的底部，其底部在 SQL Server 文档中称为叶级。索引 B 树结构的上半部分称为非叶级索引。为每个索引建立的 B 树结构都在 SQL Server 表中定义。

图 1  聚集和非聚集索引 B 树结构

　　图1 说明了非聚集索引和聚集索引在结构上的不同。要记住的关键点是：在非聚集索引中，叶级节点仅包含参与索引的数据以及快速找到相关数据页上其它行数据的指针。最糟糕的情况是，从非聚集索引中获得的每一行都要求一个额外的不连续磁盘 I/O 才能检索行数据。最好的情况是，所需要的行有许多都位于相同的数据页，因此在提取每个数据页时可检索多行。如果是聚集索引，索引的叶级节点是表的实际数据行。因此，检索表数据时不需要指针跳动。基于聚集索引的范围扫描执行情况很好，因为聚集索引的叶级（即表的所有行）在物理上按照组成聚集索引的列顺序排列在磁盘上，因此，可以执行 64 KB 扩展盘区 I/O。并且，如果聚集索引 B 树（非叶级和叶级）上没有大量分割的页，这些 64 KB I/O 还可以在物理上连续。虚线表示 B 树结构中还有其它 8 KB 页，但未显示出来。

 

聚集索引

　　每个表中只能有一个聚集索引。物理原因很简单。尽管聚集索引 B 树结构的上半部分（在 SQL Server 文档中通常称为非叶级）与非聚集索引 B 树的结构相似，但是聚集索引 B 树的下半部分是与表相关的实际的 8 KB 数据页。这里暗含着两种性能：

通过聚集索引基于关键字搜索来检索 SQL 数据时不需要指针跳动（类似于硬盘上位置的不连续改变）就可以获得相关的数据页，因为叶级聚集索引已经是相关的数据页。

聚集索引的叶级按照组成聚集索引的列排序。因为聚集索引的叶级包含表的实际 8 KB 数据页，这意味着整个表的行数据在物理上按照聚集索引确定的顺序排列在磁盘驱动器上。当根据聚集索引的值从这个表中提取大量行时，这种排列提供了一种潜在的 I/O 性能优势，因为使用的是连续磁盘 I/O（除非该表上发生了页拆分，我们将在后面的“FILLFACTOR 和 PAD_INDEX 的重要性”中讨论这个问题）。这就是根据执行范围扫描检索大量的行时所使用的列来提取表中的聚集索引很重要的原因。

 

非聚集索引

　　如果要根据键值从大型 SQL Server 表提取具有良好选择性的少数几行，非聚集索引最有用。以前已提到过，非聚集索引是由 8 KB 索引页组成的 B 树。索引页的 B 树的底部或叶级包含组成该索引的列中的所有数据。当用非聚集索引检索表中与键值匹配的信息时，将搜索整个索引 B 树，直到在索引叶级找到一个与键值匹配的值。如果需要的列不是索引组成的一部分，则会发生指针跳动。该指针跳动可能需要在磁盘上进行一个不连续 I/O 操作。如果表以及它相应的索引 B 树很大，甚至可能要求从另一个磁盘中读取数据。如果多个指针指向同一个 8 KB 数据页，则对 I/O 性能的影响比较小，因为只须将该数据页读入数据高速缓存一次。如果某个 SQL 查询涉及到要用非聚集索引进行搜索，那么对于所返回的每一行，均需要一次指针跳动。这些指针跳动可以解释为什么非聚集索引更适合于只返回表中一行或几行的 SQL 查询，而聚集索引更适合于要求返回许多行的查询。

有关详细信息，请在 SQL Server Books Online 中搜索字符串“nonclustered index”。

 

覆盖索引

　　非聚集索引的一个特例是覆盖索引。覆盖索引的定义是在选择条件和 WHERE 谓词上均满足 SQL 查询的所有列的基础上建立的非聚集索引。覆盖索引可以节省大量的 I/O，因此可极大地改善查询的性能。但是有必要在新建索引（以及与它相关的 B 树索引结构维护）所需要的代价和覆盖索引所带来的 I/O 性能增益之间进行权衡。如果覆盖索引对于 SQL Server 上经常运行的查询或查询组极其有利，那么创建覆盖索引是值得的。

覆盖索引的示例

Select col1,col3 from table1 where col2 = 'value'.

Create index indexname1 on table1(col2,col1,col3).

或者

使用 SQL Server Enterprise Manager 中的 Create Index Wizard 创建索引。从 SQL Server Enterprise Manager 菜单栏中选择 Tools/Wizards，左击 Database 旁边的 + 图标将出现数据库向导，然后双击 Create Index Wizard 启动该向导。

　　本例中创建出来的索引“indexname1”是一个覆盖索引，因为它包括 SELECT 语句和 WHERE 谓词中的所有列。即在执行此查询期间，SQL Server 不需要访问与 table1 相关的数据页。SQL Server 使用索引 indexname1 可以获得满足查询所需要的全部信息。在 SQL Server 已遍历与 indexname1 相关的 B 树，并找到 col2 等于“value”的索引关键字范围，SQL Server 就知道它可以从覆盖索引的叶级（底层）提取所有需要的数据 (col1,col2,col3)。这从两个方面改进了 I/O 性能：SQL Server 从索引页而不是数据页获取所有需要的数据，因此数据的压缩率更高，使 SQL Server 可以节省磁盘 I/O 操作。覆盖索引按照 col2 将所有需要的数据以物理方式组织在磁盘上。使硬盘得以连续返回与 where 谓词 (col2 = "value") 相关的所有索引行。从而为我们提供了更好的 I/O 性能。实际上，从磁盘 I/O 的角度来看，覆盖索引成了此查询以及可被此覆盖查询中的列完全满足的任何其它查询的聚集索引。

　　总而言之，如果覆盖索引中的所有列的字节数比该表中单行的字节数少，并且可以肯定将反复执行使用此覆盖索引的查询，那么使用覆盖索引是有意义的。