ＯＲＡＣＬＥ中大數(shù)據(jù)量下索引效率的測試與分析

摘要：通過模擬海量數(shù)據(jù)的產(chǎn)生，生成測試數(shù)據(jù)并進(jìn)行數(shù)據(jù)查詢、插入，對(duì)索引的效率進(jìn)行分析，給出了Oracle數(shù)據(jù)庫中大數(shù)據(jù)量下如何合理使用全局索引與分區(qū)索引的建議。

關(guān)鍵詞：大數(shù)據(jù)量；索引；效率；分區(qū)

0 引言

一些大的應(yīng)用系統(tǒng)如醫(yī)保、移動(dòng)、銀行等行業(yè)的應(yīng)用系統(tǒng)，出于節(jié)約管理成本、提高數(shù)據(jù)共享度等方面的考慮，業(yè)務(wù)數(shù)據(jù)一般以省為單位集中，數(shù)據(jù)庫中存放的數(shù)據(jù)量很大(一般為T級(jí))，而這類業(yè)務(wù)系統(tǒng)一般是OLTP系統(tǒng)，每個(gè)業(yè)務(wù)所操作的數(shù)據(jù)量相對(duì)較小，因此業(yè)務(wù)實(shí)現(xiàn)時(shí)能否使用索引、索引是否高效等就成為需要解決的問題。為充分了解數(shù)據(jù)庫在大數(shù)據(jù)量下的性能和工作負(fù)載，對(duì)數(shù)據(jù)庫的索引效率進(jìn)行測試和分析，指導(dǎo)系統(tǒng)設(shè)計(jì)和開發(fā)非常有必要。

我們以某部門基于Oracle數(shù)據(jù)庫的J2EE架構(gòu)的應(yīng)用系統(tǒng)為例，對(duì)Oracle數(shù)據(jù)庫產(chǎn)品進(jìn)行了測試。測試與分析的步驟如下：

(1)生成模擬數(shù)據(jù)并進(jìn)行查詢測試；

(2)分析索引對(duì)數(shù)據(jù)插入的影響；

(3)分析索引對(duì)數(shù)據(jù)查詢的影響；

(4)結(jié)論與建議。

1 生成模擬數(shù)據(jù)與查詢測試

1．1測試目的

根據(jù)對(duì)業(yè)務(wù)數(shù)據(jù)的估算，系統(tǒng)中用戶數(shù)將達(dá)到2000萬，數(shù)據(jù)庫中要保留三年的業(yè)務(wù)數(shù)據(jù)，最大的業(yè)務(wù)表中的數(shù)據(jù)量大致在30億條，因此測試前先模擬出2000萬用戶3年的數(shù)據(jù)。我們希望通過對(duì)這張表的實(shí)際操作來確定大數(shù)據(jù)量表的設(shè)計(jì)原則，即：

(1)對(duì)大表是采用Oracle分區(qū)特性，還是每個(gè)業(yè)務(wù)單獨(dú)建立一張物理表；

(2)索引的建立原則是建立分區(qū)索引還是建立全局索引；

(3)如果使用分區(qū)，分區(qū)策略是先按時(shí)間分區(qū)還是先按業(yè)務(wù)單位分區(qū)。

1．2測試環(huán)境

一臺(tái)IBM P570(4CPU／16G內(nèi)存)，磁盤陣列為IBM4500。Oracle數(shù)據(jù)庫版本為10．2．0．2，操作系統(tǒng)版本為AIX 5．3。Oracle SGA的大小為6000M。數(shù)據(jù)表空間和索引表空間的數(shù)據(jù)塊大小均為8K。

1．3測試步驟

生成模擬數(shù)據(jù)采取以下步驟進(jìn)行：

(1)以某地—個(gè)表05年12月份數(shù)據(jù)為基礎(chǔ)，保存為種子表DATA_SEED(Custld，F(xiàn)eym，Acptld，Custldl，hsUnit，Dept，Unit)。表中共有17萬用戶的47萬條數(shù)據(jù)。

(2)建立分區(qū)表DATA_DEST，結(jié)構(gòu)與DATA_SEED相同。并對(duì)列CustId建立全局索引GLO_DATA_DEST及分區(qū)索引LOC_DATA_DEST(因同一列不允許建兩個(gè)索引，需加字段Custldl，值等于CustId并對(duì)其建立分區(qū)索引)。

(3)復(fù)制DATA_SEED達(dá)到每月2000萬用戶產(chǎn)生的數(shù)據(jù)量。為保證數(shù)據(jù)真實(shí)的離散度，對(duì)索引列每次復(fù)制都用升位的方式處理，索引列前4位為單位碼，從0001開始，每次加1。

(4)修改分區(qū)列Feym為次月(如200601)，以同樣的方式復(fù)制產(chǎn)生次月數(shù)據(jù)，以此類推得到3年歷史數(shù)據(jù)。

(5)插入完06年數(shù)據(jù)后就刪除Custld上的全局索引，插入完2007年的數(shù)據(jù)后就刪除該表上的所有索引。

這種方式獲取的數(shù)據(jù)與實(shí)際的數(shù)據(jù)產(chǎn)生情況比較接近，即索引是預(yù)先建好的，在插入數(shù)據(jù)的過程中，自動(dòng)維護(hù)索引。索引字段Custld和Acptld的離散度也和實(shí)際情況相符。

1．4實(shí)際測試結(jié)果

生成模擬數(shù)據(jù)總共耗時(shí)178，378秒。插入每個(gè)月的數(shù)據(jù)時(shí)所耗時(shí)間見表1。

存在三個(gè)索引(custId字段上的全局索引，ACPTID，Cusddl上的分區(qū)索引)時(shí)插入數(shù)據(jù)的耗時(shí)情況參見表1：

表1 存在三個(gè)索引時(shí)插入數(shù)據(jù)的耗時(shí)

刪除Custld字段上的全局索引，保留Acptld，Cusfidl上的分區(qū)索引，再進(jìn)行數(shù)據(jù)插入耗時(shí)情況參見表2：

表2 僅存在分區(qū)索引時(shí)插入數(shù)據(jù)的耗時(shí)

刪除該表上所有三個(gè)索引后，再進(jìn)行數(shù)據(jù)插入的耗時(shí)情況參見表3：

表3 不存在索引時(shí)插入數(shù)據(jù)的耗時(shí)

2 索引對(duì)數(shù)據(jù)插入的影響

(1)存在全局索引，數(shù)據(jù)插入的時(shí)間隨分區(qū)數(shù)的增加而增加

從表1中可以看出，在2006年1月至2006年12月期間，存在CustId的全局索引，隨著數(shù)據(jù)量的增長，每新增一個(gè)月的數(shù)據(jù)，耗時(shí)需要多增加1000秒左右。從表2中可以看出，在刪除了CustId的全局索引后(此時(shí)還存在CustIdl及Acptld的分區(qū)索引)每次插入數(shù)據(jù)的時(shí)間維持在3000秒左右。

(2)索引的存在會(huì)影響數(shù)據(jù)插入的速度

從表3中可以看出，沒有索引時(shí)每個(gè)分區(qū)插入的時(shí)間為1200秒左右，比存在索引時(shí)插入的速度快一倍多。這說明并非索引越多性能越好，是否建立列索引需根據(jù)具體應(yīng)用決定。

(3)如果被索引的列的值與原來的數(shù)據(jù)相同，會(huì)影響插入的性能

插入第二個(gè)月的數(shù)據(jù)時(shí)，對(duì)于B樹索引，不同月份同一CustId的索引數(shù)據(jù)存放在一起，這需要更多的資源去維護(hù)索引，索引空間不夠時(shí)，還需分裂索引塊。此時(shí)全局索引的表現(xiàn)比分區(qū)索引明顯得多，因?yàn)槿炙饕?，被索引列中相同的值的重?fù)率會(huì)比分區(qū)索引高很多。

(4)當(dāng)插入數(shù)據(jù)量很大時(shí)，索引需重新分裂，用更多的數(shù)據(jù)塊來存放索引數(shù)據(jù)

在插入200606月份的數(shù)據(jù)后，進(jìn)行查詢并跟蹤執(zhí)行語句所掃描的數(shù)據(jù)塊。查詢語句為：select Custld，AcptId，Cusfldl，hsUnit，Dept，Unit from DATA_DEST where Custld='1380745518'；插入200606月份的數(shù)據(jù)后查詢跟蹤的結(jié)果如表4所示：

表4 插入200606月份的數(shù)據(jù)后查詢跟蹤的結(jié)果

插入200612月份的數(shù)據(jù)后查詢跟蹤的結(jié)果如表5所示

表5 插入200612月份的數(shù)據(jù)后查詢跟蹤的結(jié)果

比較表4和表5的黑體部分，可以看到隨著相同的值數(shù)據(jù)插入，索引塊出現(xiàn)了遷移。在2006年12月份的時(shí)候，同一CustId列數(shù)據(jù)所在的索引數(shù)據(jù)塊(數(shù)據(jù)塊編號(hào)為：5769587、9462046、5771162、548)與6月份時(shí)的索引數(shù)據(jù)塊(數(shù)據(jù)塊編號(hào)為：5769587、13225043、9462046、5771162、548)是不同的。

3 索引對(duì)查詢性能的影響

下面分析有18億條記錄的表在單個(gè)分區(qū)內(nèi)、多個(gè)分區(qū)內(nèi)及全表內(nèi)使用索引字段的一些表現(xiàn)。以下的查詢均基于CustIdl列來查詢。考慮到SGA緩存數(shù)據(jù)在DATA BUFFER對(duì)查詢的影響，在每次查詢前清空SGA中的數(shù)據(jù)。

(1)單個(gè)分區(qū)，離散度高的列上的分區(qū)索引效率比按高

模擬過程中發(fā)現(xiàn)，存在B樹索引時(shí)，離散度高的列(如Cusfld)索引的效率較高，數(shù)據(jù)量的增長并未造成索引性能的降低。通過ORADEBUG命令DUMP出Oracle內(nèi)存中DATABUFFER部分的數(shù)據(jù)塊，發(fā)現(xiàn)在一個(gè)分區(qū)內(nèi)只需4次分裂，索引就可以定位到需要的數(shù)據(jù)。測試結(jié)果如表6所示。

查詢語句為：

select Custld，Acptld，Custldl，hsUnit，Dept，Unit from DATA_DEST

where Custldl='1380745518'and FEYM='200610'；

表6 單個(gè)分區(qū)內(nèi)的根據(jù)CustId查詢的結(jié)果

(2)多個(gè)分區(qū)內(nèi)使用索引查詢的致年沒有差異

在數(shù)據(jù)插入過程中單獨(dú)查詢某CustIdl在200610，200710，200810三個(gè)年月的數(shù)據(jù)，可以看出：無論對(duì)哪個(gè)年月進(jìn)行查詢，只要指定查詢的CustIdl和Feym條件，查詢都是對(duì)特定的分區(qū)進(jìn)行掃描，消耗很少(此例中耗時(shí)為00：00：00．31)。

(3)全表所有分區(qū)的索引掃描盡管效率按高，但由于要掃描所有分區(qū)，響應(yīng)時(shí)間相對(duì)長

如果只指定CustIdl條件進(jìn)行查詢，則會(huì)搜索該表的全部分區(qū)，共返回36條記錄，耗時(shí)0．85秒。測試結(jié)果如表7所示。

表7 全表范圍索引掃描的輸出片斷

4 結(jié)束語

綜上所述，可以得出以下結(jié)論與建議：

(1)對(duì)于大表(記錄超過1億條)采用Oracle提供的分區(qū)特性就可以滿足大數(shù)據(jù)量下的系統(tǒng)性能要求，不必為每個(gè)業(yè)務(wù)單位單獨(dú)建立一張物理表；

(2)在每個(gè)分區(qū)5000萬條記錄的情況下，分區(qū)范圍內(nèi)的查詢效率較低。對(duì)于像CustId這樣的在每個(gè)分區(qū)中離散度都高的列，建議建成分區(qū)索引，以保證系統(tǒng)的可維護(hù)性以及性能的穩(wěn)定，并在SQL中盡可能限制查詢所需要涉及到的表分區(qū)。

(3)如果不是主鍵列，而且DML語句中多數(shù)情況下可以使用分區(qū)列作為數(shù)據(jù)操作條件，則建議建立分區(qū)索引。

(4)如果使用分區(qū)，分區(qū)策略需要根據(jù)實(shí)際業(yè)務(wù)需要來確定。比如如果經(jīng)常按業(yè)務(wù)單位進(jìn)行DML操作，有時(shí)按業(yè)務(wù)單位加上年月進(jìn)行DML操作，則應(yīng)該進(jìn)行復(fù)合分區(qū)：先按業(yè)務(wù)單位分區(qū)，子分區(qū)再按年月分區(qū)。如果多數(shù)情況下是按年月進(jìn)行DML操作，少數(shù)時(shí)間是用業(yè)務(wù)單位進(jìn)行DML操作，則應(yīng)考慮先按年月分區(qū)，再按業(yè)務(wù)單位建立子分區(qū)。

注：本文中所涉及到的圖表、注解、公式等內(nèi)容請(qǐng)以PDF格式閱讀原文。