淺析索引在SQL語句中的使用技巧
引 言
在DBMS 中,索引是訪問數(shù)據(jù)庫中數(shù)據(jù)的重要手段,特別是在大型系統(tǒng)中,更是必備方法。如果說數(shù)據(jù)庫是一本字典, 其中的數(shù)據(jù)是字典的內(nèi)容,那么,索引就是這本字典的目錄。查字典可以使用偏旁部首、漢語拼音、筆畫等不同的方法進行查找,查詢數(shù)據(jù)庫中的數(shù)據(jù)也有各種各樣的索引可供使用。在數(shù)據(jù)庫中,索引使用DML 操作可以迅速找到表數(shù)據(jù),不用對整張表逐行訪問。索引是一種結(jié)構(gòu)很強的方案對象,它將數(shù)據(jù)和存放數(shù)據(jù)的位置對應(yīng)起來。對于數(shù)據(jù)量非常大的表來說,通過全表掃描來訪問和通過索引來訪問,速度可能有是數(shù)量級上的差別。
索引能通過事先保存的索引鍵,按照一定順序記錄數(shù)據(jù)的位置,由此替代 DML 操作原本要進行的全表掃描,通過“以空間換時間”的方法,犧牲少量的存儲空間,換取快速的反應(yīng)時間。在一張表上是否該建立索引、建立怎樣的索引、怎樣才能有效利用建立起的索引是數(shù)據(jù)庫優(yōu)化中最常見的問題。
1 SQL語句的執(zhí)行過程
是否會使用索引要從Oracle 執(zhí)行查詢的機制開始分析。在Oracle 進行必要的語法檢查和語義分析之后,會自動對語句進行優(yōu)化,其目的就是找到最高效的運行路徑??赡軙褂玫膬?yōu)化器有兩種 :RBO 基于規(guī)則的優(yōu)化器,CBO 基于成本的優(yōu)化器。默認情況下是 CBO 優(yōu)化器,它會快速統(tǒng)計數(shù)據(jù)量的大小,選擇開銷最小(盡量消耗最少的 CPU 和I/O)的執(zhí)行計劃。確定了執(zhí)行計劃之后,Oracle 會將SQL 語句格式化為內(nèi)部執(zhí)行代碼。
由此可見,想要提高SQL 語句的性能,就要從優(yōu)化器會選擇怎樣的執(zhí)行計劃這塊考慮。如果表上沒有建立索引,執(zhí)行計劃就是全表掃描,它會根據(jù)PGA 中的系統(tǒng)設(shè)定,讀取一批數(shù)據(jù)塊,如果表數(shù)據(jù)較小,全表可以一次性全部讀入 ;如果數(shù)據(jù)量很大,則需要多次讀取數(shù)據(jù)塊。如果在數(shù)據(jù)量稍大的表上建立了索引,優(yōu)化器會比較利用索引和全表掃描的讀取數(shù)據(jù)次數(shù),如果利用索引代價小,則選擇利用索引,否則還是選擇全表掃描。一般來說,查詢語句要讀取 10% 以上的數(shù)據(jù)量的話,優(yōu)化器就寧愿選擇全表掃描,而不是索引。因為一個索引項指向了一個數(shù)據(jù)塊,所要讀取的數(shù)據(jù)塊太多,就有大量的I/O 操作要進行,反而降低性能。
2 索引的分類
建立怎樣的索引。常見的索引有以下幾種 :B* 索引(包含唯一索引),位圖索引,函數(shù)索引,本地前綴分區(qū)索引,全局范圍分區(qū)索引等。
(1) B*樹索引
B* 樹索引就是利用建立B 樹,將索引列和指向表中各行的 rowid 組織起來,按照順序建立一顆平衡樹,根據(jù)索引列的數(shù)量,自動建立合理高度的B 樹。在B 樹索引中,所有葉子的深度一樣,結(jié)構(gòu)自動保持平衡,在增刪改之后都由Oracle 自動維護。更重要的是,B 樹索引可以適應(yīng)多種查詢條件,包含范圍查找 >、<、>=、<=、like 和完全匹配。由于樹的高度是基于索引列的數(shù)量,當訪問的數(shù)據(jù)量占總數(shù)據(jù)的 10% 以上的時候,需要訪問 B 樹的范圍太大,這個時候,其優(yōu)勢無法體現(xiàn)。
(2) 位圖索引
當某一列上的值可選擇性較低的時候,一般使用位圖索引。所謂的“可選擇性”就是指查詢這一列的 distinct 值與這一列所有值的比例,這個比值越高,說明可選擇性越高。對于“性別”列,只存在兩種值,可選擇性低 ;“姓名”列,可選擇性一般較高。
(3) 函數(shù)索引
通過在某一列上利用函數(shù)來創(chuàng)建索引。例如 :createINDEX idx_func_sub on emp substr(empno,1,2);就是利用substr 函數(shù),在empno上建立索引。
3 使用索引的方法和技巧
(1) 對于索引的建立,我們一般選擇在可選擇性高的列上建立索引。以地稅系統(tǒng)的一張登記表為例,這張表記錄了納稅人的登記信息,一個納稅人識別號對應(yīng)一條記錄,納稅人所屬的區(qū)縣對應(yīng)字段dwid。在識別號 sbh和區(qū)縣 dwid上分別建立索引IDX_SWJ_SBH和IDX_SWJ_dwid。如果有查詢語句:
select * from glfw_swdj_tempwyp where sbh=’12345’ and dwid=’54321’;
查看執(zhí)行計劃如圖 1所示, 執(zhí)行計劃沒有使用IDX_ SWJ_dwid,因為sbh列的可選性要遠高于dwid字段。
圖1在SBH和DWID上建立索引后的執(zhí)行計劃
(2) 如果查詢條件中包含多列,則在多列上建立復合索引效果更好。還是以上面的 SQL語句為例,glfw_swdj_ tempwyp 表上,建立基于sbh和dwid 的復合索引
create index IDX_SWJ_SBH_DWID on GLFW_SWDJ_ TEMPWYP(SBH,DWID);
這個時候,新的執(zhí)行計劃如圖 2 所示。此時,利用條件語句中的兩列復合索引,顯然要比單一索引效率高。
圖 2建立符合索引的執(zhí)行計劃
(3) 在建立復合索引的時候需要注意,復合索引的列的順序很重要,最好依據(jù)各列的可選擇性,由高到低排序。這樣,通過B索引樹進行訪問的時候,由復合索引的第一列就能過濾掉更多的數(shù)據(jù),使得I/O地讀取更小。有些觀點認為, 如果在 SQL語句中沒有使用復合索引的前導列,則查詢不會利用這個復合索引。這種說法是錯誤的。Oracle對于索引的訪問分為索引唯一掃描(indexuniquescan)、跳躍式索引掃描(index skip scan)、索引范圍掃描(index range scan)、索引全掃描(index full scan)等多種方式。就算條件語句中沒有利用前導列,跳躍式索引掃描(index skip scan)依然起效。這種訪問方式,通過將前導列分為各個不同的區(qū)域,在各區(qū)域內(nèi)部使用復合索引的剩余部分來訪問數(shù)據(jù),最后將各個區(qū)域內(nèi)符合條件的數(shù)據(jù)做union 操作,得出結(jié)果集。
(4) 對于多表連接時,需要建立復合索引的情況下,首先要判斷驅(qū)動表和被驅(qū)動表。驅(qū)動表就是查詢范圍較少的表, 以它作為嵌套連接 nested loops 的外層循環(huán),被驅(qū)動表作為內(nèi)存循環(huán)。執(zhí)行時,從驅(qū)動表中選取一個結(jié)果,與被驅(qū)動表匹配, 匹配上的就并入結(jié)果集,再選取驅(qū)動表的下一個結(jié)果,依次往后。這個時候,可以在被驅(qū)動表的連接字段和該表的其它約束條件上建立復合索引,這樣,就能提高內(nèi)層循環(huán)的效率。以地稅局的應(yīng)征表為例,當應(yīng)征表(數(shù)據(jù)量很大)與稅種代碼表(數(shù)據(jù)量很小)發(fā)生關(guān)聯(lián)時,稅種代碼表 dm_shuizhong_ wyp 是驅(qū)動表,在應(yīng)征表上建立關(guān)聯(lián)字段(szdm)和其它約束字段(szpq)的聯(lián)合主鍵,還在應(yīng)征表上建立 szdm 的單一主鍵,查看執(zhí)行計劃,發(fā)現(xiàn)優(yōu)化器選擇的是復合索引,如圖 3 所示。
圖 3 在表連接的連接字段上建立復合索引
對于建立的索引,在編寫SQL 的時候要注意條件語句的寫法,以免索引不被使用:
(1)在 where 條件中對索引列使用了函數(shù),導致不能使用索引。
例如 在 應(yīng)征 表 上 對 glbm 建 立 索引 INX_YZTEST_ GLBM,比較使用substr 函數(shù)的區(qū)別。
使用函數(shù)后如圖 4 所示。
圖 4 在索引字段上使用了函數(shù)時的執(zhí)行計劃
沒有使用函數(shù)的如圖 5 所示。
圖 5 沒有在索引字段上使用函數(shù)的執(zhí)行計劃
很明顯,對索引所在的列使用函數(shù),會導致索引無法使用。引起索引失效的函數(shù)還有decode、instr 等。索引如果真的要在條件語句中添加某些函數(shù)操作,可以在等號的另一邊使用帶“%”的模糊匹配,如圖 6 所示。當然,由此也可以看見,使用like 的模糊匹配對索引的利用并沒有完全匹配,原因也是顯然的,查詢的范圍更大,匹配的結(jié)果更多。
圖 6 在等號的另一邊使用模糊匹配
但是,模糊匹配的時候“,%”不要加在匹配字符串的開始,否則索引仍然不起效,如圖 7 所示。
圖 7 將“%”放在模糊匹配的前端,無法利用索引
這是因為,模糊匹配過程中,索引會按照順序采取“部分” 匹配原則 :先選擇首字符為“1”的進行匹配,再在結(jié)果中選擇第二個字符為“2”的繼續(xù)匹配……但是如果模糊匹配一開始就要用“%”,則相當在最開始,所有結(jié)果都匹配上了,這時候索引就“罷工”了。
(2)在寫SQL 語句的時候,要注意所比較的是字符串還是數(shù)字,例如,glbm 的字段類型是 char,如果在條件語句中將它與 54321 而不是‘54321’比較,則 Oracle 會通過內(nèi)部轉(zhuǎn)換, 將整個SQL 變?yōu)?:select a.* from sb_yzss_temp_wyp a where TO_NUMBER(a.glbm)>54321,由此,導致在索引列利用了函數(shù),索引失效:
select a.* from sb_ yzss_temp_wyp a where a.glbm
>54321 ;
對于建立在數(shù)字類型字段上的索引,在條件語句中不要對數(shù)字字段進行算術(shù)運算,否則可能導致索引失效,例如在應(yīng)征表的 nse 這個數(shù)字類型字段上建立索引,沒有對 nse 進行算術(shù)運算時,索引可用:
selecta.*fromsb_yzss_temp_wypawhere a.nse>10000000.00
一旦加上了算術(shù)運算,索引就失效了:
select a.* from sb_ yzss_temp_wyp a where a.nse*1>10000000.00
(3)IS NULL 和 IS not NULL 的邏輯比較,也會導致索引失效。
4 結(jié) 語
索引不是越多越好,建立索引要遵循以下兩點:
(1) 不需要為小表建立索引,這種表還不如采用全表掃描,先讀索引再根據(jù)索引讀數(shù)據(jù)反而麻煩;
(2) 根據(jù)具體的業(yè)務(wù)需求,只在經(jīng)常使用的列上添加索引,根據(jù)可選性的不同,在可選擇性高的列上建立 B樹索引(例如員工編號字段),在可選擇性較小的列上建立位圖索引(例如性別字段),不常使用的列可以不建立索引。