数据库索引原理应用.docx

1、数据库索引原理应用Oracle索引原理索引的概念1、 类似书的目录结构2、 Oracle 的“索引”对象，与表关联的可选对象，提高SQL查询语句的速度3、 索引直接指向包含所查询值的行的位置，减少磁盘I/O4、 与所索引的表是相互独立的物理结构5、 Oracle 自动使用并维护索引，插入、删除、更新表后，自动更新索引6、 语法：CREATE INDEX index ON table (column, column.);索引分类逻辑分类singlecolumnorconcatenated对一列或多列建索引uniqueornonunique 唯一的和非唯一的索引，也就是对某一列或几列的键值（key

2、）是否是唯一的。Function-based基于某些函数索引，当执行某些函数时需要对其进行计算，可以将某些函数的计算结果事先保存并加以索引，提高效率。Doman索引数据库以外的数据，使用相对较少物理分类B-Tree：normalorreversekeyB-Tree索引也是我们传统上常见所理解的索引，它又可以分为正常所引和倒序索引。Bitmap：位图所引B-TREE索引一个B树索引只有一个根节点，它实际就是位于树的最顶端的分支节点。可以用下图一来描述B树索引的结构。其中，B表示分支节点，而L表示叶子节点。 对于分支节点块（包括根节点块）来说，其所包含的索引条目都是按照顺序排列的（缺省是升序排列，

3、也可以在创建索引时指定为降序排列）。每个索引条目（也可以叫做每条记录）都具有两个字段。第一个字段表示当前该分支节点块下面所链接的索引块中所包含的最小键值；第二个字段为四个字节，表示所链接的索引块的地址，该地址指向下面一个索引块。在一个分支节点块中所能容纳的记录行数由数据块大小以及索引键值的长度决定。比如从上图一可以看到，对于根节点块来说，包含三条记录，分别为（0 B1）、（500 B2）、（1000 B3），它们指向三个分支节点块。其中的0、500和1000分别表示这三个分支节点块所链接的键值的最小值。而B1、B2和B3则表示所指向的三个分支节点块的地址。对于叶子节点块来说，其所包含的索引条目

4、与分支节点一样，都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）。每个索引条目（也可以叫做每条记录）也具有两个字段。第一个字段表示索引的键值，对于单列索引来说是一个值；而对于多列索引来说则是多个值组合在一起的。第二个字段表示键值所对应的记录行的ROWID，该ROWID是记录行在表里的物理地址。如果索引是创建在非分区表上或者索引是分区表上的本地索引的话，则该ROWID占用6个字节；如果索引是创建在分区表上的全局索引的话，则该ROWID占用10个字节。bitmap索引 位图（bitmap）索引是另外一种索引类型，它的组织形式与B树索引相同，也是一棵平衡树。与B树索引的区别在于

5、叶子节点里存放索引条目的方式不同。从前面我们知道，B树索引的叶子节点里，对于表里的每个数据行，如果被索引列的值不为空的，则会为该记录行在叶子节点里维护一个对应的索引条目。而位图索引则不是这样，其叶子节点里存放的索引条目如下图所示。 假设某个表T里所有的记录在列C1上只具有三个值：01、02和03。在表T的C1列上创建位图索引以后，则叶子节点的内容如图9-14所示。可以看到，位图索引只有三个索引条目，也就是每个C1列的值对应一个索引条目。位图索引条目上还包含表里第一条记录所对应的ROWID以及最后一条记录所对应的ROWID。索引条目的最后一部分则是由多个bit位所组成的bitmap，每个bit位

6、就对应一条记录。 当发出where c1=01这样的SQL语句时，oracle会去搜索01所在的索引条目，然后扫描该索引条目中的bitmap里所有的bit位。第一个bit位为1，则说明第一条记录上的C1值为01，于是返回第一条记录所在的ROWID（根据该索引条目里记录的start ROWID加上行号得到该记录所在的ROWID）。第二个bit位为0，则说明第二条记录上的C1值不为01，依此类推。另外，如果索引列为空，也会在位图索引里记录，也就是将对应的bit位设置为0即可。 如果索引列上不同值的个数比较少的时候，比如对于性别列（男或女）等，则使用位图索引会比较好，因为它对空间的占用非常少（因为都

7、是用bit位来表示表里的数据行），从而在扫描索引的时候，扫描的索引块的个数也比较少。可以试想一下，如果在列的不同值非常多的列上，比如主键列上，创建位图索引，则产生的索引条目就等于表里记录的条数，同时每个索引条目里的bitmap里，只有一个1，其它都是0。这样还不如B树索引的效率高。如果被索引的列经常被更新的话，则不适合使用位图索引。因为当更新位图所在的列时，由于要在不同的索引条目之间修改bit位，比如将第一条记录从01变为02，则必须将01所在的索引条目的第一个bit位改为0，再将02所在的索引条目的第一个bit位改为1。因此，在更新索引条目的过程中，会锁定位图索引里多个索引条目。也就是同时只

8、能有一个用户能够更新表T，从而降低了并发性。位图索引比较适合用在数据仓库系统里，不适合用在OLTP系统里。HASH索引 使用HASH索引必须要使用HASH集群。建立一个集群或HASH集群的同时，也就定义了一个集群键。这个键告诉Oracle如何在集群上存储表。在存储数据时，所有与这个集群键相关的行都被存储在一个数据库块上。如果数据都存储在同一个数据库块上，并且将HASH索引作为WHERE子句中的确切匹配，Oracle就可以通过执行一个HASH函数和I/O来访问数据- 而通过使用一个二元高度为4的B树索引来访问数据，则需要在检索数据时使用4个I/O。如图2-5所示，其中的查询是一个等价查询，用于匹

9、配HASH列和确切的值。Oracle可以快速使用该值，基于HASH函数确定行的物理存储位置。 HASH索引可能是访问数据库中数据的最快方法，但它也有自身的缺点。集群键上不同值的数目必须在创建HASH集群之前就要知道。需要在创建HASH集群的时候指定这个值。低估了集群键的不同值的数字可能会造成集群的冲突(两个集群的键值拥有相同的HASH值)。这种冲突是非常消耗资源的。冲突会造成用来存储额外行的缓冲溢出，然后造成额外的I/O。如果不同HASH值的数目已经被低估，您就必须在重建这个集群之后改变这个值。ALTER CLUSTER命令不能改变HASH键的数目。 HASH集群还可能浪费空间。如果无法确定需

10、要多少空间来维护某个集群键上的所有行，就可能造成空间的浪费。如果不能为集群的未来增长分配好附加的空间，HASH集群可能就不是最好的选择。如果应用程序经常在集群表上进行全表扫描，HASH集群可能也不是最好的选择。由于需要为未来的增长分配好集群的剩余空间量，全表扫描可能非常消耗资源。在实现HASH集群之前一定要小心。您需要全面地观察应用程序，保证在实现这个选项之前已经了解关于表和数据的大量信息。通常，HASH对于一些包含有序值的静态数据非常有效。技巧：HASH索引在有限制条件(需要指定一个确定的值而不是一个值范围)的情况下非常有用。聚族索引 在这里还是用字典来进行类比，一般来说汉语字典中有几种索引

11、，如拼音、偏旁、笔画等。字典本身的组织也是排序的，我记得一般是按照拼音排序的。这里的拼音就是聚族索引。也就是说聚族索引的组织顺序和数据本身的组织顺序是一致的，这也解释了数据库中只能定义一个聚族索引的原因，因为数据本身只能按一种方式进行排序。 那聚族索引有什么特别的好处呢，这个好处就是在数据库中执行查找一批数据的语句会比较快，因为数据已经按照聚族索引排好序了，很少的io操作就可以将数据从库中取出。好比你在字典中查找发音从从a到c的汉字，只需要查到a的开始页和c的结束页，中间的所有页都符合查询要求，不用再一页一页地查找。非聚族索引 非聚族索引就好比字典里的偏旁、笔画索引，其索引组织顺序和数据组织顺

12、序不一致，因此非聚族索引可以创建多个。当查找一条数据时，非聚族索引和聚族索引的效率相差不大，但查找一批数据(n)时，非聚族索引需要的io可能是聚族索引的n倍，因为非聚族索引需要一条一条地进行查找。Oracle索引应用与类型选择三、 唯一索引1、 何时创建：当某列任意两行的值都不相同2、 当建立Primary Key(主键)或者Unique constraint(唯一约束)时，唯一索引将被自动建立3、 语法：CREATE UNIQUE INDEX index ON table (column);4、 演示四、 组合索引1、 何时创建：当两个或多个列经常一起出现在where条件中时，则在这些列上同

13、时创建组合索引2、 组合索引中列的顺序是任意的，也无需相邻。但是建议将最频繁访问的列放在列表的最前面3、 演示(组合列，单独列)五、 位图索引1、 何时创建：列中有非常多的重复的值时候。例如某列保存了 “性别”信息。Where 条件中包含了很多OR操作符。较少的update操作，因为要相应的跟新所有的bitmap2、 结构：位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE(1)、FALSE(0)、或NULL值。3、 优点：位图以一种压缩格式存放，因此占用的磁盘空间比标准索引要小得多4、 语法：CREATE BITMAP INDEX index ON table (column,

14、 column.);5、 演示：create table bitmaptable as select * from indextable where owner in(SYS,PUBLIC);分析，查找，建立索引，查找六、 基于函数的索引 1、 何时创建：在WHERE条件语句中包含函数或者表达式时2、 函数包括：算数表达式、PL/SQL函数、程序包函数、SQL函数、用户自定义函数。3、 语法：CREATE INDEX index ON table (FUNCTION(column);4、 演示必须要分析表，并且 query_rewrite_enabled=TRUE或者使用提示/*+ INDEX

15、(ic_index)*/七、 反向键索引目的：比如索引值是一个自动增长的列：多个用户对集中在少数块上的索引行进行修改，容易引起资源的争用，比如对数据块的等待。此时建立反向索 引。性能问题：语法：重建为标准索引：反之不行八、 键压缩索引比如表landscp的数据如下：site feature jobBritten Park, Rose Bed 1, PruneBritten Park, Rose Bed 1, MulchBritten Park, Rose Bed 1,SprayBritten Park, Shrub Bed 1, MulchBritten Park, Shrub Bed 1,

16、WeedBritten Park, Shrub Bed 1, Hoe查询时，以上3列均在where条件中同时出现，所以建立基于以上3列的组合索引。但是发现重复值很多，所以考虑压缩特性。Create index zip_idxon landscp(site, feature, job)compress 2;将索引项分成前缀(prefix)和后缀(postfix)两部分。前两项被放置到前缀部分。Prefix 0: Britten Park, Rose Bed 1Prefix 1: Britten Park, Shrub Bed 1实际所以的结构为：0 Prune0 Mulch0 Spray1 Mu

17、lch1 Weed1 Hoe特点：组合索引的前缀部分具 有非选择性时，考虑使用压缩。减少I/O,增加性能。九、 索引组织表(IOT)将表中的数据按照索 引的结构存储在索引中，提高查询速度。牺牲插入更新的性能，换取查询 性能。通常用于数据仓库，提供大量的查询，极少的插入修改工作。必须指定主键。插入数据时，会根据主键列进行B树索引排序，写入磁盘。十、 分区索引簇:A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.