某次和朋友描述建立索引，覺得自己講得很模糊，決定做一份筆記。
在資料庫當中
- 為了改進查詢效率，在資料量大的時候，可以透過建立索引讓資料庫加速查詢效率。
- 如果沒有對資料表建立索引，將會使用 Full table scan (sequential scans) 來查詢資料。
建立索引
- 簡單來說：『 一個數據庫中的索引就是一本書的目錄。』
- 原理來說：『 額外在硬碟建立一張索引表，當作目錄，當進行資料庫查詢時，針對查詢條件在索引表尋找相對應的索引。再透過此索引指向實體資料。假如查詢條件不在索引表裡，就會對整個表做循序查詢。』
建立索引，有助於加快 SELECT 查询和 WHERE 子句，但它會減慢使用 UPDATE 和 INSERT 语句时的數據操作。
當然可以創建或刪除索引，但不會影響數據。
索引功能分類
唯一索引
- 有兩個作用，一個是資料約束，一個是資料索引，其中資料約束主要用來保證資料的完整性，唯一索引產生的索引記錄中每一條記錄都對應一個唯一的 rowid(註1)
主關鍵字索引
- 產生的索引同唯一索引，只不過它是在資料庫建立主關鍵字時系統自動建立的
一般索引
- 不產生資料約束作用，其功能主要是對欄位建立索引表，
建立索引，用來提高資料查詢速度。
索引管理成本：
- 存儲索引的磁碟空間
- 執行資料修改操作（
INSERT,DELETE,UPDATE）產生的索引維護 - 在資料處理時會需額外的回退空間。
建立索引，資料修改時間會延長。
B-Tree 索引
B Tree 是一個專門為硬碟優化的平衡樹，是最常用的索引，其存儲結構類似書的索引結構，有分支和葉兩種類型的存儲資料塊，分支相當於書的大目錄，葉塊相當於索引到的具體的書頁。
- 一般索引及唯一約束索引都使用 B-Tree 索引。
- 在 PostgreSQL 中，如果沒有特別指定演算法，預設使用 B-Tree 來建立索引。
什麼情況下應該建立索引
- 表的主關鍵字
- 自動建立唯一索引
- 表的欄位唯一約束
- 直接條件查詢的欄位
- 用於條件約束的欄位
- 查詢中與其他表關聯的欄位
- 欄位常常建立了外鍵關係
- 查詢中排序的欄位
- 排序的欄位如果通過索引去訪問那將大大提高排序速度
什麼情況下應該不建或少建索引
- 資料筆數太少的表
- 頻繁大批量操作，或時常進行
INSERT,DELETE,UPDATE的表 - 資料重複且分佈平均的表字
- 含有大量的 NULL 值的欄位上
索引個人額外筆記
- 可以針對單個 column 做索引。也可以一次對多個 column 建立索引。
- 可以使用
EXPLAIN來觀察 query 的效能。 - 資料量小
- 建立索引，資料庫仍然會使用 Full table scan 查詢。
- 因為 Random I/O 的代價會比 table scan 的方式還高，因此就算建立索引也不會優化查詢效能。
- 浪費了硬碟空間。
- 資料量大
- 建立索引，提高查詢效率。
- 導致
INSERT,DELETE,UPDATE的時間成本提高。 - 頻繁上述操作，則需要定期維護，不然也會浪費硬碟空間。
- 刪除資料並不會減少索引大小，因為預設刪除的方式並不是真正從硬碟當中釋放空間，而是標上類似「已刪除」的標記。
- 使用
CREATE INDEX建立索引時會鎖住整張表，在資料量大時可能需要耗費不少時間，嚴重可能導致伺服器資料庫死機。 - 建立索引時，可加入
CREATE INDEX CONCURRENTLY來確保資料表不會被 lock 住，但需要花更多的時間來建立索引。 - 使用
REINDEX的方式重新建立索引做維護，但是也會鎖表。- 如果服務不允許關閉維護的狀況，可以用
CREATE INDEX CONCURRENTLY的方式重新建立一個全新的索引，再把原本的索引刪除、重新命名新的索引。
- 如果服務不允許關閉維護的狀況，可以用
- 在建立索引時如果沒有特別宣告排序，預設會使用 ASC 來建立索引。
這是學習筆記，任何問題歡迎留言來信糾正喔