加载中…
个人资料
蓝人
蓝人
  • 博客等级:
  • 博客积分:0
  • 博客访问:146,879
  • 关注人气:61
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
相关博文
推荐博文
谁看过这篇博文
加载中…
正文 字体大小:

索引原理(转)

(2006-03-27 18:08:44)
分类: 技术收藏
(一)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是否优秀的很重要的指标。

0

阅读 评论 收藏 转载 喜欢 打印举报/Report
  • 评论加载中,请稍候...
发评论

    发评论

    以上网友发言只代表其个人观点,不代表新浪网的观点或立场。

      

    新浪BLOG意见反馈留言板 电话:4000520066 提示音后按1键(按当地市话标准计费) 欢迎批评指正

    新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 会员注册 | 产品答疑

    新浪公司 版权所有