MS SQL 索引(二)

旧城等待， 2022-07-16 19:43 508阅读 0赞

**(一）索引结构：**

**    表****（Tables）**中数据实际上都存储在**页（pages）**里，除了BLOB类型的数据。如果某列的字段的类型为BLOB那么将有一个16字节的指针指向BLOB page。页是MS SQL Server中数据存储的最小单位。每页包含以行（row）为单位保存数据。一行只能存储在一个页中。**每页可以容纳8KB的信息****。**因为这个原因，每行的最大值为8KB。一组相邻的8个页被称为一个**盘区（Extent）**  
**   堆文件和分配映射索引（Heap and the Index Allocation Map(IAM)）**

**    堆文件在sysindexs表中只有一行记录，并且其indid = 0. sysindexs.FIRSTIAM字段指向了IAM页链表中一个IAM页，IAM页是用来管理SQL Server已经给堆文件分配的空间。MS SQL Server2000用IAM（Index Allocation Map）页来在堆文件中导航（navigate）。在堆文件中，数据页（data page）和数据页中数据没有按照特定的顺序存储，也没有链接在一起。数据页之间唯一的逻辑链接是通过IAM页中记录来实现的。**

**    所有的SQL Server 索引都是 B-Trees。在这种树的顶端有一个根页（root page），通过root page来访问N个中级（intermediate level）页，直到树的底部、或叶级（leaf level）。可以通过树中每个节点的指针从上向下扫描整个索引树。另外，每个索引级（index leves）（可能是intermediate leve or leaf level）都有一个页链（page chain）。在一个索引中有许多intermediate level。索引树的级数（树的高度）与索引码的宽度、索引类型、记录行数和表中的页数有关，并且索引树的级数是影响索引性能的一个重要参数。  
    如果当前的表是以聚集索引方式存储，那么非聚集索引的位置信息就是聚集索引的索引码（index key）；否则，位置信息就是row ID（RID)，每个RID由file number、page number和 slot number of row（每行记录的槽号）。比如，要在一个表中检索某个employee ID（emp\_id），该表已经有在emp\_id列上创建了非聚集索引，SQL Server查找索引树，找到一个索引条目包含你需要查找的emp\_id,然后利用其中RID来访问到对应数据页中的值。**

在《数据库原理》里面，对聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索引的解释是:索引顺序与数据物理排列顺序无关。正式因为如此，所以一个表最多只能有一个聚簇索引。

不过这个定义太抽象了。在SQL Server中，索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。如下图：

![聚集索引][375658_1290751369wWp9.jpg]![非聚集索引][375658_12907513760PKu.jpg]

之前的BLOG里的文章[MS SQL 索引(一)][MS SQL]也介绍了SQL server的基本概念。

**（二）索引块与数据块的区别**

索引可以提高检索效率，因为它的二叉树结构以及占用空间小，所以访问速度块。让我们来算一道数学题：如果表中的一条记录在磁盘上占用1000字节的话，我们对其中10字节的一个字段建立索引，那么该记录对应的索引块的大小只有10字节。我们知道，SQL Server的最小空间分配单元是“页（Page）”，一个页在磁盘上占用8K空间，那么这一个页可以存储上述记录8条，但可以存储索引800条。现在我们要从一个有8000条记录的表中检索符合某个条件的记录，如果没有索引的话，我们可能需要遍历8000条×1000字节/8K字节=1000个页面才能够找到结果。如果在检索字段上有上述索引的话，那么我们可以在8000条×10字节/8K字节=10个页面中就检索到满足条件的索引块，然后根据索引块上的指针逐一找到结果数据块，这样IO访问量要少的多。

**（三）索引优化技术**

是不是有索引就一定检索的快呢？答案是否。有些时候用索引还不如不用索引快。比如说我们要检索上述表中的所有记录，如果不用索引，需要访问8000条×1000字节/8K字节=1000个页面，如果使用索引的话，首先检索索引，访问8000条×10字节/8K字节=10个页面得到索引检索结果，再根据索引检索结果去对应数据页面，由于是检索所有数据，所以需要再访问8000条×1000字节/8K字节=1000个页面将全部数据读取出来，一共访问了1010个页面，这显然不如不用索引快。

SQL Server内部有一套完整的数据检索优化技术，在上述情况下，SQL Server的查询计划（Search Plan）会自动使用表扫描的方式检索数据而不会使用任何索引。那么SQL Server是怎么知道什么时候用索引，什么时候不用索引的呢？SQL Server除了日常维护数据信息外，还维护着数据统计信息，下图是数据库属性页面的一个截图：（原图为SQL SERVER2000的图）

![索引统计][375658_1290751376iNg1.jpg]

**（四）聚簇索引与非聚簇索引的本质区别**

现在可以讨论聚簇索引与非聚簇索引的本质区别了。正如本文最前面的两个图所示，聚簇索引的叶节点就是数据节点，而非聚簇索引的页节点仍然是索引检点，并保留一个链接指向对应数据块。

还是通过一道数学题来看看它们的区别吧：假设有一8000条记录的表，表中每条记录在磁盘上占用1000字节，如果在一个10字节长的字段上建立非聚簇索引主键，需要二叉树节点16000个（这16000个节点中有8000个叶节点，每个页节点都指向一个数据记录），这样数据将占用8000条×1000字节/8K字节=1000个页面；索引将占用16000个节点×10字节/8K字节=20个页面，共计1020个页面。

同样一张表，如果我们在对应字段上建立聚簇索引主键，由于聚簇索引的页节点就是数据节点，所以索引节点仅有8000个，占用10个页面，数据仍然占有1000个页面。

下面我们看看在执行插入操作时，非聚簇索引的主键为什么比聚簇索引主键要快。主键约束要求主键不能出现重复，那么SQL Server是怎么知道不出现重复的呢？唯一的方法就是检索。对于非聚簇索引，只需要检索20个页面中的16000个节点就知道是否有重复，因为所有主键键值在这16000个索引节点中都包含了。但对于聚簇索引，索引节点仅仅包含了8000个中间节点，至于会不会出现重复必须检索另外1000个页数据节点才知道，那么相当于检索10+1000=1010个页面才知道是否有重复。所以聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。

让我们再来看看数据检索的效率，如果对上述两表进行检索，在使用索引的情况下（有些时候SQL Server执行计划会选择不使用索引，不过我们这里姑且假设一定使用索引），对于聚簇索引检索，我们可能会访问10个索引页面外加1000个数据页面得到结果（实际情况要比这个好），而对于非聚簇索引，系统会从20个页面中找到符合条件的节点，再映射到1000个数据页面上（这也是最糟糕的情况），比较一下，一个访问了1010个页面而另一个访问了1020个页面，可见检索效率差异并不是很大。所以不管非聚簇索引也好还是聚簇索引也好，都适合排序，聚簇索引仅仅比非聚簇索引快一点。

[375658_1290751369wWp9.jpg]: http://hi.csdn.net/attachment/201011/26/375658_1290751369wWp9.jpg
[375658_12907513760PKu.jpg]: /images/20220709/593fd0f92c634fd48dd10e41b1e82690.png
[MS SQL]: http://blog.csdn.net/java3344520/archive/2010/11/23/6029040.aspx
[375658_1290751376iNg1.jpg]: /images/20220709/95192484a584495fbf672309a1a3c4c9.png