索引【学习笔记】

1.为什么需要index

  为访问表数据提供另一种选择

 当从表中读取数据时，Oracle有两个方法：
  从表中读取每一行（全表扫描）
  通过ROWID一次读取一行

  【
    扩展ROWID格式为
    OOOOOO    FFF    BBBBBB RRR
    数据对象号 相对文件号 块号  行号
  】

当我们要访问大型表的少数行时，可能需要使用索引。
因为如果没有索引，那么只能进行全表扫描。

2.index的类型，创建，维护

     index是独立的段，可与和它相关的表位于不同的表空间中
    2.1 B树索引
       在叶子节点，存放键值和表中对应这个键值的rowid
     适用于唯一键值或键值很少重复的情况。
     对于那些键值大量重复的列，B-Tree索引不适用。
      (1)标准索引（非唯一索引）
         创建
  CREATE INDEX item_index ON item(itemcode) TABLESPACE index_tbs;
 查询
  select * from user_indexes;
               select * from user_ind_columns;
  select * from dba_indexes;
  select * from dba_ind_columns;
     重建索引
  ALTER INDEX item_index REBUILD;
 删除索引
               DROP INDEX item_index;
             alter index indname monitoring usage; 打开索引监控
             v$object_usage   查看索引是否被使用
             alter index indname nomonitoring usage;  关闭索引监控

 
  (2)唯一索引
   确保在定义索引的列中没有重复值
   Oracle 自动在表的主键列上创建唯一索引[primary key]
   CREATE UNIQUE INDEX item_index ON item(itemcode) TABLESPACE index_tbs；
  (3)组合索引
   当某个索引包含有多个列时，称这个索引为组合索引。
   如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索速度
   在使用组合索引的时候，要谨慎选择索引列中的列顺序。
             一般来说，索引的第一列应该是最有可能在where子句中使用的列，并且也是在索引中最具有选择性的列。
   CREATE INDEX comp_index ON item(p_category, itemrate) TABLESPACE index_tbs;
   select * from item where p_category='...' and itemrate=...;
  (4)基于函数的索引
   基于一个或多个列上的函数或表达式创建的索引
   创建时必须具有 QUERY REWRITE 权限    老师说不一定需要这个权限 (查询重写)
   CREATE INDEX lowercase_idx ON toys( LOWER(toyname) ) TABLESPACE index_tbs；
   SELECT * FROM toys WHERE LOWER(toyname)='doll';
   索引段中存储的数据为函数运算后的索引
  (5)反向键索引
   反转索引列键值的每个字节
              通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上
   CREATE INDEX rev_index ON item (itemcode) REVERSE;
              把反向键索引转成标准索引
   ALTER INDEX rev_index REBUID NOREVERSE;
    2.2 位图索引
       适合创建在低基数列上，位图索引不直接存储ROWID，而是存储字节位到ROWID的映射，对索引列的每一个键值分别索引。
   
   索引的存储：索引的键值  当前范围的起始rowid和终止rowid  这个键值在这个范围内的位置编码
   将这个十六进制编码转化为二进制后，编码值是1的代表记录符合索引的键值，是0则表示不符合。
   只记录当前范围的起始rowid和终止rowid，不会记录全部rowid
     
   键值的每个存储范围的大小和分段依据，主要与每次DML操作影响的记录数和这些键值相同的记录
   在数据库中的物理分布有关。二者比较起来，前者的影响要大得多。用insert来举例。
   如果插入一条记录，那么oracle会为这条记录中的键值建立一个范围，范围的大小是8条记录
   （bitmap索引中的最小范围，所有bitmap索引的范围必是8的倍数，其他7条记录为空，可以为后续的插入操作继续使用）
   如果执行一条批量插入语句(insert select),对于这条插入语句中包含的每一个键值，都会建立一个范围。
   范围的大小为CEIL(操作记录数/8)*8。实际情况由于考虑利用原有范围内的空闲空间，以及考虑到键值的
   rowid分布等因素，要远比这里描述的复杂得多，而且对于delete update操作而言，情况更为复杂。但有一点
   是明确的，即Oracle会对批量插入（修改）的数据一起进行索引。如果一次性插入1000条键值相同的数据，则只会索引
   一次，并生成一个包括1000条记录的范围。如果一条一条地插入1000条数据，则会索引1000次，并生成125个包括
   8条记录的范围。无论是从效率的角度还是空间的角度考虑，批量操作都是对包含bitmap索引的表的首选。
适用于数据仓库
     检查是否支持bitmap
      select * from v$option where parameter like 'Bit%';
       Bit-mapped indexes   TRUE
 show parameter area_size
        bitmap_merge_area_size   bitmap index进行merge操作时使用的区域
 create_bitmap_area_size  创建bitmap索引的内存区域
       
   Bitmap index 的特点 【可存储空值】
    （1）比B-Tree索引节省空间
    （2）建立速度较快（B-Tree需要排序，bitmap index不需要排序）
    （3）基于规则的优化器无法使用bitmap索引
    
    [] oracle 10g 已经不支持只有基于成本的优化器才能使用bitmap索引 []oracle 8就出现了
       （4）bitmap index可以存储空值，
                 B-Tree索引不存储空值，因此对空值进行查询的，可以使用bitmap index ，l
    分别看下面2个SQL的执行计划
    select * from emp where empno=7369
    select * from emp where empno is null
 （5）Bitmap index 更适合计算记录总数，即select count(*)
    create table test_bitmap as select * from dba_objects;
    create bitmap index ind_b_owner on test_bitmap(owner);
    analyze table test_bitmap compute statistics;
    看执行计划：
    select count(*) from test_bitmap;
    select owner,count(*) from test_bitmap group by owner;
    select * from test_bitmap where wner='SCOTT' (COST=103)
    select * from test where wner='SCOTT' (cost=45)
       （6）bitmap index 对批量DML操作只需要索引一次
  （7）bitmap index的锁机制
    相同键值的记录处在同一个索引范围中。
    对一个范围中的某一行记录加锁(delete update),会引起对这个范围内的所有记录的加锁。
    建立了bitmap index 的表，锁的最小粒度是bitmap索引的范围。这意味着对于多用户并行访问时，
    表被锁定的几率大大增加。因此，对bitmap索引操作时，应采用批量修改、迅速提交的方式。
  （8）bitmap索引适用包含键值数量有限，每个键值重复记录较多的列。
    对于采用批量DML操作或无DML操作的列，效率很高。
    更适用于数据仓库，OLTP中bitmap index并不是首选。
    对于分区表，bitmap index只能采用local,不能使用global
           创建bitmap index
    CREATE BITMAP INDEX bit_index ON order_master (orderno) TABLESPACE index_tbs;
      
    2.3 HASH索引
          应用在HASH聚簇
           HASH键+集群键+数据
               通过HASH函数得出的值找到HASH键
               使用HASH索引必须要使用HASH集群
               建立HASH集群的同时,也就定义了一个HASH键
   
    2.4 局部索引和全局索引
        应用在分区表中
    2.5 bitmap join index
    bitmap join index不是建立在一张表上，是建立在2个或多个表的链接上。
    可以在查询时消除对表的链接操作，而通过访问索引得到相应的结果。
    通过MV也可实现这种功能。但使用bitmap join index比MV节省大量空间。
    alter table test_bitmap add (id number);
    update test_bitmap set id=rownum;
              alter table test_bitmap add constraint pk_testbitmap primary key (id);
    create table test_bitmap_join(id number,f_id number,comments varchar2(200));
    nsert into test_bitmap_join select rownum,mod(id,1000)+1,null from test_bitmap;
    commit;      
    alter table test_bitmap_join add constraint pk_tbj_f_id foreign key (f_id) references test_bitmap(id);
            建立bitmap join index要求相链接的2张表存在主从关系。
            看执行计划：
    select a.* from test_bitmap_join a, test_bitmap bwhere a.f_id=b.id and b.owner='SCOTT';
              在没有建立bitmap join index时，Oracle必须把2个表join(nested loops),cost=6714
    
    创建bitmap join index
    create bitmap index ind_b_test_bitmap_owner on test_bitmap_join (test_bitmap.owner)
   from test_bitmap_join,test_bitmap where test_bitmap_join.f_id=test_bitmap.id;
  查看索引信息：
  select * from user_indexes
                select * from user_ind_columns
             看执行计划
    select a.* from test_bitmap_join a, test_bitmap b
     where a.f_id=b.id and b.owner='SCOTT'
     cost=17
  bitmap join index有4种模型：
   F代表事实表 
   D代表维度表
   pk维度表的主键列
   fk事实表连接到维度表的外键列
    （1）D c1 -- d.pk=f.fk --&gt F     (c1 is a column of D)
      create bitmap index bji on f(d.c1) from f,d where d.pk=f.fk;
      上例中就是这种模型
      where d.c1=...
                          and d.pk=f.fk
    （2）D c1 c2 -- d.pk=f.fk --&gt F     (c1 and c2 are columns of D)
      create bitmap index bji on f(d.c1,d.c2) from f,d where d.pk=f.fk;
      where d.c1=... and d.c2=...
                           and d.pk=f.fk
    （3）D1 c1 -- d1.pk=f.fk1 --&gt F       create bitmap index bji on f(d1.c1,d2.c2) from f,d1,d2 where d1.pk=f.fk1 and d2.pk=f.fk2;
      where d.c1=... and d2.c2=...
                          and d1.pk=f.fk1
                           and d2.pk=f.fk2
    （4）D1 c3 -- d1.pk=d2.c2 --&gt D2 c2 --d2.pk=f.fk--&gt F 
             create bitmap index bji on f(d1.c3) from f,d1,d2 where d1.pk=d2.c2 and d2.pk=f.fk; 
             where d1.c3=...
                            and d1.pk=d2.c2
                            and d2.pk=f.fk           
              bitmap join index 的一些约束：
    （1）只有事实表支持并行DML操作，维度表的并行DML操作将导致索引不可用；
    （2）对于使用了bitmap join index的表，不同的事务只能同时更新其中的一张表；
    （3）在表链接中，每个表只能出现一次；
    （4）不能在IOT和临时表中建立bitmap join index；
    （5）被索引的列必须是维度表的列；
    （6）维度表参与连接的列必须是主键列或者具有唯一约束；
    （7）如果维度表使用复合主键，则主键中的每一列都必须在连接中出现。
  

3.索引的性能考虑

  索引改进性能的程度取决于三个因素：
  1、数据的选择性   选择性  【数据唯一的程度】选择性高唯一性高
  2、表数据在数据块上的分布  集群因子 clustering factor 【】
     [
       (1)加大块大小
       (2)消除索引和表的碎片
       (3)重建表，对其数据按索引排序
     ]
  3、索引的高度  二元高度(binary height)

  如果选择性很高（例如身份证号码），那么根据索引值返回的ROWID很少，
  如果选择性很低（例如国家）则返回的ROWID很多，那么索引的性能将会大大降低

  如果选择性很高，但是相关的行在表中的存储位置并不互相靠近，则会进一步减少索引的益处，
  如果匹配索引值的数据分散在表的多个数据块中，则必须从表中选择多个单独的块以满足查询，
  基于索引的读取是单块读取，如果使用全表扫描，使用的是多块读取以快速扫描表，
  因此全表扫描不见得比索引扫描速度慢，但要看具体的块数。

  选择性
     user_indexes.distinct_keys
     索引列中唯一值的个数
     select t.index_name,t.table_name,
      t.distinct_keys,t.num_rows,
      t.clustering_factor,t.blevel
     from dba_indexes t
     where wner='SCOTT'
   索引的选择性越高,针对每个不同值返回的行数也越少.可以帮助基于成本的优化器来判断执行路径.

索引只能单块读，全表扫描一次可以读很多块。
当集群因子高的话，当提取数据时索引只能一块一块的读数据，这样就比全表扫描慢了。    
  集群因子 clustering factor

     B树叶块和表数据之间的关系称为clustering factor
         
     Clustering Factor计算的方法如下：
     1、扫描一个索引
     2、比较某行的rowid和前一行的rowid，如果这两个rowid不属于同一个数据块，那么cluster factor增加1
     3、整个索引扫描完毕后，就得到了该索引的cluster factor。
   
 
     索引叶子块指向的表数据块越少，该值越小，在索引范围扫描的性能越好。
     如果该值和表的块数接近，表示表行按索引顺序排序，
     如果该值和表的行数接近，表示表行不是按索引顺序排序。

     在计算索引访问成本的时候，这个值十分有用。
     Clustering Factor乘以选择性参数（selectivity ）就是访问索引的开销。

     如果这个统计数据不能真实反映出索引的真实情况，
     那么可能会造成优化器错误的选择执行计划。
     所以，当出现较大数据删除或更新后，需要重建索引。

              另外如果某张表上的大多数访问是按照某个索引做索引扫描，
     那么将该表的数据按照索引字段的顺序重新组织，可以提高该表的访问性能。
         
     聚集因子的数量和表的块数一致，因此表的数据排序和索引一致，
     因此在进行索引范围扫描的时候，对表的访问成本还是比较低的。
     聚集因子主要影响的是索引范围扫描。
     如果聚集因子很高，那么建议对表进行重新整理，降低聚集因子。

  
     集群因子记录在扫描索引时将读取的块数量。
     集群因子是索引与它所基于的表相比较而得出的有序性度量，它用于检查在
     索引访问之后执行的表查找的成本。(将集群因子与选择性相乘即可得到该操作的成本)。    
     如果使用的索引具有较大的集群因子，则必须访问更多的表数据块才可以获得每个
     索引块中的行(因为邻近行位于不同的块中)。
     
     如果集群因子接近于表中的块数量，则表示索引已适当排序；
     如果集群因子接近于表中的行数量，则表示索引没有适当排序。
     集群因子的计算简要介绍如下：

    (1) 按顺序扫描索引。
    
    (2) 将当前索引值指向的ROWID的块部分与以前的索引值进行比较(比较索引中的邻近行)。
    
    (3) 如果ROWID指向不同的TABLE块，则增加集群因子(对整个索引执行该操作)。

  Clustering_Factor列位于USER_INDEXES视图中，
  该列反映了数据相对于已索引的列是否显得有序。
  如果Clustering_Factor列的值接近于索引中的
  树叶块(leaf block)的数目，表中的数据就是有序的。
  索引的树叶块存储索引值以及它们指向的ROWID。

  例如，CUSTOMERS表上Customer_Id列的值可以由
  序列生成器产生，而且是表CUSTOMERS上的主键。
  Customer_Id的索引的集群因子就有可能非常接近于
  树叶块数(表示有序)。当往数据库中添加客户数据时，
  它们就按照序列产生器所产生的序列值有序地存储在表中。
  然而，因为整个表的客户名字排列是随机的，
  所以customer_name上的索引会有一个很高的集群因子。

  集群因子对执行范围扫描的SQL语句有一定的影响。
  如果集群因子很低(相对于树叶块的数量)，
  需要读取的表中块的数量就可以减少很多。
  这样也增加了相同的数据块已经存在于内存中的可能性。
  一个较高的集群因子(相对于树叶块的数量)会增加满足基于索引列的范围查询所需的数据块数目。
  
    
  二元高度(binary height)     
    
  dba_indexes.blevel

  如果索引只占用了一个数据块，则二元高度是0

  索引查找分为两个过程：
     1、根据树进行定位、找出ROWID（索引查找）
     2、根据ROWID找出表中的数据行（表数据查找）
           进行索引查找的时候，首先从树根开始读数据，通过中间节点，
    最后定位到叶节点，整个过程只能进行单数据块的读取。
      

    索引的二元高度对把ROWID返回给用户进程时所要求的I/O数量起到关键作用。
    二元高度的每个级别都会有一个读取块，而且由于这些块不能按顺序读取，
    它们都要求一个独立的I/O操作。

  检索一个二元高度为3的索引，返回一行数据给用户，同时有4个块被读取：3个来自索引，一个来自表。
            IndexBlock O --&gt O --&gt O --&gt TableBlock O
        
    随着索引的二元高度的增加，检索数据所要求的I/O数量也会随之增加。
    在对索引进行分析后，可以通过查询DBA_INDEXES的blevel列查看它的二元高度.
    一般来说，二元高度越低越好(最低为0)。作为数据库管理员，就是需要想方设法让这个
    二元高度的值变为0。虽然这个目标看起来比较简单，但是实现起来却有相当大的困难。
    在对索引进行优化的时候，就是要想尽一切办法，把这个值降低到最低。而要实现降低
    这个二元高度的值的目标，则数据库管理员就必须先了解哪些因素跟这个值有关。
    如此的话，数据库管理员才能够对症下药，降低二元高度的值，提高索引的性能。
  
   1)、删除操作。有时候数据库管理员可能会发现他们刚完成数据的导入工作，
    就会发现数据库的性能有所下降。而此时查询这个二元高度的值，则会发现这个值非常的大。
    照理来说，才刚完成数据的导入功能，还没有进行其它的一些业务操作，这个值不应该很大呀。
    那这是怎么一回事情呢?其实，这主要是因为大量的删除操作所造成的。原来在导入数据的时候，
    可能会发现某些数据导入有问题。故有些数据库管理员会利用delete语句清除导入到数据库中的记录。
    此时数据库管理员就需要注意，索引上如果有大量被删除的行，则它对应的二元高度的值也就会逐渐增加。
    遇到这种情况时，数据库管理员可以尝试重建索引。通常情况下，如果二元高度的值比较大确实是因为
    删除操作所引起的，那么通过这个重建索引的工作后，基本上可以把这个二元高度的值降下去。
    笔者建议，如果一个索引中被删除掉记录接近于全部记录的30%左右，此时数据库管理员就需要
    采取重建索引的作业，用于降低二元高度以及在一次I/O过程中所读取的空闲时间。
   2)、数据块尺寸。通常情况下，数据库块尺寸与二元高度的值是成反比的。
    数据块尺寸越大，则索引的二元高度就越低。
   3)、二元高度的值还会随着索引列中的非NULL数量以及索引列中值的范围狭窄程度而变化。
    一般来说，索引列中非NULL的数量越多(即空字段越少)则其二元高度的值越低。或者说，
    索引列中的值越靠近，即范围比较小，则其二元高度的值也就越低。
    为此，这就提醒数据库管理员在设计索引的时候，最好能够把索引字段设置为非空。
       

4.索引的使用

    SELECT、UPDATE、DELETE+WHERE条件可以从索引中得到好处
   （前提是：当访问的行数较少时）

  一般来说，增加索引会带来insert语句性能的下降
  如果根据未索引列update索引列，那么也会带来性能的降低
  大量的delete也会因为索引的存在而导致性能降低

  因此我们要分析具体的情况，判断索引和DML语句之间的关系
     
 A 全表扫描（Full Table Scan  FTS）
  Oracle读取表中所有的行、多块读操作可以大大的减少IO的次数、
  利用多块读可以大大的提高全表扫描的速度、只有在全表扫描的情况下才能使用多块读。
  在较大的表上不建议使用全表扫描、如果读取表的数据总量超过5%—10%，那么通常进行全表扫描。
  并行查询可能会使得我们的路径选择采用全表扫描。
     
          db_file_multiblock_read_count
     select * from emp

      B 通过ROWID对表进行读取（Table access by ROWID）
          ROWID指出了数据文件、块号、行号，通过ROWID是oracle数据库中读取单行数据最快速的方法。
          这种方法不会采用多块读、而是会采用单块读的方式。
     select t.*,t.rowid from emp t;
     select * from emp where rowid='AAAMfMAAEAAAAAfAAA';
     
 C 索引查找
       通过索引找到数据行的ROWID、然后通过ROWID直接到表中查找数据，这种方式称为索引查找或者索引扫描。
    因为一个ROWID对应一个数据行，因此这种方式采用的也是单块读。
    select * from emp where empno=7788
    访问路径走的是主键索引，因此是INDEX UNIQUE SCAN
            首先是索引扫描、然后是根据索引查找到的ROWID进行表的访问。

     
 根据索引的类型和where条件的限制不同，有四种索引扫描类型：
    1)、索引唯一扫描（INDEX UNIQUE SCAN）
    2)、索引范围扫描（INDEX RANGE SCAN）
    3)、索引跳跃扫描 ()
    4)、索引全扫描（INDEX FULL SCAN）有序
    5)、索引快速扫描（INDEX  FAST FULL SCAN） 无序

  (1)索引唯一扫描
           通过唯一键、主键，oracle通常返回一个数据行，因此采用的是索引唯一扫描
      select * from emp where empno=7788
      (2)索引范围扫描
         如果索引不是唯一型索引，那么，通过此索引的访问将是索引范围扫描。
         从根本上说，索引范围扫描和索引唯一扫描是一样的。
  1、在唯一键上使用range操作符（>、、>=、<=、between）
  2、在组合索引上，只使用部分列进行查询、导致查询出多行
  3、对非唯一索引列上进行的查询
    select * from emp where empno>7788
      (3)索引跳跃扫描
      在复合索引中，比如索引包含两个列：
        A列、B列，如果以B列为条件进行查询，将使用索引跳跃扫描。
        1 2
        2 2
        3 1
        4 2
 使用跳跃式索引的条件
　　      1 优化器认为是合适的　　
              2 索引中的前导列的唯一值的数量能满足一定的条件　　
       3 优化器要知道前导列的值分布(通过分析/统计表得到)　　
  4 合适的SQL语句
          如果oracle没有选择使用跳跃式索引扫描，那么可能选择使用索引快速全局扫描或全表扫描。 

     (4)索引全扫描
  查询出的数据必须全部从索引中得到
      索引全扫描（Index Full Scan）不读取索引结构上的每个块，
      它根据部分枝块，找到第一个叶块，然后按叶块双向链表的顺序，处理所有的叶块。
      顺序读取,可以一次读取多个块.
        create index emp_full on emp(empno,ename,deptno);
        select empno,ename,deptno
        from emp
        where deptno=30;
 
          Index Full Scan
        查询的所有列都在索引里,
        索引的前置列并不在where条件中
        select empno,ename,deptno
          from emp
          where empno=7788; -- index range scan 
        select empno,ename,deptno
          from emp ; --Index Full Scan
     
  没有where子句，但是也可能使用到索引
  查询索引列的MIN或者MAX
  对索引列执行count 
          select max(empno) from emp

     (5)索引快速全扫描
      索引快速全扫描是把索引当作表一样进行全扫描操作
  扫描索引块中的所有数据块，这点与index full scan相似，
  但是索引快速扫描不进行数据的排序，在这种方式下，
  可以使用多块读功能、也可以使用并行读功能，最大化数据的吞吐量。
  db_file_multiblock_read_count有效
         select ename,dept.deptno from emp,dept;

5. 有索引，但Oracle不使用索引的情况
    
 1)、使用不等于运算符（<>、!=）
         在where中使用不等于条件，将会使索引失效。
     select * from emp where empno<>7788;
     table access full
      2)、使用 IS NULL或IS NOT NULL
  在where子句中使用IS NULL或者IS NOT NULL同样会限制索引的使用。
  如果被索引的列在某些行中存在NULL值，在索引列中就不会有相应的条目。
  （例外：位图索引对于NULL列也会进行记录，因此位图索引对于NULL搜索通常较为快速）。
  select * from emp where empno is null
  因此建议对列加上DEFAULT 。
     3)、使用函数
  如果不使用基于函数的索引，那么在SQL语句的where子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。
  一些常见的函数：trunc、substr、to_date、to_char、instr等，都可能会使索引失效。
  解决方案：
   1、使用基于函数的索引
   2、灵活书写SQL、避免在索引列上使用SQL函数
     4)数据类型不一致
  select * from test where id=1
  --id varchar2,是索引列

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20670907/viewspace-742562/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20670907/viewspace-742562/