上海騰科Oracle B-tree、位圖、全文索引三大索引性

1、上海騰科Oracle B-tree、位圖、全文索引三大索引性能比較（上）B-Tree索引場(chǎng)合：非常適合數(shù)據(jù)重復(fù)度低的字段 例如 身份證號(hào)碼 手機(jī)號(hào)碼 QQ號(hào)等字段，常用于主鍵 唯一約束，一般在在線交易的項(xiàng)目中用到的多些。原理：一個(gè)鍵值對(duì)應(yīng)一行（rowid） 格式： 【索引頭|鍵值|rowid】?jī)?yōu)點(diǎn)：當(dāng)沒有索引的時(shí)候，oracle只能全表掃描where qq=40354446 這個(gè)條件那么這樣是灰?；页：臅r(shí)的，當(dāng)數(shù)據(jù)量很大的時(shí)候簡(jiǎn)直會(huì)讓人崩潰，那么有個(gè)B-tree索引我們就像翻書目錄一樣，直接定位rowid立刻就找到了我們想要的數(shù)據(jù)，實(shí)質(zhì)減少了I/O操作就提高速度，它有一個(gè)顯著特點(diǎn)查詢性能與表中

2、數(shù)據(jù)量無關(guān)，例如 查2萬行的數(shù)據(jù)用了3 consistent get,當(dāng)查詢1200萬行的數(shù)據(jù)時(shí)才用了4 consistent gets。當(dāng)我們的字段中使用了主鍵or唯一約束時(shí)，不用想直接可以用B-tree索引缺點(diǎn)：不適合鍵值重復(fù)率較高的字段上使用，例如 第一章 1-500page 第二章 501-1000page實(shí)驗(yàn)：alter system flush shared_pool; 清空共享池alter system flush buffer_cache; 清空數(shù)據(jù)庫緩沖區(qū)，都是為了實(shí)驗(yàn)需要?jiǎng)?chuàng)建leo_t1 leo_t2 表leo_t1 表的object_id列的數(shù)據(jù)是沒有重復(fù)值的，我們抽取了

3、10行數(shù)據(jù)就可以看出來了。SQL create table leo_t1 as select object_id,object_name from dba_objects;SQL select count(* from leo_t1;COUNT(*-9872SQL select * from leo_t1 where rownum create table leo_t2 as select mod(object_id,2 object_ID ,object_name from dba_objects;SQL select count(* from leo_t2;COUNT(*-9873SQL

4、select * from leo_t2 where rownum create index leo_t1_index on leo_t1(object_id; 創(chuàng)建B-tree索引，說明 默認(rèn)創(chuàng)建的都是B-tree索引Index created.SQL create index leo_t2_index on leo_t2(object_ID; 創(chuàng)建B-tree索引Index created.讓我們看一下leo_t1與leo_t2的重復(fù)情況SQL select count(distinct(object_id from leo_t1; 讓我們看一下leo_t1與leo_t2的重復(fù)情況，leo

5、_t1沒有重復(fù)值，leo_t2有很多COUNT(DISTINCT(OBJECT_ID-9872SQL select count(distinct(object_ID from leo_t2;COUNT(DISTINCT(OBJECT_ID-2收集2個(gè)表統(tǒng)計(jì)信息SQL execute dbms_stats.gather_table_stats(ownname=LS,tabname=LEO_T1,method_opt=for all indexed columns size 2,cascade=TRUE;SQL execute dbms_stats.gather_table_stats(ownna

6、me=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)計(jì)信息的同時(shí)收集B-tree索引的統(tǒng)計(jì)信息顯示執(zhí)行計(jì)劃和統(tǒng)計(jì)信息+設(shè)置autotrace簡(jiǎn)介序號(hào) 命令 解釋1 SET AUTOTRACE OFF 此為默認(rèn)值，即關(guān)閉Autotrace

7、2 SET AUTOTRACE ON EXPLAIN 只顯示執(zhí)行計(jì)劃3 SET AUTOTRACE ON STATISTICS 只顯示執(zhí)行的統(tǒng)計(jì)信息4 SET AUTOTRACE ON 包含2,3兩項(xiàng)內(nèi)容5 SET AUTOTRACE TRACEONLY 與ON相似，但不顯示語句的執(zhí)行結(jié)果結(jié)果鍵值少的情況set autotrace trace exp stat; （SET AUTOTRACE OFF 關(guān)閉執(zhí)行計(jì)劃和統(tǒng)計(jì)信息）SQL select * from leo_t1 where object_id=1;no rows selectedExecution Plan 執(zhí)行計(jì)劃-Plan ha

8、sh 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 RANGE SCAN索引掃描 | LEO_T1_INDEX | 1 | | 1 (0| 00:00:01 |-Predicate Information (identifie

9、d by operation id:-2 - access(OBJECT_ID=1Statistics 統(tǒng)計(jì)信息-0 recursive calls0 db block gets2 consistent gets 我們知道leo_t1表的object_id沒有重復(fù)值，因此使用B-tree索引掃描只有2次一致性讀0 physical reads0 redo size339 bytes sent via SQL*Net to client370 bytes received via SQL*Net from client1 SQL*Net roundtrips to/from client0 so

10、rts (memory0 sorts (disk0 rows processed結(jié)果鍵值多的情況SQL select * from leo_t2 where object_ID=1; （select /*+full(leo_t2 */ * from leo_t2 where object_ID=1;hint方式強(qiáng)制全表掃描）4943 rows selected. Execution Plan 執(zhí)行計(jì)劃-Plan hash value: 3657048469-| Id | Operation | Name | Rows | Bytes | Cost (%CPU| Time |-| 0 | SEL

11、ECT 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:-1 - filter(OBJECT_ID=1Statistics 統(tǒng)計(jì)信息-1 recursive calls0 db block gets366 consistent gets 導(dǎo)致有366次一致性讀0

12、 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 processed大家肯定會(huì)疑惑，為什么要用全表掃描而不用B-tree索引呢，這是因?yàn)閛racle基于成本優(yōu)化器CBO認(rèn)為使用全表掃描要比使用B-tree索引性能更好更快，由于我們結(jié)果重復(fù)率很高，導(dǎo)致有366次一致性讀，從cup使用率

13、12%上看也說明了B-tree索引不適合鍵值重復(fù)率較高的列我們?cè)诳匆幌聫?qiáng)制使用B-tree索引時(shí)，效率是不是沒有全表掃描高呢？SQL select /*+index(leo_t2 leo_t2_index */ * from leo_t2 where object_ID=1; hint方式強(qiáng)制索引掃描4943 rows selected.Execution Plan 執(zhí)行計(jì)劃-Plan hash value: 321706586-| Id | Operation | Name | Rows | Bytes | Cost (%CPU| Time |-| 0 | SELECT STATEMENT

14、| | 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 |-Predicate Information (identified by operation id:-2 - access(OBJECT_ID=1Statistics 統(tǒng)計(jì)信息-1 recursive calls0 db block gets7

15、04 consistent gets 使用B-tree索引704次一致性讀 全表掃描366次一致性讀，而且cpu使用率也非常高，顯然效果沒有全表掃描高0 physical reads0 redo size171858 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 processed小結(jié)：從以上的測(cè)試我們可以了解到，B-tree索引在什么情況下使

16、用跟鍵值重復(fù)率高低有很大關(guān)系的，之間沒有一個(gè)明確的分水嶺，只能多測(cè)試分析執(zhí)行計(jì)劃后來決定。位圖索引 Bitmap index場(chǎng)合：列的基數(shù)很少，可枚舉，重復(fù)值很多，數(shù)據(jù)不會(huì)被經(jīng)常更新原理：一個(gè)鍵值對(duì)應(yīng)很多行（rowid）， 格式：鍵值 start_rowid end_rowid 位圖優(yōu)點(diǎn)：OLAP 例如報(bào)表類數(shù)據(jù)庫 重復(fù)率高的數(shù)據(jù) 特定類型的查詢例如count、or、and等邏輯操作因?yàn)橹恍枰M(jìn)行位運(yùn)算即可得到我們需要的結(jié)果缺點(diǎn)：不適合重復(fù)率低的字段，還有經(jīng)常DML操作（insert，update，delete），因?yàn)槲粓D索引的鎖代價(jià)極高，修改一個(gè)位圖索引段影響整個(gè)位圖段，例如修改一個(gè)鍵值，會(huì)

17、影響同鍵值的多行，所以對(duì)于OLTP 系統(tǒng)位圖索引基本上是不適用的實(shí)驗(yàn)：位圖索引和B-tree索引的性能比較set pagesize 100; 設(shè)置頁大小利用dba_objects數(shù)據(jù)字典創(chuàng)建一個(gè)15萬行的表SQL create table leo_bm_t1 as select * from dba_objects;Table created.SQL insert into leo_bm_t1 select * from leo_bm_t1; 翻倍插入9876 rows created.SQL /19752 rows created.SQL /39504 rows created.SQL /7

18、9008 rows created.SQL /158016 rows created.因object_type字段重復(fù)值較高，顧在此字段上創(chuàng)建bitmap索引SQL create bitmap index leo_bm_t1_index on leo_bm_t1(object_type;Index created.創(chuàng)建一個(gè)和leo_bm_t1表結(jié)構(gòu)一模一樣的表leo_bm_t2，并在object_type列上創(chuàng)建一個(gè)B-tree索引（15萬行記錄）SQL create table leo_bm_t2 as select * from leo_bm_t1;Table created.SQL cr

19、eate index leo_bm_t2_bt_index on leo_bm_t2(object_type;Index created.對(duì)比位圖索引和B-tree索引所占空間大小,很明顯位圖要遠(yuǎn)遠(yuǎn)小于B-tree索引所占用的空間，節(jié)約空間特性也是我們選擇位圖的理由之一SQL select segment_name,bytes from user_segments where segment_type=INDEX;SEGMENT_NAME BYTES- -LEO_BM_T1_INDEX 327680(327KLEO_BM_T2_BT_INDEX 7340032(7M顯示執(zhí)行計(jì)劃和統(tǒng)計(jì)信息set

20、 autotrace trace exp stat;在創(chuàng)建有位圖索引的表上做count操作對(duì)比執(zhí)行計(jì)劃SQL select count(* from leo_bm_t1 where object_type=TABLE;Execution Plan 執(zhí)行計(jì)劃-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

21、| | | 2 | BITMAP CONVERSION COUNT | | 36315 | 390K| 4 (0| 00:00:01 |* 3 | BITMAP INDEX SINGLE VALUE| LEO_BM_T1_INDEX | | | | |-位圖索引上只掃描了一個(gè)值Predicate Information (identified by operation id:-3 - access(OBJECT_TYPE=TABLENote- dynamic sampling used for this statement 動(dòng)態(tài)采樣Statistics 統(tǒng)計(jì)信息-9 recursive cal

22、ls0 db block gets93 consistent gets oracle選擇使用位圖索引訪問數(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操作對(duì)比執(zhí)行計(jì)劃SQL select count(* from

23、 leo_bm_t2 where object_type=TABLE;Execution Plan 執(zhí)行計(jì)劃-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:

24、00:01 |-B-tree索引上全部掃描，cpu使用率達(dá)到了59%，比位圖索引cpu使用率4%高出許多Predicate Information (identified by operation id:-2 - access(OBJECT_TYPE=TABLENote- dynamic sampling used for this statement 動(dòng)態(tài)采樣Statistics 統(tǒng)計(jì)信息-32 recursive calls0 db block gets161 consistent gets B-tree索引表上發(fā)生了161次一致性讀要遠(yuǎn)遠(yuǎn)高于位圖索引表上93次一致性讀，因此還是位圖索引效

25、率高74 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我們?cè)倏纯吹戎挡檎襴here object_type=TABLE情況下位圖索引和B-tree索引的性能對(duì)比SQL select * from leo_bm_t1 where object_type=TABLE ;28512 r

26、ows selected.Execution Plan 執(zhí)行計(jì)劃-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 ROWIDS| 位圖映像-

27、rowids | | | | |* 3 | BITMAP INDEX SINGLE VALUE | LEO_BM_T1_INDEX | | | | |-Predicate Information (identified by operation id:-3 - access(OBJECT_TYPE=TABLENote- dynamic sampling used for this statement動(dòng)態(tài)采樣Statistics 統(tǒng)計(jì)信息-7 recursive calls0 db block gets4407 consistent 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í)行計(jì)劃SQL