淺析索引在SQL語句中的使用技巧

夏嶺梅　王一鵬

摘 要：隨著信息化的不斷深入發展，數據的量呈現出幾何級增長。而伴隨著數據量的不斷攀升，如何提高對數據庫的訪問性能成為每個信息系統的重點優化方向。索引技術是數據庫性能優化中最常用的技術手段。以目前最流行的Oracle數據庫為例，對數據庫中索引的使用進行重點說明和舉例，使讀者對SQL語言中的索引技術有較深的了解。

關鍵詞：索引；SQL語言；優化；數據庫

中圖分類號：TN911 文獻標識碼：A 文章編號：2095-1302（2015）04-00-03

0 引 言

在DBMS中，索引是訪問數據庫中數據的重要手段，特別是在大型系統中，更是必備方法。如果說數據庫是一本字典，其中的數據是字典的內容，那么，索引就是這本字典的目錄。查字典可以使用偏旁部首、漢語拼音、筆畫等不同的方法進行查找，查詢數據庫中的數據也有各種各樣的索引可供使用。在數據庫中，索引使用DML操作可以迅速找到表數據，不用對整張表逐行訪問。索引是一種結構很強的方案對象，它將數據和存放數據的位置對應起來。對于數據量非常大的表來說，通過全表掃描來訪問和通過索引來訪問，速度可能有是數量級上的差別。

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

1 SQL語句的執行過程

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

由此可見，想要提高SQL語句的性能，就要從優化器會選擇怎樣的執行計劃這塊考慮。如果表上沒有建立索引，執行計劃就是全表掃描，它會根據PGA中的系統設定，讀取一批數據塊，如果表數據較小，全表可以一次性全部讀入；如果數據量很大，則需要多次讀取數據塊。如果在數據量稍大的表上建立了索引，優化器會比較利用索引和全表掃描的讀取數據次數，如果利用索引代價小，則選擇利用索引，否則還是選擇全表掃描。一般來說，查詢語句要讀取10%以上的數據量的話，優化器就寧愿選擇全表掃描，而不是索引。因為一個索引項指向了一個數據塊，所要讀取的數據塊太多，就有大量的I/O操作要進行，反而降低性能。

2 索引的分類

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

（1）B*樹索引

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

（2）位圖索引

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

（3）函數索引

通過在某一列上利用函數來創建索引。例如：create INDEX idx_func_sub on emp substr（empno，1，2）；就是利用substr函數，在empno上建立索引。

3 使用索引的方法和技巧

（1）對于索引的建立，我們一般選擇在可選擇性高的列上建立索引。以地稅系統的一張登記表為例，這張表記錄了納稅人的登記信息，一個納稅人識別號對應一條記錄，納稅人所屬的區縣對應字段dwid。在識別號sbh和區縣dwid上分別建立索引IDX_SWJ_SBH和IDX_SWJ_dwid。如果有查詢語句：

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

查看執行計劃如圖1所示，執行計劃沒有使用IDX_SWJ_dwid，因為sbh列的可選性要遠高于dwid字段。

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

create index IDX_SWJ_SBH_DWID on GLFW_SWDJ_TEMPWYP （SBH， DWID）；

這個時候，新的執行計劃如圖2所示。此時，利用條件語句中的兩列復合索引，顯然要比單一索引效率高。

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

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

4 結 語

索引不是越多越好，建立索引要遵循以下兩點：

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

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

參考文獻

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

[2]羅敏.品悟性能優化 [M].北京：清華大學出版社，2011.

[3]蓋國強.深入淺出Oracle [M].人民郵電出版社，2006.