PostgreSQL 筆記 — 索引
在資料庫當中，為了改進查詢效率，在資料量大的時候，可以透過建立索引讓資料庫加速查詢效率。本篇文章將會探討 PostgreSQL 的 index。雖然在大部分的情況，直接使用 CREATE INDEX 的語法已經能夠應付常見的開發場景，但是在 PostgreSQL 當中，其實還提供了許多不同的 index types 使用。
索引的基本原理
資料庫最重要的就是查詢資料，當資料存儲在硬碟時就要考慮如何處理 I/O 的效能問題。外接硬碟因為其本身硬體的限制，會比直接在記憶體讀取耗費更多的時間。在資料庫當中，如果沒有對資料表建立索引，將會使用 sequential scans 來查詢資料。在資料數量小的時候並沒有太明顯的效能差異，甚至還比建立索引還快，不過當資料量越來越大，sequential scans 很有可能造成讀取過慢的問題。
索引如何解決 sequential scans 過慢的問題
透過額外在硬碟建立一張索引表，我們可以透過索引表當作目錄，資料庫在查詢時，就會用給定的索引表尋找相對應的索引。再透過由索引指向實體資料，連結到硬碟當中的實體位址。
B Tree
B Tree 是一個專門為硬碟優化的平衡樹，跟一般的二元樹不同，B Tree 的樹可以有很多分支，有效減少樹的深度。B tree 的原理需要比較長的篇幅介紹，所以這邊省略介紹。
PostgreSQL 中的索引
建立索引
在 PostgreSQL 中可以透過 SQL 語法來建立索引:
- CREATE INDEX index_name ON table_name (column_name) USING method;
用 EXPLAIN 來觀察 query 的效能。發現透過 B-Tree 的方式成功減少了查詢時間。
注意：
恰如其分的 index
為了解釋資料庫使用 index 的時機，我們先對某個只有兩筆資料的 table 下 index:
對於資料量小的資料，儘管建立了索引，資料庫仍然會使用 sequential scan 查詢。這是因為 Random I/O 的花費會比循序查詢的方式還高，因此如果資料量小時，就算建立 index 也不會增加查詢效能，反而浪費了硬碟空間。
index size
對一個具有 1300 多筆的資料建立 index:
透過 pg_relation_size 拿取相關 relation 的大小。刪除 index 可使用 SQL 如 DROP INDEX IF EXISTS index_name. 為了確保索引的完整性，在建立索引時，PostgreSQL 會將整張表做 exclusive lock，建立完索引後才 release。因此對頻繁查改的資料表來說很有可能造成服務中斷，對於筆數相當多的資料來說，建立索引有可能花上幾分鐘或是幾十分鐘。
維護 index
首先先在資料庫刪除幾筆資料，對於資料庫來說，預設刪除的方式並不是真正從硬碟當中釋放空間，而是標上類似「已刪除」的標記。對於索引來說，刪除資料並不會減少索引大小:
這時可以透過 REINDEX 的方式重新建立索引。REINDEX INDEX index_name:
重建索引會把無用的索引刪除，索引表的大小減少了。從上面簡單的測試可以得知，對於增刪頻繁的資料表來說，定期重建索引可以減少索引表的大小，如果需要刪除大量的資料，也盡量重建索引來維護索引表的大小，否則可能造成硬碟空間的浪費。不過 REINDEX 也會造成鎖表，如果不允許服務維修或暫停的話，可以直接用 CREATE INDEX CONCURRENTLY 的方式重新建立一個全新的索引，再把舊的索引刪除，並且重新命名索引。這會比 REINDEX 耗費更久時間，而且也比較麻煩一些，不過可以確保資料表不會被鎖住。
對特定資料做索引
有些時候我們只會頻繁查詢某些特定條件的資料，這時候就不一定要對所有資料下索引。透過 CREATE INDEX index_name ON table_name WHERE ... 的方式，我們可以針對需要的資料做索引。
索引排序
在建立索引時如果沒有特別宣告排序，預設會使用 ASC 來建立索引。不過在某些使用情境下，我們可能更常用 DESC 的方式來排序，例如排名、文章發佈日期等等，這時使用 DESC 建立索引更有效率。
- CREATE INDEX index_articles_on_published_date ON articles (published_date DESC);
B-Tree索引只有在全部清空索引 page 才會被重複利用，所以在資料庫刪除某個值並不會減少索引表的大小。原本的資料為 1400 筆，刪除 50 筆，索引大小沒有變化。
重新建立索引
Postgre 當中的 index type
上面介紹完了 postgre index 與使用方式，接下來介紹 Postgre 當中常見的 index type。如果在建立 index 時沒有特別指定使用的方法，PostgreSQL 會使用 B Tree 來建立索引。
Btree
B Tree 支援下列幾種查詢操作：
GIN(Generalized Inverted Index)
GIN 被用在全文搜尋或是或是像 Array 這種可能會被。如同字面上的意思，倒排索引，就是利用 Value 當作索引值，再去找相對應出現的索引。例如：
- this is cat
- this is an apple.
- cat meows.
- // build inverted index
- "this": {(0, 0), (1,0)}
- "is": {(0,1), (1,1)}
- "an": {(1,2)}
- "apple: {(1,3)}
- "cat": {(0,2), (2,0)}
- "meows": {(2,1)}
適用場景：
在 PostgreSQL 中使用 GIN：
- CREATE INDEX index_on_document on sentences using gin(document);
GiST 其實更算是一種通用的 interface，我們可以使用 GiST 來定義自己的 index 實現方式（B-Tree, R-Tree 等）。一般來說比較少使用到。但像是空間結構的資料，用 B-Tree 可能較難加速像是包含、相鄰、相交的搜索， 因此像是 PostGIS 就是使用 GiST 來建立對空間結構查詢效能較好的索引。
適用場景：
BRIN(Block Range Indexes)
跟 B-Tree 對每一行做索引不同，BRIN 會對一段 range 做索引，雖然性能方面上仍然是 B-TREE 勝，但是 size 比 B-Tree 小很多。
如果資料常常是以範圍查詢，例如 log 檔，某個範圍的交易訂單、出帳資料等等，這些資料量通常相當驚人，用 B-Tree 建立索引可能會耗費不小的硬碟空間。不過這些資料通常比較少使用精確搜尋，而是用像範圍搜尋的方式來批次處理這些資料，這時候使用 BRIN 可以得到還不錯的效能，同時節省了相當大的空間。
適用場景：
Bloom
Bloom Filter 是為了解決 Hash table 的空間以及查詢時間，算法本身的原理可以相當快速地判斷某個元素是否有出現在集合，但會有 false positive 的情形出現，因此在 PostgreSQL 當中需要做二次檢查。
- CREATE INDEX bloom_index_event ON events USING bloom (startTime, endTime, name, gifts)
- WITH (length=80, startTime=2, endTime=2, name=4, gifts=2);
適用場景：
總結
一般來說，B-Tree 幾乎能夠處理大部分的使用情境。但 PostgreSQL 有豐富的 index 類型可供使用，讓資料能夠更容易地被查詢、索引，也給開發者更高的彈性。這是我相當喜歡 PostgreSQL 的一大原因之一。這次介紹的 index types，並沒有很深入探討背後的實作原理，但有興趣的讀者可以自行尋找相關的資源閱讀。簡單整理重點如下:
Supplement
* Tutorialspoint - PostgreSQL - INDEXES
沒有留言:
張貼留言