ORACLE_索引篇分析

1、Oracle 索引索引RDM 吳桂林索引的概念 索引是與表關(guān)聯(lián)的可選獨立對象,提高查詢速度 通過默認(rèn)的鍵值排序來取代全表掃描，提高查詢效率 索引是以二叉樹的機構(gòu)存儲的，葉節(jié)點中存儲的是表中數(shù)據(jù)行的rowid（數(shù)據(jù)的物理地址）B樹索引位圖索引索引優(yōu)缺點 優(yōu)點：提高查詢速度，含索引條件的select，update，delete；提高分組排序的速度 缺點：創(chuàng)建和維護索引需要耗費時間，隨著數(shù)據(jù)量的增加而增加；索引需要物理空間，對表中的數(shù)據(jù)進(jìn)行insert，update，delete時，索引要進(jìn)行動態(tài)維護索引的類型 1. B_tree單列索引 2. B_tree復(fù)合索引 3.位圖索引 4.函數(shù)索引 5.

2、反向索引 6.分區(qū)索引和全局索引B_tree單列索引 基于單個列創(chuàng)建的B_TREE索引，是oracle默認(rèn)的索引類型 由于索引是通過rowid來訪問數(shù)據(jù)的，當(dāng)范圍掃描的數(shù)據(jù)占總數(shù)據(jù)量的10%以上時，使用索引的消耗不如全表掃描（全表掃描是多塊讀取，索引掃描每次有兩次IO，一次對索引塊，一次對數(shù)據(jù)）示例1select /*+full(a)*/a.passwd from t_userinfo a 2 where phonenumber =and phonenumber select /*+index(a pk_t_userinfo)*/a.passwd from t_use

3、rinfo a 2 where phonenumber =and phonenumber ”“ select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name=ID_LOCAL;INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS- - - -ID_LOCAL P1 10000 USABLEID_LOCAL P2 20000 USABLEID_LOCAL P3 MAXVALUE USABLE全局分區(qū)索引 全局分區(qū)索引

4、是對整個分區(qū)表建立的索引，然后由oracle對索引進(jìn)行分區(qū)，索引分區(qū)與分區(qū)表之間不是簡單的一對一關(guān)系全局分區(qū)索引刪除id_local索引drop index id_local;重新在ID列上創(chuàng)建一個GLOBAL的索引create index id_global on test(id) global;SQL select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name=ID_GLOBAL;no rows selectedSQL select INDEX_NAME,INDEX_

5、TYPE,TABLE_NAME from dba_indexes where index_name=ID_GLOBAL;INDEX_NAME INDEX_TYPE TABLE_NAME- - -ID_GLOBAL NORMAL TEST從上面可以看出，它此時是個普通索引。dba_ind_partitions里根本就沒有記錄。全局分區(qū)索引SQLcreate index i_id_global on test(data) global partition by range(id) ( partition p1 values less than (10000) , partition p2 valu

6、es less than (MAXVALUE) ); partition by range(id) *ERROR at line 2:ORA-14038: GLOBAL partitioned index must be prefixed此錯誤表示GLOBAL的索引必須是prefixed，即索引分區(qū)的列，必須是其基表的分區(qū)列。SQLcreate index id_global on test(id) global partition by range(id) ( partition p1 values less than (10000) , partition p2 values less t

7、han (MAXVALUE) );Index created.SQL select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name=ID_GLOBAL;INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS- - - -ID_GLOBAL P1 10000 USABLEID_GLOBAL P2 MAXVALUE USABLE典型索引失效1、在索引列上使用函數(shù)。如SUBSTR,DECODE,INSTR等，對索引列進(jìn)行運算.需要建立函數(shù)索引就可以

8、解決了。2、新建的表還沒來得及生成統(tǒng)計信息，分析一下就好了3、基于cost的成本分析，訪問的表過小，使用全表掃描的消耗小于使用索引。4、使用、not in 、not exist，對于這三種情況大多數(shù)情況下認(rèn)為結(jié)果集很大，一般大于5%-15%就不走索引而走FTS。5、單獨的、。6、like %_ 百分號在前。7、單獨引用復(fù)合索引里非第一位置的索引列。8、字符型字段為數(shù)字時在where條件里不添加引號。9、當(dāng)變量采用的是times變量，而表的字段采用的是date變量時.或相反情況。10、索引失效，可以考慮重建索引，rebuild online。11、B-tree索引 is null不會走,is n

9、ot null會走,位圖索引 is null,is not null 都會走、聯(lián)合索引 is not null 只要在建立的索引列（不分先后）都會走。創(chuàng)建索引的注意 1、一般來說，不需要為比較小的表創(chuàng)建索引； 2、即使是大表，如果經(jīng)常需要查詢的數(shù)據(jù)不超過10%到15%的話，那就沒有必要為其建立索引的必要。因為此時建立索引的開銷可能要比性能的改善大的多。這個比例只是一個經(jīng)驗的數(shù)據(jù)。如果數(shù)據(jù)庫管理員需要得出一個比較精確的結(jié)論，那么就需要進(jìn)行測試分析。 3、如對于一些重復(fù)內(nèi)容比較少的列，特別是對于那些定義了唯一約束的列。在這些列上建立索引，往往可以起到非常不錯的效果。如對于一些null值的列與非Nu

10、ll值的列混合情況下，如果用戶需要經(jīng)常查詢所有的非Null值記錄的列，則最好為其設(shè)置索引。如果經(jīng)常需要多表連接查詢，在用與連接的列上設(shè)置索引可以達(dá)到事半功倍的效果。創(chuàng)建索引的注意 4、數(shù)據(jù)庫管理員，需要隔一段時間，如一年，對數(shù)據(jù)庫的索引進(jìn)行優(yōu)化。該去掉的去掉，該調(diào)整的調(diào)整，以提高數(shù)據(jù)庫的性能。 5、通常來說，表的索引越多，其查詢的速度也就越快。但是，表的更新速度則會降低。這主要是因為表的更新(如往表中插入一條記錄)速度，反而隨著索引的增加而增加。這主要是因為，在更新記錄的同時需要更新相關(guān)的索引信息。為此，到底在表中創(chuàng)建多少索引合適，就需要在這個更新速度與查詢速度之間取得一個均衡點。創(chuàng)建索引的注

11、意6、對于一些數(shù)據(jù)倉庫或者決策型數(shù)據(jù)庫系統(tǒng)，其主要用來進(jìn)行查詢。相關(guān)的記錄往往是在數(shù)據(jù)庫初始化的時候倒入。此時，設(shè)置的索引多一點，可以提高數(shù)據(jù)庫的查詢性能。同時因為記錄不怎么更新，所以索引比較多的情況下，也不會影響到更新的速度。即使在起初的時候需要導(dǎo)入大量的數(shù)據(jù)，此時也可以先將索引禁用掉。等到數(shù)據(jù)導(dǎo)入完畢后，再啟用索引?？梢酝ㄟ^這種方式來減少索引對數(shù)據(jù)更新的影響。相反，如果那些表中經(jīng)常需要更新記錄，如一些事務(wù)型的應(yīng)用系統(tǒng)，數(shù)據(jù)更新操作是家常便飯的事情。此時如果在一張表中建立過多的索引，則會影響到更新的速度。7、關(guān)于位圖索引?；鶖?shù)是位圖索引中的一個基本的定義，它是指數(shù)據(jù)庫表中某個字段內(nèi)容中不重復(fù)

12、的數(shù)值。如在員工信息表中的性別字段，一般就只有男跟女兩個值，所以，其基數(shù)為2;婚姻狀況字段的話，則其只有已婚、未婚、離婚三種狀態(tài)，其基數(shù)就為3;民族一覽內(nèi)也是只有有限的幾個值Oracle創(chuàng)建索引的基本規(guī)則選擇索引字段的原則:在WHERE子句中最頻繁使用的字段 聯(lián)接語句中的聯(lián)接字段 選擇高選擇性的字段(如果很少的字段擁有相同值,即有很多獨特值,則選擇性很好) Oracle在UNIQUE和主鍵字段上自動建立索引 在選擇性很差的字段上建索引只有在這個字段的值分布非常傾斜的情況下才有益(在這種情況下,某一,兩個字段值比其它字段值少出現(xiàn)很多) 不要在很少獨特值的字段上建B-TREE索引,在這種情況下,你

13、可以考慮在這些字段上建位圖索引.在聯(lián)機事務(wù)處理環(huán)境下,并發(fā)性非常高,索引經(jīng)常被修改,所以不應(yīng)該建位圖索引 不要在經(jīng)常被修改的字段上建索引.當(dāng)有UPDATE,DELETE,INSETT操作時,ORACLE除了要更新表的數(shù)據(jù)外,同時也要更新索引,而且就象更新數(shù)據(jù)一樣,或產(chǎn)生還原和重做條目 不要在有用到函數(shù)的字段上建索引,ORACLE在這種情況,優(yōu)化器不會用到索引,除非你建立函數(shù)索引 當(dāng)建立索引后,請比較一下索引后所獲得的查詢性能的提高和UPDATE,DELETE,INSERT操作性能上的損失,比較得失后,再最后決定是否需建立這個索引 Oracle創(chuàng)建索引的基本規(guī)則復(fù)合索引的優(yōu)點:改善選擇性:復(fù)合索

14、引比單個字段的索引更具選擇性 減少I/O:如果要查詢的字段剛好全部包含在復(fù)合索引的字段里,則ORACLE只須訪問索引,無須訪問表 什么情況下優(yōu)化器會用到復(fù)合索引呢? (a) 當(dāng)SQL語句的WHERE子句中有用到復(fù)合索引的領(lǐng)導(dǎo)字段時,ORACLE優(yōu)化器會考慮用到復(fù)合索引來訪問. (b) 當(dāng)某幾個字段在SQL語句的WHERE子句中經(jīng)常通過AND操作符聯(lián)合在一起使用作為過濾謂詞,并且這幾個字段合在一起時選擇性比各自單個字段的選擇性要更好時,可 能考慮用這幾個字段來建立復(fù)合索引. (c) 當(dāng)有幾個查詢語句都是查詢同樣的幾個字段值時,則可以考慮在這幾個字段上建立復(fù)合索引.復(fù)合索引字段排序的原則:確保在W

15、HERE子句中使用到的字段是復(fù)合索引的領(lǐng)導(dǎo)字段 如果某個字段在WHERE子句中最頻繁使用,則在建立復(fù)合索引時,考慮把這個字段排在第一位(在CREATE INDEX語句中) 如果所有的字段在WHERE子句中使用頻率相同,則將最具選擇性的字段排在最前面,將最不具選擇性的字段排在最后面 如果所有的字段在WHERE子句中使用頻率相同,如果數(shù)據(jù)在物理上是按某一個字段排序的,則考慮將這個字段放在復(fù)合索引的第一位 Oracle創(chuàng)建索引的基本規(guī)則四、建立索引常用的規(guī)則如下表的主鍵、外鍵必須有索引； 數(shù)據(jù)量超過300的表應(yīng)該有索引； 經(jīng)常與其他表進(jìn)行連接的表，在連接字段上應(yīng)該建立索引； 經(jīng)常出現(xiàn)在Where子句

16、中的字段，特別是大表的字段，應(yīng)該建立索引； 索引應(yīng)該建在選擇性高的字段上； 索引應(yīng)該建在小字段上，對于大的文本字段甚至超長字段，不要建索引； 復(fù)合索引的建立需要進(jìn)行仔細(xì)分析；盡量考慮用單字段索引代替： A、正確選擇復(fù)合索引中的主列字段，一般是選擇性較好的字段； B、復(fù)合索引的幾個字段是否經(jīng)常同時以AND方式出現(xiàn)在Where子句中？單字段查詢是否極少甚至沒有？如果是，則可以建立復(fù)合索引；否則考慮單字段索引； C、如果復(fù)合索引中包含的字段經(jīng)常單獨出現(xiàn)在Where子句中，則分解為多個單字段索引； D、如果復(fù)合索引所包含的字段超過3個，那么仔細(xì)考慮其必要性，考慮減少復(fù)合的字段； E、如果既有單字段索引

17、，又有這幾個字段上的復(fù)合索引，一般可以刪除復(fù)合索引；頻繁進(jìn)行數(shù)據(jù)操作的表，不要建立太多的索引； 刪除無用的索引，避免對執(zhí)行計劃造成負(fù)面影響； 不走索引不走索引的幾種情況1. 隱式的類型轉(zhuǎn)換例：fnumber是字符型，但是查詢時使用數(shù)字型select * from t_lea_waybill where fnumber = 122355722. 符號的查詢例：select * from wlbussiness where id 12030001 and id select * from tbl where userid = 100;執(zhí)行計劃-Plan hash value: 1167568666

18、-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 | 1 | TABLE ACCESS BY INDEX ROWID| TBL | 1 | 38 | 2 (0)| 00:00:01 |* 2 | INDEX UNIQUE SCAN | TBLID | 1 | | 1 (0)| 00:00:01 |-從上面的執(zhí)行計劃可以看出，優(yōu)化器首先是根據(jù)為剛才建立的索引TBLID來找到100的ROWID，然后根據(jù)ROWID去找到10

19、0所在的行數(shù)據(jù)。示例 index full scan 為上面的表增加一個聯(lián)合索引，在TBL.NAME和TBL.DEPTNO兩個列上，SQL如下： CREATE INDEX INDEX_TBL_NAME_DEPTNO ON TBL(NAME,DEPTNO); Select NAME,DEPTNO from tbl示例index range scan執(zhí)行下面的語句：ChenZw select * from tbl where userid between 10 and 100;已選擇91行。執(zhí)行計劃-Plan hash value: 2314926374-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 91 | 3458 | 3 (0)| 00:00:01 | 1 |