Oracle B-tree、位圖、全文索引三大索引性能比較及優(yōu)缺點匯總

1、引言:大家都知道“效率”是數(shù)據(jù)庫中非常重要的一個指標(biāo),如何提高效率大家可能都會想起索引,但索引又這么多種,什么場合應(yīng)該使用什么索引呢?哪種索引可以提高我們的效率,哪種索引可以讓我們的效率大大降低(有時還不如全表掃描性能好下面要講的“索引”如何成為我們的利器而不是災(zāi)難!多說一點,由于不同索引的存儲結(jié)構(gòu)不同,所以應(yīng)用在不同組織結(jié)構(gòu)的數(shù)據(jù)上,本篇文章重點就是:理解不同的技術(shù)都適合在什么地方應(yīng)用!B-Tree索引場合:非常適合數(shù)據(jù)重復(fù)度低的字段例如身份證號碼手機號碼 QQ號等字段,常用于主鍵唯一約束,一般在在線交易的項目中用到的多些。原理:一個鍵值對應(yīng)一行(rowid格式:【索引頭|鍵值|rowid】

2、優(yōu)點:當(dāng)沒有索引的時候,oracle只能全表掃描where qq=40354446 這個條件那么這樣是灰?；页：臅r的,當(dāng)數(shù)據(jù)量很大的時候簡直會讓人崩潰,那么有個B-tree索引我們就像翻書目錄一樣,直接定位rowid 立刻就找到了我們想要的數(shù)據(jù),實質(zhì)減少了I/O操作就提高速度,它有一個顯著特點查詢性能與表中數(shù)據(jù)量無關(guān),例如查2萬行的數(shù)據(jù)用了3 consistent get,當(dāng)查詢1200萬行的數(shù)據(jù)時才用了4 consistent gets。當(dāng)我們的字段中使用了主鍵or唯一約束時,不用想直接可以用B-tree索引缺點:不適合鍵值重復(fù)率較高的字段上使用,例如第一章1-500page 第二章501-

3、1000page實驗:alter system flush shared_pool; 清空共享池alter system flush buffer_cache; 清空數(shù)據(jù)庫緩沖區(qū),都是為了實驗需要創(chuàng)建leo_t1 leo_t2 表leo_t1 表的object_id列的數(shù)據(jù)是沒有重復(fù)值的,我們抽取了10行數(shù)據(jù)就可以看出來了。LSLEO> create table leo_t1 as select object_id,object_name from dba_objects;LSLEO> select count(* from leo_t1;COUNT(*-9872LSLEO>

4、 select * from leo_t1 where rownum <= 10;OBJECT_ID OBJECT_NAME- -20 ICOL$44 I_USER128 CON$15 UNDO$29 C_COBJ#3 I_OBJ#25 PROXY_ROLE_DATA$39 I_IND151 I_CDEF226 I_PROXY_ROLE_DATA$_1leo_t2 表的object_id列我們是做了取余操作,值就只有0,1兩種,因此重復(fù)率較高,如此設(shè)置為了說明重復(fù)率對B樹索引的影響LSLEO> create table leo_t2 as select mod(object_id,

5、2 object_ID ,object_name from dba_objects;LSLEO> select count(* from leo_t2;COUNT(*-9873LSLEO> select * from leo_t2 where rownum <= 10;OBJECT_ID OBJECT_NAME- -0 ICOL$0 I_USER10 CON$1 UNDO$1 C_COBJ#1 I_OBJ#1 PROXY_ROLE_DATA$1 I_IND11 I_CDEF20 I_PROXY_ROLE_DATA$_1LSLEO> create index leo_t1

6、_index on leo_t1(object_id; 創(chuàng)建B-tree索引,說明默認(rèn)創(chuàng)建的都是B-tree索引Index created.LSLEO> create index leo_t2_index on leo_t2(object_ID; 創(chuàng)建B-tree索引Index created.讓我們看一下leo_t1與leo_t2的重復(fù)情況LSLEO> select count(distinct(object_id from leo_t1; 讓我們看一下leo_t1與leo_t2的重復(fù)情況,leo_t1沒有重復(fù)值,leo_t2有很多COUNT(DISTINCT(OBJECT_ID

7、-9872LSLEO> select count(distinct(object_ID from leo_t2;COUNT(DISTINCT(OBJECT_ID-2收集2個表統(tǒng)計信息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.ga

8、ther_table_stats(ownname=>'LS',tabname=>'LEO_T2',method_opt=>'for all indexed columns size 2',cascade=>TRUE;參數(shù)詳解:method_opt=>'for all indexed columns size 2' size_clause=integer 整型,范圍1254 ,使用柱狀圖 histogram analyze 分析列數(shù)據(jù)的分布情況cascade=>TRUE 收集表的統(tǒng)計信息的同時收集

9、B-tree索引的統(tǒng)計信息顯示執(zhí)行計劃和統(tǒng)計信息+設(shè)置autotrace簡介序號命令解釋1 SET AUTOTRACE OFF 此為默認(rèn)值,即關(guān)閉Autotrace2 SET AUTOTRACE ON EXPLAIN 只顯示執(zhí)行計劃3 SET AUTOTRACE ON STATISTICS 只顯示執(zhí)行的統(tǒng)計信息4 SET AUTOTRACE ON 包含2,3兩項內(nèi)容5 SET AUTOTRACE TRACEONLY 與ON相似,但不顯示語句的執(zhí)行結(jié)果結(jié)果鍵值少的情況set autotrace trace exp stat; (SET AUTOTRACE OFF 關(guān)閉執(zhí)行計劃和統(tǒng)計信息LSLEO

10、> select * from leo_t1 where object_id=1;no rows selectedExecution Plan 執(zhí)行計劃-Plan hash value: 3712193284-| Id | Operation | Name | Rows | Bytes | Cost (%CPU| Time |-| 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

11、RANGE SCAN索引掃描 | LEO_T1_INDEX | 1 | | 1 (0| 00:00:01 |- Predicate Information (identified by operation id:-2 - access("OBJECT_ID"=1Statistics 統(tǒng)計信息-0 recursive calls0 db block gets2 consistent gets 我們知道leo_t1表的object_id沒有重復(fù)值,因此使用B-tree索引掃描只有2次一致性讀0 physical reads0 redo size339 bytes sent vi

12、a SQL*Net to client370 bytes received via SQL*Net from client1 SQL*Net roundtrips to/from client0 sorts (memory0 sorts (disk0 rows processed結(jié)果鍵值多的情況LSLEO> select * from leo_t2 where object_ID=1; (select /*+full(leo_t2 */ * from leo_t2 where object_ID=1;hint方式強制全表掃描4943 rows selected.Execution Pla

13、n 執(zhí)行計劃-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| LEO_T2 | 4943 | 98860 | 12 (0| 00:00:01 | sql結(jié)果是4943row,那么全表掃描也是4943row-Predicate Information (identified by operation id

14、:-1 - filter("OBJECT_ID"=1Statistics 統(tǒng)計信息-1 recursive calls0 db block gets366 consistent gets 導(dǎo)致有366次一致性讀0 physical reads0 redo size154465 bytes sent via SQL*Net to client4000 bytes received via SQL*Net from client331 SQL*Net roundtrips to/from client0 sorts (memory0 sorts (disk4943 rows p

15、rocessed大家肯定會疑惑,為什么要用全表掃描而不用B-tree索引呢,這是因為oracle基于成本優(yōu)化器CBO認(rèn)為使用全表掃描要比使用B-tree索引性能更好更快,由于我們結(jié)果重復(fù)率很高,導(dǎo)致有366次一致性讀,從cup使用率12%上看也說明了B-tree索引不適合鍵值重復(fù)率較高的列我們在看一下強制使用B-tree索引時,效率是不是沒有全表掃描高呢?LSLEO> select /*+index(leo_t2 leo_t2_index */ * from leo_t2 where object_ID=1; hint 方式強制索引掃描4943 rows selected.Executi

16、on Plan 執(zhí)行計劃-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| LEO_T2 | 4943 | 98860 | 46 (0| 00:00:01 |* 2 | INDEX RANGE SCAN | LEO_T2_INDEX | 4943 | | 10 (0| 00:00:01

17、 |- Predicate Information (identified by operation id:-2 - access("OBJECT_ID"=1Statistics 統(tǒng)計信息-1 recursive calls0 db block gets704 consistent gets 使用B-tree索引704次一致性讀> 全表掃描366次一致性讀,而且cpu 使用率也非常高,顯然效果沒有全表掃描高0 physical reads0 redo size171858 bytes sent via SQL*Net to client4000 bytes recei

18、ved via SQL*Net from client331 SQL*Net roundtrips to/from client0 sorts (memory0 sorts (disk4943 rows processed小結(jié):從以上的測試我們可以了解到,B-tree索引在什么情況下使用跟鍵值重復(fù)率高低有很大關(guān)系的,之間沒有一個明確的分水嶺,只能多測試分析執(zhí)行計劃后來決定。位圖索引 Bitmap index場合:列的基數(shù)很少,可枚舉,重復(fù)值很多,數(shù)據(jù)不會被經(jīng)常更新原理:一個鍵值對應(yīng)很多行(rowid,格式:鍵值 start_rowid end_rowid 位圖優(yōu)點:OLAP 例如報表類數(shù)據(jù)庫重

19、復(fù)率高的數(shù)據(jù)特定類型的查詢例如count、or、and等邏輯操作因為只需要進行位運算即可得到我們需要的結(jié)果缺點:不適合重復(fù)率低的字段,還有經(jīng)常DML操作(insert,update,delete,因為位圖索引的鎖代價極高,修改一個位圖索引段影響整個位圖段,例如修改一個鍵值,會影響同鍵值的多行,所以對于OLTP 系統(tǒng)位圖索引基本上是不適用的實驗:位圖索引和B-tree索引的性能比較set pagesize 100; 設(shè)置頁大小利用dba_objects數(shù)據(jù)字典創(chuàng)建一個15萬行的表LSLEO> create table leo_bm_t1 as select * from dba_objec

20、ts;T able 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字段重復(fù)值較高,顧在此字段上創(chuàng)建bitmap索引LSLEO> create bitmap index leo_bm_t1_index

21、on leo_bm_t1(object_type;Index created.創(chuàng)建一個和leo_bm_t1表結(jié)構(gòu)一模一樣的表leo_bm_t2,并在object_type列上創(chuàng)建一個B-tree索引(15萬行記錄LSLEO> create table leo_bm_t2 as select * from leo_bm_t1;T able created.LSLEO> create index leo_bm_t2_bt_index on leo_bm_t2(object_type;Index created.對比位圖索引和B-tree索引所占空間大小,很明顯位圖要遠遠小于B-tree

22、索引所占用的空間,節(jié)約空間特性也是我們選擇位圖的理由之一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 顯示執(zhí)行計劃和統(tǒng)計信息set autotrace trace exp stat;在創(chuàng)建有位圖索引的表上做count操作對比執(zhí)行計劃LSLEO> select count(* from leo_bm_t

23、1 where object_type='TABLE'Execution Plan 執(zhí)行計劃-Plan hash value: 3251686305-| Id | Operation | Name | Rows | Bytes | Cost (%CPU| Time | -| 0 | SELECT STATEMENT | | 1 | 11 | 4 (0| 00:00:01 | 1 | SORT AGGREGATE | | 1 | 11 | | | 2 | BITMAP CONVERSION COUNT | | 36315 | 390K| 4 (0| 00:00:01 |* 3 |

24、 BITMAP INDEX SINGLE VALUE| LEO_BM_T1_INDEX | | | | |-位圖索引上只掃描了一個值Predicate Information (identified by operation id:-3 - access("OBJECT_TYPE"='TABLE'Note- dynamic sampling used for this statement 動態(tài)采樣Statistics 統(tǒng)計信息-9 recursive calls0 db block gets93 consistent gets oracle選擇使用位圖索引訪

25、問數(shù)據(jù),導(dǎo)致93次一致性讀7 physical reads0 redo size413 bytes sent via SQL*Net to client381 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory0 sorts (disk1 rows processed在創(chuàng)建有B-tree索引的表上做count操作對比執(zhí)行計劃LSLEO> select count(* from leo_bm_t2 where object_type='TABLE'E

26、xecution Plan 執(zhí)行計劃-Plan hash value: 613433245-| Id | Operation | Name | Rows | Bytes | Cost (%CPU| Time |-| 0 | SELECT STATEMENT | | 1 | 11 | 59 (0| 00:00:01 | 1 | SORT AGGREGATE | | 1 | 11 | | |* 2 | INDEX RANGE SCAN| LEO_BM_T2_BT_INDEX | 25040 | 268K| 59 (0| 00:00:01 |-B-tree索引上全部掃描,cpu使用率達到了59%,比

27、位圖索引cpu使用率4%高出許多Predicate Information (identified by operation id:-2 - access("OBJECT_TYPE"='TABLE'Note- dynamic sampling used for this statement 動態(tài)采樣Statistics 統(tǒng)計信息-32 recursive calls0 db block gets161 consistent gets B-tree索引表上發(fā)生了161次一致性讀要遠遠高于位圖索引表上93次一致性讀,因此還是位圖索引效率高74 physical

28、reads0 redo size413 bytes sent via SQL*Net to client381 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory0 sorts (disk1 rows processed我們再看看等值查找where object_type='TABLE'情況下位圖索引和B-tree索引的性能對比LSLEO> select * from leo_bm_t1 where object_type='TABLE&#

29、39; ;28512 rows selected.Execution Plan 執(zhí)行計劃-Plan hash value: 4228542614-| Id | Operation | Name | Rows | Bytes | Cost (%CPU| Time | -| 0 | SELECT STATEMENT | | 36315 | 6277K| 562 (0| 00:00:07 | 1 | TABLE ACCESS BY INDEX ROWID | LEO_BM_T1 | 36315 | 6277K| 562 (0| 00:00:07 | 2 | BITMAP CONVERSION TO

30、ROWIDS| 位圖映像->rowids | | | | |* 3 | BITMAP INDEX SINGLE VALUE | LEO_BM_T1_INDEX | | | | |- Predicate Information (identified by operation id:-3 - access("OBJECT_TYPE"='TABLE'Note- dynamic sampling used for this statement動態(tài)采樣Statistics 統(tǒng)計信息-7 recursive calls0 db block gets4407 co

31、nsistent gets 使用位圖索引發(fā)生了4407次一致性讀0 physical reads0 redo size2776927 bytes sent via SQL*Net to client21281 bytes received via SQL*Net from client1902 SQL*Net roundtrips to/from client0 sorts (memory0 sorts (disk28512 rows processedleo_bm_t2表上使用B-tree索引得到執(zhí)行計劃LSLEO> select /*+index(leo_bm_t2 leo_bm_t

32、2_bt_index */ * from leo_bm_t2 where object_type='TABLE' ;28512 rows selected. 我們強制使用B-tree索引掃描等值條件Execution Plan 執(zhí)行計劃-Plan hash value: 1334503202-| Id | Operation | Name | Rows | Bytes | Cost (%CPU| Time | -| 0 | SELECT STATEMENT | | 25040 | 4328K| 2063 (1| 00:00:25 | 1 | TABLE ACCESS BY IN

33、DEX ROWID| LEO_BM_T2 | 25040 | 4328K| 2063 (1| 00:00:25 |* 2 | INDEX RANGE SCAN | LEO_BM_T2_BT_INDEX | 25040 | | 59 (0| 00:00:01 |-B-tree索引上全部掃描,cpu使用率達到了2063%,比位圖索引cpu使用率562%高出許多Predicate Information (identified by operation id:-2 - access("OBJECT_TYPE"='TABLE'Note- dynamic sampli

34、ng used for this statementStatistics-7 recursive calls0 db block gets6621 consistent gets0 physical reads0 redo size2776927 bytes sent via SQL*Net to client21281 bytes received via SQL*Net from client1902 SQL*Net roundtrips to/from client0 sorts (memory0 sorts (disk28512 rows processed小結(jié):在等值查找中我們可以看

35、出位圖索引的效率依言高于B-tree索引全文索引Text index定義:全文索引就是通過將文字按照某種語言進行詞匯拆分,重新將數(shù)據(jù)組合存儲,來達到快速檢索的目的場合:當(dāng)字段里存儲的都是文本時適合用全文索引,常用于搜索文字優(yōu)點:全文索引不是按照鍵值存儲的,而是按照分詞重組數(shù)據(jù),常用于模糊查詢Where name like '%leonarding%'效率比全表掃描高很多,適用OLAP系統(tǒng),OLTP系統(tǒng)里面用到的并不多。缺點:全文索引會占用大量空間有時比原表本身占的空間還多,bug較多,維護困難。實驗:全文索引性能優(yōu)勢創(chuàng)建一個表包含2個字段LSLEO> create tab

36、le leo_text_t1 (id int,name varchar(10;T able created.在name字段上創(chuàng)建B-tree索引,但檢索的時候并沒有用,還是全表掃描LSLEO> create index leo_text_t1_bt_index on leo_text_t1(name;Index created.插入4條記錄insert into leo_text_t1 values(1,'Tom'insert into leo_text_t1 values(2,'Tom Tom'insert into leo_text_t1 values

37、(1,'Tom'insert into leo_text_t1 values(2,'Tom Tom'commit;LSLEO> select * from leo_text_t1;ID NAME- -1 Tom2 Tom Tom1 Tom2 Tom Tom我們在創(chuàng)建一個表,并在name字段上創(chuàng)建全文索引create table leo_text_t2 as select * from leo_text_t1;創(chuàng)建全文索引的前提ORACLE10g 創(chuàng)建全文索引過程:1,首先查看ORACLE是否已安裝“全文檢索工具”通過查看是否存在CTXSYS 用戶,CTXA

38、PP角色即可判斷。LSLEO> select username from dba_users;USERNAME-LSCTXSYS 默認(rèn)是沒有的,需要安裝2個腳本catctx.sql,drdefus.sql 2,如果ORACLE沒有安裝“全文檢索工具”,則使用以下步驟手工安裝。a進入ORACLE安裝目錄cd $ORACLE_HOMEb使用DBA 角色登陸數(shù)據(jù)庫sqlplus sys/sys as sysdbac查看表空間文件存放路徑select name from v$datafile;d為CTXSYS 用戶創(chuàng)建表空間CREATE TABLESPACE ctxsysLOGGINGDATAF

39、ILE '/u01/app/oracle/oradata/LEO/file1/ctxsys01.dbf' SIZE 32mAUTOEXTEND ONNEXT 32m MAXSIZE 2048mEXTENT MANAGEMENT LOCAL ;e創(chuàng)建CTXSYS 用戶,創(chuàng)建CTXAPP 角色?/ctx/admin/catctx.sql ctxsys ctxsys temp1 nolock-(密碼、表空間、臨時表空間、用戶狀態(tài)-如果當(dāng)前sql腳本無執(zhí)行權(quán)限,請手工添加。f為CTXSYS 執(zhí)行初始化工作,如果沒有此操作,后續(xù)操作會失敗。connect ctxsys/ctxsys;?/

40、ctx/admin/defaults/drdefus.sql3,創(chuàng)建全文索引a創(chuàng)建詞法分析器及相關(guān)表-詞法分析器execute ctx_ddl.create_preference('offerProdAddrLexer','CHINESE_LEXER'-詞法execute ctx_ddl.create_preference('offerProdAddrList', 'BASIC_WORDLIST'execute ctx_ddl.set_attribute('offerProdAddrList','PREFI

41、X_INDEX','TRUE'execute ctx_ddl.set_attribute('offerProdAddrList','PREFIX_MIN_LENGTH',1;execute ctx_ddl.set_attribute('offerProdAddrList','PREFIX_MAX_LENGTH', 5;execute ctx_ddl.set_attribute('offerProdAddrList','SUBSTRING_INDEX', 'YES

42、9;b創(chuàng)建全文索引LSLEO> conn ctxsys/ctxsysConnected.CTXSYSLEO> create index ls.leo_text_t2_text_index on ls.leo_text_t2(name indextype is ctxsys.context;CTXSYSLEO> conn ls/lsConnected.LSLEO> set autotrace on;LSLEO> select * from leo_text_t1 where name like '%Tom%'ID NAME- -1 Tom2 Tom

43、Tom1 Tom2 Tom TomExecution Plan 執(zhí)行計劃-Plan hash value: 3687902158-| Id | Operation | Name | Rows | Bytes | Cost (%CPU| Time |-| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0| 00:00:01 |* 1 | TABLE ACCESS FULL| LEO_TEXT_T1 | 4 | 80 | 3 (0| 00:00:01 |-全表掃描,cpu使用率3%Predicate Information (identified by operation id:-1 - filter("NAME" LIKE '%Tom%'Note- dynamic