怎么理解oracle復(fù)合索引

這篇文章主要講解了“怎么理解oracle復(fù)合索引”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“怎么理解oracle復(fù)合索引”吧！

首先,在大多數(shù)情況下,復(fù)合索引比單字段索引好.以稅務(wù)系統(tǒng)的SB_ZSXX(申報(bào)類_征收信息表)為例,該表為稅務(wù)系統(tǒng)最大的交易表.如果分別按納稅人識(shí)別號(hào),稅務(wù)機(jī)關(guān)代碼,月份3個(gè)字段查詢,每個(gè)字段在該表中的可選性或約束性都不強(qiáng),如一個(gè)納稅人識(shí)別號(hào)有很多納稅記錄,一個(gè)稅務(wù)機(jī)關(guān)代碼和同一月份記錄就更多了,所以3個(gè)字段合起來,"某個(gè)納稅人識(shí)別號(hào)+某個(gè)稅務(wù)機(jī)關(guān)代碼+某月"的記錄就少多了.因此復(fù)合索引比單字段索引的效率高多了.很多系統(tǒng)就是靠新建一些合適的復(fù)合索引,使效率大幅度提高.

      但是,復(fù)合索引比單字段索引的內(nèi)容原理復(fù)雜,復(fù)合索引有兩個(gè)重要原則需要把握: 前綴性和可選性.如果糊里糊涂的濫用復(fù)合索引,效果適得其反.

以例子來說明,例子如下:

      假設(shè)在員工表(emp)的(ename,job,mgr)3個(gè)字段上建了一個(gè)索引,例如索引名叫idx_1.3個(gè)字段分別為員工姓名,工作和所屬經(jīng)理號(hào).然后,寫如下一個(gè)查詢語句,并不斷進(jìn)行查詢條件和次序的排列組合,例如:

Sql代碼  

select * from emp where ename = 'a' and job = 'b' and mgr = 3 ;  

select * from emp where job = 'b' and ename = 'a' and mgr = 3 ;  

select * from emp where mgr = 3 and ename = 'a' and job = 'b' ;  

select * from emp where mgr = 3 and job = 'b' and ename = 'a' ;  

select * from emp where job = 'b' and mgr = 3 and ename = 'a' ;  

.....  

回答問題:在各種條件組合情況下,剛才建的索引(idx_1) 是用還是不用?也就是說對(duì)emp表的訪問是全表掃描還是按索引(idx_1)訪問?

答案是 :  上述語句中只要有ename='a'條件,就能用上索引(ind_1),而不是全表掃描(這就是復(fù)合索引的前綴性).

復(fù)合索引的原理和設(shè)計(jì)建議

1.復(fù)合索引的第一個(gè)建議: 前綴性(Prefixing)

     先從例子說起.假設(shè)省,市,縣分別用3個(gè)字段存儲(chǔ)數(shù)據(jù),并建立了一個(gè)復(fù)合索引.請(qǐng)記住: oracle索引,包括復(fù)合索引都是排序的.例如該復(fù)合索引在數(shù)據(jù)庫索引樹上是這樣排序的,即先按省排序,再按市排序,最后按縣排序:

省  市  縣

北京  北京  東城

北京  北京  西城

北京  北京  海淀

... ...

黑龍江  哈爾濱  道里區(qū)

黑龍江  哈爾濱  道外區(qū)

黑龍江  哈爾濱  香坊區(qū)

... ...

黑龍江  齊齊哈爾 龍沙區(qū)

黑龍江  齊齊哈爾 鐵鋒區(qū)

黑龍江  齊齊哈爾 富拉爾基區(qū)

... ...

湖南  長沙  芙蓉區(qū)

湖南  長沙  岳路區(qū)

湖南  長沙  開福區(qū)

... ...

oracle不是智能的,它只會(huì)按圖索驥,該索引結(jié)構(gòu)是先按省排序的,所以只要給出省名,就能使用索引.如果沒有省名,oracle就成了無頭蒼蠅,亂找一氣,變成了全表掃描了.例如,如果你只給一個(gè)縣條件,如"開福區(qū)",oracle肯定不會(huì)使用該索引了.

2.關(guān)于skip scan index

有時(shí)候復(fù)合索引第一個(gè)字段沒有在語句中出現(xiàn),oralce也會(huì)使用該索引.對(duì),這叫oralce的skip scan index功能,oracle 9i才提供的.

skip scan index功能適合于什么情況呢?如果oracle發(fā)現(xiàn)第一個(gè)字段值很少的情況下,例如假設(shè)emp表有g(shù)ender(性別)字段,并且建立了(gender,ename,job,mgr)復(fù)合索引.因?yàn)樾詣e只有男和女,所以為了提高索引的利用率,oracle可將這個(gè)索引拆成('男',ename,job,mgr),('女',ename,job,mgr)兩個(gè)復(fù)合索引.這樣即便沒有g(shù)ender條件,oracle也會(huì)分別到男索引樹和女索引樹進(jìn)行搜索.

但是,(gender,ename,job,mgr)索引本身設(shè)計(jì)是不合理的,它違背了復(fù)合索引的第二個(gè)原理,可選性(Selectivity),見下面描述.

3.復(fù)合索引的第二個(gè)原理:可選性(Selectivity)

您可能會(huì)問:復(fù)合索引中如何排序字段順序?這時(shí)就要用到復(fù)合索引的第二個(gè)原理:可選性(Selectivity)規(guī)則.oracle建議按字段可選性高低進(jìn)行排序,即字段值多的排在前面.例如,(ename,job,mgr,gender),(縣,市,省).這是因?yàn)?字段值多,可選性越強(qiáng),定位的記錄越少,查詢效率越高.例如,全國可能只有一個(gè)"開福區(qū)",而湖南省的記錄則太多了.

4.復(fù)合索引設(shè)計(jì)建議

(1).分析SQL語句中的約束條件字段.

(2).如果約束條件字段比較固定,則優(yōu)先考慮創(chuàng)建針對(duì)多字段的普通B*樹復(fù)合索引.如果同時(shí)涉及到月份,納稅人識(shí)別號(hào),稅務(wù)機(jī)關(guān)代碼3個(gè)字段的條件,則可以考慮建立一個(gè)復(fù)合索引.

(3).如果單字段是主鍵或唯一字段,或者可選性非常高的字段,盡管約束條件比較固定,也不一定要建成復(fù)合索引,可建成單字段索引,降低復(fù)合索引開銷.

(4).在復(fù)合索引設(shè)計(jì)中,需首先考慮復(fù)合索引的第一個(gè)設(shè)計(jì)原理:復(fù)合索引的前綴性.即在SQL語句中,只有將復(fù)合索引的第一個(gè)字段作為約束條件,該復(fù)合索引才會(huì)啟用.

(5).在復(fù)合索引設(shè)計(jì)中,其實(shí)應(yīng)考慮復(fù)合索引的可選性.即按可選性高低,進(jìn)行復(fù)合索引字段的排序.例如上述索引的字段排序順序?yàn)?納稅人識(shí)別號(hào),稅務(wù)機(jī)關(guān)代碼,月份.

(6).如果條件涉及的字段不固定,組合比較靈活,則分別為月份,稅務(wù)機(jī)關(guān)代碼和納稅人識(shí)別號(hào)3個(gè)字段建立索引.

(7).如果是多表連接SQL語句,注意是否可以在被驅(qū)動(dòng)表(drived table)的連接字段與該表的其他約束條件字段上創(chuàng)建復(fù)合索引.

(8).通過多種SQL分析工具,分析執(zhí)行計(jì)劃以量化形式評(píng)估效果.

感謝各位的閱讀，以上就是“怎么理解oracle復(fù)合索引”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對(duì)怎么理解oracle復(fù)合索引這一問題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！