|
１.介紹 1.1 概述 建表時(shí)，為提高檢索速度和保證列無重復(fù)行，對(duì)表的任何一列或多列都可建索引，而該列稱為索引（key column）。建索引有3種目的： 1．為建索引列提供快速檢索。改善某些特殊列或值的查詢性能。 2．為建索引提供升序或降序功能。該功能對(duì)有ORDER BY和GROUP BY子句的查詢特別有用。加快這些子句的實(shí)施。當(dāng)然，這些子句也可以不用索引實(shí)施，系統(tǒng)只作簡(jiǎn)單的分類處理。 3．為表中某些列保證不出現(xiàn)重復(fù)行。因?yàn)樵诮ㄎㄒ凰饕龝r(shí)，如列中有重復(fù)行，建唯一索引失敗返回，利用這一性質(zhì)，可處理列中重復(fù)行。 在表中，用戶可按自己需求，組合各列作為索引項(xiàng)列，并對(duì)它建索引，但是，建索引要花費(fèi)一定開銷。主要指空間占用的開銷。因?yàn)?，建索引時(shí)，對(duì)列值要全部復(fù)制，再加上指針，都需占用空間。另一方面開銷是在發(fā)生表修改時(shí)，每次對(duì)表的列值修改，相應(yīng)索引必然要改。實(shí)際上，數(shù)據(jù)庫的維護(hù)工作中，索引維護(hù)經(jīng)驗(yàn)是很重要的實(shí)踐內(nèi)容。雖然，關(guān)系型數(shù)據(jù)庫的數(shù)據(jù)，獨(dú)立于庫模型，允許增加或刪除索引。但是，查詢的每次執(zhí)行，與索引的選擇是相關(guān)的。 在UDB中，只有三種方法去實(shí)施對(duì)特殊列保證無重復(fù)行。一種方法是對(duì)列建唯一索引，另一種方法是對(duì)該列指定主關(guān)鍵字，最后一種方法是對(duì)該列建唯一關(guān)鍵字。如指定SERIALNO為EMPLOYEE表的主關(guān)鍵字或唯一關(guān)鍵字，為保證列無重復(fù)的行，對(duì)它建唯一索引。對(duì)一個(gè)表來說，主關(guān)鍵字只有一個(gè)，而唯一關(guān)鍵字可以有多個(gè)。 1.2 分類 索引的分類 按照索引的應(yīng)用和結(jié)構(gòu)可以有不同的分類方法。 （1） 聚集索引和非聚集索引 聚集索引不僅是把關(guān)鍵字相近的記錄用指針邏輯地連在一起，而且把關(guān)鍵字相近的記錄物理地存放在一起。每個(gè)表中僅能有一個(gè)聚集索引。我們一般建的索引均是非聚集索引，如要建立聚集索引，就要在建索引的命令中加CLUSTER子句。 （2） 唯一關(guān)鍵字索引和非唯一關(guān)鍵字索引 唯一關(guān)鍵字索引主要是建索引的關(guān)鍵字的值必須唯一，特別是在已存在的表上建唯一索引時(shí)，如有關(guān)鍵字的值不唯一，那么建索引失敗。對(duì)主關(guān)鍵字和唯一關(guān)鍵字，系統(tǒng)會(huì)自動(dòng)建唯一索引。在建索引時(shí)，要加UNION的子句。 （3） 復(fù)合關(guān)鍵字索引和包含關(guān)鍵字索引 如建索引，系統(tǒng)會(huì)針對(duì)關(guān)鍵字的升序或降序方式來幫助提高查詢性能，對(duì)復(fù)合關(guān)鍵字會(huì)逐個(gè)地按升序或降序來索引。而對(duì)包含關(guān)鍵字索引，只對(duì)所要索引的關(guān)鍵字按序進(jìn)行，對(duì)所包含的字段不按序進(jìn)行，只是在索引中增加了字段，常常是對(duì)唯一關(guān)鍵字索引做包含字段。 1.3 索引的設(shè)計(jì) 1.3.1 準(zhǔn)則 1. 一個(gè)表如果建有大量索引會(huì)影響 INSERT、UPDATE 和 DELETE 語句的性能，因?yàn)樵诒碇械臄?shù)據(jù)更改時(shí)，所有索引都須進(jìn)行適當(dāng)?shù)恼{(diào)整。另一方面，對(duì)于不需要修改數(shù)據(jù)的查詢（SELECT 語句），大量索引有助于提高性能，因?yàn)閿?shù)據(jù)庫有更多的索引可供選擇，以便確定以最快速度訪問數(shù)據(jù)的最佳方法。 2. 組合索引：組合索引即多列索引，指一個(gè)索引含有多個(gè)列。一個(gè)組合索引相當(dāng)于多個(gè)單列索引，如索引(ColA, ColB, ColC)至少相當(dāng)于(ColA)、(ColA, ColB)、(ColA, ColB, ColC)三個(gè)索引。 2. 覆蓋的查詢可以提高性能。覆蓋的查詢是指查詢中所有指定的列都包含在同一個(gè)索引(組合索引)中。例如，如果在一個(gè)表的 a、b 和 c 列上創(chuàng)建了組合索引，則從該表中檢索 a 和 b 列的查詢被視為覆蓋的查詢。創(chuàng)建覆蓋一個(gè)查詢的索引可以提高性能，因?yàn)樵摬樵兊乃袛?shù)據(jù)都包含在索引自身當(dāng)中；檢索數(shù)據(jù)時(shí)只需引用表的索引頁，不必引用數(shù)據(jù)頁，因而減少了 I/O 總量。盡管給索引添加列以覆蓋查詢可以提高性能，但在索引中額外維護(hù)更多的列會(huì)產(chǎn)生更新和存儲(chǔ)成本。 3. 對(duì)小型表進(jìn)行索引可能不會(huì)產(chǎn)生優(yōu)化效果，因?yàn)閿?shù)據(jù)庫在遍歷索引以搜索數(shù)據(jù)時(shí)，花費(fèi)的時(shí)間可能會(huì)比簡(jiǎn)單的表掃描還長(zhǎng)。 4. 應(yīng)使用 SQL 事件探查器和索引優(yōu)化向?qū)椭治霾樵?，確定要?jiǎng)?chuàng)建的索引。為數(shù)據(jù)庫及其工作負(fù)荷選擇正確的索引是非常復(fù)雜的，需要在查詢速度和更新成本之間取得平衡。窄索引（搜索關(guān)鍵字中只有很少的列的索引）需要的磁盤空間和維護(hù)開銷都更少。而另一方面，寬索引可以覆蓋更多的查詢。確定正確的索引集沒有簡(jiǎn)便的規(guī)則。經(jīng)驗(yàn)豐富的數(shù)據(jù)庫管理員常常能夠設(shè)計(jì)出很好的索引集，但是，即使對(duì)于不特別復(fù)雜的數(shù)據(jù)庫和工作負(fù)荷來說，這項(xiàng)任務(wù)也十分復(fù)雜、費(fèi)時(shí)和易于出錯(cuò)?？梢允褂盟饕齼?yōu)化向?qū)惯@項(xiàng)任務(wù)自動(dòng)化。有關(guān)更多信息，請(qǐng)參見索引優(yōu)化向?qū)А?/font> 5. 可以在視圖上指定索引。 6. 可以在計(jì)算列上指定索引。 7. 避免在索引列上使用IS NULL和IS NOT NULL。避免在索引中使用任何可以為空的列，數(shù)據(jù)庫將無法使用該索引。對(duì)于單列索引，如果列包含空值，索引中將不存在此記錄；對(duì)于復(fù)合索引，如果每個(gè)列都為空，索引中同樣不存在此記錄. 如果至少有一個(gè)列不為空，則記錄存在于索引中。 8. 如果經(jīng)常檢索包含大量數(shù)據(jù)的表中的少于15%的行則需要?jiǎng)?chuàng)建索引。 9. 衡量索引效率的 95/5 規(guī)則：如果查詢的結(jié)果返回的行數(shù)少于表中所有行的5%，則索引是檢索數(shù)據(jù)的最快方法，如果查詢的結(jié)果超過5%，那么通常使用索引就不是最快的方式。 10.主關(guān)鍵字和唯一關(guān)鍵字所在的列自動(dòng)具有索引，但外部關(guān)鍵字沒有自動(dòng)索引。 1.3.2 規(guī)范 1.索引中的最大列數(shù)是 64。若是對(duì)類型表建立索引，則最大列數(shù)是 63。索引鍵的最大長(zhǎng)度不能大于頁大小的索引鍵長(zhǎng)度限制。 2. 3. 若要讓主鍵索引成為集群索引，不應(yīng)在 CREATE TABLE 中指定主鍵。一旦創(chuàng)建了主鍵，就不能修改相關(guān)的索引。而是執(zhí)行不帶主鍵子句的 CREATE TABLE。然后，發(fā)出 CREATE INDEX 語句，并指定集群屬性。最后，使用 ALTER TABLE 語句添加與剛創(chuàng)建的索引對(duì)應(yīng)的主鍵。將把此索引用作主鍵索引。 通常，若集群索引是唯一的，則集群維護(hù)起來就更有效率。 ２.如何建立索引 建立索引有兩種方法： 一種是采用命令行方式，另一種是采用圖形用戶界面（GUI） (1)命令行方式 語句： CREATE <UNIQUE> INDEX <index name> ON <table name> ( column 1 <ASC | DESC> , column 2 <ASC | DESC> ... ) UNIQUE 屬性告訴 DB2，索引必須強(qiáng)制所有插入值的唯一性。 如果結(jié)果集是以升序和降序兩種方式排序的，ALLOW REVERSE SCANS 會(huì)告訴 DB2 在索引中包括附加的指針，以允許在記錄中有效地進(jìn)行正向和反向鏈接。 所創(chuàng)建的索引可以在控制中心中查看到。
DB2能夠向正在創(chuàng)建的索引中添加另外的列。CREATE INDEX 命令允許用戶指定那些不屬于實(shí)際索引但因?yàn)樾阅茉蚨４嬖谒饕涗浿械牧小?/font> CREATE UNIQUE INDEX ON EMPLOYEE (EMPNO) INCLUDE (LASTNAME,FIRSTNAME) 對(duì)于索引中包含的列而言，索引必須是 UNIQUE 的。當(dāng)創(chuàng)建索引時(shí)，另外的列被添加到索引值中。索引不使用這些值進(jìn)行排序或確定唯一性，但可以在滿足 SQL 查詢時(shí)使用它們。例如，下列 SELECT 語句將不需要讀取實(shí)際數(shù)據(jù)行： SELECT LASTNAME, FIRSTNAME FROM EMPLOYEE WHERE EMPNO < '000300'> 要在表上創(chuàng)建群集索引，將 CLUSTER 關(guān)鍵字附加到 CREATE INDEX 命令的末尾，例如： CREATE INDEX DEPTS-IX ON EMPLOYEE(WORKDEPT) CLUSTER (2)GUI方式 進(jìn)入控制中心，在左邊的樹中展開要?jiǎng)?chuàng)建索引所在的的數(shù)據(jù)庫，并點(diǎn)擊右鍵，選擇索引向?qū)А?/p> ３.如何發(fā)揮索引的功能 3.1索引的快速排序查詢 舉例： 在命令行中為表student（id int not null,name varchar(20)）新建索引： 命令如下： Sql 語句： CREATE UNIQUE INDEX INDEXTABLE ON STUDENT (ID,NAME) PCTFREE 10 MINPCTUSED 10 ALLOW REVERSE SCANS PAGE SPLIT SYMMETRIC COLLECT SAMPLED DETAILED STATISTICS 界面如下所示：
說明：要避免某些排序，只要有可能，就通過使用 CREATE UNIQUE INDEX 語句定義主鍵和唯一鍵。 要改善數(shù)據(jù)檢索，將 INCLUDE 列添加至唯一索引。合適的列為： 被頻繁訪問，因此可從純索引訪問中受益的列。 不需要用來限制索引掃描的范圍的列 不影響索引鍵的排序或唯一性的列。 要有效訪問小表，使用關(guān)系索引來優(yōu)化對(duì)含有較多數(shù)據(jù)頁的表的頻繁查詢，數(shù)據(jù)頁數(shù)記錄在 SYSCAT.TABLES 目錄視圖的 NPAGES 列中。您應(yīng)該： 根據(jù)連接表時(shí)要使用的任何一列來創(chuàng)建索引。 根據(jù)將用于定期搜索特定值的任何列來創(chuàng)建索引。 要有效地搜索，決定對(duì)鍵使用升序還是降序，這取決于將最常使用的順序。盡管當(dāng)在 CREATE INDEX 語句中指定了 ALLOW REVERSE SCANS 參數(shù)時(shí)可以按逆向方向搜索值，但是，執(zhí)行按指定索引順序的掃描比執(zhí)行逆向掃描稍微更快一些。 要保存索引維護(hù)成本和空間： 避免創(chuàng)建的關(guān)系索引是這些列上其他索引鍵的部分鍵。例如，如果列 a、b 和 c 上有索引，則列 a 和 b 上的第二個(gè)索引一般用處不大。 不要在所有列上任意創(chuàng)建關(guān)系索引。不必要的索引不僅使用空間，而且導(dǎo)致大量準(zhǔn)備時(shí)間。當(dāng)使用具有動(dòng)態(tài)編程連接枚舉的優(yōu)化級(jí)別時(shí)，這對(duì)于復(fù)雜的查詢特別重要。 使用下列一般規(guī)則來確定將為表定義的關(guān)系索引的典型數(shù)目。此數(shù)目根據(jù)數(shù)據(jù)庫的主要使用來確定： 對(duì)于聯(lián)機(jī)事務(wù)處理（OLTP）環(huán)境，創(chuàng)建一個(gè)或兩個(gè)索引 對(duì)于只讀查詢環(huán)境，可以創(chuàng)建 5 個(gè)以上索引 對(duì)于混合查詢和 OLTP 環(huán)境，可以創(chuàng)建 2 到 5 個(gè)索引。 要提高對(duì)父表執(zhí)行刪除和更新操作的性能，在外鍵上創(chuàng)建關(guān)系索引。 要提高涉及到 IMMEDIATE 和 INCREMENTAL MQT 的 DELETE 和 UPDATE 操作的性能，對(duì) MQT 的隱含唯一鍵創(chuàng)建唯一關(guān)系索引，該隱含唯一鍵是 MQT 定義的 GROUP BY 子句中的列。 對(duì)于快速排序操作，在頻繁用于排序關(guān)系數(shù)據(jù)的列上創(chuàng)建關(guān)系索引。 要提高多列關(guān)系索引的連接性能，如果第一個(gè)鍵列有多項(xiàng)選擇，則使用最常用“=”（等值連接）謂詞指定的那一列，或使用如第一個(gè)鍵那樣具有最多單值的那些列。 要幫助新插入的行根據(jù)索引進(jìn)行集群并避免頁分割，定義一個(gè)集群索引。集群索引應(yīng)顯著減少重組表的需要。 當(dāng)定義表時(shí)使用 PCTFREE 關(guān)鍵字來指定頁上應(yīng)該留下多少可用空間，才能允許將插入行適當(dāng)?shù)胤旁陧撋稀Ｒ部梢灾付?LOAD 命令的 pagefreespace MODIFIED BY 子句。 要啟用聯(lián)機(jī)索引整理碎片，創(chuàng)建關(guān)系索引時(shí)使用 MINPCTUSED 選項(xiàng)。MINPCTUSED 指定索引葉子頁中最小使用空間量的閾值并啟用聯(lián)機(jī)索引整理碎片。如果這些刪除實(shí)際上從索引頁除去鍵，則這可以在鍵刪除期間以性能損失為代價(jià)而減少重組的需要。 在新建索引之前向數(shù)據(jù)表中添加數(shù)據(jù)，添加數(shù)據(jù)之前表中的記錄為
所添加的數(shù)據(jù)為： insert into student values(8,'Hh') insert into student values(6,'a') insert into student values(7,'a') insert into student values(16,'a') insert into student values(54,'a') insert into student values(65,'a') insert into student values(14,'a') insert into student values(25,'a') insert into student values(56,'a') insert into student values(64,'a') insert into student values(27,'a') insert into student values(68,'a') insert into student values(45,'a') insert into student values(87,'a') insert into student values(47,'a') insert into student values(23,'a') insert into student values(89,'a') 可以看到student表中的id不是按照一定順序輸入的。因此在新建索引之前，插入的記錄為如下所示：
當(dāng)創(chuàng)建索引后，顯示的記錄為
檢測(cè)新建的索引，確定此索引是否是對(duì)所有以后添加的記錄同時(shí)保持這個(gè)規(guī)則： 在命令行中輸入命令：insert into student values(9,'a') 然后再輸入命令查詢表student：select * from student 界面顯示如下所示：
可以看到所添加的記錄仍然保持升序規(guī)則。
CREATE INDEX INDEXmytest ON mytest (name desc) PCTFREE 10 MINPCTUSED 10 ALLOW REVERSE SCANS PAGE SPLIT SYMMETRIC COLLECT SAMPLED DETAILED STATISTICS
在建立索引之前，從表steamwatermonitor中我們可以查看samplingtime的值，如下面所示：
對(duì)時(shí)間進(jìn)行降序排列，并對(duì)每次查詢生效。 建立索引的命令如下所示： CREATE INDEX INDEXSAMPLINGTIME ON SteamWaterMonitor (samplingTime desc) PCTFREE 10 MINPCTUSED 10 ALLOW REVERSE SCANS PAGE SPLIT SYMMETRIC COLLECT SAMPLED DETAILED STATISTICS
測(cè)試索引的有效性： 插入記錄 insert into STEAMWATERMONITOR(ACCRUVOLUME,PRESSURE,SAMPLINGTIME,STATIONID,TEMP,VOLUME) values(100,10,'2007-02-24 10:35:00',1,100,100) 再查詢samplingtime
在建立索引indexstationid之前，從表steamwatermonitor中我們可以查看stationid的值，如下面所示：
建立索引indexstationid 語句為： CREATE INDEX INDEXSTATIONID ON SteamWaterMonitor (stationid) PCTFREE 10 MINPCTUSED 10 ALLOW REVERSE SCANS PAGE SPLIT SYMMETRIC COLLECT SAMPLED DETAILED STATISTICS 查看
Sql語句測(cè)試 select * from steamwatermonitor where stationid>7 and stationid<60 顯示結(jié)果如下所示：
select * from steamwatermonitor where date(samplingtime)>date('2007-02-12') 查詢結(jié)果為
select * from steamwatermonitor where date(samplingtime) between date('2007-02-14') and date('2007-02-24') 顯示結(jié)果為：
select * from steamwatermonitor where date(samplingtime)=date('2007-02-12') select * from steamwatermonitor where date(samplingtime)=date('2007-02-12') and stationid=7 顯示結(jié)果為： |
|
|
