昨天介紹完索引是什麼、為什麼需要索引和索引運作機制,今天要來聊聊 PostgreSQL 有哪些索引類型。除了昨天提到的 B-Tree
之外,其實 PostgreSQL 還有提供多種索引類型,像是 Hash
、 GiST
、 SP-GiST
、 GIN
和 BRIN
,每個類型適合的場景不一樣,下面會一一介紹。
B-Tree
是一種自平衡的樹形數據結構,每個節點可以有多個子節點,並且所有葉子節點都位於同一層,不會有深淺不一的情況。這使得 B-Tree
非常適合用於高效處理大型數據集的隨機存取和排序操作,所以 PostgreSQL 和 MySQL 的都預設 B-Tree
為索引類型。
下 SQL query的時候,如果有使用 <
、 <=
、 =
、 >=
和 >
這些運算符號去設計搜尋條件,資料庫將會考慮使用 B-Tree
索引對搜尋進行優化。當然,除了上面的運算符號之外, BETWEEN
和 IN
,甚至是 LIKE
也都可以使用 B-Tree
優化,而且有的時候也有助於排序,也就是 ORDER BY
這個指令。
Hash
這個索引主要是把欄位資料轉換成 32-bit 的 hash code ,並在搜尋的時候使用 hash code 去做優化,這個索引的類型適用於條件是 =
的情境。
GiST
跟 B-Tree
一樣都是樹狀結構,但有別於 B-Tree
局限於一維的數據搜尋, GiST
可以用於多維度的資料查詢,例如座標搜尋,如果搜尋條件有使用 <->
、 <<
、 &<
、 &>
、 >>
、 <<|
、 &<|
、 |&>
、 |>>
、 @>
、 <@
、 ~=
、 &&
,資料庫將會使用 GiST
的索引做優化。舉例來說,用 <->
搜尋鄰近的座標,就可以使用 GiST
。
SELECT * FROM locations ORDER BY position <-> point '(101,456)' LIMIT 10;
跟 GiST
類似的一種索引類型,但是 SP-GiST
比 GiST
更彈性,支援更多的資料結構做優化,像是 quadtrees 、k-d trees 和 radix trees (tries) ,有別於 GiST
使用節點分割, SP-GiST
使用空間分割,對於座標搜尋的效果會更好,搜尋條件使用 <<
、 >>
、 ~=
、 <@
、 <<|
、 |>>
時,就可以使用 SP-GiST
索引做優化。
是 Generalized Inverted Index 的縮寫,適合用於多維度資料的搜尋,像是 array 和 json , GIN
的資料結構會有 Entry Tree
, Entry Tree
使用的資料結構是 B-Tree
,然後每個節點對應 Posting Lists
,這個 Posting Lists
包含該資料庫欄位的部分資料,如下面圖片所示。
可以想像 GIN
的設計就像是 tree 的結構在搭配 hash 的結構,並達成分群的效果,所以在文字搜尋,GIN
的效果也會比前面的索引好,搜尋條件使用 <@
、 @>
、 =
和 &&
,資料庫會嘗試使用 GIN
索引做優化。
BRIN
是 B-Tree
的變形,跟 B-Tree
不一樣的地方在於, B-Tree
的節點是一個明確的值,而 BRIN 的節點則是一個區間,這麼做的優點是可以有效的降低樹的深度,提升搜尋效率,非常適合用於資料表有多筆資料的情境,像是要從幾千萬筆 log 中,撈出特定區間的 log ,就很適合使用這個索引。
但是在資料很分散的情況下,並無法顯現這個資料結構的優勢。跟 B-Tree
類似,搜尋條件使用 <
、 <=
、 =
、 >=
和 >
時,資料庫可能會使用 BRIN
索引做優化。