索引原理，查询机制（转）

（一）SQLS如何访问没有建立索引的数据表
　　Heap译成汉语叫做“堆”，其本义暗含杂乱无章、无序的意思，前面提到数据值被写进数据页时，由于每一行记录之间并没有特定的排列顺序，所以行与行的顺序就是随机无序的，当然表中的数据页也就是无序的了，而表中所有数据页就形成了“堆”。可以说，一张没有索引的数据表，就像一个只有书柜而没有索引卡片柜的图书馆，书库里面塞满了一堆乱七八糟的图书。当读者对管理员提交查询请求后，管理员就一头钻进书库，对照查找内容从头开始一架一柜的逐本查找。运气好的话，在第一个书架的第一本书就　　找到了，运气不好的话，要到最后一个书架的最后一本书才找到。
　　SQLS在接到查询请求时，首先会分析sysindexes表中一个叫做索引标志符(INDID: Index ID)的字段的值，如果该值为0，表示这是一张数据表而不是索引表，SQLS就会使用sysindexes表的另一个字段——也就是在前面提到过的FirstIAM值中找到该表的IAM页链，也就是所有数据页集合。
　　这就是对一个没有建立索引的数据表进行数据查找的方式，是不是很没效率？对于没有索引的表，对于一“堆”这样的记录，SQLS也只能这样做，而且更没劲的是，即使在第一行就找到了被查询的记录，SQLS仍然要从头到尾的将表扫描一次。这种查询称为“遍历”，又叫“表扫描”。
　　可见没有建立索引的数据表照样可以运行，不过这种方法对于小规模的表来说没有什么太大的问题，但要查询海量的数据效率就太低了。

（二）SQLS如何访问建立了非聚集索引的数据表
　　如前所述，非聚集索引可以建多个，具有B树结构，其叶级节点不包含数据页，只包含索引行。假定一个表中只有非聚集索引，则每个索引行包含了非聚集索引键值以及行定位符（ROW ID,RID），他们指向具有该键值的数据行，每一个RID由文件ID、页编号和在页中行的编号组成。
　　当INDID的值在2至250之间时，意味着表中存在非聚集索引页。此时，SQLS调用ROOT字段的值指向非聚集索引B树的ROOT，在其中查找与被查询最相近的值，根据这个值找到在非叶级节点中的页号，然后顺藤摸瓜，在叶级节点相应的页面中找到该值的RID，最后根据这个RID在Heap中定位所在的页和行并返回到查询端。
　　例如：假定在Lastname上建立了非聚集索引，则执行Select * From Member Where Lastname=’Ota’时，查询过程是：
　　①SQLS查询INDID值为2；
　　②立即从根出发，在非叶级节点中定位最接近Ota的值“Martin”，并查到其位于叶级页面的第61页；
　　③仅在叶级页面的第61页的Martin下搜寻Ota的RID，其RID显示为N∶706∶4，表示Lastname字段中名　　为Ota的记录位于堆的第706页的第4行，N表示文件的ID值，与数据无关；
　　④根据上述信息，SQLS立刻在堆的第706页第4行将该记录“揪”出来并显示于前台（客户端）。视表的数据量大小，整个查询过程费时从百分之几毫秒到数毫秒不等。
　　在谈到索引基本概念的时候，我们就提到了这种方式：图书馆的前台有很多索引卡片柜，里面分了若干的类别，诸如按照书名笔画或拼音顺序、作者笔画或拼音顺序等，但有两点不同之处：
　　① 索引卡片上记录了每本书摆放的具体位置——位于某柜某架的第几本——而不是“特殊编号”；
　　② 书脊上并没有那个“特殊编号”。管理员在索引柜中查到所需图书的具体位置（RID）后，根据RID直接在书库中的具体位置将书提出来。
　　显然，这种查询方式效率很高，但资源占用极大，因为书库中书的位置随时在发生变化，必然要求管理员花费额外的精力和时间随时做好索引更新。

（三）SQLS如何访问建立聚集索引的数据表
　　在聚集索引中，数据所在的数据页是叶级，索引数据所在的索引页是非叶级。
查询原理和上述对非聚集索引的查询相似，但由于记录是按照聚集索引中索引键值进行排序，换句话说，聚集索引的索引键值也就是具体的数据页。
　　这就好比书库中的书就是按照书名的拼音在排序，而且也只按照这一种排序方式建立相应的索引卡片，于是查询起来要比上述只建立非聚集索引的方式要简单得多。仍以上面的查询为例：
　　假定在Lastname字段上建立了聚集索引，则执行Select * From Member Where Lastname=’Ota’时，查询过程是：
　　①SQLS查询INDID值为1，这是在系统中只建立了聚集索引的标志；
　　②立即从根出发，在非叶级节点中定位最接近Ota的值“Martin”，并查到其位于叶级页面的第120页；
　　③在位于叶级页面第120页的Martin下搜寻到Ota条目，而这一条目已是数据记录本身；
　　④将该记录返回客户端。
　　这一次的效率比第二种方法更高，以致于看起来更美，然而它最大的优点也恰好是它最大的缺点——由于同一张表中同时只能按照一种顺序排列，所以在任何一种数据表中的聚集索引只能建立一个；并且建立聚集索引需要至少相当于源表120%的附加空间，以存放源表的副本和索引中间页。
　　难道鱼和熊掌就不能兼顾了吗？办法是有的。

（四）SQLS如何访问既有聚集索引、又有非聚集索引的数据表
　　如果我们在建立非聚集索引之前先建立了聚集索引的话，那么非聚集索引就可以使用聚集索引的关键字进行检索。就像在图书馆中，前台卡片柜中可以有不同类别的图书索引卡，然而每张卡片上都载明了那个特殊编号——并不是书籍存放的具体位置。这样在最大程度上既照顾了数据检索的快捷性，又使索引的日常维护变得更加可行，这是最为科学的检索方法。
　　也就是说，在只建立了非聚集索引的情况下，每个叶级节点指明了记录的行定位符（RID）；而在既有聚集索引又有非聚集索引的情况下，每个叶级节点所指向的是该聚集索引的索引键值，即数据记录本身。
假设聚集索引建立在Lastname上，而非聚集索引建立在Firstname上，当执行Select * From Member Where Firstname=’Mike’时，查询过程是：
　　①SQLS查询INDID值为2；
　　②立即从根出发，在Firstname的非聚集索引的非叶级节点中定位最接近Mike的值“Jose”条目；
　　③从Jose条目下的叶级页面中查到Mike逻辑位置——不是RID而是聚集索引的指针；
　　④根据这一指针所指示位置，直接进入位于Lastname的聚集索引中的叶级页面中到达Mike数据记录本身；
　　⑤将该记录返回客户端。
　　这就完全和我们在“索引的基本概念”中讲到的现实场景完全一样了，当数据发生更新的时候，SQLS只负责对聚集索引的键值加以维护，而不必考虑非聚集索引。只要我们在ID类的字段上建立聚集索引，而在其它经常需要查询的字段上建立非聚集索引，通过这种科学的、有针对性的在一张表上分别建立聚集索引和非聚集索引的方法，我们既享受了索引带来的灵活与快捷，又相对避免了维护索引所导致的大量的额外资源消耗。

索引的优点和不足
　　索引有一些先天不足
　　1、系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引；
　　2、更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性。
　　当然建立索引的优点也是显而易见的，在海量数据的情况下，如果合理的建立了索引，则会大大加强SQLS执行查询、对结果进行排序、分组的操作效率。
　　实践表明，不恰当的索引不但于事无补，反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引要花费更多的系统时间。
　　在如下字段建立索引应该是不恰当的：
　　1、很少或从不引用的字段；
　　2、逻辑型的字段，如男或女(是或否)等。
　　综上所述，提高查询效率是以消耗一定的系统资源为代价的，索引不能盲目的建立，必须要有统筹的规划，一定要在“加快查询速度”与“降低修改速度”之间做好平衡。有得必有失，此消则彼长，这是考验一个DBA是否优秀的很重要的指标

建立索引时一定要在“加快查询速度”与“降低修改速度”之间做好平衡，有得必有失，此消则彼长。那么，SQLS维护索引时究竟怎样消耗资源？应该从哪些方面对索引进行管理与优化？以下从六个方面来回答这些问题。 

一．页分裂 

微软MOC教导我们：当一个数据页达到了8K容量，如果此时发生插入或更新数据的操作，将导致页的分裂(又名页拆分)： 

1．有聚集索引的情况下：聚集索引将被插入和更新的行指向特定的页，该页由聚集索引关键字决定； 

2．只有堆的情况下：只要有空间就可以插入新的行，但是如果我们对行数据的更新需要更多的空间，以致大于当前页的可用空间，行就被移到新的页中，并且在原位置留下一个转发指针，指向被移动的新行，如果具有转发指针的行又被移动了，那么原来的指针将重新指向新的位置； 

3．如果堆中有非聚集索引，那么尽管插入和更新操作在堆中不会发生页分裂，但是在非聚集索引上仍然产生页分裂。 

无论有无索引，大约一半的数据将保留在老页面，而另一半将放入新页面，并且新页面可能被分配到任何可用的页。所以，频繁页分裂，后果很严重，将使物理表产生大量数据碎片，导致直接造成I/O效率的急剧下降，最后，不得不停止SQLS的运行并重建索引。 

二．填充因子 

然而在“混沌之初”，就可以在一定程度上避免不愉快出现，在创建索引时，可以为这个索引指定一个填充因子，以便在索引的每个叶级页面上保留一定百分比的空间，将来数据可以进行扩充和减少页分裂。填充因子是从0到100的百分比数值，设为100时表示将数据页填满，只有当不会对数据进行更改时(例如只读表中)才用此设置。值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中进行页分裂的需要，但这一操作需要占用更多的硬盘空间。 

填充因子只在创建索引时执行，索引创建以后，当表中进行数据的添加、删除或更新时，是不会保持填充因子的，如果想在数据页上保持额外的空间，则有悖于使用填充因子的本意，因为随着数据的输入，SQLS必须在每个页上进行页拆分，以保持填充因子指定的空闲空间。因此，只有在表中的数据进行了较大的变动，才可以填充数据页的空闲空间。这时，可以从容的重建索引，重新指定填充因子，重新分布数据。 

反之，填充因子指定不当，就会降低数据库的读取性能，其降低量与填充因子设置值成反比。例如，当填充因子的值为50时，数据库的读取性能会降低两倍。所以，只有在表中根据现有数据创建新索引，并且可以预见将来会对这些数据进行哪些更改时，设置填充因子才有意义。 

三．两道数学题 

假定数据库设计没有问题，那么是否像上篇分析的那样，当你建立了众多的索引，在查询工作中SQLS就只能按照“最高指示”用索引处理每一个提交的查询呢？答案是否定的。
实际上，SQLS几乎完全是“自主”的决定是否使用索引或使用哪一个索引。 


这是怎么回事呢？ 

让我们先来算一道题：如果某表的一条记录在磁盘上占用1000字节(1K)的话，我们对其中10字节的一个字段建立索引，那么该记录对应的索引大小只有10字节(0.01K)。上篇说过，SQLS的最小空间分配单元是“页（Page）”，一个页面在磁盘上占用8K空间，所以一页只能存储8条“记录”，但可以存储800条“索引”。现在我们要从一个有8000条记录的表中检索符合某个条件的记录(有Where子句)，如果没有索引的话，我们需要遍历8000条×1000字节/8K字节=1000个页面才能够找到结果。如果在检索字段上有上述索引的话，那么我们可以在8000条×10字节/8K字节=10个页面中就检索到满足条件的索引块，然后根据索引块上的指针逐一找到结果数据块，这样I/O访问量肯定要少得多。 

然而有时用索引比不用索引还快。 

同上，如果要无条件检索全部记录(不用Where子句)，不用索引的话，需要访问8000条×1000字节/8K字节=1000个页面；而使用索引的话，首先检索索引，访问8000条×10字节/8K字节=10个页面得到索引检索结果，再根据索引检索结果去对应数据页面，由于是检索全部数据，所以需要再访问8000条×1000字节/8K字节=1000个页面将全部数据读取出来，一共访问了1010个页面，这显然不如不用索引快。 

SQLS内部有一套完整的数据索引优化技术，在上述情况下，SQLS会自动使用表扫描的方式检索数据而不会使用任何索引。那么SQLS是怎么知道什么时候用索引，什么时候不用索引的呢？因为SQLS除了维护数据信息外，还维护着数据统计信息。 

四．统计信息 

打开企业管理器，单击“Database”节点，右击Northwind数据库→单击“属性”→选择“Options”选项卡，观察“Settings”下的各项复选项，你发现了什么？ 

从Settings中我们可以看到，在数据库中，SQLS将默认的自动创建和更新统计信息，这些统计信息包括数据密度和分布信息，正是它们帮助SQLS确定最佳的查询策略：建立查询计划和是否使用索引以及使用什么样的索引。 

在创建索引时，SQLS会创建分布数据页来存放有关索引的两种统计信息：分布表和密度表。查询优化器使用这些统计信息估算使用该索引进行查询的成本(Cost)，并在此基础上判断该索引对某个特定查询是否有用。 

随着表中的数据发生变化，SQLS自动定期更新这些统计信息。采样是在各个数据页上随机进行。从磁盘读取一个数据页后，该数据页上的所有行都被用来更新统计信息。统计信息更新的频率取决于字段或索引中的数据量以及数据更改量。比如，对于有一万条记录的表，当1000个索引键值发生改变时，该表的统计信息便可能需要更新，因为1000 个值在该表中占了10%，这是一个很大的比例。而对于有1千万条记录的表来说，1000个索引值发生更改的意义则可以忽略不计，因此统计信息就不会自动更新。 

五．索引的人工维护 

上面讲到,某些不合适的索引将影响到SQLS的性能,随着应用系统的运行,数据不断地发生变化,当数据变化达到某一个程度时将会影响到索引的使用。这时需要用户自己来维护索引。 

随着数据行的插入、删除和数据页的分裂，有些索引页可能只包含几页数据，另外应用在执行大量I/O的时候，重建非聚聚集索引可以维护I/O的效率。重建索引实质上是重新组织B树。需要重建索引的情况有： 

1．数据和使用模式大幅度变化； 

2．排序的顺序发生改变； 

3．要进行大量插入操作或已经完成； 

4．使用I/O查询的磁盘读次数比预料的要多； 

5．由于大量数据修改，使得数据页和索引页没有充分使用而导致空间的使用超出估算； 

6．dbcc检查出索引有问题。

六．索引的使用原则 

接近尾声的时候，让我们再从另一个角度认识索引的两个重要属性----惟一性索引和复合性索引。 

惟一性索引保证在索引列中的全部数据是惟一的，不会包含冗余数据。如果表中已经有一个主键约束或者惟一性约束，那么当创建表或者修改表时，SQLS自动创建一个惟一性索引。但出于必须保证惟一性，那么应该创建主键约束或者惟一性键约束，而不是创建一个惟一性索引。 

复合索引就是一个索引创建在两个列或者多个列上。在搜索时，当两个或者多个列作为一个关键值时，最好在这些列上创建复合索引。当创建复合索引时，应该考虑这些规则：最多可以把16个列合并成一个单独的复合索引，构成复合索引的列的总长度不能超过900字节；在复合索引中，所有的列必须来自同一个表中，不能跨表建立复合列；在复合索引中，列的排列顺序是非常重要的，原则上，应该首先定义最惟一的列，例如在（COL1，COL2）上的索引与在（COL2，COL1）上的索引是不相同的，因为两个索引的列的顺序不同；为了使查询优化器使用复合索引，查询语句中的WHERE子句必须参考复合索引中第一个列。 

综上所述，我们总结了如下索引使用原则： 

1．逻辑主键使用惟一的成组索引，对系统键（作为存储过程）采用惟一的非成组索引，对任何外键列采用非成组索引。考虑数据库的空间有多大，表如何进行访问，还有这些访问是否主要用作读写； 

2．不要索引memo/note 字段，不要索引大型字段（有很多字符），这样作会让索引占用太多的存储空间； 

3．不要索引常用的小型表； 

4．一般不要为小型数据表设置过多的索引，如果经常有插入和删除操作就更不要设置索引，因为SQLS对插入和删除操作提供的索引维护可能比扫描表空间消耗的时间更多。 

查询是一个物理过程，表面上是SQLS在东跑西跑，其实真正大部分压马路的工作是由磁盘输入输出系统(I/O)完成，全表扫描需要从磁盘上读表的每一个数据页，如果有索引指向数据值，则I/O读几次磁盘就可以了。但是，在随时发生的增、删、改操作中，索引的存在会大大增加工作量，因此，合理的索引设计是建立在对各种查询的分析和预测上的，只有正确地使索引与程序结合起来,才能产生最佳的优化方案。 

SQLS是一个很复杂的系统，让索引以及查询背后的东西真相大白，可以帮助我们更为深刻的了解我们的系统。一句话，索引就像盐，少则无味多则咸。

from http://blog.csdn.net/arrow_gx/archive/2008/05/22/2469555.aspx

转载于:https://www.cnblogs.com/sutengcn/archive/2008/06/15/1222650.html