Oracle Btree位图全文索引三大索引性能比较及优缺点汇总模板.docx
- 文档编号:6430622
- 上传时间:2023-01-06
- 格式:DOCX
- 页数:12
- 大小:22.12KB
Oracle Btree位图全文索引三大索引性能比较及优缺点汇总模板.docx
《Oracle Btree位图全文索引三大索引性能比较及优缺点汇总模板.docx》由会员分享,可在线阅读,更多相关《Oracle Btree位图全文索引三大索引性能比较及优缺点汇总模板.docx(12页珍藏版)》请在冰豆网上搜索。
OracleBtree位图全文索引三大索引性能比较及优缺点汇总模板
引言:
大家都知道“效率”是数据库中非常重要的一个指标,如何提高效率大家可能都会想起索引,但索引又这么多种,什么场合应该使用什么索引呢?
哪种索引可以提高我们的效率,哪种索引可以让我们的效率大大降低(有时还不如全表扫描性能好)下面要讲的“索引”如何成为我们的利器而不是灾难!
多说一点,由于不同索引的存储结构不同,所以应用在不同组织结构的数据上,本篇文章重点就是:
理解不同的技术都适合在什么地方应用!
B-Tree索引
场合:
非常适合数据重复度低的字段例如身份证号码 手机号码 QQ号等字段,常用于主键唯一约束,一般在在线交易的项目中用到的多些。
原理:
一个键值对应一行(rowid) 格式:
【索引头|键值|rowid】
优点:
当没有索引的时候,oracle只能全表扫描whereqq=40354446这个条件那么这样是灰常灰常耗时的,当数据量很大的时候简直会让人崩溃,那么有个B-tree索引我们就像翻书目录一样,直接定位rowid立刻就找到了我们想要的数据,实质减少了I/O操作就提高速度,它有一个显著特点查询性能与表中数据量无关,例如查2万行的数据用了3consistentget,当查询1200万行的数据时才用了4consistentgets。
当我们的字段中使用了主键or唯一约束时,不用想直接可以用B-tree索引
缺点:
不适合键值重复率较高的字段上使用,例如第一章1-500page第二章501-1000page
实验:
altersystemflushshared_pool; 清空共享池
altersystemflushbuffer_cache; 清空数据库缓冲区,都是为了实验需要
创建leo_t1 leo_t2表
leo_t1表的object_id列的数据是没有重复值的,我们抽取了10行数据就可以看出来了。
LS@LEO>createtableleo_t1asselectobject_id,object_namefromdba_objects;
LS@LEO>selectcount(*)fromleo_t1;
COUNT(*)
----------
9872
LS@LEO> select*fromleo_t1whererownum<=10;
OBJECT_IDOBJECT_NAME
---------------------
20ICOL$
44I_USER1
28CON$
15UNDO$
29C_COBJ#
3I_OBJ#
25PROXY_ROLE_DATA$
39I_IND1
51I_CDEF2
26I_PROXY_ROLE_DATA$_1
leo_t2表的object_id列我们是做了取余操作,值就只有0,1两种,因此重复率较高,如此设置为了说明重复率对B树索引的影响
LS@LEO>createtableleo_t2asselectmod(object_id,2)object_ID,object_namefromdba_objects;
LS@LEO>selectcount(*)fromleo_t2;
COUNT(*)
----------
9873
LS@LEO>select*fromleo_t2whererownum<=10;
OBJECT_IDOBJECT_NAME
---------------------
0ICOL$
0I_USER1
0CON$
1UNDO$
1C_COBJ#
1I_OBJ#
1PROXY_ROLE_DATA$
1I_IND1
1I_CDEF2
0I_PROXY_ROLE_DATA$_1
LS@LEO>createindexleo_t1_indexonleo_t1(object_id); 创建B-tree索引,说明默认创建的都是B-tree索引
Indexcreated.
LS@LEO>createindexleo_t2_indexonleo_t2(object_ID); 创建B-tree索引
Indexcreated.
让我们看一下leo_t1与leo_t2的重复情况
LS@LEO>selectcount(distinct(object_id))fromleo_t1; 让我们看一下leo_t1与leo_t2的重复情况,leo_t1没有重复值,leo_t2有很多
COUNT(DISTINCT(OBJECT_ID))
--------------------------
9872
LS@LEO>selectcount(distinct(object_ID))fromleo_t2;
COUNT(DISTINCT(OBJECT_ID))
--------------------------
2
收集2个表统计信息
LS@LEO>executedbms_stats.gather_table_stats(ownname=>'LS',tabname=>'LEO_T1',method_opt=>'forallindexedcolumnssize2',cascade=>TRUE);
LS@LEO>executedbms_stats.gather_table_stats(ownname=>'LS',tabname=>'LEO_T2',method_opt=>'forallindexedcolumnssize2',cascade=>TRUE);
参数详解:
method_opt=>'forallindexedcolumnssize2' size_clause=integer整型,范围1~254,使用柱状图[histogramanalyze]分析列数据的分布情况
cascade=>TRUE 收集表的统计信息的同时收集B-tree索引的统计信息
显示执行计划和统计信息+设置autotrace简介
序号 命令 解释
1 SETAUTOTRACEOFF 此为默认值,即关闭Autotrace
2 SETAUTOTRACEONEXPLAIN 只显示执行计划
3 SETAUTOTRACEONSTATISTICS 只显示执行的统计信息
4 SETAUTOTRACEON 包含2,3两项内容
5 SETAUTOTRACETRACEONLY 与ON相似,但不显示语句的执行结果
结果键值少的情况
setautotracetraceexpstat; (SETAUTOTRACEOFF关闭执行计划和统计信息)
LS@LEO>select*fromleo_t1whereobject_id=1;
norowsselected
ExecutionPlan执行计划
----------------------------------------------------------
Planhashvalue:
3712193284
--------------------------------------------------------------------------------------------
|Id |Operation |Name |Rows |Bytes|Cost(%CPU)|Time |
--------------------------------------------------------------------------------------------
| 0|SELECTSTATEMENT | | 1| 21| 2 (0)|00:
00:
01|
| 1| TABLEACCESSBYINDEXROWID|LEO_T1 | 1| 21| 2 (0)|00:
00:
01|
|* 2| INDEXRANGESCAN索引扫描 |LEO_T1_INDEX| 1| | 1 (0)|00:
00:
01|
--------------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
2-access("OBJECT_ID"=1)
Statistics 统计信息
----------------------------------------------------------
0 recursivecalls
0 dbblockgets
2 consistentgets 我们知道leo_t1表的object_id没有重复值,因此使用B-tree索引扫描只有2次一致性读
0 physicalreads
0 redosize
339 bytessentviaSQL*Nettoclient
370 bytesreceivedviaSQL*Netfromclient
1 SQL*Netroundtripsto/fromclient
0 sorts(memory)
0 sorts(disk)
0 rowsprocessed
结果键值多的情况
LS@LEO>select*fromleo_t2whereobject_ID=1;(select/*+full(leo_t2)*/* fromleo_t2whereobject_ID=1;hint方式强制全表扫描)
4943rowsselected.
ExecutionPlan 执行计划
----------------------------------------------------------
Planhashvalue:
3657048469
----------------------------------------------------------------------------
|Id |Operation |Name |Rows |Bytes|Cost(%CPU)|Time |
----------------------------------------------------------------------------
| 0|SELECTSTATEMENT | | 4943|98860| 12 (0)|00:
00:
01|
|* 1| TABLEACCESSFULL|LEO_T2| 4943|98860| 12 (0)|00:
00:
01|sql结果是4943row,那么全表扫描也是4943row
----------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
1-filter("OBJECT_ID"=1)
Statistics 统计信息
----------------------------------------------------------
1 recursivecalls
0 dbblockgets
366 consistentgets 导致有366次一致性读
0 physicalreads
0 redosize
154465 bytessentviaSQL*Nettoclient
4000 bytesreceivedviaSQL*Netfromclient
331 SQL*Netroundtripsto/fromclient
0 sorts(memory)
0 sorts(disk)
4943 rowsprocessed
大家肯定会疑惑,为什么要用全表扫描而不用B-tree索引呢,这是因为oracle基于成本优化器CBO认为使用全表扫描要比使用B-tree索引性能更好更快,由于我们结果重复率很高,导致有366次一致性读,从cup使用率12%上看也说明了B-tree索引不适合键值重复率较高的列
我们在看一下强制使用B-tree索引时,效率是不是没有全表扫描高呢?
LS@LEO>select/*+index(leo_t2leo_t2_index)*/*fromleo_t2whereobject_ID=1;hint方式强制索引扫描
4943rowsselected.
ExecutionPlan 执行计划
----------------------------------------------------------
Planhashvalue:
321706586
--------------------------------------------------------------------------------------------
|Id |Operation |Name |Rows |Bytes|Cost(%CPU)|Time |
--------------------------------------------------------------------------------------------
| 0|SELECTSTATEMENT | | 4943|98860| 46 (0)|00:
00:
01|
| 1| TABLEACCESSBYINDEXROWID|LEO_T2 | 4943|98860| 46 (0)|00:
00:
01|
|* 2| INDEXRANGESCAN |LEO_T2_INDEX| 4943| | 10 (0)|00:
00:
01|
--------------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
2-access("OBJECT_ID"=1)
Statistics 统计信息
----------------------------------------------------------
1 recursivecalls
0 dbblockgets
704 consistentgets 使用B-tree索引704次一致性读>全表扫描366次一致性读,而且cpu使用率也非常高,显然效果没有全表扫描高
0 physicalreads
0 redosize
171858 bytessentviaSQL*Nettoclient
4000 bytesreceivedviaSQL*Netfromclient
331 SQL*Netroundtripsto/fromclient
0 sorts(memory)
0 sorts(disk)
4943 rowsprocessed
小结:
从以上的测试我们可以了解到,B-tree索引在什么情况下使用跟键值重复率高低有很大关系的,之间没有一个明确的分水岭,只能多测试分析执行计划后来决定。
位图索引 Bitmapindex
场合:
列的基数很少,可枚举,重复值很多,数据不会被经常更新
原理:
一个键值对应很多行(rowid),格式:
键值 start_rowid end_rowid 位图
优点:
OLAP例如报表类数据库重复率高的数据特定类型的查询例如count、or、and等逻辑操作因为只需要进行位运算即可得到我们需要的结果
缺点:
不适合重复率低的字段,还有经常DML操作(insert,update,delete),因为位图索引的锁代价极高,修改一个位图索引段影响整个位图段,例如修改
一个键值,会影响同键值的多行,所以对于OLTP系统位图索引基本上是不适用的
实验:
位图索引和B-tree索引的性能比较
setpagesize100; 设置页大小
利用dba_objects数据字典创建一个15万行的表
LS@LEO>createtableleo_bm_t1asselect*fromdba_objects;
Tablecreated.
LS@LEO>insertintoleo_bm_t1select*fromleo_bm_t1; 翻倍插入
9876rowscreated.
LS@LEO>/
19752rowscreated.
LS@LEO>/
39504rowscreated.
LS@LEO>/
79008rowscreated.
LS@LEO>/
158016rowscreated.
因object_type字段重复值较高,顾在此字段上创建bitmap索引
LS@LEO>createbitmapindexleo_bm_t1_indexonleo_bm_t1(object_type);
Indexcreated.
创建一个和leo_bm_t1表结构一模一样的表leo_bm_t2,并在object_type列上创建一个B-tree索引(15万行记录)
LS@LEO>createtableleo_bm_t2asselect*fromleo_bm_t1;
Tablecreated.
LS@LEO>createindexleo_bm_t2_bt_indexonleo_bm_t2(object_type);
Indexcreated.
对比位图索引和B-tree索引所占空间大小,很明显位图要远远小于B-tree索引所占用的空间,节约空间特性也是我们选择位图的理由之一
LS@LEO>selectsegment_name,bytesfromuser_segmentswheresegment_type='INDEX';
SEGMENT_NAME BYTES
-------------------------------------------------------------------------------------------
LEO_BM_T1_INDEX 327680(327K)
LEO_BM_T2_BT_INDEX 7340032(7M)
显示执行计划和统计信息
setautotracetraceexpstat;
在创建有位图索引的表上做count操作对比执行计划
LS@LEO>selectcount(*)fromleo_bm_t1whereobject_type='TABLE';
ExecutionPlan 执行计划
----------------------------------------------------------
Planhashvalue:
3251686305
-----------------------------------------------------------------------------------------------
|Id |Operation |Name |Rows |Bytes|Cost(%CPU)|Time |
---------------------------------------------------------------------------------------------
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle Btree位图全文索引三大索引性能比较及优缺点汇总模板 Btree 位图 全文 索引 性能 比较 优缺点 汇总 模板