《高性能SQL调优精要与案例解析》一书谈主流关系库SQL调优(SQL TUNING或SQL优化)核心机制之——索引(index)

继《高性能SQL调优精要与案例解析》一书谈SQL调优(SQL TUNING或SQL优化),我们今天就谈谈各主流关系库中,占据SQL调优技术和工作半壁*的、最重要的核心机制之一——索引(index)。我们知道,《高性能SQL调优精要与案例解析》一书中也再三强调索引对SQL调优的重要性,可是上篇文章中也谈到,只看案例和解决问题的具体方法,而不掌握SQL调优的基础知识,是没有用的,我们必须做到知其然,更要知其所以然,才能做到融会贯通,活学活用,进而将SQL调优技术掌握到炉火纯青的地步。因《高性能SQL调优精要与案例解析》主要以Oracle数据库为基础,对SQL调优技术进行了讲解,上篇文章中,我也说到,各关系库就SQL调优的分析和解决问题的思路、方法和步骤来说,几乎完全一样,只是具体命令、方法和形式有所差别而已。那么据此,我们今天就对各主流关系库的索引机制进行简要阐述,以帮助读者能更加深入理解和掌握《高性能SQL调优精要与案例解析》一书中的内容和精髓,更寄希望能使其他同学多多受益。但关系库中,索引类型不止一种,而不同关系库,相同关系库不同版本之间,也有差别,那么,下面,我们仅就应用最广泛的B*tree索引加以介绍。

1、Oracle B*tree索引:Oracle中B*Tree索引的组织结构图在《高性能SQL调优精要与案例解析》一书中均有详细描述,这里不再赘述,需要强调的是,Oracle中B*Tree的非叶级块(non-leaf level block )中,存储的只有索引列的键值(单列索引的列值或多列索引的列值组合)最大值和指向下一级叶级块(leaf level block)或非叶级块的指针(pointer),这里的指针,也就是块的文件号+块号。而Oracle中B*Tree的叶级块中,则存储了索引列的键值+ROWID(数据行所在文件号+块号+槽号)这样,通过B*Tree中的键值和ROWID值,就能很容易的通过索引查找到表中的数据行。而Oracle中表对应的段中数据行,则是堆结构(heap),具体见《高性能SQL调优精要与案例解析》。而值得一提的是,Oracle中还有一种特殊的表组织结构,那就是索引组织表(IOT),该类表虽然在Oracle中应用不多,但在其他关系库中,确应用很广,只不过名称和细节不同而已,具体继续看下面的内容。

2、MYSQL B*Tree索引:大家知道,MYSQL数据库是一种插件数据库,也就是其中的数据存储引擎可以方便的进行插拔,因此,MYSQL中也有多种存储引擎同时存在。因为本文不是专门讲述MYSQL存储引擎的,因此,我们就拿应用最广的INNODB为例来进行说明。INNODB中的B*Tree索引(MYSQL中又称为key),和Oracle中不同的是,根据具体的组织结构,又可分为簇索引(clustered index或primary key index)和非簇索引(secondary index),在innodb中创建一个表时,系统会为表的主键创建一个簇索引,如果不指定一个主键,系统会选定一个唯一非空索引作为主键,如果不存在唯一非空索引,系统也会自动创建一个隐式主键,总之,表的主键非有不可。INNODB中表的数据,都存储在表的簇索引中,具体说,簇索引是一个B*Tree结构,只是叶级页(leaf level page)内除了簇索引的键值外,还包含了表中所有的数据列值,因此,INNODB表的数据是有序的。而INNODB表中非簇索引,其叶级块中并不包含数据行的物理地址(类似Oracle中数据行的ROWID),而是包含了表上簇索引中的键值,因此,INNODB中,通过非簇索引查找数据,一般要经历两次键值查找,第一次在非簇索引上,找到簇索引的键值后,再到簇索引上再次查找,才能找到真正要查找的数据行。这里强调的是,INNODB中表的簇索引并不是个可选项。其组织结构和Oracle中的索引组织表类似。

3.SQL SERVER B*Tree索引:SQL SERVER数据库,作为微软的拳头产品之一,有时也被人们俗称为MSSQL,目前国内外市场有着相当的占有率。MSSQL中的B*Tree索引,和MYSQL中的类似,也分为簇索引(clustered index)和非簇索引(nonclustered index),但和MYSQL中B*Tree索引不同的是,MSSQL中表上的簇索引并非是强制的,也就是,你创建一张表,该表是一张堆表,也就是其对应的段是以堆的形式组织和存储的,堆的概念,《高性能SQL调优精要与案例解析》一书中有详细论述,这里不再赘述。MSSQL中的堆表上,如果你创建了一个簇索引,那么,该表中的数据都被移到该簇索引的叶级页(leaf level page)中,并且以该簇索引的键值顺序排序组织和存储,原来的堆不再存在,如果你选择在该表上不创建簇索引,那么,该表就会一直以堆的形式存在。而MSSQL中的非簇索引,因为簇索引的存在与否,其内部组织和机制也分两种情况,当表上存在簇索引时,非簇索引的叶级页中存储的是簇索引的键值,也就是不存在指向相关数据行物理地址的指针;而如果表上不存在簇索引时,非簇索引叶级页中就会存储指向表中相关数据行物理地址的指针(这个指针MSSQL中叫做rid)。而不管表上是否存在簇索引,MSSQL将通过非簇索引查找数据行的行为称为书签查找(bookmark lookup),虽然,MSSQL中,一般将主键作为表上的簇索引来进行使用和创建。我们可以看到,MSSQL中的簇索引,在组织结构上和Oracle中的索引组织表及MYSQL中的簇索引相似。

4.Postgresql B*Tree索引:Postgresql数据库,作为最强大的开源关系库之一,号称免费版的Oracle,但就其应用特点、行锁及MVCC等方面具体内部实现来讲,确实与Oracle有很多相似之处,也可以说是关系库大家族中,和Oracle最相近的一款关系库。上面我们也说到,Postgresql数据库在很多方面和Oracle非常相似,那么,B*Tree索引的组织结构和应用也不例外,Postgresql数据库中的B*Tree索引和Oracle中很相似,也并没有MYSQL和MSSQL数据库中簇索引一说。值得一提的是,Postgresql中的表和索引等数据库对象,都是以单独的文件形式组织和存储,8k大小的数据页也与Oracle中的块不同,Postgresql中的mvcc与Oracle中的实现机制也有很大区别,因为,Postgresql表中数据的前影像数据和当前版本存储在一起,需要定期通过vaccum进行清除。更值得一提的是,因为Postgresql中B*Tree索引中并不存在数据的版本信息,因此,SQL语句的索引覆盖技术并不存在,无论索引列是否能覆盖SQL语句的所有列,都需要回表操作来确认具体数据行的版本信息,虽然,在postgresql9.2版本中引进了scan-only scans操作,这虽然在有些时候可以避开回表操作,但需要访问表的VM文件,更关键的是,如果VM文件中的相应位(bit)为unset状态,还是需要回表,因此,鉴于其机制上的局限,该技术实际中价值并没那么大,也许对写少读多的业务,会有一定的价值。

以上,仅就常用主流关系库索引机制做简要陈述,以帮助各位读者理解《高性能SQL调优精要及案例解析》一书内容,同时,也希望其他同学多多受益。

上一篇:Windows下更改MySQL数据库的存储位置


下一篇:UNIX环境高级编程——线程同步之条件变量以及属性