数据库索引的原理到底是什么剖析

数据库索引的原理到底是什么？
中小企业MIS 系统的管理基本上由两大部份组成，一是前台的可视化操作，二是后台的数据库管理。

网管对前台的管理和维护工作包括保障网络链路通畅、处理MIS 终端的突发事件以及对操作员的管理、培训等，这是网管们日常做得最多、最辛苦的功课；然而MIS 系统架构中同等重要的针对数据库的管理、维护和优化工作，现实中似乎并没有得到网管朋友的足够重视，看起来这都是程序员的事，事实上，一个网管如果能在MIS 设计期间就数据表的规范化、表索引优化、容量设计、事务处理等诸多方面与程序员进行卓有成效的沟通和协作，那么日常的前台管理工作将会变得大为轻松, 因为在某种意义上，数据库管理系统就相当于操作系统，在系统中占有同样重要的位置。

这正是SQL SERVER等数据库管理系统和dBASEX 、ACCESS 等数据库文件系统的本质区别，所以，对数据库管理系统操作能力的强弱在某种程度上也折射出了网管的水平——个人认为，称得上优秀的Admin, 至少应该是一个称职的DBA （数据库管理员）。

下面以SQL SERVER（下称 SQLS ）为例，将数据库管理中难于理解的“索引原理”问题给各位朋友作一个深入浅出的介绍。

其他的数据库管理系统如Oracle 、Sybase 等，朋友们可以融会贯通，举一反三。

一、数据表的基本结构
建立数据库的目的是管理大量数据，而建立索引的目的就是提高数据检索效率，改善数据库工作性能，提高数据访问速度。

对于索引，我们要知其然，更要知其所以然，关键在于认识索引的工作原理，才能更好的管理索引。

为认识索引工作原理，首先有必要对数据表的基本结构作一次全面的复习。

SQLS 当一个新表被创建之时，系统将在磁盘中分配一段以8K 为单位的连续空间，当字段的值从内存写入磁盘时，就在这一既定空间随机保存，当一个8K 用完的时候，SQLS 指针会自动分配一个8K 的空间。

这里，每个8K 空间被称为一
个数据页（Page ），又名页面或数据页面，并分配从0-7的页号, 每个文件的第0页记录引导信息，叫文件头（File header）；每8个数据页（64Ｋ）的组合形成扩展区（Extent ），称为扩展。

全部数据页的组合形成堆（Heap ）。

SQLS 规定行不能跨越数据页，所以，每行记录的最大数据量只能为8K 。

这就是char 和varchar 这两种字符串类型容量要限制在8K 以内的原因，存储超过8K 的数据应使用text 类型，实际上，text 类型的字段值不能直接录入和保存，它只是存储一个指针，指向由若干8K 的文本数据页所组成的扩展区，真正的数据正是放在这些数据页中。

页面有空间页面和数据页面之分。

当一个扩展区的8个数据页中既包含了空间页面又包括了数据或索引页面时，称为混合扩展（Mixed Extent）, 每张表都以混合扩展开始；反之，称为一致扩展（Uniform Extent），专门保存数据及索引信息。

表被创建之时，SQLS 在混合扩展中为其分配至少一个数据页面，随着数据量的增长，SQLS 可即时在混合扩展中分配出7个页面，当数据超过8个页面时，则从一致扩展中分配数据页面。

空间页面专门负责数据空间的分配和管理，包括：PFS 页面（Page free space）：记录一个页面是否已分配、位于混合扩展还是一致扩展以及页面上还有多少可用空间等信息；GAM 页面（Global allocation map）和SGAM 页面(Secodary global allocation map：用来记录空闲的扩展或含有空闲页面的混合扩展的位置。

SQLS 综合利用这三种类型的页面文件在必要时为数据表创建新空间；
数据页或索引页则专门保存数据及索引信息，SQLS 使用4种类型的数据页面来管理表或索引：它们是IAM 页、数据页、文本/图像页和索引页。

在WINDOWS 中，我们对文件执行的每一步操作，在磁盘上的物理位置只有系统（system ）才知道；SQL SERVER沿袭了这种工作方式，在插入数据的过程
中，不但每个字段值在数据页面中的保存位置是随机的，而且每个数据页面在“堆”中的排列位置也只有系统（system ）才知道。

这是为什么呢？众所周知，OS 之所以能管理DISK ，是因为在系统启动时首先加载了文件分配表：FAT （File Allocation Table），正是由它管理文件系统并记录对文件的一切操作，系统才得以正常运行；同理，作为管理系统级的SQL SERVER,也有这样一张类似FAT 的表存在，它就是索引分布映像页：IAM （Index Allocation Map）。

IAM 的存在，使SQLS 对数据表的物理管理有了可能。

IAM 页从混合扩展中分配，记录了8个初始页面的位置和该扩展区的位置，每个IAM 页面能管理512,000个数据页面，如果数据量太大，SQLS 也可以增加更多的IAM 页，可以位于文件的任何位置。

第一个IAM 页被称为FirstIAM, 其中记录了以后的IAM 页的位置。

数据页和文本/图像页互反，前者保存非文本/图像类型的数据，因为它们都不超过8K 的容量，后者则只保存超过8K 容量的文本或图像类型数据。

而索引页顾名思义，保存的是与索引结构相关的数据信息。

了解页面的问题有助我们下一步准确理解SQLS 维护索引的方式，如页拆分、填充因子等。

二、索引的基本概念
索引是一种特殊类型的数据库对象，它与表有着密切的联系。

索引是为检索而存在的。

如一些书籍的末尾就专门附有索引，指明了某个关键字在正文中的出现的页码位置，方便我们查找，但大多数的书籍只有目录，目录不是索引，只是书中内容的排序，并不提供真正的检索功能。

可见建立索引要单独占用空间；索引也并不是必须要建立的，它们只是为更好、更快的检索和定位关键字而存在。

再进一步说，我们要在图书馆中查阅图书，该怎么办呢？图书馆的前台有很多叫做索引卡片柜的小柜子，里面分了若干的类别供我们检索图书，比如你可以用书
名的笔画顺序或者拼音顺序作为查找的依据，你还可以从作者名的笔画顺序或拼音顺序去查询想要的图书，反正有
许多检索方式，但有一点很明白，书库中的书并没有按照这些卡片柜中的顺序排列——虽然理论上可以这样做，事实上，所有图书的脊背上都人工的粘贴了一个特定的编号①，它们是以这个顺序在排列。

索引卡片中并没有指明这本书摆放在书库中的第几个书架的第几本，仅仅指明了这个特定的编号。

管理员则根据这一编号将请求的图书返回到读者手中。

这是很形象的例子，以下的讲解将会反复用到它。

SQLS 在安装完成之后，安装程序会自动创建master 、model 、tempdb 等几个特殊的系统数据库，其中master 是SQLS 的主数据库，用于保存和管理其它系统数据库、用户数据库以及SQLS 的系统信息，它在SQLS 中的地位与WINDOWS 下的注册表相当。

master 中有一个名为sysindexes 的系统表，专门管理索引。

SQLS 查询数据表的操作都必须用到它，毫无疑义，它是本文主角之一。

查看一张表的索引属性，可以在查询分析器中使用以下命令：select * from sysindexes where id=object_id(…tablename‟；而要查看表的索引所占空间的大小，可以使用系统存储过程命令：sp_spaceused tablename，其中参数tablename 为被索引的表名。

三、平衡树
如果你通过书后的索引知道了一个关键字所在的页码，你有可能通过随机的翻寻，最终到达正确的页码。

但更科学更快捷的方法是：首先把书翻到大概二分之一的位置，如果要找的页码比该页的页码小，就把书向前翻到四分之一处，否则，就把书向后翻到四分之三的地方，依此类推，把书页续分成更小的部分，直至正确的页码。

这叫“两分法”，微软在官方教程MOC 里另有一种说法：叫B 树（B-Tree ，Balance Tree），即平衡树。

一个表索引由若干页面组成，这些页面构成了一个树形结构。

B 树由“根”（root ）开始，称为根级节点，它通过指向另外两个页，把一个表的记录从逻辑上分成两个部分：“枝”—--非叶级节点（Non-Leaf Level）；而非叶级节点又分别指向更小的部分：“叶”——叶级节点（Leaf Level ）。

根节点、非叶级节点和叶级节点都位于索引页中，统称为索引节点，属于索引页的范筹。

这些“枝”、“叶”最终指向了具体的数据页（Page ）。

在根级节点和叶级节点之间的叶又叫数据中间页。

“根”（root ）对应了sysindexes 表的Root 字段，其中记载了非叶级节点的物理位置（即指针）；非叶级节点位于根节点和叶节点之间，记载了指向叶级节点的指针；而叶级节点则最终指向数据页。

这就是“平衡树”。

四、聚集索引和非聚集索引
从形式上而言，索引分为聚集索引（Clustered Indexes）和非聚集索引（NonClustered Indexes ）。

聚集索引相当于书籍脊背上那个特定的编号。

如果对一张表建立了聚集索引，其索引页中就包含着建立索引的列的值（下称索引键值），那么表中的记录将按照该索引键值进行排序。

比如，我们如果在“姓名”这一字段上建立了聚集索引，则表中的记录将按照姓名进行排列；如果建立了聚集索引的列是数值类型的，那么记录将按照该键值的数值大小来进行排列。

非聚集索引用于指定数据的逻辑顺序，也就是说，表中的数据并没有按照索引键值指定的顺序排列，而仍然按照插入记录时的顺序存放。

其索引页中包含着索引键值和它所指向该行记录在数据页中的物理位置，叫做行定位符（RID ：Row ID）。

好似书后面的的索引表，索引表中的顺序与实际的页码顺序也是不一致的。

而且一本书也许有多个索引。

比如主题索引和作者索引。

SQL Server在默认的情况下建立的索引是非聚集索引，由于非聚集索引不对表中的数据进行重组，而只是存储索引键值并用一个指针指向数据所在的页面。

一个
表如果没有聚集索引时, 理论上可以建立249个非聚集索引。

每个非聚集索引提供访问数据的不同排序顺序。

五、数据是怎样被访问的
若能真正理解了以上索引的基础知识，那么再回头来看索引的工作原理就简单和轻松多了。

（一）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 的记录位于堆的第707页的第4行，N 表示文件的ID 值，与数据无关；④根据上述信息，SQLS 立马在堆的第 707页第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 在维护索引时要消耗系统资源，那么SQLS 维护索引时究竟消耗了什么资源？会产生哪些问题？究竟应该才能优化字段的索引？
____________________________________________________________________ __________________________
在上篇中，我们就索引的基本概念和数据查询原理作了详细阐述，知道了建立索引时一定要在“加快查询速度”与“降低修改速度”之间做好平衡，有得必有失，此消则彼长。

那么，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 是怎么知道什么时候用。