Oracle B-tree、位图、全文索引三大索引性能比较及优缺点汇总.docVIP

引言:大家都知道“效率”是数据库中非常重要的一个指标,如何提高效率大家可能都会想起索引,但索引又这么多种,什么场合应该使用什么索引呢?哪种索引可以提高我们的效率,哪种索引可以让我们的效率大大降低(有时还不如全表扫描性能好下面要讲的“索引”如何成为我们的利器而不是灾难!多说一点,由于不同索引的存储结构不同,所以应用在不同组织结构的数据上,本篇文章重点就是:理解不同的技术都适合在什么地方应用! B-Tree索引 场合:非常适合数据重复度低的字段例如身份证号码手机号码 QQ号等字段,常用于主键唯一约束,一般在在线交易的项目中用到的多些。 原理:一个键值对应一行(rowid格式:【索引头|键值|rowid】 优点:当没有索引的时候,oracle只能全表扫描where qq这个条件那么这样是灰常灰常耗时的,当数据量很大的时候简直会让人崩溃,那么有个B-tree索引我们就像翻书目录一样,直接定位rowid 立刻就找到了我们想要的数据,实质减少了I/O操作就提高速度,它有一个显著特点查询性能与表中数据量无关,例如查2万行的数据用了3 consistent get,当查询1200万行的数据时才用了4 consistent gets。 当我们的字段中使用了主键or唯一约束时,不用想直接可以用B-tree索引 缺点:不适合键值重复率较高的字段上使用,例如第一章1-500page 第二章501-1000page 实验: alter system flush shared_pool; 清空共享池 alter system flush buffer_cache; 清空数据库缓冲区,都是为了实验需要 创建leo_t1 leo_t2 表 leo_t1 表的object_id列的数据是没有重复值的,我们抽取了10行数据就可以看出来了。 LS@LEO create table leo_t1 as select object_id,object_name from dba_objects; LS@LEO select count(* from leo_t1; COUNT(* 9872 LS@LEO select * from leo_t1 where rownum = 10; OBJECT_ID OBJECT_NAME 20 ICOL$ 44 I_USER1 28 CON$ 15 UNDO$ 29 C_COBJ# 3 I_OBJ# 25 PROXY_ROLE_DATA$ 39 I_IND1 51 I_CDEF2 26 I_PROXY_ROLE_DATA$_1 leo_t2 表的object_id列我们是做了取余操作,值就只有0,1两种,因此重复率较高,如此设置为了说明重复率对B树索引的影响 LS@LEO create table leo_t2 as select mod(object_id,2 object_ID ,object_name from dba_objects; LS@LEO select count(* from leo_t2; COUNT(* 9873 LS@LEO select * from leo_t2 where rownum = 10; OBJECT_ID OBJECT_NAME 0 ICOL$ 0 I_USER1 0 CON$ 1 UNDO$ 1 C_COBJ# 1 I_OBJ# 1 PROXY_ROLE_DATA$ 1 I_IND1 1 I_CDEF2 0 I_PROXY_ROLE_DATA$_1 LS@LEO create index leo_t1_index on leo_t1(object_id; 创建B-tree索引,说明默认创建的都是B-tree索引 Index created. LS@LEO create index leo_t2_index on leo_t2(object_ID; 创建B-tree索引 Index created. 让我们看一下leo_t1与leo_t2的重复情况 LS@LEO select count(distinct(object_id from leo_t1; 让我们看一下leo_t1与leo_t2的重 复情况,leo_t1没有重复值,leo_t2有很多 COUNT(DISTINCT(OBJECT_ID 9872 LS@LEO select count(distinct(object_ID from leo_t2; COUNT(DISTINCT(OBJECT_ID 2 收集2个表统计信息 LS@LEO execute dbms_stats.gather_table_stats(ownname=LS,tabname=LEO_T1,method_opt=for all in