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

孫 錕

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

0 引言

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

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

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

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

2 DB2索引

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

2.1 DB2索引類(lèi)型

2.1.1 非唯一索引

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

2.1.2 唯一索引

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

2.1.3 純索引

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

2.1.4 群集索引

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

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

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

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

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

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

圖1 B+樹(shù)的一個(gè)簡(jiǎn)單實(shí)例

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

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

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

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

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

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

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

快速索引式訪問(wèn)

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

在DB2使用索引來(lái)查詢(xún)數(shù)據(jù)前，必須滿(mǎn)足以下要求：

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

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

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

DB2索引的數(shù)據(jù)結(jié)構(gòu)實(shí)現(xiàn)是一個(gè)B+樹(shù)，通過(guò)索引可以實(shí)現(xiàn)快速查詢(xún)，避免全表掃描以此來(lái)減少I(mǎi)O操作。

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

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

“CREATE INDEXON（）”。

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

索引應(yīng)該用來(lái)提高查詢(xún)速度，但是會(huì)對(duì)更新和刪除操作帶來(lái)負(fù)面影響，因?yàn)橐礁滤饕?。所以索引?yīng)該創(chuàng)建到更新、刪除相對(duì)比讀取少的表上。

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

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

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

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

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

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

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

2.5 DB2索引優(yōu)化

謂詞類(lèi)型 可索引 注釋Col∝con Y ∝代表>,>=,=,<=,<,但是<>是可能不可索引的。Col between con1 and con2 Y 在匹配系列中必須是最后的。Col in list Y 僅對(duì)一個(gè)匹配列Col is null Y Col like ‘xyz%’Y模糊匹配%在后面。Col like ‘%xyz’N模糊匹配%在前面。Col1∝Col2 N Col1和col2來(lái)自同一個(gè)表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 或者任何的等價(jià)形式：Not between,Not in,Not like等等。

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

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

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

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