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

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

2、unction-based基于某些函數(shù)索引，當(dāng)執(zhí)行某些函數(shù)時需要對其進(jìn)行計(jì)算，可以將某些函數(shù)的計(jì)算結(jié)果事先保存并加以索引，提高效率。Doman索引數(shù)據(jù)庫以外的數(shù)據(jù)，使用相對較少物理分類B-Tree：normalorreversekeyB-Tree索引也是我們傳統(tǒng)上常見所理解的索引，它又可以分為正常所引和倒序索引。Bitmap：位圖所引B-TREE索引一個B樹索引只有一個根節(jié)點(diǎn)，它實(shí)際就是位于樹的最頂端的分支節(jié)點(diǎn)?？梢杂孟聢D一來描述B樹索引的結(jié)構(gòu)。其中，B表示分支節(jié)點(diǎn)，而L表示葉子節(jié)點(diǎn)。 對于分支節(jié)點(diǎn)塊（包括根節(jié)點(diǎn)塊）來說，其所包含的索引條目都是按照順序排列的（缺省是升序排列，也可以在創(chuàng)建索引時

3、指定為降序排列）。每個索引條目（也可以叫做每條記錄）都具有兩個字段。第一個字段表示當(dāng)前該分支節(jié)點(diǎn)塊下面所鏈接的索引塊中所包含的最小鍵值；第二個字段為四個字節(jié)，表示所鏈接的索引塊的地址，該地址指向下面一個索引塊。在一個分支節(jié)點(diǎn)塊中所能容納的記錄行數(shù)由數(shù)據(jù)塊大小以及索引鍵值的長度決定。比如從上圖一可以看到，對于根節(jié)點(diǎn)塊來說，包含三條記錄，分別為（0 B1）、（500 B2）、（1000 B3），它們指向三個分支節(jié)點(diǎn)塊。其中的0、500和1000分別表示這三個分支節(jié)點(diǎn)塊所鏈接的鍵值的最小值。而B1、B2和B3則表示所指向的三個分支節(jié)點(diǎn)塊的地址。對于葉子節(jié)點(diǎn)塊來說，其所包含的索引條目與分支節(jié)點(diǎn)一樣，都

4、是按照順序排列的（缺省是升序排列，也可以在創(chuàng)建索引時指定為降序排列）。每個索引條目（也可以叫做每條記錄）也具有兩個字段。第一個字段表示索引的鍵值，對于單列索引來說是一個值；而對于多列索引來說則是多個值組合在一起的。第二個字段表示鍵值所對應(yīng)的記錄行的ROWID，該ROWID是記錄行在表里的物理地址。如果索引是創(chuàng)建在非分區(qū)表上或者索引是分區(qū)表上的本地索引的話，則該ROWID占用6個字節(jié)；如果索引是創(chuàng)建在分區(qū)表上的全局索引的話，則該ROWID占用10個字節(jié)。bitmap索引 位圖（bitmap）索引是另外一種索引類型，它的組織形式與B樹索引相同，也是一棵平衡樹。與B樹索引的區(qū)別在于葉子節(jié)點(diǎn)里存放索引

5、條目的方式不同。從前面我們知道，B樹索引的葉子節(jié)點(diǎn)里，對于表里的每個數(shù)據(jù)行，如果被索引列的值不為空的，則會為該記錄行在葉子節(jié)點(diǎn)里維護(hù)一個對應(yīng)的索引條目。而位圖索引則不是這樣，其葉子節(jié)點(diǎn)里存放的索引條目如下圖所示。 假設(shè)某個表T里所有的記錄在列C1上只具有三個值：01、02和03。在表T的C1列上創(chuàng)建位圖索引以后，則葉子節(jié)點(diǎn)的內(nèi)容如圖9-14所示。可以看到，位圖索引只有三個索引條目，也就是每個C1列的值對應(yīng)一個索引條目。位圖索引條目上還包含表里第一條記錄所對應(yīng)的ROWID以及最后一條記錄所對應(yīng)的ROWID。索引條目的最后一部分則是由多個bit位所組成的bitmap，每個bit位就對應(yīng)一條記錄。

6、當(dāng)發(fā)出where c1=01這樣的SQL語句時，oracle會去搜索01所在的索引條目，然后掃描該索引條目中的bitmap里所有的bit位。第一個bit位為1，則說明第一條記錄上的C1值為01，于是返回第一條記錄所在的ROWID（根據(jù)該索引條目里記錄的start ROWID加上行號得到該記錄所在的ROWID）。第二個bit位為0，則說明第二條記錄上的C1值不為01，依此類推。另外，如果索引列為空，也會在位圖索引里記錄，也就是將對應(yīng)的bit位設(shè)置為0即可。 如果索引列上不同值的個數(shù)比較少的時候，比如對于性別列（男或女）等，則使用位圖索引會比較好，因?yàn)樗鼘臻g的占用非常少（因?yàn)槎际怯胋it位來表示

7、表里的數(shù)據(jù)行），從而在掃描索引的時候，掃描的索引塊的個數(shù)也比較少?？梢栽囅胍幌拢绻诹械牟煌捣浅６嗟牧猩希热缰麈I列上，創(chuàng)建位圖索引，則產(chǎn)生的索引條目就等于表里記錄的條數(shù)，同時每個索引條目里的bitmap里，只有一個1，其它都是0。這樣還不如B樹索引的效率高。如果被索引的列經(jīng)常被更新的話，則不適合使用位圖索引。因?yàn)楫?dāng)更新位圖所在的列時，由于要在不同的索引條目之間修改bit位，比如將第一條記錄從01變?yōu)?2，則必須將01所在的索引條目的第一個bit位改為0，再將02所在的索引條目的第一個bit位改為1。因此，在更新索引條目的過程中，會鎖定位圖索引里多個索引條目。也就是同時只能有一個用戶能夠更

8、新表T，從而降低了并發(fā)性。位圖索引比較適合用在數(shù)據(jù)倉庫系統(tǒng)里，不適合用在OLTP系統(tǒng)里。HASH索引 使用HASH索引必須要使用HASH集群。建立一個集群或HASH集群的同時，也就定義了一個集群鍵。這個鍵告訴Oracle如何在集群上存儲表。在存儲數(shù)據(jù)時，所有與這個集群鍵相關(guān)的行都被存儲在一個數(shù)據(jù)庫塊上。如果數(shù)據(jù)都存儲在同一個數(shù)據(jù)庫塊上，并且將HASH索引作為WHERE子句中的確切匹配，Oracle就可以通過執(zhí)行一個HASH函數(shù)和I/O來訪問數(shù)據(jù)- 而通過使用一個二元高度為4的B樹索引來訪問數(shù)據(jù)，則需要在檢索數(shù)據(jù)時使用4個I/O。如圖2-5所示，其中的查詢是一個等價查詢，用于匹配HASH列和確切

9、的值。Oracle可以快速使用該值，基于HASH函數(shù)確定行的物理存儲位置。 HASH索引可能是訪問數(shù)據(jù)庫中數(shù)據(jù)的最快方法，但它也有自身的缺點(diǎn)。集群鍵上不同值的數(shù)目必須在創(chuàng)建HASH集群之前就要知道。需要在創(chuàng)建HASH集群的時候指定這個值。低估了集群鍵的不同值的數(shù)字可能會造成集群的沖突(兩個集群的鍵值擁有相同的HASH值)。這種沖突是非常消耗資源的。沖突會造成用來存儲額外行的緩沖溢出，然后造成額外的I/O。如果不同HASH值的數(shù)目已經(jīng)被低估，您就必須在重建這個集群之后改變這個值。ALTER CLUSTER命令不能改變HASH鍵的數(shù)目。 HASH集群還可能浪費(fèi)空間。如果無法確定需要多少空間來維護(hù)某

10、個集群鍵上的所有行，就可能造成空間的浪費(fèi)。如果不能為集群的未來增長分配好附加的空間，HASH集群可能就不是最好的選擇。如果應(yīng)用程序經(jīng)常在集群表上進(jìn)行全表掃描，HASH集群可能也不是最好的選擇。由于需要為未來的增長分配好集群的剩余空間量，全表掃描可能非常消耗資源。在實(shí)現(xiàn)HASH集群之前一定要小心。您需要全面地觀察應(yīng)用程序，保證在實(shí)現(xiàn)這個選項(xiàng)之前已經(jīng)了解關(guān)于表和數(shù)據(jù)的大量信息。通常，HASH對于一些包含有序值的靜態(tài)數(shù)據(jù)非常有效。技巧：HASH索引在有限制條件(需要指定一個確定的值而不是一個值范圍)的情況下非常有用。聚族索引 在這里還是用字典來進(jìn)行類比，一般來說漢語字典中有幾種索引，如拼音、偏旁、筆

11、畫等。字典本身的組織也是排序的，我記得一般是按照拼音排序的。這里的拼音就是聚族索引。也就是說聚族索引的組織順序和數(shù)據(jù)本身的組織順序是一致的，這也解釋了數(shù)據(jù)庫中只能定義一個聚族索引的原因，因?yàn)閿?shù)據(jù)本身只能按一種方式進(jìn)行排序。 那聚族索引有什么特別的好處呢，這個好處就是在數(shù)據(jù)庫中執(zhí)行查找一批數(shù)據(jù)的語句會比較快，因?yàn)閿?shù)據(jù)已經(jīng)按照聚族索引排好序了，很少的io操作就可以將數(shù)據(jù)從庫中取出。好比你在字典中查找發(fā)音從從a到c的漢字，只需要查到a的開始頁和c的結(jié)束頁，中間的所有頁都符合查詢要求，不用再一頁一頁地查找。非聚族索引 非聚族索引就好比字典里的偏旁、筆畫索引，其索引組織順序和數(shù)據(jù)組織順序不一致，因此非聚

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

13、 組合索引中列的順序是任意的，也無需相鄰。但是建議將最頻繁訪問的列放在列表的最前面3、 演示(組合列，單獨(dú)列)五、 位圖索引1、 何時創(chuàng)建：列中有非常多的重復(fù)的值時候。例如某列保存了 “性別”信息。Where 條件中包含了很多OR操作符。較少的update操作，因?yàn)橐鄳?yīng)的跟新所有的bitmap2、 結(jié)構(gòu)：位圖索引使用位圖作為鍵值，對于表中的每一數(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.)

14、;5、 演示：create table bitmaptable as select * from indextable where owner in(SYS,PUBLIC);分析，查找，建立索引，查找六、 基于函數(shù)的索引 1、 何時創(chuàng)建：在WHERE條件語句中包含函數(shù)或者表達(dá)式時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

15、)*/七、 反向鍵索引目的：比如索引值是一個自動增長的列：多個用戶對集中在少數(shù)塊上的索引行進(jìn)行修改，容易引起資源的爭用，比如對數(shù)據(jù)塊的等待。此時建立反向索 引。性能問題：語法：重建為標(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, WeedBritt

16、en Park, Shrub Bed 1, Hoe查詢時，以上3列均在where條件中同時出現(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)：組合索引的前綴部分具 有非選擇性時，考慮使用壓縮。減少I/O,增加性能