Oracle 索引入门与提高

来源:互联网 发布:程序员的个人简历 编辑:程序博客网 时间:2024/05/16 18:19
     对Oracle数据库的接触算起来也有3年多了,刚学习时更多的是了解Oracle数据库的架构,慢慢的学习日常的基本管理工作。随着对Oracle数据库的不断深入,工作中涉及到一些数据库的调优问题,总的看下来,无非就是调内存、调存储I/O、调网络、调SQL语句。随着Oracle数据库的发展11g对内存的自动管理度有了很大的提高,况且现在内存也便宜,因此现在对内存的可调度已经不是关键的了。存储的I/O问题在好的系统架构上一般来说不会有太大的问题,而且现在的存储性能也非常的好。对于ERP系统来说,大多是内网或城际专线,网络对ERP系统来说一般不是大问题,更多的是考虑网络的冗余链路,保证网络的畅通性和可靠性。剩下的就是对SQL语句的调优了,不好的SQL语句可能会导致Oracle的内存和I/O问题,索引和统计信息对数据库SQL语句执行计划的SQL PATH至关重要,因此花点时间收集和总结下索引相关的知识。


一、索引分类

索引可以从逻辑上、物理上和结构上来划分。

逻辑上分为:

1. 单列索引

索引键只有一列的索引。

2. 多列索引

索引键由多列组成的索引。

3.唯一索引

索引键为唯一值的索引。

4. 非唯一索引

索引键为非唯一的索引。

5. 函数索引

B*Tree的衍生产物,应用于查询语句条件列上包含函数的情况,索引中储存了经过函数计算的索引码值。可以在不修改应用程序的基础上能提高查询效率。

6. 簇索引(cluster index)

物理上分为:

1. 分区索引

对应到分区表的概念,对索引也进行分区。

2. 非分区索引

全局索引。

结构上分为:

1. B-Tree索引(常规B-Tree、反向B-Tree、降序索引)

反向B-Tree:B*Tree的衍生产物,应用于特殊场合,在ops环境加序列增加的列上建立,不适合做区域扫描。

降序索引::B*Tree的衍生产物,应用于有降序排列的搜索语句中,索引中储存了降序排列的索引码,提供了快速的降序搜索。

2. 位图索引

    B-Tree索引和位图索引的区别是:

    B-Tree索引适合于OLTP系统,有大量的增删改动作,适合于基数cardinality高的列(即列的唯一值除以行数为一个很大的值,存在很少的相同值),不能用于包含OR操作符的查询,在访问数据量比较小的适合比较适合。B-Tree索引结构上来说呈现典型的树状结构,每个节点都是数据块,一般物理上是1到3层,逻辑上是3层,叶子块数据是排序的,从左向右递增。分支块和根块存放的是索引的范围,索引头记录了开始的Rowid和结束的Rowid。

    位图索引比较适合数据仓库系统和决策支持系统,适合集中读取,不太适合做大量的频繁插入和更新操作,但比B-Tree索引更节省空间,对基数低的列比较适合(如性别男女)。位图索引的每一个BIT对应一个ROWID,它的值是1或0,1表示BIT对应的ROWID。

    在位图Oracle索引中,并没有行级锁。如果你更新或插入其中一条数值为N的记录,那么相应表中数值为N的记录(可能成百上千条)全部被Oracle锁定,这就意味着其它用户不能同时更新这些数值为N的记录,其它用户必须要等第一个用户提交后,才能获得锁,更新或插入数据,bitmap index它主要用于决策支持系统或静态数据。可以google下tom的位图索引的故事

二、索引的结构
1. 平衡数索引(B-Tree索引)
     Oracle使用平衡树(B-tree)存储索引以便提升数据访问速度。当不使用索引时,用户必须对数据进行顺序扫描(sequential scan)来查找指定的值。如果有 n 行数据,那么平均需要扫描的行为 n/2。因此当数据量增长时,这种方法的开销将显著增长。

如果将一个已排序的值列(list of the values)划分为多个区间(range),每个区间的末尾包含指向下个区间的指针(pointer),而搜索树(search tree)中则保存指向每个区间的指针。此时在 n 行数据中查询一个值所需的时间为 log(n)。这就是 Oracle索引的基本原理。

下图显示了平衡树索引(B-tree index)的结构:

在一个平衡树索引(B-tree index)中,最底层的索引块(叶块(leaf block))存储了被索引的数据值,以及对应的 rowid。叶块之间以双向链表的形式相互连接。位于叶块之上的索引块被称为分支块,分枝块中包含了指向下层索引块的指针。如果被索引的列存储的是字符数据,那么索引值为这些字符数据在当前数据库字符集中的二进制值。

对于唯一索引,每个索引值对应着唯一的一个 rowid。对于非唯一索引,每个索引值对应着多个已排序的 rowid。因此在非唯一索引中,索引数据是按照索引键(index key)及 rowid 共同排序的。键值(key value)全部为 NULL 的行不会被索引,只有位图索引(bitmap index)和簇索引(cluster index)例外。在数据表中,如果两个数据行的全部键值都为 NULL,也不会与唯一索引相冲突。

有两种类型的索引块:

1、用于搜索的分支块(branch block)

2、用于存储索引数据的叶块(leaf block)

分支块中存储以下信息:

1、最小的键值前缀,用于在(本块的)两个键值之间做出分支选择。

2、指向包含所查找键值的子块的指针。

包含 n 个键值的分支块含有 n+1 个指针。键值及指针的数量同时还受索引块容量的限制。

所有叶块相对于其根分支块的深度是相同的。

叶块用于存储以下信息:

1、数据行的键值(key value) 。

2、键值对应数据行的 ROWID 。

所有的 键值-ROWID 对都与其左右的兄弟节点向链接,并按照(key,ROWID)的顺序排序。

平衡树数据结构(B-tree structure)具有以下优势:

1、平衡树(B-tree)内所有叶块的深度相同,因此获取索引内任何位置的数据所需的时间大致相同。

2、平衡树索引(B-tree index)能够自动保持平。

3、平衡树内的所有块的使用容量平均在块总容量的 3/4 左右。

4、在大区间范围内进行查询时,无论匹配个别值还是搜索一个区间,平衡树都能提供较好的查询性能。

5、数据插入(insert),更新(update),及删除(delete)的效率较高,且易于维护键值的顺序。

6、大型表,小型表利用平衡树进行搜索的效率都较好,且搜索效率不会因数据增长而降低。

 
三、创建索引的原则:
1. 表的大小
   A. 小表一般来说不建议创建索引
   B. 大表也不是一定要创建索引,一般来说经常查询的表且查询数据在10~15%之间要建立索引。有别要的话查看执行计划比较使用索引和全表扫描的成本决定是否创建索引。
2. 列的选择
   A. 一般来说对应重复数据比较少的列,如唯一性的列。对应唯一性索引系统默认会创建索引。
   B. 对既有NULL又有非NULL的列,如果经常要查询非NULL的值,最好为其建立索引。
   C.  经常参与join的列
 
  
索引的争论点:
1. 块的大小是否影响索引性能
   网上看到有实验证明大的数据块有利用提高索引的性能。理论是一个大的数据块能存储更多的索引键和指针及ROWID,使得创建的索引更加平整,也可以有效的降低数的高度,可以提高索引查询的性能。我觉得在有一定道理,但也不是绝对的。有时间自己做个实验验证下。
2. 重建索引是否可提高性能
  网上有些争论是针对重建索引是否能提高性能,什么情况下需要重建?先分析下索引块的扩展机制,为正确管理这些数据块,Oracle控制着每个数据块中指针的分配。随着树的增长(插入数据),Oracle会填充这个数据块,当这个数据块满时Oracle会进行分裂,创建新的索引节点来管理索引内的符合键。当删除数据时并不会释放空间,只有当新插入数据又匹配可用空间时才被利用,时间长了碎片比较多,树的深度也比较大,此时如果重建索引清理碎片、合并相邻节点,降低树的深度,一定程度上能提高索引的查询性能。
 
 
四、索引的维护
由于频繁的对索引进行增删改动作,会产生很多碎片,这将影响查询的I/O性能,需要经常对索引进行维护。对索引的维护主要从两个方面来考虑一个是空间利用率一个是树的深度。
通过ANALYSE INDEX ... validate structure来查看INDEX_STATS表的空间情况可以判断是否需要重建索引,一般select (del_lf_rows_len/lf_rows_len)*100 from index_stats where name=’------‘的比例》20%则需要重建索引
通过ANALYSE INDEX ...compute statistics来查看dba_indexes的blevel树的深度,如果Select index_name,blevel from dba_indexes where blevel>=4则需要重建。
以下摘自http://space.itpub.net/17179887/viewspace-662545

一:考虑重建索引的场合
1:表上频繁发生update,delete操作
2:表上发生了alter table ..move操作(move操作导致了rowid变化)
二:判断重建索引的标准
  索引重建是否有必要,一般看索引是否倾斜的严重,是否浪费了空间;
  那应该如何才可以判断索引是否倾斜的严重,是否浪费了空间,如下:
1,        对索引进行结构分析
Analyze index indexname validate structure;
2, 在执行步骤1的session中查询index_stats表,不要到别的session去查询
   select height,DEL_LF_ROWS/LF_ROWS from index_stats;
3, 在步骤2查询出来的height>=4或者DEL_LF_ROWS/LF_ROWS>0.2的场合,该索引考虑重建;
Example:
   SQL> select count(*) from test_index;
         COUNT(*)
----------
                   2072327
SQL> analyze index pk_t_test validate structure;
Index analyzed
        SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;
                     HEIGHT         DEL_LF_ROWS/LF_ROWS
---------- -------------------
         3                   0
SQL> delete from test_index where rownum<250000;
          249999 rows deleted
        SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;
                    HEIGHT                 DEL_LF_ROWS/LF_ROWS
---------- -------------------
                         3                   0
        SQL> analyze index pk_t_test validate structure;
                Index analyzed
SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;
                    HEIGHT                  DEL_LF_ROWS/LF_ROWS
---------- -------------------
                 3                           0.0777430939338362
三:重建索引的方式
    1:drop 原来的索引,然后再创建索引;
    2:alter index indexname rebuild (online);
    方式一:耗时间,无法在24*7环境中实现
    方式二:比较快,可以在24*7环境中实现
    建议使用方式二
四:alter index rebuid内部过程和注意点
    1:alter index rebuild 和alter index rebuil online的区别
(1)        扫描方式不同
Rebuild以index fast full scan(or table full scan) 方式读取原索引中的数据来构建一个新的索引,有排序的操作; rebuild online 执行表扫描获取数据,有排序的操作;
Rebuild  方式 (index fast full scan  or  table full scan  取决于统计信息的cost)
Eg1:
SQL> explain plan for alter index idx_policy_id2 rebuild;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
| Id  | Operation              |  Name           | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | ALTER INDEX STATEMENT  |                 |   999K|  4882K|  3219 |
|   1 |  INDEX BUILD NON UNIQUE| IDX_POLICY_ID2  |       |       |       |
|   2 |   SORT CREATE INDEX     |                 |   999K|  4882K|       |
|   3 |    INDEX FAST FULL SCAN | IDX_POLICY_ID2  |   999K|  4882K|       |
---------------------------------------------------------------------
Eg2:
SQL>  explain plan for alter index idx_policy_id rebuild;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
| Id  | Operation              |  Name          | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | ALTER INDEX STATEMENT  |                |  2072K|     9M|   461 |
|   1 |  INDEX BUILD NON UNIQUE| IDX_POLICY_ID  |       |       |       |
|   2 |   SORT CREATE INDEX    |                |  2072K|     9M|       |
|   3 |    TABLE ACCESS FULL   | TEST_INDEX     |  2072K|     9M|   461 |


Eg3: (注意和Eg1比较)
Rebuil online 方式:
SQL> explain plan for alter index idx_policy_id2 rebuild online;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
| Id  | Operation              |  Name           | Rows  | Bytes | Cost  |
---------------------------------------------------------------------|   0 | ALTER INDEX STATEMENT  |                 |   999K|  4882K|  3219 |
|   1 |  INDEX BUILD NON UNIQUE| IDX_POLICY_ID2  |       |       |       |
|   2 |   SORT CREATE INDEX    |                 |   999K|  4882K|       |
|   3 |    TABLE ACCESS FULL   | TEST_INDEX2     |   999K|  4882K|  3219 |
(2)        rebuild 会阻塞dml操作,rebuil online 不会阻塞dml操作;
(3)        rebuild online时系统会产生一个SYS_JOURNAL_xxx的IOT类型的系统临时日志表,所有rebuild online时索引的变化都记录在这个表中,当新的索引创建完成后,把这个表的记录维护到新的索引中去,然后drop掉旧的索引,rebuild online就完成了。

注意点:
1,        执行rebuild操作时,需要检查表空间是否足够;
2,        虽然说rebuild online操作允许dml操作,但是还是建议在业务部繁忙时间段进行;
3,        Rebuild操作会产生大量redo log ;

五:重建分区表上的分区索引
   1:重建分区索引方法:
     Alter index indexname rebuild partition paritionname tablespace tablespacename;
     Alter index indexname rebuild subpartition partitioname tablespace  tablespacename;
     Partition name 可以从user_ind_partitions查找
     Tablepace 参数允许alter index操作更改索引的存储空间;

六:索引状态描述

在数据字典中查看索引状态,发现有三种:   
  VALID   
  N/A   
  UNUSABLE   
   
valid:当前索引有效
N/A :分区索引 有效
unusable:索引失效

七:术语

  高基数:简单理解就是表中列的不同值多
  低基数:建单理解就是表中的列的不同值少
  以删除的叶节点数量:指得是数据行的delete操作从逻辑上删除的索引节点   的数量,要记住
oracle在删除数据行后,将“死“节点保留在索引中,这样做可以加快sql删除操作的速度,因此oracle删除数据行后可以不必重新平衡索引。
   索引高度:索引高度是指由于数据行的插入操作而产生的索引层数,当表中添加大量数据时,oracle将生成索引的新层次以适应加入的数据行,因此,oracle索引可能有4层,但是这只会出现在索引数中产生大量插入操作的区域。Oracle索引的三层结构可以支持数百万的项目,而具备4层或是更多层的需要重建。
    每次索引访问的读取数:是指利用索引读取一数据行时所需要的逻辑I/O操作数,逻辑读取不必是物理读取,因为索引的许多内容已经保存在数据缓冲区,然而,任何数据大于10的索引都需要重建。

    那么什么时候重建呢?我们可以利用analyze index …….. compute statistics 对表进行分析。然后察看dba_indexes中的blevel。这列是说明索引从根块到叶快的级别,或是深度。如果级别大于等于4。则需要重建,如下:
Select index_name,blevel from dba_indexes where blevel>=4.
   另一个从重建中受益的指标显然是当该索引中的被删除项占总的项数的百分比。如果在20%以上时,也应当重建,如下
SQL>anlyze index ------ validate structure
SQL>select (del_lf_rows_len/lf_rows_len)*100 from index_stats where name=’------‘
就能看到是否这个索引被删除的百分比。
上面只是判断,那么,怎样重建会更好呢?
建索引的办法:
a.        删除并从头开始建立索引。
b.        使用alter index -------- rebuild 命令重建索引
c.        使用alter index -------- coalesce命令重建索引。
下面讨论一下这三种方法的优缺点:
1).删除并从头开始建索引:方法是最慢的,最耗时的。一般不建议。
2).Alter index ---- rebuild 快速重建索引的一种有效的办法,因为使用现有索引项来重建新索引,如果客户操作时有其他用户在对这个表操作,尽量使用带online参数来最大限度的减少索引重建时将会出现的任何加锁问题,alter index ------- rebuild online.但是,由于新旧索引在建立时同时存在,因此,使用这种技巧则需要有额外的磁盘空间可临时使用,当索引建完后把老索引删除,如果没有成功,也不会影响原来的索引。利用这种办法可以用来将一个索引以到新的表空间。
Alter index ------ rebuild  tablespace -----。
  这个命令的执行步骤如下:
   首先,逐一读取现有索引,以获取索引的关键字。
   其次,按新的结构填写临时数据段。
   最后,一旦操作成功,删除原有索引树,降临时数据段重命名为新的索引。
   需要注意的是alter index ---rebuild 命令中必须使用tablespace字句,以保证重建工作是在现有索引相同的表空间进行。
3).alter index ----- coalesce 使用带有coalesce参数时重建期间不需要额外空间,它只是在重建索引时将处于同一个索引分支内的叶块拼合起来,这最大限度的减少了与查询过程中相关的潜在的加锁问题,但是,coalesce选项不能用来讲一个索引转移到其他表空间。

八:其他
   1:truncate 分区操作和truncate 普通表的区别
      Truncate 分区操作会导致全局索引失效; truncate 普通表对索引没有影响;
      Truncate 分区操作不会释放全局索引中的空间,而truncate 普通表会释放
索引所占空间;
   2:rename 表名操作对索引没有影响,因为rename操作只是更改了数据字典,表中数据行的rowid并没有发生变化

五、索引扫描的种类和机制
 
索引扫描有5中类型:
A. 索引唯一扫描(Index unique scan)
如果查询时是通过unique或primary key约束来保证只返回一条数据,那么优化器就会选择索引唯一扫描,这是访问一条数据的最快方式。
B. 索引范围扫描(Index range scan)
索引键非唯一,当遇到大于小于不等的条件时会使用索引范围扫描
C. 索引全扫描(Index Full scan)
D. 索引快速扫描(Index Fast full scan)
Index full scan的时候oracle定位到索引的root block,然后到branch block(如果有的话),再定位到第一个leaf block, 然后根据leaf block的双向链表顺序读取。它所读取的块都是有顺序的,也是经过排序的。
而index fast full scan则不同,它是从段头开始,读取包含位图块,root block,所有的branch block, leaf block,读取的顺序完全有物理存储位置决定,并采取多块读,没次读取db_file_multiblock_read_count个块。
对应排序的查询一般会选择索引全扫描,对于不排序的采用索引快速扫描。这两种索引都必须是索引键中不包括NULL值。
E. 跳跃式索引(Skip Scan Index)
当表有一个复合索引,而在查询中有除了索引中第一列的其他列作为条件,并且优化器模式为CBO,这时候查询计划就有可能使用到SS,另外通过使用提示index_ss(CBO下)来强制使用SS。跳跃式索引使复合索引从逻辑上分为几个小的子索引,分的条件就是复合索引列的第一个字段,可以这样理解,Oracle将索引从逻辑上划分为a.num_distinct个子索引,每次对一个子索引进行扫描。因此SS的索引扫描成本为a.num_distinct.而且使用SS的条件需要第一列的distinct num要足够小
原创粉丝点击
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防,我是瞎子 m8螺丝牙距孔距差一个毫米怎么办 内丝弯头坏了怎么办 螺纹底孔打大了怎么办 内螺纹止规过了怎么办 牌照螺丝装错了怎么办 帽式扳手大了怎么办 内六角扳手小了怎么办 内六角扳手不够大怎么办? 苹果6螺丝拧花了怎么办 苹果电脑螺丝拧花了怎么办 苹果螺丝滑牙了怎么办 苹果7螺丝滑丝了怎么办 外六角螺丝滑牙怎么办 六角螺帽滑丝了怎么办 内六角螺丝滑头了怎么办 内六角螺丝滑失了怎么办 内六角螺钉滑了怎么办 三视图看不出来怎么办 小猫断奶以后母猫涨奶怎么办 手机螺丝滑丝了怎么办 螺丝孔道滑丝了怎么办 螺丝生锈了拧不下来怎么办 钣金加工六角网孔变形怎么办 外六角螺帽滑丝怎么办 内六角螺丝螺帽滑丝怎么办 一字螺丝钉脱扣拧不下来怎么办 一字螺丝拧花了怎么办 小螺丝卸不下来怎么办 机油螺丝滑丝了怎么办 刚滑双板膝盖滑的疼怎么办 lv包真皮弄脏了怎么办 lv包压变形了怎么办 lv的包包被压了怎么办 固态硬盘太小了怎么办 联想笔记本网络连接不可用怎么办 联想g50玩dnf卡怎么办 手机有wifi电脑没有网怎么办 电脑网卡被禁用了怎么办 win8系统装win7蓝屏怎么办 联想笔记本装win7蓝屏怎么办 联想g40-70开机黑屏怎么办