Oracle數(shù)據(jù)庫的位圖索引原理與應(yīng)用

DOI：10.19850/j.cnki.2096-4706.2021.08.045

摘? 要：指出了執(zhí)行查詢優(yōu)化時(shí)，效果最顯著的方法是使用B樹索引，給出了使用B樹索引的限制條件，分析了位圖索引不同于B樹索引的主要特點(diǎn)，以實(shí)例驗(yàn)證了位圖索引存儲(chǔ)的內(nèi)容，得出了位圖索引的結(jié)構(gòu)。設(shè)計(jì)簡(jiǎn)潔的實(shí)驗(yàn)步驟，驗(yàn)證了使用位圖索引對(duì)查詢速度的提高幅度，以實(shí)例給出了位圖索引所占空間的大小，最后給出了不適合使用位圖索引的情況。

關(guān)鍵詞：Oracle數(shù)據(jù)庫;位圖索引;查詢優(yōu)化

中圖分類號(hào)：TP311.13? ? ? 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：2096-4706（2021）08-0159-03

Principle and Application of Bitmap Index in Oracle Database

LI Aiwu

（Guangdong Vocational College of Post and Telecom，Guangzhou? 510630，China）

Abstract：It is pointed out that the most effective way to perform query optimization is to use B-tree index，and the restrictions of using B-tree index are given. The main features of bitmap index that are different from B-tree index are analyzed，and the storage content of bitmap indexes is verified by examples，and obtains the structure of bitmap index. A concise experimental procedure is designed to verify the magnitude of improvement in query speed by using bitmap index. The size of space occupied by bitmap index is given with examples，and finally，the cases in which the use of bitmap indexes is not suitable are given.

Keywords：Oracle database;bitmap index;query optimization

0? 引? 言

使用B樹索引要求索引列值的重復(fù)率要很低，但在實(shí)際應(yīng)用中，列值重復(fù)率高的情況是很常見的，如性別字段只有“男”“女”兩個(gè)列值，日期的月份部分只有12個(gè)整數(shù)值等，季度部分只有4個(gè)值等。在查詢中，這些字段一般用作計(jì)數(shù)條件，以下為一些典型的查詢：

（1）查詢員工表中的男性員工數(shù)量。

（2）2005年入職的員工總數(shù)。

（3）5號(hào)部門的員工總數(shù)。

對(duì)于以上這些查詢，在相關(guān)字段上創(chuàng)建B樹索引，查詢效率不會(huì)得到顯著提高，位圖索引恰恰適合這類要求，在處理相關(guān)統(tǒng)計(jì)操作時(shí)，Oracle可以通過掃描位圖索引，并執(zhí)行位運(yùn)算，從而可以快速定位滿足條件的記錄。

在當(dāng)前主流數(shù)據(jù)庫產(chǎn)品中，只有Oracle支持位圖索引。文中實(shí)驗(yàn)使用的軟件版本為Oracle Database 12c企業(yè)版。

1? B樹索引的結(jié)構(gòu)及適用場(chǎng)景

B樹索引在邏輯上可以看作一顆倒立的樹，這棵樹分為若干層，頂層是樹根，底層是樹葉，中部是樹枝。除樹根由一個(gè)節(jié)點(diǎn)構(gòu)成外，其他每層都由一個(gè)或多個(gè)節(jié)點(diǎn)構(gòu)成，每個(gè)節(jié)點(diǎn)是一個(gè)數(shù)據(jù)塊。根據(jù)節(jié)點(diǎn)所在的層次，分別稱為根節(jié)點(diǎn)，分支節(jié)點(diǎn)及葉節(jié)點(diǎn)。B樹索引的葉節(jié)點(diǎn)存儲(chǔ)排序后的索引列值以及列值所在記錄的地址，根節(jié)點(diǎn)與分支節(jié)點(diǎn)可以看作是下層索引的索引，存儲(chǔ)其下層每個(gè)節(jié)點(diǎn)的索引列值范圍及其所在數(shù)據(jù)塊的塊號(hào)。

記錄的地址即rowid，由記錄的文件號(hào)、數(shù)據(jù)頁號(hào)、數(shù)據(jù)頁中的槽號(hào)（slot）三個(gè)屬性構(gòu)成。由此地址可以直接定位至此行的數(shù)據(jù)。

以下幾種情況，應(yīng)考慮創(chuàng)建B樹索引：

（1）在列值重復(fù)率低的情況下，應(yīng)在查詢條件包含的列上創(chuàng)建B樹索引，以提高查詢效率。

（2）若某個(gè)字段的函數(shù)值重復(fù)率低，而且頻繁以此函數(shù)值作為查詢字段，則應(yīng)在此列上創(chuàng)建基于函數(shù)的索引，以提高查詢效率。

（3）在連接查詢中，大表所包含的連接字段應(yīng)創(chuàng)建B樹索引。

2? 位圖索引的結(jié)構(gòu)

相比B樹索引，位圖索引的結(jié)構(gòu)很簡(jiǎn)單，沒有使用樹形結(jié)構(gòu)。在數(shù)據(jù)塊中存儲(chǔ)的每行索引數(shù)據(jù)主要由以下三部分構(gòu)成：

（1）索引鍵值。

（2）由兩個(gè)rowid表示的記錄地址范圍。

（3）位圖。

位圖部分中的每個(gè)位依序?qū)?yīng)第二部分所描述范圍內(nèi)的一個(gè)rowid，它用于描述在以上rowid范圍內(nèi)，哪些記錄的索引列取得了相應(yīng)索引鍵值，如果取得鍵值則為1，否則為0。

假定表t共有8行記錄，其數(shù)據(jù)存儲(chǔ)于4號(hào)文件的87號(hào)數(shù)據(jù)塊，表t有一個(gè)a列，其列值只有red、green、blue三個(gè)。表中的記錄共8行，其中第1、3行的a列值為red，第2、4、5行的a列值為green，第6、7、8行的a列值為blue。若在a列上創(chuàng)建位圖索引，則其包含三行數(shù)據(jù)，每行數(shù)據(jù)的三部分內(nèi)容如表1所示。

查詢某個(gè)位圖索引列值的行數(shù)時(shí)，只要計(jì)算位圖索引中對(duì)應(yīng)此值位圖中的1的個(gè)數(shù)即可。

3? 位圖索引內(nèi)容的驗(yàn)證

創(chuàng)建測(cè)試表t，其位圖索引列為a：

SQL> create table t（a char（5），b char（3））

2? tablespace users

3? /

依次執(zhí)行以下命令，對(duì)t表添加測(cè)試數(shù)據(jù)：

insert into t values（'red'，'aaa'）;

insert into t values（'green'，'bbb'）;

insert into t values（'red'，'ccc'）;

insert into t values（'green'，'ddd'）;

insert into t values（'green'，'eee'）;

insert into t values（'blue'，'fff'）;

insert into t values（'blue'，'ggg'）;

insert into t values（'blue'，'hhh'）;

創(chuàng)建表空間，以存儲(chǔ)位圖索引數(shù)據(jù)：

SQL> create tablespace idxbittbs

2? datafile 'e：＼oradata＼idxbittbs.dbf'

3? size 1m

4? /

創(chuàng)建位圖索引：

SQL> create bitmap index idx_bit_t on t（a）

2? tablespace idxbittbs

3? /

執(zhí)行以下命令把內(nèi)存修改數(shù)據(jù)寫入磁盤：

SQL> alter system flush buffer_cache;

使用十六進(jìn)制原始數(shù)據(jù)查看工具（如WinHex）中打開數(shù)據(jù)文件e：＼oradata＼idxbittbs.dbf，定位至字符串red后，位圖數(shù)據(jù)如圖1框中部分所示。

取出其中的第二行數(shù)據(jù)來分析，其偏移量范圍為106 390至106 414，其內(nèi)容如表2所示。

查詢t表中各記錄的地址，可以進(jìn)一步驗(yàn)證以上數(shù)據(jù)。

SQL> select a，

2? '（'

3? ||dbms_rowid.rowid_relative_fno（rowid）

4? ||'：'

5? ||dbms_rowid.rowid_block_number（rowid）

6? ||'：'

7? ||dbms_rowid.rowid_row_number（rowid）

8? ||'）'

9? as "rowid"

10? from t

11? /

A? ? ?rowid

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

blue? （4：87：5）

blue? （4：87：6）

blue? （4：87：7）

green （4：87：1）

green （4：87：3）

green （4：87：4）

red? ?（4：87：0）

red? ?（4：87：2）

已選擇8行。

4? 使用位圖索進(jìn)行查詢優(yōu)化

測(cè)試表big_table有10 000 000行記錄，添加記錄時(shí)，已經(jīng)設(shè)置其id2字段的列值只有3個(gè)不同的值：0、1、2。如果使用id2列作為計(jì)數(shù)條件，則此列應(yīng)該創(chuàng)建位圖索引。

執(zhí)行以下命令在id2字段創(chuàng)建位圖索引：

SQL> conn system/oracle

已連接。

SQL> create bitmap index idx_bit_id2 on big_table（id2）

2? tablespace idxtbs

3? /

設(shè)置以上位圖索引為失效狀態(tài)：

SQL> alter index idx_bit_id2 invisible;

執(zhí)行以下查詢，注意其執(zhí)行時(shí)間：

SQL> set timing on

SQL> select count（*） from big_table

2? where id2=1

3? /

COUNT（*）

----------

3333334

已用時(shí)間：? 00： 00： 48.25

id2上沒有索引，上述查詢只能使用全表掃描。

重新設(shè)置索引為可用狀態(tài)：

SQL> alter index idx_bit_id2 visible;

索引已更改。

再次執(zhí)行以上相同的查詢，可以發(fā)現(xiàn)此時(shí)的執(zhí)行時(shí)間大大降低：

SQL> select count（*）

2? from big_table

3? where id2=1

4? /

COUNT（*）

----------

3333334

已用時(shí)間：? 00： 00： 00.00

5? 位圖索引占用的空間

位圖索引占用空間很少，可以用以下查詢驗(yàn)證以上位圖索引占用空間的大小：

SQL> select sum（bytes）/1024/1024 as "SIZE（MB）"

2? from dba_segments

3? where segment_name='IDX_BIT_ID2'

4? /

SIZE（MB）

----------

6.0625

與B樹索引相比，位圖索引占用的空間基本可以忽略不計(jì)了。

6? 位圖索引不同于B樹索引的特點(diǎn)

與B樹索引相比，位圖索引具備以下特點(diǎn)：

（1）位圖索引只存儲(chǔ)鍵值記錄的物理地址范圍及位圖，需要的存儲(chǔ)空間非常少。

（2）創(chuàng)建B樹索引時(shí)，需要執(zhí)行排序操作，而創(chuàng)建位圖索引時(shí)，不需要排序，創(chuàng)建速度會(huì)比較快。

（3）與B樹索引不同，位圖索引存儲(chǔ)空鍵值，查詢索引列為空的行，可以使用索引。

（4）對(duì)表執(zhí)行計(jì)數(shù)統(tǒng)計(jì)時(shí)，直接訪問索引即可快速得出結(jié)果。

（5）對(duì)于B樹索引，修改鍵值時(shí)，只會(huì)鎖定鍵值所在的行。而對(duì)于位圖索引，修改鍵值時(shí)，會(huì)鎖定其鍵值所在的整個(gè)位圖范圍，若其他連接也修改此范圍的鍵值，則會(huì)發(fā)生等待。

（6）位圖索引應(yīng)建立在列值重復(fù)率很高的列上，一般情況下，外鍵列上應(yīng)創(chuàng)建位圖索引。

7? 結(jié)? 論

如果需要以表的某個(gè)字段執(zhí)行計(jì)數(shù)統(tǒng)計(jì)，并且這個(gè)字段的列值重復(fù)率高，則這個(gè)字段應(yīng)該創(chuàng)建位圖索引。相對(duì)B樹索引，位圖索引占用空間少。修改位圖索引列值時(shí)，會(huì)同時(shí)對(duì)原值和新值所在的位圖片段加鎖，從而大幅降低操作效率，因此，在常用的OLTP應(yīng)用中應(yīng)避免創(chuàng)建位圖索引。

參考文獻(xiàn)：

[1] VAIDYANATHA G K，DESHPANDE K，JR J A k，et al. Oracle Performance Tuning 101 [M].Bangor：Osborne Media，2001.

[2] RICHARD N. Oracle 10g Database Performance Tuning Tips & Techniques [M].New Bangor：Osborne Media，2007.

[3] DONALD K，BURLESON. Oracle High Performance SQL Tuning [M].New York City： McGraw-Hill Education ，2001.

[4] CONWAY H，AULT M，BURLESON D. Oracle Tuning Power Tuning Scripts [M].Kittrell：Rampant Techpress，2005.

[5] LEWIS J. Cost-Based Oracle Fundamentals [M].[S.L]：Apress，2005.

[6] CELKO J. SQL for Smarties：Advanced SQL Programming [M].3rd ed.San Francisco：Morgan Kaufmann，2005.

作者簡(jiǎn)介：李愛武（1969.07—），男，河北肅寧人，副教授，理學(xué)碩士，研究方向：數(shù)據(jù)庫技術(shù)，數(shù)據(jù)分析。

收稿日期：2021-02-12