PowerPoint Presentation

Size: px

Start display at page:

Download "PowerPoint Presentation"

矮胥
7 years ago
Views:

1 数据库培训项目研究 Oracle 索引探究 B*tree 索引与位图索引的特点作者 : 赵超 2008 年 12 月 18 日

2 实验环境 Windows-server2003 内存 :2G Oracle ORACLE_SID=orcl

3 索引类型 B*tree 索引 ( 默认方式 ) 位图索引 (bitmap) 反向键索引 (reverse key) 位图连接索引 (bitmap Join) 基于函数的索引 (function-based) 域索引 (domain) B*tree 索引是比较基础的索引, 大多数索引是在其存储结构上的此外, 根据不同的分类方法, 可分为不同的类型 : 根据存储结构可共分为 b*tree 索引位图索引反向索引 ; 按照索引个数可分为单列索引复合索引 ; 按照索引列值唯一性可分为唯一性索引和非唯一性索引

其存储结构上的此外, 根据不同的分类方法, 可分为不同的类型 : 根据存储结构可共分为 b*tree 索引位图索

4 B*tree 索引 B*tree 索引 :B 是 (balance) 是默认的索引类型, 是数值的有序列表他是基于二叉树, 索引的顶层是根块儿, 指向下的分支块儿, 最终指向叶块叶块中包含被索引列的值和行所对应的 rowid 结构 : 少于等于 50 大于等于 > rid 101 rid 102 rid ~~~ 50 rid 51 rid 52 rid ~~~ 25 rid 26 rid 27 rid ~~~ 0 rid 2 rid 3 rid ~~~

的 rowid 结构 : 少于等于 50 大于等于 50 50-75 75-100 >100 0-25 25-50 100 rid 101 rid

5 位图索引 Oracle 7.3 版本开始引入, 企业版和个人版支持, 标准版不支持位图索引是一个索引键条目存储指向多个指针 ; 有很少的索引条目, 每个条目指向多行行值 :A/B A B

6 用户创建用户 test create user test identified by test default tablespace users quota unlimited on users temporary tablespace temp; 授权 :create table,create index, connect,plustrace 角色等等

7 实验表建表 : Create table order2 (a1 number(10), a2 varchar2(40), a3 varchar2(20), a4 number(10),a5 number(10), a6 varchar2(20),a7 varchar2(20)); 插入值 : begin for i in Loop insert into order2 values(i,dbms_random.string( a',30), dbms_random.string( b',30), dbms_random.value(1,100), dbms_random.value(1000,100000), dbms_random.string( c',30), chr(round(dbms_random.value(97,98)))); if mod(i, 10000) = 0 then Commit; end if; end loop; end; / 表说明 :a1 字段中的值为唯一性值, 各不相同 ;a2 a3 a6 中的值为极少数相同 ;a4 100 个不同的值,100/ = , 比例非常小 ;a 个值, 99000/ = ;a7 只有两个值 2/ = ; 其中这个比例可以叫做基数, 越小, 选择做位图索引越有可能

8 语句执行跟踪使用 autotrace 自动为用户指定 execution plan Sys 建立 PLAN_TABLE 表建立 plustrace 角色 Set autot on 或者 set autotrace traceonly 每一次执行查询插入等操作均执行 shutdown immediate 清空缓存

sql, 建立 plustrace 角色 : @$ORACLE_HOME/sqlplus/admin/plustrace.

9 a1 未建立索引 Select * from order2 where a1=1; Select * from order2 where a1=20000; Select * from order2 where a1= ; Select * from order2 where a2=yuxvfnftll;

10 Select * from order2 where a1>=1 and a1<= ; Select * from order2 where a1>=1 and a1<=

11 a1 建立 b*tree 索引 Create index ix_order2_a1 on order2(a1) nologging; Nologging 可以减少归档空间

12 Select * from order2 where a1=1; Select * from order2 where a1=20000; Select * from order2 where a1= ; Select * from order2 where a2=yuxvfnftll;

13 Select * from order2 where a2= yuxvfnftll and a1= ;

14 Select * from order2 where a1>=1 and a1<= ; Select * from order2 where a1>=1 and a1<= ; Select * from order2 where a1>=1 and a1<= ; Select * from order2 where a1>=1 and a1<= ; Select * from order2 where a1>=1 and a1<= ;

where a1>=1 and a1<=2000000; Select * from order2 where a1>=1

15 Select * from order2 where a4=1; a4( 列中 100 个值重复 ) 作为条件

16 a4 建立 B*tree 索引 : Create index ix_order2_a4 on order2(a4) nologging;

17 Select * from order2 where a4=1;

18 Select owner,segment_name,round(bytes/1024/1024,2) M from dba_segments where owner= TEST ;

19 Drop index ix_order2_a4; create bitmap index ix_order2_a4_b on order2(a4) nologging; 如果有多个重复值,Bitmap 索引比 b*tree 节省物理空间

20 Select * from order2 where a4=1;

21 ( 属性表 ) 10 7 a1 Ix_order2_a1 a4 Ix_order2_a4 Ix_order2_a4_b 数值 / 行数 1 * 1/10 5 * * 类型 * B*tree * B*tree Bitmap 索引大小 * 192M * 160M 22M

22 (cost(%cpu) 表 ) 字段条件 time 索引利用 a1 ix_order 2_a1 1 行 : * %10: %30: 25~30s * 16807(3) 50~60s * 16849(4) 1 行 : %10: %15: %20: %25: %30: 0.40~ ~22s 25~30s 38~42s 48~53s 60s 以上 ( 利 ) ( 利 ) ( 利 ) ( 利 ) ( 未 ) ( 未 ) 4(0) 2898(1) 8562(1) 14226(1) 16839(4) 16846(4) a4 1 行 9~10s * 16216(4) ix_order 2_a4 ix_order 2_a4_b 1 行 1 行 9~10s ( 未 ) 16216(4) 8~9s ( 利 ) 9812(1) Cost (%cpu) 7~8s 16119(3)

23 a7( 只有 a b 两个值 ) 未建立索引 Select * from order2 where a7= a ; Select * from order2 where a7= b ;

24 建立 b*tree 索引 : Create index ix_order2_a7 on order2(a7); Select owner,segment_name,round(bytes/1024/1024,2) M from dba_segments where owner= TEST ; Select * from order2 where a7= a ; Select * from order2 where a7= b ;

25 建立 bitmap 索引 Drop index ix_order2_a7; Create bitmap index ix_order2_a7_b on order2(a7) nologging; Select owner,segment_name,round(bytes/1024/1024,2) M from dba_segments where owner= TEST ; Select * from order2 where a7= a ; Select * from order2 where a7= b ;

26 ix_order2_a7_b,ix_order2_a4_b 比较 10 7 a4 Ix_order2_a4 Ix_order2_a4_ b a7 Ix_order2_a7 Ix_order2_ a7_b 数值 / 行数 1/10 5 * * 2/10 7 * * 类型 * B*tree Bitmap B*tree Bitmap 以该列为条件查询 (s) 9~10s 9~10s( 未利用索引 ) 8~9s( 利用了索引 ) 80-90s s( 未利 s 用索引 ) ( 利用了索引 ) 索引大小 * 160M 22M * 144M 4M 当使用位图索引时, 如果表中的行数比较少, 可以迅速得到结果, 如果数值比较大, 看似时间上没有什么变化, 但是结果输出也占用一定的时间, 对于查询的性能还是提高的, 占用较少的 cpu 资源

27 Update 测试以上索引均正常情况下 : Update 1 行 : 00:0:13.93(table_full) Update 10 4 行 : 无法执行,session 锁住

28 建立主键, 同时在该列上也有了唯一索引 drop index ix_order2_a1; 删除系统为了保证 b*tree 索引的唯一性而加上的 rowid 索引 Alter table order2 add constraint cst_a1 primary key (a1); Select * from order2 where a1=1;

29 总结一 B*tree 索引可以很好的自动平衡创建了主键, 同时创建了索引 ; 索引会对频繁的 DML 操作造成影响, 而不合适的位图索引造成的结果是严重的, 可能造成表锁会话锁在实际应用中, 应结合表数据特点检索行数主外键多表结合检索等因素, 选取合适的列建立相应类别的检索

30 总结二索引的好处 : 1 创建唯一性索引, 保证数据库表中每一行数据的唯一性 2 大大加快数据的检索速度 3 加速表和表之间的连接, 特别是在实现数据的参考完整性方面特别有意义索引的缺点 : 1 索引降低了数据库插入修改删除等维护任务的速度 2 创建索引和维护索引要耗费时间, 这种时间随着数据量的增加而增加 3 每一个索引要占一定的物理空间

31 结束!

32 (cost(%cpu) 表 ) 10 7 a1 Ix_order2_a1 a4 Ix_order2_a4 Ix_order2_a4_b 数值 / 行数 1 * 1/10 5 * * 类型 * B*tree * B*tree Bitmap 以该列为条件查询 (s) 1 行 :7~8s %10: 25~30s %30: 50~60s 1 行 :0.40~0.50 ( 利 ) %10:18~22s ( 利 ) %15:25~30s ( 利 ) %20:38~42s ( 利 ) %25:48~53s ( 未 ) 9~10s 9~10( 未 ) Cost (%cpu): ~9( 利用了索引 ) Cost (%cpu):9812 %30:60s 以上 ( 未 ) 索引大小 * 192M * 160M 22M

ebook 96-16

ebook 96-16 16 13 / ( ) 16-1 SQL*Net/Net8 SQL*Net/Net8 SQL*Net/Net8 16-1 / S Q L SQL*Net V2 N e t 8 S Q L * N e t N e t ( ) 16.1 S Q L O r a c l e S Q L 16 401 ) ( H R _ L I N K create database link p u b l i c (