正確創(chuàng)建合理使用索引 提高數(shù)據(jù)庫(kù)查詢速度

【摘要】在數(shù)據(jù)庫(kù)系統(tǒng)中，一條SQL查詢語(yǔ)句的實(shí)現(xiàn)可有多種不同的表達(dá)方法，而不同的表達(dá)方法又會(huì)使數(shù)據(jù)庫(kù)系統(tǒng)的響應(yīng)速度大相徑庭。深入討論了在數(shù)據(jù)庫(kù)中，怎樣正確創(chuàng)建和使用索引，通過(guò)合理書寫SQL語(yǔ)句來(lái)優(yōu)化查詢，充分發(fā)揮索引的特性，進(jìn)一步提高數(shù)據(jù)查詢速度，使系統(tǒng)性能得到更充分有效的改善和提高。

【關(guān)鍵詞】數(shù)據(jù)庫(kù)；索引；優(yōu)化；查詢；效率

0 引言

在數(shù)據(jù)庫(kù)管理系統(tǒng)中，索引是在數(shù)據(jù)表列上建立的一種數(shù)據(jù)庫(kù)對(duì)象，是數(shù)據(jù)庫(kù)中重要的輔助數(shù)據(jù)結(jié)構(gòu)。它是可選的，如果不使用索引，所有的數(shù)據(jù)查詢將通過(guò)表掃描實(shí)現(xiàn)，這就意味著一個(gè)表中的所有數(shù)據(jù)必須都被讀入，并且與所請(qǐng)求的數(shù)據(jù)進(jìn)行逐行比較，這將產(chǎn)生大量的I/O 操作（除非要從該表中選擇絕大多數(shù)的行）。對(duì)于大型的表，執(zhí)行表掃描將消耗大量的系統(tǒng)資源。

索引的引用雖然是由系統(tǒng)決定的，但它與創(chuàng)建索引時(shí)編寫SQL語(yǔ)句的格式密切相關(guān)。一個(gè)合理的索引和一個(gè)好的SQL查詢語(yǔ)句往往可以使系統(tǒng)性能提高幾倍乃至數(shù)十倍，下面就以O(shè)racle為例來(lái)討論索引的創(chuàng)建、使用和查詢方法。

1 索引鍵和唯一索引

1.1 索引鍵。索引鍵是用于創(chuàng)建索引的列。如果要通過(guò)索引訪問(wèn)一條記錄，那就必須在SQL語(yǔ)句的WHERE子句內(nèi)部包含一個(gè)或多個(gè)索引鍵。

1.2 惟一索引。惟一索引就是要求插入到索引字段的值必須是惟一的，不允許有重復(fù)的鍵值被插入。對(duì)于復(fù)合鍵，這意味著所有列的值的組合必須是惟一的。

2 創(chuàng)建和使用索引的原則

2.1 選擇索引類型應(yīng)考慮的因素。位圖索引適用于那些基數(shù)（基數(shù)是指某個(gè)字段可能擁有的不重復(fù)數(shù)值的個(gè)數(shù)，比如sex字段的基數(shù)為2，因?yàn)樾詣e只能是男或女）比較小的字段。通常如果字段的基數(shù)只達(dá)到表中記錄數(shù)的1%，或字段中大部分值都會(huì)重復(fù)出現(xiàn)100次以上，則對(duì)該字段應(yīng)建立位圖索引。此外，某些字段雖然有比較高的基數(shù)，也不會(huì)出現(xiàn)很多重復(fù)值，但經(jīng)常會(huì)被具有復(fù)雜查詢重要條件的WHERE子句引用，也應(yīng)為其建立位圖索引。而B+樹索引則適用于那些具有高基數(shù)的字段，特別是那些具有PRIMARY KEY、UNIQUE約束的不能具有重復(fù)值的字段。如果字段的特性介于上述兩2種情形之間，則（1）考慮是否需要節(jié)省存儲(chǔ)空間。B+樹索引將占用大量的存儲(chǔ)空間；而位圖索引通常只占用很少的存儲(chǔ)空間。（2）對(duì)于一些特殊類型的查詢語(yǔ)句，位圖索引能比B+樹索引更有效地提高查詢速度。比如，如果在查詢的WHERE子句中包含AND、OR等邏輯運(yùn)算符，使用位圖索引可以極大地提高查詢的執(zhí)行速度，因?yàn)檫壿嬤\(yùn)算可以直接在位圖索引內(nèi)部完成。（3）對(duì)于另外一些查詢語(yǔ)句，B+樹索引能比位圖索引更有效地提高查詢速度。比如，那些經(jīng)常會(huì)使用“<”、“>”等比較運(yùn)算符進(jìn)行查詢的字段，應(yīng)當(dāng)使用B+樹索引而不是位圖索引，這是由B+樹數(shù)據(jù)結(jié)構(gòu)的搜索特性決定的。（4）在位圖索引中可以記錄具有NULL值的字段，而在B+樹索引中將忽略所有NULL值字段。因此，如果某個(gè)字段需要進(jìn)行與NULL值相關(guān)的查詢，應(yīng)當(dāng)為它創(chuàng)建位圖索引。

2.2 創(chuàng)建索引的原則。（1）當(dāng)表主要是為了查詢時(shí)，可以考慮創(chuàng)建索引；否則在數(shù)據(jù)頻繁插入、更新、刪除時(shí)系統(tǒng)要花費(fèi)大量的時(shí)間開銷來(lái)維護(hù)在索引上的操作，反而影響性能。（2）應(yīng)該為較大的表創(chuàng)建索引。表越大索引的作用就越顯著，效率也越高。（3）對(duì)一個(gè)表可建立多個(gè)索引，但建議一般最多不超過(guò)5個(gè)。因?yàn)樘嗟乃饕粌H要占用更多的磁盤空間，而且還會(huì)由于較大的索引維護(hù)工作而降低插入、更新及刪除的速度。（4）創(chuàng)建索引時(shí)應(yīng)選擇適當(dāng)?shù)牧校?）經(jīng)常要查詢的列；2）選擇性比較好的列；3）經(jīng)常要進(jìn)行排序和分組的列；4）經(jīng)常用于多表連接的列；5）在WHERE子句中頻繁使用的列。（5）創(chuàng)建索引應(yīng)選擇適當(dāng)?shù)谋砜臻g。在創(chuàng)建索引時(shí)可以把索引存放在任何表空間中，默認(rèn)情況下Oracle會(huì)自動(dòng)把索引和它所對(duì)應(yīng)的基表存放在同一個(gè)空間內(nèi)。但在創(chuàng)建索引時(shí)也可以顯式地指定存儲(chǔ)表空間，可以把索引存放在與其基表不同硬盤上的不同表空間中，這樣比和基表同在一個(gè)表空間內(nèi)更能提高查詢速度。因?yàn)檫@樣Oracle能夠并行讀取不同硬盤中的數(shù)據(jù)，避免產(chǎn)生I/O沖突。

2.3 不使用索引的準(zhǔn)則。在下列情況下，一般不使用索引：（1）數(shù)據(jù)記錄不多的表；（2）如果查詢經(jīng)常返回的行數(shù)超過(guò)總行數(shù)的10%；（3）如果被索引的列太長(zhǎng)（如超過(guò)50個(gè)字節(jié)）。

2.4 復(fù)合索引的應(yīng)用。復(fù)合索引允許用戶在同一張表上的2個(gè)或多個(gè)列上建立索引，是具有多列排碼的索引。對(duì)于經(jīng)常需要將多個(gè)列作為一個(gè)整體同時(shí)查詢的場(chǎng)合，復(fù)合索引往往要比創(chuàng)建多個(gè)單列索引更加可取。其優(yōu)點(diǎn)如下：（1）在數(shù)據(jù)處理過(guò)程中比多個(gè)單列索引開銷要少；（2）對(duì)數(shù)據(jù)修改語(yǔ)句的性能影響較小；（3）能有效地減少表中索引的總數(shù)，充分節(jié)約磁盤空間；（4）在表中沒(méi)有單獨(dú)的任何列能夠惟一確定數(shù)據(jù)行，而是用所有索引列鍵值的組合來(lái)惟一確定數(shù)據(jù)行時(shí)，使用具有多個(gè)綜合附加列的復(fù)合索引可以強(qiáng)化索引的惟一性，因此可以制造更好的機(jī)會(huì)來(lái)加速數(shù)據(jù)存取。

3 充分利用索引特性提高查詢效率

對(duì)索引的引用是Oracle自動(dòng)進(jìn)行的，數(shù)據(jù)庫(kù)系統(tǒng)把已建好的索引作為一類資源，在存取操作時(shí)自動(dòng)判定能否使用這些資源。在這個(gè)過(guò)程中索引是否能被有效地引用，是Oracle系統(tǒng)優(yōu)化查詢的關(guān)鍵。而一個(gè)SQL查詢語(yǔ)句又可以有很多種執(zhí)行策略，Oracle優(yōu)化器將選擇出全部的執(zhí)行方法中所需時(shí)間最少，也就是所謂成本最低的一種方法。SQL語(yǔ)句的書寫格式將直接關(guān)系到系統(tǒng)對(duì)索引的引用，因?yàn)镺racle所有優(yōu)化的進(jìn)行都是基于SQL語(yǔ)句中的WHERE子句。盡管現(xiàn)在數(shù)據(jù)庫(kù)產(chǎn)品在查詢優(yōu)化方面已經(jīng)做得越來(lái)越好，但它對(duì)查詢數(shù)據(jù)的對(duì)象特性并不十分了解，難免要帶有一定的盲目性。為了給數(shù)據(jù)庫(kù)應(yīng)用設(shè)計(jì)者提供更強(qiáng)更有效的控制手段，以便靈活決定哪些查詢啟動(dòng)索引，啟動(dòng)哪些索引；怎樣才能更充分地發(fā)揮索引的作用，以及如何使查詢策略得到最佳的優(yōu)化，Oracle提供了許多約定，用于根據(jù)可能的查詢結(jié)果選擇最佳查詢方案。

4 結(jié)語(yǔ)

據(jù)統(tǒng)計(jì)，約有80%以上的性能問(wèn)題都是由于使用了不恰當(dāng)?shù)牟樵冋Z(yǔ)句造成的。因此，了解SQL語(yǔ)句的執(zhí)行和優(yōu)化過(guò)程，提高SQL語(yǔ)句的質(zhì)量對(duì)提高系統(tǒng)性能有很大幫助。然而查詢語(yǔ)句的好壞往往同實(shí)際數(shù)據(jù)庫(kù)中表的結(jié)構(gòu)、記錄的數(shù)量以及字段的取值等多種因素有關(guān)，所以無(wú)法只用幾條簡(jiǎn)單的普遍適用的規(guī)律來(lái)總結(jié)優(yōu)化查詢語(yǔ)句。但首先應(yīng)對(duì)Oracle最基本的工作過(guò)程和規(guī)律有一個(gè)了解；并采取適當(dāng)?shù)牟樵儾呗裕浞掷盟饕匦裕瑸椴樵兲峁┮粋€(gè)最佳的執(zhí)行方案，使系統(tǒng)性能得到有效的改善和提高。

參考文獻(xiàn)：

[1]顧 誠(chéng).Oracle 數(shù)據(jù)庫(kù)系統(tǒng)應(yīng)用開發(fā)[M].北京：電子工業(yè)出版社，1998.

[2]David Lockman.Oracle8數(shù)據(jù)庫(kù)開發(fā)[M].北京：電子工業(yè)出版社，1999.

[3]林存德.Oracle 8i for NT DBA培訓(xùn)手冊(cè)[M].北京：北京大學(xué)出版社，2001.

[4]Steve Lemme John R.Colby.Oracle 數(shù)據(jù)庫(kù)的實(shí)施和管理[M].北京：機(jī)械工業(yè)出版社，2001.

[5]Edward Whalen Mitchell Schroeter.Oracle 性能調(diào)整與優(yōu)化[M].北京：人民郵電出版社，2002.