你了解SQL的索引原理吗

数据库是怎样访问表数据的
服务器君一共花费了194.795 ms进行了4次数据库查询,努力地为您提供了这个页面。
试试阅读模式?希望听取您的建议

上篇文章粗略的总结了些SQL聚集索引与非聚集索引的区别,但看起来好像不太清晰,这篇我通过索引原理来再一次分析下。

索引是为检索而存在的,就是说索引并不是一个表必须的。表索引由多个页面组成,这些页面一起组成了一个树形结构,即我们通常说的B树,首先来看下表索引的组成部分:

根极节点,root,它指向另外两个页,把一个表的记录从逻辑上分成非叶级节点Non-Leaf Level(枝),它指向了更加小的叶级节点Leaf Level(叶)。 根节点、非叶级节点和叶级节点都位于索引页中,统称为索引叶节点,属于索引页的范筹。这些"枝"、"叶"最终指向数据页Page。根级节点和叶级节点之间的叶又叫数据中间页。根节点对应了sysindexes表的Root字段,记载了非叶级节点的物理位置(即指针);非叶级节点位于根节点和叶节点之间,记载了指向叶级节点的指针;而叶级节点则最终指向数据页,这就是最后的B树。

数据库是怎样访问表数据的:

第一:没有创建任何索引的表。

这种表我们称为堆表,因为所有的数据页都是无序的,杂乱无章的,在查询数据时,需要一条一条记录查询,有时第一条记录就能找到,最坏的情况是在最后一条记录中查找到,但是千万不要认为SQL此时查找到数据后会当成结果立即返回,SQL即使查找到了记录,也会将所有数据遍历一次,这能从最终的执行计划中得知,就是平时说的表扫描,对于没有索引的表也能查询,就是效率会特别低,如果数据量稍大的话。

问题:SQL是如何得知表没有索引呢?

SQL在接到查询请求的时候,会分析sysindexes表中索引标志符(INDID: Index ID)的字段的值,如果该值为0,表示这是一张数据表而不是索引表,SQL就会使用sysindexes表的另一个字段FirstIAM值中找到该表的IAM 页链也就是所有数据页集合。至于什么是IAM,大家可以网上搜索下。

第二:访问创建有非聚集索引的表。

非聚集索引可以建多个,形成B树结构,叶级节点不包含数据页,只包含索引行。如果表中只有非聚集索引,则每个索引行包含了非聚集索引键值以及行定位符(ROW ID,RID),他们指向具有该键值的数据行。RID由文件ID、页编号和在页中行的编号组成。当 INDID的值在2-250之间时,说明表中存在非聚集索引页。SQL调用ROOT字段的值指向非聚集索引B树的ROOT,查找与被查询最相近的值,根据这个值找到在非叶级节点中的页号,在叶级节点相应的页面中找到该值的RID,最后根据这个RID在Heap中定位所在的页和行并返回到查询端。

上篇文章的cityid上建立了非聚集索引,执行Select * From student Where cityid='0101'时,查询过程是:

  1. 在sysindexes表查询INDID值为2,说明有非聚集索引;
  2. 从根出发,在非叶级节点中定位最接近0101的值(枝节点),查到其位于叶级页面的第n页;
  3. 在叶级页面的第n页下搜寻0101的RID,其RID显示为N∶i∶j,表示cityid字段中名为0101的记录位于堆的第i页的第j行,N代表文件的ID值。
  4. 在堆的第 i页第j行将该记录返回给客户端。

第三:访问创建有聚集索引的表。

聚集索引中,数据所在的数据页是叶级,索引数据所在的索引页是非叶级。原理和上述非聚集索引的查询差不多,由于记录是按聚集索引键值进行排序,即聚集索引的索引键值也就是具体的数据页。这种情况比起非聚集索引要简单很多,因为比非聚集索引少了一层节点查询。

上篇文章的username字段上建立了聚集索引,此时执行Select* From student Where username='1'时,查询过程是:

  1. 在sysindexes表查询INDID值为1,说明表中建立了聚集索;
  2. 从根出发,在非叶级节点中定位最接近1的值(枝节点),再查到其位于叶级页面的第n页;
  3. 在叶级页面第n页下搜寻值为1的条目,而这一条目就是数据记录本身;
  4. 将该记录返回客户端。

下图可做参考:

第四:怎样访问既有聚集索引、又有非聚集索引的数据表:

username字段上建立了聚集索引,cityid上建立了非聚集索引,当执行Select * From student Where cityid='0101'时,查询过程是:

  1. 在sysindexes表查询INDID值为2,说明有非聚集索引;
  2. 从根出发,在cityid的非聚集索引的非叶级节点中定位最接近0101的条目;
  3. 从上面条目下的叶级页面中查到0101的逻辑位置,是聚集索引的指针;
  4. 根据指针所指示位置,进入位于username的聚集索引中的叶级页面中找到0101数据记录;
  5. 将该记录返回客户端。

通过上面数据库访问索引的原理,我们就很容易解释聚集索引与非聚集索引的区别了,原理都一样,关键看什么场合应用什么索引了,下一篇我来总结一些不同场合最适合采用什么样的索引,不对之外多多指点。

本文地址:http://www.nowamagic.net/librarys/veda/detail/823,欢迎访问原出处。

不打个分吗?

转载随意,但请带上本文地址:

http://www.nowamagic.net/librarys/veda/detail/823

如果你认为这篇文章值得更多人阅读,欢迎使用下面的分享功能。
小提示:您可以按快捷键 Ctrl + D,或点此 加入收藏

阅读一百本计算机著作吧,少年

很多人觉得自己技术进步很慢,学习效率低,我觉得一个重要原因是看的书少了。多少是多呢?起码得看3、4、5、6米吧。给个具体的数量,那就100本书吧。很多人知识结构不好而且不系统,因为在特定领域有一个足够量的知识量+足够良好的知识结构,系统化以后就足以应对大量未曾遇到过的问题。

奉劝自学者:构建特定领域的知识结构体系的路径中再也没有比学习该专业的专业课程更好的了。如果我的知识结构体系足以囊括面试官的大部分甚至吞并他的知识结构体系的话,读到他言语中的一个词我们就已经知道他要表达什么,我们可以让他坐“上位”毕竟他是面试官,但是在知识结构体系以及心理上我们就居高临下。

所以,阅读一百本计算机著作吧,少年!

《代码大全(第2版)》 史蒂夫•迈克康奈尔 (Steve McConnell) (作者), 金戈 (译者)

代码大全(第2版)是著名IT畅销书作者、《IEEE Software》杂志前主编、具有20年编程与项目管理经验的Steve McConnell十余年前的经典著作的全新演绎:第2版做了全面的更新,增加了很多与时俱进的内容,包括对新语言、新的开发过程与方法论的讨论等等。这是一本百科全书式的软件构建手册,涵盖了软件构建活动的方方面面,尤其强调提高软件质量的种种实践方法。

更多计算机宝库...