接觸過資料庫系統的使用者應該都知道， Index 對於 SQL 效能上有者舉足輕重的影響，為什麼建立 Index 可以提升效能呢 ? 在 Oracle 資料庫中， Index 紀錄了每筆資料的 Rowid ， Rowid 表示資料實際存放在 Data File 上的實體位置，以 64 進位的方式所表示，由 object number + file number + block number + row number 所組成。我們使用 SQL 語法來查詢資料，就有如在現實生活中找人一般，假設我們只知道要找的人住在台北市，那麼就只能走遍台北市的所有巷弄才有機會能夠找到這個人，如果我們已經很清楚的知道此人是住在台北市的哪條街哪條巷哪幾號，是不是馬上很快地找到這個人了，同樣的道理， SQL 要是知道此筆資料位在哪個 Data File 哪個 Block 上，那麼也可以很快的就找到這筆資料，這就是為何透過 Index 的查詢會這麼有效率。舉例來說， Index 紀錄著欄位的值與相對應的 Rowid :
假設一個查詢 where 條件查找 22 這筆資料，那麼就會先透過 Index 找到 22 這筆資料並且獲得此筆資料的 Rowid ，既然已經知道了 Rowid ，那麼就可以利用 Rowid 來獲得 Table 上的整筆資料，這個動作我們俗稱為 “回表” 。
Index 在架構上可以分為 B-Tree Index 與 Bitmap Index 兩大類:
B-Tree Index
為預設的 Index 架構， Index 呈現出一種樹狀的架構，最頂端的為 Root 、 其次為 Branch ， 最底層的為 Leaf Block ， Leaf Block 在結構上記錄著 Index Entry Header 、 Key Column Length 、 Key Column Value 以及 Rowid ，也就是前述所提到的欄位值與 Rowid 都是在 Leaf Block 當中。例如一個 Table 有 name 與 NO 兩個欄位，那麼在 NO 這個欄位上建立 Index ，結構上大概會形成這樣:
Index 會將所使用的欄位資料進行排序，原本 NO 欄位的資料沒有順序性，這些資料在 Index 建立的過程中會做好排序並且有順序的存放在 Leaf Block 當中。例如一個 where 條件查找 NO=4 ， Index 就會透過 Root 、 Branch 找到 NO=4 所在的 Leaf Block ； 若條件為 NO>=3 ，那麼就會先找到 NO=3 所在的 Leaf Block ，然後再由此 Block 直接向後尋找 4,5,6,7 等 Leaf Block ，此時不會再重複的透過 Root 與 Branch 查找 4,5,6,7。
Bitmap Index
Bitmap Index 在結構上也是一個樹狀結構，差別在於 Bitmap Index 的 Leaf Block 紀錄資料的方式與 B-Tree 不同。 B-Tree Index 的 Leaf Block 是直接紀錄資料的數值與 Rowid ，而 Bitmap Index 的 Leaf Block 則是以 1,0 的型態來標示資料所在的相對位置，例如在性別的欄位上建立 Bitmap Index ，那麼結構上就會形成這樣:
由於資料只有 “男” 、 “女” 兩種類別，因此 Bitmap Index 只會產生出兩個 Leaf Block ，藍色的代表資料為 “女” 的 Leaf Block ； 綠色代表資料為 “男” 。產生出 Leaf Block 之後，就會開始來對應原始資料的位置，第一筆資料為 “男” ，所以在綠色表示 “男” 的 Leaf Block 上標示第一筆資料為 1 ； 而藍色表示 “女” 的 Leaf Block 則標示為 0 ，第二筆資料為 “女” ，所以綠色標示為 0 ； 藍色標示為 1 ，第三筆資料為 “男” ，所以綠色標示為 1 ； 藍色標示為 0 … 以此類推。假設一個查詢 where 條件查找性別為 “女” ，那麼透過 Bitmap Index 就會先找到這個藍色代表 “女” 的 Leaf Block ，然後由 Leaf Block 上找出標示為 1 的資料，很快的就可以知道在第 2,7,8 筆資料是性別=”女” 。
一般來說，在欄位資料重複性比較大的時候比較適合建立 Bitmap Index ，原因是在這種情況下所產生的 Leaf Block 較少，查詢起來會比 B-Tree 來的有效率。
Index 在種類上可以建立為 Unique Index 、 Non-Unique Index 、 Composite Index 、 Function-Based Index 與 Partition Index 。 Unique Index 建立的前提是欄位的所有資料必須是唯一值，而 Non-Unique Index 則是我們一般所建立的 Index 沒有特殊要求 ； Composite Index 表示可以使用多個欄位來組成 Index ； Function-Based Index 表示欄位可以經過 Function 函式的轉換之後再存入 Index ，這邊要注意的是， Function-Based Index 只能使用系統內建的 Function 函式，例如 upper 、 lower …等，使用者自行建立的 Function 不可用來建立 Function-Based Index ； Partition Index 表示 Index 本身也可以使用 Partition ， 分為 Local Partition Index 與 Global Partition Index ， Local Partition Index 表示 Index 的 Partition Key 與 Table 的 Partition Key Column 相同；而 Global Partition Index 則表示 Index 所做的 Partition Key 與 Table 的 Partition Key Column 不同。
建立 Index 使用的是 create index 語法:
Index 的建立預設是使用 ASC (升冪排序) ，欄位後面不加 asc 的話就是使用預設的升冪排序，如果要使用降冪排序，則在建立的語法當中必須在欄位後面加上 desc ，例如:
建立 Index 後面如果不指定 Tablespace 的話，會建立在使用者的預設 Tablespace 下，一般來說都會建立一個獨立的 Tablespace 來存放 Index ，一方面可以避免單一 Tablespace 空間過大，另一方面在維護上將 Tablespace 分門別類也比較好管理。
若是要將 Index 重建，除了將它 drop 掉再重新 create 外，也可以使用 rebuild 語法:
在此需要注意的是，由於 Index 會將資料進行排序，因此在 Index 建立的過程當中 (create / rebuild) 是會造成 Table Lock ，這是為了避免在排序的過程當中又有新的資料進來造成無法排序，如果不希望在 Index 建立的過程當中造成 Table Lock ，那麼就可以加上 online 關鍵字，此時不會造成 Table Lock ，而是會使用 Temporary Table 來記錄資料的異動， Index 將原本的資料排序完之後再來處理這些異動的資料，這種方式雖然不會造成 Table Lock ，但 Index 建立的時間會比原本有 Table Lock 時來得長。
如果不使用某個 Index ，那麼有 drop 、 unusable 與 invisible 三種選項:
將 Index 設定為 unusable 表示要廢棄此 Index 但暫時不刪除，一般都是考慮到使用 drop index 會造成系統額外的負載而又想廢棄此 Index 時，就可以使用 unusable ，等到離峰時間再將此 Index 來 drop 掉，這邊要注意的是一旦 Index 被設定為 unusable ，就不可以再還原為 usable ，想要重新使用的話此 Index 就必須重新 create / rebuild 。
Index 設定為 invisible 是 Oracle 11g 之後的新功能，表示暫時遮蔽掉此 Index ， SQL 在查詢過程中不會使用狀態為 invisible 的 Index 。這邊要注意的是，雖然 invisible 的 Index 不會被 SQL 使用，但是資料的異動仍然會持續更新在此 Index 上， invisible 不同於 unusable 的是，如果此 Index 想要再被使用，可以再將它 alter 為 visible ；而 unusable 則不行。
Index 相關的資訊可以由 dba_objects 、 dba_indexes 、 dba_ind_columns 或是 user_objects 、 user_indexes 、 user_ind_columns 來查找，例如要列出當前使用者所有 Table 與其相關 Index 的資訊:
雖然 Index 有助於 SQL 的效能，但也不是建立越多 Index 就越好，因為 Index 上欄位的資料會隨著 Table 上的異動一起更新，會增加 I/O 的負載，另外過多的 Index 也有可能會造成執行計畫的誤判，沒有挑選到最佳的 Index 來使用。 Index 的建立必須考慮到資料的特性以及 SQL 語法的需求來建立，才能夠達到效能優化的目的。
沒有留言:
張貼留言