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

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

關鍵詞：大數據量；索引；效率；分區

0 引言

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

我們以某部門基于Oracle數據庫的J2EE架構的應用系統為例，對Oracle數據庫產品進行了測試。測試與分析的步驟如下：

(1)生成模擬數據并進行查詢測試；

(2)分析索引對數據插入的影響；

(3)分析索引對數據查詢的影響；

(4)結論與建議。

1 生成模擬數據與查詢測試

1．1測試目的

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

(1)對大表是采用Oracle分區特性，還是每個業務單獨建立一張物理表；

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

(3)如果使用分區，分區策略是先按時間分區還是先按業務單位分區。

1．2測試環境

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

1．3測試步驟

生成模擬數據采取以下步驟進行：

(1)以某地—個表05年12月份數據為基礎，保存為種子表DATA_SEED(Custld，Feym，Acptld，Custldl，hsUnit，Dept，Unit)。表中共有17萬用戶的47萬條數據。

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

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

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

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

這種方式獲取的數據與實際的數據產生情況比較接近，即索引是預先建好的，在插入數據的過程中，自動維護索引。索引字段Custld和Acptld的離散度也和實際情況相符。

1．4實際測試結果

生成模擬數據總共耗時178，378秒。插入每個月的數據時所耗時間見表1。

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

表1 存在三個索引時插入數據的耗時

刪除Custld字段上的全局索引，保留Acptld，Cusfidl上的分區索引，再進行數據插入耗時情況參見表2：

表2 僅存在分區索引時插入數據的耗時

刪除該表上所有三個索引后，再進行數據插入的耗時情況參見表3：

表3 不存在索引時插入數據的耗時

2 索引對數據插入的影響

(1)存在全局索引，數據插入的時間隨分區數的增加而增加

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

(2)索引的存在會影響數據插入的速度

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

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

插入第二個月的數據時，對于B樹索引，不同月份同一CustId的索引數據存放在一起，這需要更多的資源去維護索引，索引空間不夠時，還需分裂索引塊。此時全局索引的表現比分區索引明顯得多，因為全局索引中，被索引列中相同的值的重復率會比分區索引高很多。

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

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

表4 插入200606月份的數據后查詢跟蹤的結果

插入200612月份的數據后查詢跟蹤的結果如表5所示

表5 插入200612月份的數據后查詢跟蹤的結果

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

3 索引對查詢性能的影響

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

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

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

查詢語句為：

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

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

表6 單個分區內的根據CustId查詢的結果

(2)多個分區內使用索引查詢的致年沒有差異

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

(3)全表所有分區的索引掃描盡管效率按高，但由于要掃描所有分區，響應時間相對長

如果只指定CustIdl條件進行查詢，則會搜索該表的全部分區，共返回36條記錄，耗時0．85秒。測試結果如表7所示。

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

4 結束語

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

(1)對于大表(記錄超過1億條)采用Oracle提供的分區特性就可以滿足大數據量下的系統性能要求，不必為每個業務單位單獨建立一張物理表；

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

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

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

注：本文中所涉及到的圖表、注解、公式等內容請以PDF格式閱讀原文。