DB2數(shù)據(jù)庫索引

孫 錕

同濟(jì)大學(xué)軟件學(xué)院，上海 201804

0 引言

數(shù)據(jù)庫對于銀行的業(yè)務(wù)來說無疑有著至關(guān)重要的作用，核心數(shù)據(jù)全部保存于數(shù)據(jù)庫中。每天銀行都有著成千上萬筆交易，其中查詢功能占據(jù)著業(yè)務(wù)的很大一部分比例。如何在現(xiàn)有的數(shù)據(jù)基礎(chǔ)上大幅提升性能，如何提升效率從而降低企業(yè)成本，是每個銀行需要面對的問題。本文從數(shù)據(jù)庫索引的特征方面入手簡單介紹一下在IBM大型機(jī)平臺上的大型關(guān)系數(shù)據(jù)庫db2索引的特征和簡單用法。

1 大型關(guān)系數(shù)據(jù)庫DB2簡介

1970 年IBM公司的研究員E.F.Codd 發(fā)表了業(yè)界第一篇關(guān)于關(guān)系數(shù)據(jù)庫理論的論文"A Relational Model of Data for Large Shared Data Banks"，首次提出了關(guān)系模型的概念。這篇論文是計算機(jī)科學(xué)史上最重要的論文之一，奠定了Codd博士"關(guān)系數(shù)據(jù)庫之父"的地位，同時也為DB2數(shù)據(jù)庫打下了堅實的理論基礎(chǔ)。1983年，IBM推出了這款大型數(shù)據(jù)庫軟件， DATABASE 2（DB2）for MVS（內(nèi)部代號為"Eagle"）。

經(jīng)歷25年的發(fā)展，DB2現(xiàn)在除了應(yīng)用于IBM的OS/390、z/OS大型機(jī)平臺之外，還可以應(yīng)用于中型機(jī)AS/400以及OS/2、windows2000等pc機(jī)上，平臺具有非常好的伸縮性。DB2為用戶提供數(shù)據(jù)可用性，完整性、安全性、可恢復(fù)性以及高效的執(zhí)行能力，滿足了各個層次用戶的需要。本文接著就DB2的索引方面進(jìn)行一下簡單介紹。

2 DB2索引

DB2索引也是一種 DB2 對象（一個單獨(dú)的 VSAM 數(shù)據(jù)集），它由一組排好序的鍵組成，這些鍵是從相應(yīng)表中的一個列或多個列抽取出來的。

2.1 DB2索引類型

2.1.1 非唯一索引

實際應(yīng)用當(dāng)中大部分的索引是非唯一索引。一般性的數(shù)據(jù)都具有可重復(fù)性特性，所以他們不能被定義為唯一索引。

2.1.2 唯一索引

唯一索引用來保證數(shù)據(jù)的唯一性，唯一索引一般性能要高于非唯一索引，這與索引的稠密度有關(guān)。唯一索引的稠密度永遠(yuǎn)等于數(shù)據(jù)總條數(shù)的倒數(shù)。

2.1.3 純索引

純索引的概念是相對與一般索引。如下方式表中有倆個字段，其中字段1是唯一主鍵，字段2為數(shù)據(jù)，實際的查詢中經(jīng)常是select * from 表 where col1=?這樣的查詢條件可以使用純索引來避免表查詢，具體創(chuàng)建命令為：CREATE UNIQUE INDEXON（COL1_NAME）INCLUDE（COL2_NAME）。上述的語句的意思就是在col1上創(chuàng)建唯一索引，選擇包含col2的數(shù)據(jù)，這些附加的數(shù)據(jù)將與鍵存儲到一起，但是不作為索引的一部分，所以不被排序。純索引訪問是用來減少對數(shù)據(jù)頁的訪問，因為所需要的數(shù)據(jù)已經(jīng)顯示在索引中了。

2.1.4 群集索引

群集索引允許對數(shù)據(jù)頁采用更線性的訪問模式，允許更有效的預(yù)取，并且避免排序。群集索引是要求數(shù)據(jù)在插入時，做更多的操作，將相臨的數(shù)據(jù)條目放入相同的頁，使得查詢速度更快，因為每次訪問索引頁要將所有的索引條目都訪問完畢才移到下一頁，保證了緩存池中任何一個時刻都只有一個索引頁存在。

群集索引的特點(diǎn)：

1）高查詢速度，數(shù)據(jù)頁以鍵的順序排列；

2）以鍵的順序掃描整張表。

插入和更新需要做更多的事情，不建議經(jīng)常插入和更新的表上做群集索引。

2.2 DB2索引結(jié)構(gòu)

圖1 B+樹的一個簡單實例

在DB2中，索引的物理結(jié)構(gòu)是一個獨(dú)立的VSAM數(shù)據(jù)集，邏輯結(jié)構(gòu)是一顆B+樹。B+樹把它的存儲塊組織成一棵樹。這棵樹是平衡的，即從樹根到樹葉的所有路徑都一樣長。通常B+樹有3層：根、中間層和葉，但也可以是任意多層。

典型的B+樹結(jié)構(gòu)：

根結(jié)點(diǎn)中至少有兩個指針被使用。所有指針指向位于B+樹下一層的存儲塊；

葉結(jié)點(diǎn)中，最后一個指針指向它右邊的下一個葉結(jié)點(diǎn)存儲塊，即指向下一個鍵值大于它的塊。在葉塊的其他n個指針當(dāng)中，至少有個指針被使用且指向數(shù)據(jù)記錄；未使用的指針可看作空指針且不指向任何地方。如果第i個指數(shù)被使用，則指向具有第i個鍵值的記錄；

在內(nèi)層結(jié)點(diǎn)中，所有的n+1個指針都可以用來指向B+樹中下一層的塊。其中至少2個指針被實際使用（如果是根結(jié)點(diǎn)，則不管n多大都只要求至少兩個指針被使用）。如果j個指針被使用，那該塊中將有j-1個鍵，設(shè)為K1，K2……，Kj-1。第一個指針指向B+樹的一部分，一些鍵值小于K1的記錄可在這一部分找到。第二個指針指向B+樹的另一部分，所有鍵值大小等于K1且小于K2的記錄可在這一部分中。依此類推。最后，第j個指針指向B+樹的又一部分，一些鍵值大于等于Kj-1的記錄可以在這一部分中找到。注意：某些鍵值遠(yuǎn)小于K1或遠(yuǎn)大于Kj-1的記錄可能根本無法通過該塊到達(dá)，但可通過同一層的其他塊到達(dá)。

假若我們以常規(guī)的畫樹方式來畫B+樹，任一給定結(jié)點(diǎn)的子結(jié)點(diǎn)按從左（第一個子結(jié)點(diǎn)）到右（最后一個子結(jié)點(diǎn)）的順序排列。那么，我們在任何一個層次上從左到右來看B+樹的結(jié)點(diǎn)，結(jié)點(diǎn)的鍵值將按非減的順序出現(xiàn)。下圖為B+樹的一個簡單實例。（圖1）

2.3 DB2索引訪問機(jī)制

快速索引式訪問

一般來將DB2最快的數(shù)據(jù)訪問方式就是使用索引。索引是為了快速找著數(shù)據(jù)塊的數(shù)據(jù)結(jié)構(gòu)。

在DB2使用索引來查詢數(shù)據(jù)前，必須滿足以下要求：

1）至少有一個SQL謂詞必須是可索引的；

2）其中一列必須作為可用索引中的列而存在。

2.4 DB2索引創(chuàng)建原則

DB2索引的數(shù)據(jù)結(jié)構(gòu)實現(xiàn)是一個B+樹，通過索引可以實現(xiàn)快速查詢，避免全表掃描以此來減少IO操作。

索引是對表數(shù)據(jù)的一種抽象，通過抽取有限數(shù)據(jù)，對數(shù)據(jù)的分布進(jìn)行計算，以此來完成對數(shù)據(jù)的快速檢索。

索引創(chuàng)建基本語句：

“CREATE INDEXON（）”。

創(chuàng)建索引需要注意的地方：

索引應(yīng)該用來提高查詢速度，但是會對更新和刪除操作帶來負(fù)面影響，因為要同步更新索引。所以索引應(yīng)該創(chuàng)建到更新、刪除相對比讀取少的表上。

索引需要獨(dú)立的空間進(jìn)行存儲和管理。索引是需要磁盤空間來存儲。所以避免重復(fù)創(chuàng)建冗余索引。

索引用來避免表掃描。通過索引對大量數(shù)據(jù)抽取有限部分，形成一個相對少量的有序數(shù)據(jù)結(jié)構(gòu)，通過對有序數(shù)據(jù)結(jié)構(gòu)的查找可以快速想要查找的數(shù)據(jù)。所以索引適合建立在數(shù)據(jù)量比較大的表上，而且該表上的查詢經(jīng)常是根據(jù)條件查詢部分?jǐn)?shù)據(jù)。比如一些系統(tǒng)基礎(chǔ)表，如SYSTEM表，這些表數(shù)據(jù)量小，而且經(jīng)常是查詢?nèi)繑?shù)據(jù)，所以這些表上建立索引對性能的影響不是很大，完全可以避免，以免對管理造成影響。

創(chuàng)建索引的目的還有一個就是保證數(shù)據(jù)唯一性。

主鍵會隱式創(chuàng)建索引，所以請不要在主鍵上創(chuàng)建索引浪費(fèi)空間。

盡量減少索引的創(chuàng)建。DB2路徑訪問優(yōu)化器會根據(jù)表中所提供的索引來完成盡可能多的訪問路徑的成本估計。創(chuàng)建過多的索引意味著DB2優(yōu)化器生成更多的訪問路徑，完成更多的訪問計劃成本估算，這會增加SQL語句編譯時間。

創(chuàng)建唯一索引可以避免排序。因為索引是有序數(shù)據(jù)結(jié)構(gòu)，在進(jìn)行掃描時，DB2會默認(rèn)按照順序輸出結(jié)果，而不是按照插入先后。通過創(chuàng)建唯一索引可以避免排序，提高查詢性能。

具有大量重復(fù)數(shù)據(jù)的列上不要創(chuàng)建索引。在大量重復(fù)的列上創(chuàng)建索引沒有任何意義。

2.5 DB2索引優(yōu)化

謂詞類型 可索引 注釋Col∝con Y ∝代表>,>=,=,<=,<,但是<>是可能不可索引的。Col between con1 and con2 Y 在匹配系列中必須是最后的。Col in list Y 僅對一個匹配列Col is null Y Col like ‘xyz%’Y模糊匹配%在后面。Col like ‘%xyz’N模糊匹配%在前面。Col1∝Col2 N Col1和col2來自同一個表Col∝Expression N 例如：c1（c1+1）/2 Pred1 and Pred2 Y Pred1和Pred2都是可索引的，指相同索引的列Pred1 or Pred2 N 除了（c1=a or c1=b）外，他可以被認(rèn)為是c1 in（a，b）Not Pred1 N 或者任何的等價形式：Not between,Not in,Not like等等。

[1]牛新莊.DB2數(shù)據(jù)庫性能調(diào)整和優(yōu)化[M].清華大學(xué)出版社，2009.

[2]牛新莊.循序漸進(jìn)DB2—DBA系統(tǒng)管理、運(yùn)維與應(yīng)用案例[M].清華大學(xué)出版社，2009.

[3]溫濤，戴慰，等.DB2深度解析——高級DBA和開發(fā)者篇[M].東軟電子出版社，2009.

[4]王飛鵬，等.DB2設(shè)計與性能優(yōu)化——原理、方法與實踐[M].電子工業(yè)出版社，2011.