《Oracle 複合壓縮索引場景及效能對比》-原理引航-例項演示-可下載

《Oracle 複合壓縮索引場景及效能對比》

摘要：今天為什麼提到這個話題，出於一個偶然，一個同事在最佳化新開發的系統時向我請教如何新增複合壓縮索引的問題。我總結了一下，問題有三。

第一：需不需要壓縮

第二：對第幾列壓縮

第三：效能對比，選出最優

好，問題提出我們一一來解答一下

1.場景

在我的文章中經常會看到這個詞，當然也可以不談場景說技術，但我覺的那只是隔空建樓，毫無目的，沒有根據性。我們在深入研究的時候要避免這樣的盲目討論，效果也不會好太多。注重技術的應用場景，才是研究技術、應用技術之根本，不管多麼高科技的東東，或者說多牛的技術，最終還是要落到實處。那麼本節講的“複合壓縮索引”的使用場景常用在，where子句中同時查詢多列的情況下可以建立。如果要是有的列上資料重複度較高可以考慮進行壓縮，重複度越高壓縮效果越好，索引壓縮以後一個索引塊可以存放更多的鍵值。今天我們遇到的情況就和上述差不多。簡介如下：

BB_BORROWER  表名，這是我們需要檢索的表名

BB_BORROWER.VALIDITYFLAG  VARCHAR2(5) 列名，資料有效狀態，此列只有三個值 “1”“2”“3”

特點：列的基數不多且重複值極高

BB_BORROWER.FINANCECODE  VARCHAR2(14) 列名，金融機構程式碼，此列相對來講基數略大，大到多少呢->1000家，這是徵信管理局今年審批過的機構數，可能有的朋友會問，此列要不要壓縮啊，實踐是檢驗真理的唯一標準，經過測試後才能得到答案。

2.複合壓縮索引語法

create index 索引名 on 表名（列名） compress  1;

compress後面跟的數字表示字首的深度，也就是需要用來壓縮的columns數

例

create index idx_leo on t(a,b,c) compress1;   只壓縮第一列

create index idx_leo on t(a,b,c) compress2;   對前兩列進行壓縮

create index idx_leo on t(a,b,c) compress3;   對前三列進行壓縮

3.檢視動態效能檢視指標

官方文件 books：Reference->INDEX_STATS

selectbtree_space,used_space,height,br_blks,lf_blks,opt_cmpr_count from index_stats;

btree_space：B-tree索引當前分配的空間

used_space：分配空間中已經被使用的部分

height：B-tree索引的高度（層數）

br_blks：B-tree索引的分支塊數，分支塊不能夠被壓縮

lf_blks：B-tree索引的葉子塊數，葉子塊能夠被壓縮

opt_cmpr_count：列中關鍵字最小的壓縮長度

4.實驗

（1）非壓縮複合索引

drop index idx_bb_borrower1;

create index idx_bb_borrower1on bb_borrower(validityflag,financecode);

SQL> analyze index idx_bb_borrower1validate structure;             分析索引結構

Index analyzed

SQL> selectbtree_space,used_space,height,br_blks,lf_blks,opt_cmpr_count from index_stats;

BTREE_SPACE    USED_SPACE   HEIGHT   BR_BLKS    LF_BLKS     OPT_CMPR_COUNT

----------- -------------------- ---------- ---------- ------------------ -------------------- ---------- ---------- --------

             7996     868                 1             0                1                 2

（2）只壓縮第一列的複合壓縮索引

SQL> drop index idx_bb_borrower1;

Index dropped

SQL> create index idx_bb_borrower1 onbb_borrower(validityflag,financecode) compress 1;

Index created

SQL> analyze index idx_bb_borrower1validate structure;          分析索引結構

Index analyzed

SQL> select btree_space,used_space,height,br_blks,lf_blks,opt_cmpr_countfrom index_stats;

BTREE_SPACE     USED_SPACE    HEIGHT    BR_BLKS    LF_BLKS         OPT_CMPR_COUNT

----------- ---------- -------------------- ---------- ---------------------- ---------- ---------- ------------------------

            7992       830                  1               0               1                     2

（3）壓縮前兩列複合壓縮索引

drop index idx_bb_borrower1;

create index idx_bb_borrower1 onbb_borrower(validityflag,financecode) compress 2;

SQL> drop index idx_bb_borrower1;

Index dropped

SQL> create index idx_bb_borrower1 onbb_borrower(validityflag,financecode) compress 2;

Index created

SQL> analyze indexidx_bb_borrower1 validate structure;          分析索引結構

Index analyzed

SQL> selectbtree_space,used_space,height,br_blks,lf_blks,opt_cmpr_count from index_stats;

BTREE_SPACE     USED_SPACE   HEIGHT    BR_BLKS   LF_BLKS      OPT_CMPR_COUNT

----------- ---------- -------------------- ---------- ---------------------- ---------- ---------- ------------------------

            7992       571                 1               0               1                  2

最後分析表並級聯索引

SQL> executedbms_stats.gather_table_stats('sfcp','bb_borrower',cascade=>true);

PL/SQL procedure successfully completed

附：如果索引之前已經建立完畢，rebuildindex可重建壓縮索引&恢復為普通索引之方法

alterindex idx_bb_borrower1 rebuild compress;

alterindex idx_bb_borrower1 rebuild nocompress;

5.結論

我們現在對上述三個實驗結果進行對比可發現“對前兩列進行壓縮”效果最好，如果表中記錄越多，那麼測試的效果越顯著。

複合壓縮索引優勢

（1）對鍵值重複度越高的列壓縮效果越好，可以節省更多的儲存空間

（2）索引壓縮後一個索引塊可以存放更多的索引鍵值

（3）掃描同樣的記錄數物理IO更少

（4）提高緩衝區命中率

複合壓縮索引不足

（1）索引更新時需要重新解壓->更新->加壓，使用更多的CPU資源，維護成本高

（2）索引查詢時需要更多的CPU計算時長，解壓後才能比較查詢

（3）熱塊競爭，當查詢的結果集都集中在一個塊中時會引起熱塊

（4）爭用更多的CPU資源

小結：索引壓縮技術是否可以幫助提升總體效能，這就需要根據實際硬體資源來具體分析了。在IO資源與CPU資源之間做好取捨，經過實際測試後得出結論。由此看出，一種技術的使用必然離不開它所應用的場景，使用的效果也要根據當時的場景具體問題具體分析。

注：bitmap索引不能壓縮

   請點選下載

複合  壓縮  索引  場景  效能

Leonarding劉盛

2014.04.01

北京&spring

分享技術~成就夢想

Blog：