Index

前情提要

Index索引，就如同查字典時後面所使用的索引，
有些人用部首，有些人用注音查字，而且同類的也會放在一起，
可以依照需求查詢，速度勝於從第一頁翻到最後一頁。
資料庫也是一樣，我們可以選定一些Key做出索引結構，
更是可以依照資料的結構與查詢的特性使用不同資料結構的索引，
以便加速查詢。
不過呢，建立索引並不是越多越好，太多反而會拖慢了新增修改與刪除records的時間，
因為每建立一個index就是建立一顆index tree，
每新增一筆reocrd，不只要將record存入table中，更要將每個index新增該筆record

索引主要分為兩大類

• Clustered Indexes
• Secondary Index

DataStruct

Clustered Index

在了解這個index之前麻煩先對Table新增records的方式有初步的了解喔!
我們知道資料庫系統，主要使用了兩種Table儲存方式。

• Heap Organized Table (HOT)
• Index Organized Table (IOT)
  Clustered Index就是透過某key所建立的B+Tree，並且有用真實record的pointer，
  因此，
  Heap Organized Table (HOT)，可以任選key作為index，
  並且透過index的pointer直接指向真實record的位置，
  HOT使用Clustered Index即可完成index的效果囉!
  (HOT的records與index是兩個不同的東西)

Index Organized Table (IOT)，
因Table的儲存結構本身就是一個使用pk作為index的結構，並且透過index的pointer直接指向真實record的位置，
所以當pk不具有任何意義時，就單純只是拿來找recorder的真實位置而已喔。
(IOT的records與index是兩個是相同東西)

Secondary Index

只有Index Organized Table (IOT)，才有Secondary Index，
Secondary Index就是IOT正常的index了，
每個node有被作為index key的value與該record的主鍵
注意!這裡存的是record的主鍵，並非真實record的pointer
因此，當我們使用Secondary Index取資料時會先取出主鍵，再拿那些主鍵去Clustered Index中，取出真實的資料。

在使用Secondary Index的情況下，訪問索引組織表的效率非常低，
所以盡量只使用到index就好，減少去Clustered Index取出真實的records的步驟。

Compose Index

compose index就是將多個key作為index，
上面我們舉例了拿一個key作為index，建立出一顆b+tree index，
但如果常常使用1個以上的key作為搜尋條件，建立兩顆B+Tree Index又覺得浪費，
要是可以先以key1做出第一篩選，在使用key2做第二篩選，而且只要建出一顆b+tree index就好，
這時就引入Compose Index的概念，Compose Index就是key1做出第一篩選，在使用key2做第二篩選。

CREATE INDEX XXX_INDEX ON XXX_TABLE (key1，key2);

這裡需要特別注意建立index時的順序(不是query時的順序)，
你可以想像兩個key都有index功能，類似於同部首的國字，在使用筆畫做排序的概念。
所以key的順序特別重要。

有效查詢

1。 SELECT * XXX_TABLE WHERE key1='XXX'
2。 SELECT * XXX_TABLE WHERE key1='XXX' and key2='YYY'
3。 SELECT * XXX_TABLE WHERE key2='YYY' and key1='XXX'
4。 SELECT * XXX_TABLE WHERE key1='XXX' and key2='YYY' and key3='ZZZ'

咦?剛剛說了特別注意建立index時的順序，那麼為什麼index明明是設定key1，key2但範例3還是有效呢?
其實聰明的SQL Planner會幫你把該行query必成範例2的樣子。
就如同老師說，同學幫我查詢一下筆畫為10，火部的字有哪些，
字典根本沒有做筆畫的索引，一時反應不過來的同學可能就直接翻了整本字典，
靈活的同學當然就是先查部首再查筆畫啦!

無效查詢

SELECT * XXX_TABLE WHERE key2='XXX'

雖然建立index時同時使用了key1，key2，但最主要還是以key1為主建立的Tree
當key1相同時，使用key2作優先順序，
所以key2無法受到Compose Index的加速。

補充說明:

這裡的Compose Index與Postgres的INCLUDE clause不同，
INCLUDE clause沒有index的功能，單純使用key1作為index，然後順便帶上key2
Compose Index兩個key都有index功能，類似於同部首的國字，在使用筆畫做排序的概念。

結論

在使用index時，可以先確認一下目前想優化的table是使用
Heap Organized Table (HOT)還是 Index Organized Table (IOT)，
如果是HOT，基本上只需要針對index type與index key做規劃
如果是IOT，必須多考量Secondary Index存取的效率喔

下表為各家DBMS的Organized Table

參考資料

1. Index-Only Scan: Avoiding Table Access
2. Single vs Composite Indexes in Relational Databases