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

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

?????

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

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

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)?是用還是不用?也就是說對emp表的訪問是全表掃描還是按索引(idx_1)訪問?

?

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

?

?

復合索引的原理和設計建議

?

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

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

省??市??縣
北京??北京??東城
北京??北京??西城
北京??北京??海淀
... ...
黑龍江??哈爾濱??道里區
黑龍江??哈爾濱??道外區
黑龍江??哈爾濱??香坊區
... ...
黑龍江??齊齊哈爾?龍沙區
黑龍江??齊齊哈爾?鐵鋒區
黑龍江??齊齊哈爾?富拉爾基區
... ...
湖南??長沙??芙蓉區
湖南??長沙??岳路區
湖南??長沙??開福區
... ...

?

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

?

2.關于skip scan index

有時候復合索引第一個字段沒有在語句中出現,oralce也會使用該索引.對,這叫oralce的skip scan index功能,oracle 9i才提供的.

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

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

?

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

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

?

4.復合索引設計建議

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

(2).如果約束條件字段比較固定,則優先考慮創建針對多字段的普通B*樹復合索引.如果同時涉及到月份,納稅人識別號,稅務機關代碼3個字段的條件,則可以考慮建立一個復合索引.

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

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

(5).在復合索引設計中,其實應考慮復合索引的可選性.即按可選性高低,進行復合索引字段的排序.例如上述索引的字段排序順序為:納稅人識別號,稅務機關代碼,月份.

(6).如果條件涉及的字段不固定,組合比較靈活,則分別為月份,稅務機關代碼和納稅人識別號3個字段建立索引.

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

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

總結

以上是生活随笔為你收集整理的oracle复合索引介绍(多字段索引)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。