本文详细介绍了SQL Server中索引的工作原理及优化方法，包括索引类型如簇索引和非簇索引的区别，以及如何通过理解索引结构来提高查询效率。
CHAPTER 6
规划索引
现在我们先来做一下实验，看一看有无索引对查询速度的影响：在一个有 1500000 条纪录的表中，选择一条数据，在无索引时用时：用时 9 秒，而有索引时用时 0 秒，打开执行计划可以看到更加详细的纪录 !
如果你访问一家网站，但是每打开一个网页要等待 60 秒，你还愿意再次访问吗？由于索引能够大副度提高提高性能，所以索引对于我们来说很重要 ! 我们现在就来看看索引如何改进数据库的性能：
1- 我们先来介绍一下索引：
行以数据页的形式存储，每个数据页包括 8K 的信息。每八个相连的数据页被称为一个扩展，堆是表中数据页的集合。
SQL SERVER 提供两种方法来访问数据：表扫描和使用索引。当我们使用表扫描时，就从表的开始开始，逐页的扫描所有数据页，并从中抽取满足条件的行；当我们使用索引里，遍历索引树结构来查找要求的行，然后从数据页上抽取这些满足条件的行；在决定是否使用索引里， SQL SERVER 的优化器会决定对访问数据来说，扫描表和使用索引结构哪个更加有效 !
任何事物都有两面性，索引也是如此，它可以对数据的访问速度，但是也会增加磁盘的空间使用和开销 ( 维护索引和更新数据时 )
2 ．索引结构：
堆：如果我们没有定义簇索引， SQL SERVER 维护堆中的数据页。它使用 IAM 页来连接数据页，浏览和查找插入新行所需的空间，当行被删除时，进行空间的回收
簇索引：如果我们要指定关键值的范围或者是进行排序查找，则簇索引会非常有用。每个表只能有一个簇索引，表的物理顺序与索引中的物理顺序是一致的，当索引被创建期间，它使用当前数据库的空间，一般需要 1.2 倍的表大小 !
非簇索引：这是我们创建索引的默认选择。它于簇索引不同的就是它的页级页顺序与表的物理顺序不同，叶级页以升序排列。它有它可以在一个表中建 249 个非簇索引，而簇索引只能够建一个，因为我们知道一个物理表只能够拥有一个物理顺序 ! 它在簇索引被卸载，创建和使用 drop_existing 改变簇索引的列时会自动重建 !
3 ． SQL SERVER 如何检索存储的数据：
1 ．我们首先要使用 SYSINDEXES 表，其中 indid 为 0 时为堆，这时 FIRSTIAM 指向表中数据页集合的 IAM 页链。为 1 时此表上建有簇索引， ROOT 指向簇索引 B-TREE 的根结构。为 2-250 时，为表中存在非簇索引。与簇索引相同，它也使用 ROOT 列中的值指向非簇索引平衡树的根结构 !
2 ．不使用索引而查找行：此时使用表扫描来检索行。刚才我们讲到也 SYSINDEXES 表，现在我们就要使用 SYSINDEXES 表中的 FIRSTIAM 来查找 IAM 页，因为 IAM 页中包括有与表相关的所以页的列表 !
3 ．使用非簇索引在堆中查找行： B-TREE ；它像一个带有索引的课本，索引存在一个地方而数据存在另一个地方。在叶级索引页上的指针指示了索引条目的存储位置 ! 这个指针 (RID) 是由文件 ID ，页 ID ，和行 ID 组成 !
4 ．使用簇索引来查找行：簇索引和非簇索引与 B-TREE 都很相似：但是与非簇索引的不同之处在于：它的叶级页就是数据页，同时簇索引中的数据行被排序并根据其簇关键字进行存储 ! 它就像一个电话号码表目录，同姓的人被分到书的同一部份。我们要保持簇索引中的关键字较小，此时可以减少 I/0;
5 ．使用带有非簇索引的簇索引来查找行：它时非簇索引的行定位符不是以前的那个由文件 ID ，页 ID 和行 ID 组成的 RID 了，而是簇关键字索引值 !
由于在遍历簇索引结构和非簇索引结构，所以产生附加的 I/O 。由时由于 簇索引的字值都大于堆中的 8 位 RID ，所以要保持簇索引中的关键字较小 !
4 ． SQL SERVER 如何维护索引和堆：
1. 页拆分：簇索引将被插入和更新的行指向特定的页，该页由簇索引关键字的值决定 ! 如果数据页或索引页没有足够的空间来容纳数据，那么在页分裂过程中将增加新的页，同时大约一半的数据还留在老的页中，一半将留在新的页中 !
逻辑上看，新的页是接着老的页的，但是物理上说，新的页与老的页可能被指派到任何可用的页。所以如果一个索引产生的大量的页分裂，我们要重建索引来改进性能 !
2 ．堆中的转发指针：堆中不发生页分裂。只要有空间可以使用就可以插入新行 ! 但是如果我们对行的更新需要比当前页中的可使用空间更多的空间，行就被移到新的页中。行在原位置会留下一个转发指针，由这个指针指向被移到的新的行 !
如果堆中有非簇索引，那么尽管插入和更新在堆中不会发生页分裂，但是在非簇索引上可能会产生页分裂 !
3 ．行更新：在堆中，如果更新没有增大记录或者就算增大了记录但在该页仍可存放时，导致行的移到 ! 批量更新只涉及到每个索引一次 !
4 ．行删除：如果我们从数据页上删除了页上的最后一行，那么该数据页将整修消失 !
在索引中的行如果被删除，那么它的空间可被相邻的行使用，但是会遗留下一些空隙 !
在堆中删除行并不被压缩，直到该空间被要求用插入 !
幽灵记录：
5 ．决定在哪一列上创建索引：查询，高选择性和低密度 !
http://www.cnblogs.com/net2004/articles/69042.html
现在我们先来做一下实验，看一看有无索引对查询速度的影响：在一个有 1500000 条纪录的表中，选择一条数据，在无索引时用时：用时 9 秒，而有索引时用时 0 秒，打开执行计划可以看到更加详细的纪录 !
如果你访问一家网站，但是每打开一个网页要等待 60 秒，你还愿意再次访问吗？由于索引能够大副度提高提高性能，所以索引对于我们来说很重要 ! 我们现在就来看看索引如何改进数据库的性能：
1- 我们先来介绍一下索引：
行以数据页的形式存储，每个数据页包括 8K 的信息。每八个相连的数据页被称为一个扩展，堆是表中数据页的集合。
SQL SERVER 提供两种方法来访问数据：表扫描和使用索引。当我们使用表扫描时，就从表的开始开始，逐页的扫描所有数据页，并从中抽取满足条件的行；当我们使用索引里，遍历索引树结构来查找要求的行，然后从数据页上抽取这些满足条件的行；在决定是否使用索引里， SQL SERVER 的优化器会决定对访问数据来说，扫描表和使用索引结构哪个更加有效 !
任何事物都有两面性，索引也是如此，它可以对数据的访问速度，但是也会增加磁盘的空间使用和开销 ( 维护索引和更新数据时 )
2 ．索引结构：
堆：如果我们没有定义簇索引， SQL SERVER 维护堆中的数据页。它使用 IAM 页来连接数据页，浏览和查找插入新行所需的空间，当行被删除时，进行空间的回收
簇索引：如果我们要指定关键值的范围或者是进行排序查找，则簇索引会非常有用。每个表只能有一个簇索引，表的物理顺序与索引中的物理顺序是一致的，当索引被创建期间，它使用当前数据库的空间，一般需要 1.2 倍的表大小 !
非簇索引：这是我们创建索引的默认选择。它于簇索引不同的就是它的页级页顺序与表的物理顺序不同，叶级页以升序排列。它有它可以在一个表中建 249 个非簇索引，而簇索引只能够建一个，因为我们知道一个物理表只能够拥有一个物理顺序 ! 它在簇索引被卸载，创建和使用 drop_existing 改变簇索引的列时会自动重建 !
3 ． SQL SERVER 如何检索存储的数据：
1 ．我们首先要使用 SYSINDEXES 表，其中 indid 为 0 时为堆，这时 FIRSTIAM 指向表中数据页集合的 IAM 页链。为 1 时此表上建有簇索引， ROOT 指向簇索引 B-TREE 的根结构。为 2-250 时，为表中存在非簇索引。与簇索引相同，它也使用 ROOT 列中的值指向非簇索引平衡树的根结构 !
2 ．不使用索引而查找行：此时使用表扫描来检索行。刚才我们讲到也 SYSINDEXES 表，现在我们就要使用 SYSINDEXES 表中的 FIRSTIAM 来查找 IAM 页，因为 IAM 页中包括有与表相关的所以页的列表 !
3 ．使用非簇索引在堆中查找行： B-TREE ；它像一个带有索引的课本，索引存在一个地方而数据存在另一个地方。在叶级索引页上的指针指示了索引条目的存储位置 ! 这个指针 (RID) 是由文件 ID ，页 ID ，和行 ID 组成 !
4 ．使用簇索引来查找行：簇索引和非簇索引与 B-TREE 都很相似：但是与非簇索引的不同之处在于：它的叶级页就是数据页，同时簇索引中的数据行被排序并根据其簇关键字进行存储 ! 它就像一个电话号码表目录，同姓的人被分到书的同一部份。我们要保持簇索引中的关键字较小，此时可以减少 I/0;
5 ．使用带有非簇索引的簇索引来查找行：它时非簇索引的行定位符不是以前的那个由文件 ID ，页 ID 和行 ID 组成的 RID 了，而是簇关键字索引值 !
由于在遍历簇索引结构和非簇索引结构，所以产生附加的 I/O 。由时由于 簇索引的字值都大于堆中的 8 位 RID ，所以要保持簇索引中的关键字较小 !
4 ． SQL SERVER 如何维护索引和堆：
1. 页拆分：簇索引将被插入和更新的行指向特定的页，该页由簇索引关键字的值决定 ! 如果数据页或索引页没有足够的空间来容纳数据，那么在页分裂过程中将增加新的页，同时大约一半的数据还留在老的页中，一半将留在新的页中 !
逻辑上看，新的页是接着老的页的，但是物理上说，新的页与老的页可能被指派到任何可用的页。所以如果一个索引产生的大量的页分裂，我们要重建索引来改进性能 !
2 ．堆中的转发指针：堆中不发生页分裂。只要有空间可以使用就可以插入新行 ! 但是如果我们对行的更新需要比当前页中的可使用空间更多的空间，行就被移到新的页中。行在原位置会留下一个转发指针，由这个指针指向被移到的新的行 !
如果堆中有非簇索引，那么尽管插入和更新在堆中不会发生页分裂，但是在非簇索引上可能会产生页分裂 !
3 ．行更新：在堆中，如果更新没有增大记录或者就算增大了记录但在该页仍可存放时，导致行的移到 ! 批量更新只涉及到每个索引一次 !
4 ．行删除：如果我们从数据页上删除了页上的最后一行，那么该数据页将整修消失 !
在索引中的行如果被删除，那么它的空间可被相邻的行使用，但是会遗留下一些空隙 !
在堆中删除行并不被压缩，直到该空间被要求用插入 !
幽灵记录：
5 ．决定在哪一列上创建索引：查询，高选择性和低密度 !
http://www.cnblogs.com/net2004/articles/69042.html
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
