淺析索引在SQL語(yǔ)句中的使用技巧

夏嶺梅　王一鵬

摘 要：隨著信息化的不斷深入發(fā)展，數(shù)據(jù)的量呈現(xiàn)出幾何級(jí)增長(zhǎng)。而伴隨著數(shù)據(jù)量的不斷攀升，如何提高對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)性能成為每個(gè)信息系統(tǒng)的重點(diǎn)優(yōu)化方向。索引技術(shù)是數(shù)據(jù)庫(kù)性能優(yōu)化中最常用的技術(shù)手段。以目前最流行的Oracle數(shù)據(jù)庫(kù)為例，對(duì)數(shù)據(jù)庫(kù)中索引的使用進(jìn)行重點(diǎn)說(shuō)明和舉例，使讀者對(duì)SQL語(yǔ)言中的索引技術(shù)有較深的了解。

關(guān)鍵詞：索引；SQL語(yǔ)言；優(yōu)化；數(shù)據(jù)庫(kù)

中圖分類號(hào)：TN911 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：2095-1302（2015）04-00-03

0 引 言

在DBMS中，索引是訪問(wèn)數(shù)據(jù)庫(kù)中數(shù)據(jù)的重要手段，特別是在大型系統(tǒng)中，更是必備方法。如果說(shuō)數(shù)據(jù)庫(kù)是一本字典，其中的數(shù)據(jù)是字典的內(nèi)容，那么，索引就是這本字典的目錄。查字典可以使用偏旁部首、漢語(yǔ)拼音、筆畫等不同的方法進(jìn)行查找，查詢數(shù)據(jù)庫(kù)中的數(shù)據(jù)也有各種各樣的索引可供使用。在數(shù)據(jù)庫(kù)中，索引使用DML操作可以迅速找到表數(shù)據(jù)，不用對(duì)整張表逐行訪問(wèn)。索引是一種結(jié)構(gòu)很強(qiáng)的方案對(duì)象，它將數(shù)據(jù)和存放數(shù)據(jù)的位置對(duì)應(yīng)起來(lái)。對(duì)于數(shù)據(jù)量非常大的表來(lái)說(shuō)，通過(guò)全表掃描來(lái)訪問(wèn)和通過(guò)索引來(lái)訪問(wèn)，速度可能有是數(shù)量級(jí)上的差別。

索引能通過(guò)事先保存的索引鍵，按照一定順序記錄數(shù)據(jù)的位置，由此替代DML操作原本要進(jìn)行的全表掃描，通過(guò)“以空間換時(shí)間”的方法，犧牲少量的存儲(chǔ)空間，換取快速的反應(yīng)時(shí)間。在一張表上是否該建立索引、建立怎樣的索引、怎樣才能有效利用建立起的索引是數(shù)據(jù)庫(kù)優(yōu)化中最常見的問(wèn)題。

1 SQL語(yǔ)句的執(zhí)行過(guò)程

是否會(huì)使用索引要從Oracle執(zhí)行查詢的機(jī)制開始分析。在Oracle進(jìn)行必要的語(yǔ)法檢查和語(yǔ)義分析之后，會(huì)自動(dòng)對(duì)語(yǔ)句進(jìn)行優(yōu)化，其目的就是找到最高效的運(yùn)行路徑。可能會(huì)使用的優(yōu)化器有兩種：RBO基于規(guī)則的優(yōu)化器，CBO基于成本的優(yōu)化器。默認(rèn)情況下是CBO優(yōu)化器，它會(huì)快速統(tǒng)計(jì)數(shù)據(jù)量的大小， 選擇開銷最小 （盡量消耗最少的CPU和I/O）的執(zhí)行計(jì)劃。確定了執(zhí)行計(jì)劃之后，Oracle會(huì)將SQL語(yǔ)句格式化為內(nèi)部執(zhí)行代碼。

由此可見，想要提高SQL語(yǔ)句的性能，就要從優(yōu)化器會(huì)選擇怎樣的執(zhí)行計(jì)劃這塊考慮。如果表上沒(méi)有建立索引，執(zhí)行計(jì)劃就是全表掃描，它會(huì)根據(jù)PGA中的系統(tǒng)設(shè)定，讀取一批數(shù)據(jù)塊，如果表數(shù)據(jù)較小，全表可以一次性全部讀入；如果數(shù)據(jù)量很大，則需要多次讀取數(shù)據(jù)塊。如果在數(shù)據(jù)量稍大的表上建立了索引，優(yōu)化器會(huì)比較利用索引和全表掃描的讀取數(shù)據(jù)次數(shù)，如果利用索引代價(jià)小，則選擇利用索引，否則還是選擇全表掃描。一般來(lái)說(shuō)，查詢語(yǔ)句要讀取10%以上的數(shù)據(jù)量的話，優(yōu)化器就寧愿選擇全表掃描，而不是索引。因?yàn)橐粋€(gè)索引項(xiàng)指向了一個(gè)數(shù)據(jù)塊，所要讀取的數(shù)據(jù)塊太多，就有大量的I/O操作要進(jìn)行，反而降低性能。

2 索引的分類

建立怎樣的索引。常見的索引有以下幾種：B*索引（包含唯一索引），位圖索引，函數(shù)索引，本地前綴分區(qū)索引，全局范圍分區(qū)索引等。

（1）B*樹索引

B*樹索引就是利用建立B樹，將索引列和指向表中各行的rowid組織起來(lái)，按照順序建立一顆平衡樹，根據(jù)索引列的數(shù)量，自動(dòng)建立合理高度的B樹。在B樹索引中，所有葉子的深度一樣，結(jié)構(gòu)自動(dòng)保持平衡，在增刪改之后都由Oracle自動(dòng)維護(hù)。更重要的是，B樹索引可以適應(yīng)多種查詢條件，包含范圍查找>、<、>=、<=、like和完全匹配。由于樹的高度是基于索引列的數(shù)量，當(dāng)訪問(wèn)的數(shù)據(jù)量占總數(shù)據(jù)的10%以上的時(shí)候，需要訪問(wèn)B樹的范圍太大，這個(gè)時(shí)候，其優(yōu)勢(shì)無(wú)法體現(xiàn)。

（2）位圖索引

當(dāng)某一列上的值可選擇性較低的時(shí)候，一般使用位圖索引。所謂的“可選擇性”就是指查詢這一列的distinct值與這一列所有值的比例，這個(gè)比值越高，說(shuō)明可選擇性越高。對(duì)于 “性別”列，只存在兩種值，可選擇性低；“姓名”列，可選擇性一般較高。

（3）函數(shù)索引

通過(guò)在某一列上利用函數(shù)來(lái)創(chuàng)建索引。例如：create INDEX idx_func_sub on emp substr（empno，1，2）；就是利用substr函數(shù)，在empno上建立索引。

3 使用索引的方法和技巧

（1）對(duì)于索引的建立，我們一般選擇在可選擇性高的列上建立索引。以地稅系統(tǒng)的一張登記表為例，這張表記錄了納稅人的登記信息，一個(gè)納稅人識(shí)別號(hào)對(duì)應(yīng)一條記錄，納稅人所屬的區(qū)縣對(duì)應(yīng)字段dwid。在識(shí)別號(hào)sbh和區(qū)縣dwid上分別建立索引IDX_SWJ_SBH和IDX_SWJ_dwid。如果有查詢語(yǔ)句：

select * from glfw_swdj_tempwyp where sbh=12345 and dwid=54321；

查看執(zhí)行計(jì)劃如圖1所示，執(zhí)行計(jì)劃沒(méi)有使用IDX_SWJ_dwid，因?yàn)閟bh列的可選性要遠(yuǎn)高于dwid字段。

（2）如果查詢條件中包含多列，則在多列上建立復(fù)合索引效果更好。還是以上面的SQL語(yǔ)句為例， glfw_swdj_tempwyp表上，建立基于sbh和dwid的復(fù)合索引

create index IDX_SWJ_SBH_DWID on GLFW_SWDJ_TEMPWYP （SBH， DWID）；

這個(gè)時(shí)候，新的執(zhí)行計(jì)劃如圖2所示。此時(shí)，利用條件語(yǔ)句中的兩列復(fù)合索引，顯然要比單一索引效率高。

（3）在建立復(fù)合索引的時(shí)候需要注意，復(fù)合索引的列的順序很重要，最好依據(jù)各列的可選擇性，由高到低排序。這樣，通過(guò)B索引樹進(jìn)行訪問(wèn)的時(shí)候，由復(fù)合索引的第一列就能過(guò)濾掉更多的數(shù)據(jù)，使得I/O地讀取更小。有些觀點(diǎn)認(rèn)為，如果在SQL語(yǔ)句中沒(méi)有使用復(fù)合索引的前導(dǎo)列，則查詢不會(huì)利用這個(gè)復(fù)合索引。這種說(shuō)法是錯(cuò)誤的。Oracle對(duì)于索引的訪問(wèn)分為索引唯一掃描（index unique scan）、跳躍式索引掃描（index skip scan）、索引范圍掃描（index range scan）、索引全掃描（index full scan）等多種方式。就算條件語(yǔ)句中沒(méi)有利用前導(dǎo)列，跳躍式索引掃描（index skip scan）依然起效。這種訪問(wèn)方式，通過(guò)將前導(dǎo)列分為各個(gè)不同的區(qū)域，在各區(qū)域內(nèi)部使用復(fù)合索引的剩余部分來(lái)訪問(wèn)數(shù)據(jù)，最后將各個(gè)區(qū)域內(nèi)符合條件的數(shù)據(jù)做union操作，得出結(jié)果集。

（4）對(duì)于多表連接時(shí)，需要建立復(fù)合索引的情況下，首先要判斷驅(qū)動(dòng)表和被驅(qū)動(dòng)表。驅(qū)動(dòng)表就是查詢范圍較少的表，以它作為嵌套連接nested loops的外層循環(huán)，被驅(qū)動(dòng)表作為內(nèi)存循環(huán)。執(zhí)行時(shí)，從驅(qū)動(dòng)表中選取一個(gè)結(jié)果，與被驅(qū)動(dòng)表匹配，匹配上的就并入結(jié)果集，再選取驅(qū)動(dòng)表的下一個(gè)結(jié)果，依次往后。這個(gè)時(shí)候，可以在被驅(qū)動(dòng)表的連接字段和該表的其它約束條件上建立復(fù)合索引，這樣，就能提高內(nèi)層循環(huán)的效率。以地稅局的應(yīng)征表為例，當(dāng)應(yīng)征表（數(shù)據(jù)量很大）與稅種代碼表（數(shù)據(jù)量很小）發(fā)生關(guān)聯(lián)時(shí)，稅種代碼表dm_shuizhong_wyp是驅(qū)動(dòng)表，在應(yīng)征表上建立關(guān)聯(lián)字段（szdm）和其它約束字段（szpq）的聯(lián)合主鍵，還在應(yīng)征表上建立szdm的單一主鍵，查看執(zhí)行計(jì)劃，發(fā)現(xiàn)優(yōu)化器選擇的是復(fù)合索引，如圖3所示。

4 結(jié) 語(yǔ)

索引不是越多越好，建立索引要遵循以下兩點(diǎn)：

（1）不需要為小表建立索引，這種表還不如采用全表掃描，先讀索引再根據(jù)索引讀數(shù)據(jù)反而麻煩；

（2）根據(jù)具體的業(yè)務(wù)需求，只在經(jīng)常使用的列上添加索引，根據(jù)可選性的不同，在可選擇性高的列上建立B樹索引（例如員工編號(hào)字段），在可選擇性較小的列上建立位圖索引（例如性別字段），不常使用的列可以不建立索引。

參考文獻(xiàn)

[1]路川，胡欣杰，閻文麗.Oracle 10g寶典[M].北京：電子工業(yè)出版社，2010.

[2]羅敏.品悟性能優(yōu)化 [M].北京：清華大學(xué)出版社，2011.

[3]蓋國(guó)強(qiáng).深入淺出Oracle [M].人民郵電出版社，2006.