Oracle Btree位图全文索引三大索引性能比较及优缺点汇总剖析.docx

1、Oracle Btree位图全文索引三大索引性能比较及优缺点汇总剖析引言：大家都知道“效率”是数据库中非常重要的一个指标，如何提高效率大家可能都会想起索引，但索引又这么多种，什么场合应该使用什么索引呢？哪种索引可以提高我们的效率，哪种索引可以让我们的效率大大降低（有时还不如全表扫描性能好）下面要讲的“索引”如何成为我们的利器而不是灾难！多说一点，由于不同索引的存储结构不同，所以应用在不同组织结构的数据上，本篇文章重点就是：理解不同的技术都适合在什么地方应用！B-Tree索引场合：非常适合数据重复度低的字段 例如 身份证号码手机号码QQ号等字段，常用于主键 唯一约束，一般在在线交易的项目中用到的

2、多些。原理：一个键值对应一行（rowid）格式： 【索引头|键值|rowid】优点：当没有索引的时候，oracle只能全表扫描where qq=40354446 这个条件那么这样是灰常灰常耗时的，当数据量很大的时候简直会让人崩溃，那么有个B-tree索引我们就像翻书目录一样，直接定位rowid立刻就找到了我们想要的数据，实质减少了I/O操作就提高速度，它有一个显著特点查询性能与表中数据量无关，例如 查2万行的数据用了3 consistent get,当查询1200万行的数据时才用了4 consistent gets。当我们的字段中使用了主键or唯一约束时，不用想直接可以用B-tree索引缺点：

3、不适合键值重复率较高的字段上使用，例如 第一章 1-500page 第二章 501-1000page实验：alter system flush shared_pool; 清空共享池alter system flush buffer_cache;清空数据库缓冲区，都是为了实验需要创建leo_t1leo_t2 表leo_t1 表的object_id列的数据是没有重复值的，我们抽取了10行数据就可以看出来了。LSLEO create table leo_t1 as select object_id,object_name from dba_objects;LSLEO select count(*)

4、from leo_t1;COUNT(*)- 9872LSLEOselect * from leo_t1 where rownum create table leo_t2 as select mod(object_id,2) object_ID ,object_name from dba_objects;LSLEO select count(*) from leo_t2;COUNT(*)- 9873LSLEO select * from leo_t2 where rownum create index leo_t1_index on leo_t1(object_id); 创建B-tree索引，说

5、明 默认创建的都是B-tree索引Index created.LSLEO create index leo_t2_index on leo_t2(object_ID); 创建B-tree索引Index created.让我们看一下leo_t1与leo_t2的重复情况LSLEO select count(distinct(object_id) from leo_t1; 让我们看一下leo_t1与leo_t2的重复情况，leo_t1没有重复值，leo_t2有很多COUNT(DISTINCT(OBJECT_ID)- 9872LSLEO select count(distinct(object_ID)

6、 from leo_t2;COUNT(DISTINCT(OBJECT_ID)- 2收集2个表统计信息LSLEO execute dbms_stats.gather_table_stats(ownname=LS,tabname=LEO_T1,method_opt=for all indexed columns size 2,cascade=TRUE);LSLEO execute dbms_stats.gather_table_stats(ownname=LS,tabname=LEO_T2,method_opt=for all indexed columns size 2,cascade=TRUE

7、);参数详解：method_opt=for all indexed columns size 2size_clause=integer 整型 ，范围 1254 ，使用柱状图 histogram analyze 分析列数据的分布情况cascade=TRUE 收集表的统计信息的同时收集B-tree索引的统计信息显示执行计划和统计信息+设置autotrace简介序号命令 解释1 SET AUTOTRACE OFF 此为默认值，即关闭Autotrace2 SET AUTOTRACE ON EXPLAIN 只显示执行计划3 SET AUTOTRACE ON STATISTICS 只显示执行的统计信息4

8、SET AUTOTRACE ON 包含2,3两项内容5 SET AUTOTRACE TRACEONLY 与ON相似，但不显示语句的执行结果结果键值少的情况set autotrace trace exp stat;（SET AUTOTRACE OFF 关闭执行计划和统计信息）LSLEO select * from leo_t1 where object_id=1;no rows selectedExecution Plan 执行计划-Plan hash value: 3712193284-| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Ti

9、me |-| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 | 1 |TABLE ACCESS BY INDEX ROWID| LEO_T1 | 1 | 21 | 2 (0)| 00:00:01 |*2 | INDEX RANGE SCAN索引扫描| LEO_T1_INDEX | 1 | | 1 (0)| 00:00:01 |-Predicate Information (identified by operation id):- 2 - access(OBJECT_ID=1)Statistics统计信息- 0recursive calls

10、 0db block gets 2consistent gets我们知道leo_t1表的object_id没有重复值，因此使用B-tree索引扫描只有2次一致性读 0physical reads 0redo size 339bytes sent via SQL*Net to client 370bytes received via SQL*Net from client 1SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 0rows processed结果键值多的情况LSLEO select * from leo_t

11、2 where object_ID=1; （select /*+full(leo_t2) */ *from leo_t2 where object_ID=1;hint方式强制全表扫描）4943 rows selected. Execution Plan执行计划-Plan hash value: 3657048469-| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT| |4943 | 98860 | 12 (0)| 00:00:01 |*1 |TABLE ACCESS FULL| L

12、EO_T2 |4943 | 98860 | 12 (0)| 00:00:01 | sql结果是4943row，那么全表扫描也是4943row-Predicate Information (identified by operation id):- 1 - filter(OBJECT_ID=1)Statistics统计信息- 1recursive calls 0db block gets 366consistent gets 导致有366次一致性读 0physical reads 0redo size 154465bytes sent via SQL*Net to client 4000byte

13、s received via SQL*Net from client 331SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 4943rows processed大家肯定会疑惑，为什么要用全表扫描而不用B-tree索引呢，这是因为oracle基于成本优化器CBO认为使用全表扫描要比使用B-tree索引性能更好更快，由于我们结果重复率很高，导致有366次一致性读，从cup使用率12%上看也说明了B-tree索引不适合键值重复率较高的列我们在看一下强制使用B-tree索引时，效率是不是没有全表扫描高呢？LSLEO sel

14、ect /*+index(leo_t2 leo_t2_index) */ * from leo_t2 where object_ID=1; hint方式强制索引扫描4943 rows selected.Execution Plan执行计划-Plan hash value: 321706586-| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | |4943 | 98860 | 46 (0)| 00:00:01 | 1 |TABLE ACCESS BY INDEX ROWID| LE

15、O_T2 |4943 | 98860 | 46 (0)| 00:00:01 |*2 | INDEX RANGE SCAN | LEO_T2_INDEX |4943 | | 10 (0)| 00:00:01 |-Predicate Information (identified by operation id):- 2 - access(OBJECT_ID=1)Statistics统计信息- 1recursive calls 0db block gets 704consistent gets使用B-tree索引704次一致性读 全表扫描366次一致性读，而且cpu使用率也非常高，显然效果没有全表

16、扫描高 0physical reads 0redo size 171858bytes sent via SQL*Net to client 4000bytes received via SQL*Net from client 331SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 4943rows processed小结：从以上的测试我们可以了解到，B-tree索引在什么情况下使用跟键值重复率高低有很大关系的，之间没有一个明确的分水岭，只能多测试分析执行计划后来决定。位图索引 Bitmap index场合：列的基数很

17、少，可枚举，重复值很多，数据不会被经常更新原理：一个键值对应很多行（rowid）， 格式：键值start_rowid end_rowid位图优点：OLAP 例如报表类数据库 重复率高的数据 特定类型的查询例如count、or、and等逻辑操作因为只需要进行位运算即可得到我们需要的结果缺点：不适合重复率低的字段，还有经常DML操作（insert，update，delete），因为位图索引的锁代价极高，修改一个位图索引段影响整个位图段，例如修改一个键值，会影响同键值的多行，所以对于OLTP 系统位图索引基本上是不适用的实验：位图索引和B-tree索引的性能比较set pagesize 100; 设

18、置页大小利用dba_objects数据字典创建一个15万行的表LSLEO create table leo_bm_t1 as select * from dba_objects;Table created.LSLEO insert into leo_bm_t1 select * from leo_bm_t1;翻倍插入9876 rows created.LSLEO /19752 rows created.LSLEO /39504 rows created.LSLEO /79008 rows created.LSLEO /158016 rows created.因object_type字段重复值较

19、高，顾在此字段上创建bitmap索引LSLEO create bitmap index leo_bm_t1_index on leo_bm_t1(object_type);Index created.创建一个和leo_bm_t1表结构一模一样的表leo_bm_t2，并在object_type列上创建一个B-tree索引（15万行记录）LSLEO create table leo_bm_t2 as select * from leo_bm_t1;Table created.LSLEO create index leo_bm_t2_bt_index on leo_bm_t2(object_type

20、);Index created.对比位图索引和B-tree索引所占空间大小,很明显位图要远远小于B-tree索引所占用的空间，节约空间特性也是我们选择位图的理由之一LSLEO select segment_name,bytes from user_segments where segment_type=INDEX;SEGMENT_NAME BYTES- -LEO_BM_T1_INDEX 327680(327K)LEO_BM_T2_BT_INDEX 7340032(7M)显示执行计划和统计信息set autotrace trace exp stat;在创建有位图索引的表上做count操作对比执行计划LSLEO select count(*) from leo_bm_t1 where object_type=TABLE;Execution Plan执行计划-Plan hash value: 3251686305-| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |-