數(shù)據(jù)庫索引原理、應(yīng)用

1、Oracle索引原理索引的概念1、 類似書的目錄結(jié)構(gòu)2、 Oracle 的“索引”對(duì)象，與表關(guān)聯(lián)的可選對(duì)象，提高SQL查詢語句的速度3、 索引直接指向包含所查詢值的行的位置，減少磁盤I/O4、 與所索引的表是相互獨(dú)立的物理結(jié)構(gòu)5、 Oracle 自動(dòng)使用并維護(hù)索引，插入、刪除、更新表后，自動(dòng)更新索引6、 語法：CREATE INDEX index ON table (column, column.);索引分類邏輯分類single column or concatenated   對(duì)一列或多列建索引unique or 

2、;nonunique  唯一的和非唯一的索引，也就是對(duì)某一列或幾列的鍵值（key）是否是唯一的。Function-based  基于某些函數(shù)索引，當(dāng)執(zhí)行某些函數(shù)時(shí)需要對(duì)其進(jìn)行計(jì)算，可以將某些函數(shù)的計(jì)算結(jié)果事先保存并加以索引，提高效率。 Doman  索引數(shù)據(jù)庫以外的數(shù)據(jù)，使用相對(duì)較少 物理分類B-Tree ：normal or reverse key   B-Tree索引也是我們傳統(tǒng)上常見所理解的索引，它又可以分為正常所引和倒序索引。Bitmap 

3、; ： 位圖所引B-TREE索引一個(gè)B樹索引只有一個(gè)根節(jié)點(diǎn)，它實(shí)際就是位于樹的最頂端的分支節(jié)點(diǎn)?？梢杂孟聢D一來描述B樹索引的結(jié)構(gòu)。其中，B表示分支節(jié)點(diǎn)，而L表示葉子節(jié)點(diǎn)。    對(duì)于分支節(jié)點(diǎn)塊（包括根節(jié)點(diǎn)塊）來說，其所包含的索引條目都是按照順序排列的（缺省是升序排列，也可以在創(chuàng)建索引時(shí)指定為降序排列）。每個(gè)索引條目（也可以叫做每條記錄）都具有兩個(gè)字段。第一個(gè)字段表示當(dāng)前該分支節(jié)點(diǎn)塊下面所鏈接的索引塊中所包含的最小鍵值；第二個(gè)字段為四個(gè)字節(jié)，表示所鏈接的索引塊的地址，該地址指向下面一個(gè)索引塊。在一個(gè)分支節(jié)點(diǎn)塊中所能容納的記錄行數(shù)由數(shù)據(jù)塊大小以及索

4、引鍵值的長度決定。比如從上圖一可以看到，對(duì)于根節(jié)點(diǎn)塊來說，包含三條記錄，分別為（0 B1）、（500 B2）、（1000 B3），它們指向三個(gè)分支節(jié)點(diǎn)塊。其中的0、500和1000分別表示這三個(gè)分支節(jié)點(diǎn)塊所鏈接的鍵值的最小值。而B1、B2和B3則表示所指向的三個(gè)分支節(jié)點(diǎn)塊的地址。    對(duì)于葉子節(jié)點(diǎn)塊來說，其所包含的索引條目與分支節(jié)點(diǎn)一樣，都是按照順序排列的（缺省是升序排列，也可以在創(chuàng)建索引時(shí)指定為降序排列）。每個(gè)索引條目（也可以叫做每條記錄）也具有兩個(gè)字段。第一個(gè)字段表示索引的鍵值，對(duì)于單列索引來說是一個(gè)值；而對(duì)于多列索引來說則是多個(gè)值組合在一起的。第

5、二個(gè)字段表示鍵值所對(duì)應(yīng)的記錄行的ROWID，該ROWID是記錄行在表里的物理地址。如果索引是創(chuàng)建在非分區(qū)表上或者索引是分區(qū)表上的本地索引的話，則該ROWID占用6個(gè)字節(jié)；如果索引是創(chuàng)建在分區(qū)表上的全局索引的話，則該ROWID占用10個(gè)字節(jié)。 bitmap索引      位圖（bitmap）索引是另外一種索引類型，它的組織形式與B樹索引相同，也是一棵平衡樹。與B樹索引的區(qū)別在于葉子節(jié)點(diǎn)里存放索引條目的方式不同。從前面我們知道，B樹索引的葉子節(jié)點(diǎn)里，對(duì)于表里的每個(gè)數(shù)據(jù)行，如果被索引列的值不為空的，則會(huì)為該記錄行在葉子節(jié)點(diǎn)里維護(hù)一個(gè)對(duì)應(yīng)的索

6、引條目。而位圖索引則不是這樣，其葉子節(jié)點(diǎn)里存放的索引條目如下圖所示。    假設(shè)某個(gè)表T里所有的記錄在列C1上只具有三個(gè)值：01、02和03。在表T的C1列上創(chuàng)建位圖索引以后，則葉子節(jié)點(diǎn)的內(nèi)容如圖9-14所示。可以看到，位圖索引只有三個(gè)索引條目，也就是每個(gè)C1列的值對(duì)應(yīng)一個(gè)索引條目。位圖索引條目上還包含表里第一條記錄所對(duì)應(yīng)的ROWID以及最后一條記錄所對(duì)應(yīng)的ROWID。索引條目的最后一部分則是由多個(gè)bit位所組成的bitmap，每個(gè)bit位就對(duì)應(yīng)一條記錄。       當(dāng)發(fā)出where c1='0

7、1'這樣的SQL語句時(shí)，oracle會(huì)去搜索01所在的索引條目，然后掃描該索引條目中的bitmap里所有的bit位。第一個(gè)bit位為1，則說明第一條記錄上的C1值為01，于是返回第一條記錄所在的ROWID（根據(jù)該索引條目里記錄的start ROWID加上行號(hào)得到該記錄所在的ROWID）。第二個(gè)bit位為0，則說明第二條記錄上的C1值不為01，依此類推。另外，如果索引列為空，也會(huì)在位圖索引里記錄，也就是將對(duì)應(yīng)的bit位設(shè)置為0即可。       如果索引列上不同值的個(gè)數(shù)比較少的時(shí)候，比如對(duì)于性別列（男或女）等，則使用位圖索引會(huì)比

8、較好，因?yàn)樗鼘?duì)空間的占用非常少（因?yàn)槎际怯胋it位來表示表里的數(shù)據(jù)行），從而在掃描索引的時(shí)候，掃描的索引塊的個(gè)數(shù)也比較少?？梢栽囅胍幌?，如果在列的不同值非常多的列上，比如主鍵列上，創(chuàng)建位圖索引，則產(chǎn)生的索引條目就等于表里記錄的條數(shù)，同時(shí)每個(gè)索引條目里的bitmap里，只有一個(gè)1，其它都是0。這樣還不如B樹索引的效率高。如果被索引的列經(jīng)常被更新的話，則不適合使用位圖索引。因?yàn)楫?dāng)更新位圖所在的列時(shí)，由于要在不同的索引條目之間修改bit位，比如將第一條記錄從01變?yōu)?2，則必須將01所在的索引條目的第一個(gè)bit位改為0，再將02所在的索引條目的第一個(gè)bit位改為1。因此，在更新索引條目的過程中，會(huì)鎖

9、定位圖索引里多個(gè)索引條目。也就是同時(shí)只能有一個(gè)用戶能夠更新表T，從而降低了并發(fā)性。位圖索引比較適合用在數(shù)據(jù)倉庫系統(tǒng)里，不適合用在OLTP系統(tǒng)里。 HASH索引      使用HASH索引必須要使用HASH集群。建立一個(gè)集群或HASH集群的同時(shí)，也就定義了一個(gè)集群鍵。這個(gè)鍵告訴Oracle如何在集群上存儲(chǔ)表。在存儲(chǔ)數(shù)據(jù)時(shí)，所有與這個(gè)集群鍵相關(guān)的行都被存儲(chǔ)在一個(gè)數(shù)據(jù)庫塊上。如果數(shù)據(jù)都存儲(chǔ)在同一個(gè)數(shù)據(jù)庫塊上，并且將HASH索引作為WHERE子句中的確切匹配，Oracle就可以通過執(zhí)行一個(gè)HASH函數(shù)和I/O來訪問數(shù)據(jù)- 而通過使用一個(gè)二元高

10、度為4的B樹索引來訪問數(shù)據(jù)，則需要在檢索數(shù)據(jù)時(shí)使用4個(gè)I/O。如圖2-5所示，其中的查詢是一個(gè)等價(jià)查詢，用于匹配HASH列和確切的值。Oracle可以快速使用該值，基于HASH函數(shù)確定行的物理存儲(chǔ)位置。      HASH索引可能是訪問數(shù)據(jù)庫中數(shù)據(jù)的最快方法，但它也有自身的缺點(diǎn)。集群鍵上不同值的數(shù)目必須在創(chuàng)建HASH集群之前就要知道。需要在創(chuàng)建HASH集群的時(shí)候指定這個(gè)值。低估了集群鍵的不同值的數(shù)字可能會(huì)造成集群的沖突(兩個(gè)集群的鍵值擁有相同的HASH值)。這種沖突是非常消耗資源的。沖突會(huì)造成用來存儲(chǔ)額外行的緩沖溢出，然后造成額外的I/O。如果

11、不同HASH值的數(shù)目已經(jīng)被低估，您就必須在重建這個(gè)集群之后改變這個(gè)值。ALTER CLUSTER命令不能改變HASH鍵的數(shù)目。      HASH集群還可能浪費(fèi)空間。如果無法確定需要多少空間來維護(hù)某個(gè)集群鍵上的所有行，就可能造成空間的浪費(fèi)。如果不能為集群的未來增長分配好附加的空間，HASH集群可能就不是最好的選擇。如果應(yīng)用程序經(jīng)常在集群表上進(jìn)行全表掃描，HASH集群可能也不是最好的選擇。由于需要為未來的增長分配好集群的剩余空間量，全表掃描可能非常消耗資源。 在實(shí)現(xiàn)HASH集群之前一定要小心。您需要全面地觀察應(yīng)用程序，保證在實(shí)現(xiàn)這個(gè)選項(xiàng)

12、之前已經(jīng)了解關(guān)于表和數(shù)據(jù)的大量信息。通常，HASH對(duì)于一些包含有序值的靜態(tài)數(shù)據(jù)非常有效。技巧：HASH索引在有限制條件(需要指定一個(gè)確定的值而不是一個(gè)值范圍)的情況下非常有用。 聚族索引      在這里還是用字典來進(jìn)行類比，一般來說漢語字典中有幾種索引，如拼音、偏旁、筆畫等。字典本身的組織也是排序的，我記得一般是按照拼音排序的。這里的拼音就是聚族索引。也就是說聚族索引的組織順序和數(shù)據(jù)本身的組織順序是一致的 ，這也解釋了數(shù)據(jù)庫中只能定義一個(gè)聚族索引的原因，因?yàn)閿?shù)據(jù)本身只能按一種方式進(jìn)行排序。   

13、;   那聚族索引有什么特別的好處呢，這個(gè)好處就是在數(shù)據(jù)庫中執(zhí)行查找一批數(shù)據(jù)的語句會(huì)比較快，因?yàn)閿?shù)據(jù)已經(jīng)按照聚族索引排好序了，很少的io操作就可以將數(shù)據(jù)從庫中取出。好比你在字典中查找發(fā)音從從a到c的漢字，只需要查到a的開始頁和c的結(jié)束頁，中間的所有頁都符合查詢要求，不用再一頁一頁地查找。  非聚族索引      非聚族索引就好比字典里的偏旁、筆畫索引，其 索引組織順序和數(shù)據(jù)組織順序不一致 ，因此非聚族索引可以創(chuàng)建多個(gè)。當(dāng)查找一條數(shù)據(jù)時(shí)，非聚族索引和聚族索引的效率相差不大，但查找一批數(shù)據(jù)

14、(n)時(shí)，非聚族索引需要的io可能是聚族索引的n倍，因?yàn)榉蔷圩逅饕枰粭l一條地進(jìn)行查找。 Oracle索引應(yīng)用與類型選擇三、 唯一索引1、 何時(shí)創(chuàng)建：當(dāng)某列任意兩行的值都不相同2、 當(dāng)建立Primary Key(主鍵)或者Unique constraint(唯一約束)時(shí)，唯一索引將被自動(dòng)建立3、 語法：CREATE UNIQUE INDEX index ON table (column);4、 演示四、 組合索引1、 何時(shí)創(chuàng)建：當(dāng)兩個(gè)或多個(gè)列經(jīng)常一起出現(xiàn)在where條件中時(shí)，則在這些列上同時(shí)創(chuàng)建組合索引2、 組合索引中列的順序是任意的，也無需相鄰。但是建議將最頻繁訪問的列放在列表的

15、最前面3、 演示(組合列，單獨(dú)列)五、 位圖索引1、 何時(shí)創(chuàng)建：列中有非常多的重復(fù)的值時(shí)候。例如某列保存了 “性別”信息。Where 條件中包含了很多OR操作符。較少的update操作，因?yàn)橐鄳?yīng)的跟新所有的bitmap2、 結(jié)構(gòu)：位圖索引使用位圖作為鍵值，對(duì)于表中的每一數(shù)據(jù)行位圖包含了TRUE(1)、FALSE(0)、或NULL值。3、 優(yōu)點(diǎn)：位圖以一種壓縮格式存放，因此占用的磁盤空間比標(biāo)準(zhǔn)索引要小得多4、 語法：CREATE BITMAP INDEX index ON table (column, column.);5、 演示：create table bitmaptable as sel

16、ect * from indextable where owner in('SYS','PUBLIC');分析，查找，建立索引，查找六、 基于函數(shù)的索引   1、 何時(shí)創(chuàng)建：在WHERE條件語句中包含函數(shù)或者表達(dá)式時(shí)2、 函數(shù)包括：算數(shù)表達(dá)式、PL/SQL函數(shù)、程序包函數(shù)、SQL函數(shù)、用戶自定義函數(shù)。3、 語法：CREATE INDEX index ON table (FUNCTION(column);4、 演示必須要分析表，并且 query_rewrite_enabled=TRUE或者使用提示/*+ INDEX(ic_index)*/七、

17、反向鍵索引目的：比如索引值是一個(gè)自動(dòng)增長的列：多個(gè)用戶對(duì)集中在少數(shù)塊上的索引行進(jìn)行修改，容易引起資源的爭用，比如對(duì)數(shù)據(jù)塊的等待。此時(shí)建立反向索 引。性能問題：語法：重建為標(biāo)準(zhǔn)索引：反之不行八、 鍵壓縮索引比如表landscp的數(shù)據(jù)如下：site feature jobBritten Park, Rose Bed 1, PruneBritten Park, Rose Bed 1, MulchBritten Park, Rose Bed 1,SprayBritten Park, Shrub Bed 1, MulchBritten Park, Shrub Bed 1, WeedBritten Par

18、k, Shrub Bed 1, Hoe查詢時(shí)，以上3列均在where條件中同時(shí)出現(xiàn)，所以建立基于以上3列的組合索引。但是發(fā)現(xiàn)重復(fù)值很多，所以考慮壓縮特性。Create index zip_idxon landscp(site, feature, job)compress 2;將索引項(xiàng)分成前綴(prefix)和后綴(postfix)兩部分。前兩項(xiàng)被放置到前綴部分。Prefix 0: Britten Park, Rose Bed 1Prefix 1: Britten Park, Shrub Bed 1實(shí)際所以的結(jié)構(gòu)為：0 Prune0 Mulch0 Spray1 Mulch1 Weed1 Hoe特點(diǎn)：組合索引的