iT邦幫忙

2025 iThome 鐵人賽

DAY 19
1

昨天介紹完索引是什麼、為什麼需要索引和索引運作機制,今天要來聊聊 PostgreSQL 有哪些索引類型。除了昨天提到的 B-Tree 之外,其實 PostgreSQL 還有提供多種索引類型,像是 HashGiSTSP-GiSTGINBRIN ,每個類型適合的場景不一樣,下面會一一介紹。

B-Tree

B-Tree 是一種自平衡的樹形數據結構,每個節點可以有多個子節點,並且所有葉子節點都位於同一層,不會有深淺不一的情況。這使得 B-Tree 非常適合用於高效處理大型數據集的隨機存取和排序操作,所以 PostgreSQL 和 MySQL 的都預設 B-Tree 為索引類型。

下 SQL query的時候,如果有使用 <<==>=> 這些運算符號去設計搜尋條件,資料庫將會考慮使用 B-Tree 索引對搜尋進行優化。當然,除了上面的運算符號之外, BETWEENIN ,甚至是 LIKE 也都可以使用 B-Tree 優化,而且有的時候也有助於排序,也就是 ORDER BY 這個指令。

Hash

Hash 這個索引主要是把欄位資料轉換成 32-bit 的 hash code ,並在搜尋的時候使用 hash code 去做優化,這個索引的類型適用於條件是 = 的情境。

GiST

GiSTB-Tree 一樣都是樹狀結構,但有別於 B-Tree 局限於一維的數據搜尋, GiST 可以用於多維度的資料查詢,例如座標搜尋,如果搜尋條件有使用 <-><<&<&>>><<|&<||&>|>>@><@~=&& ,資料庫將會使用 GiST 的索引做優化。舉例來說,用 <-> 搜尋鄰近的座標,就可以使用 GiST

SELECT * FROM locations ORDER BY position <-> point '(101,456)' LIMIT 10;

SP-GiST

GiST 類似的一種索引類型,但是 SP-GiSTGiST 更彈性,支援更多的資料結構做優化,像是 quadtrees 、k-d trees 和 radix trees (tries) ,有別於 GiST 使用節點分割, SP-GiST 使用空間分割,對於座標搜尋的效果會更好,搜尋條件使用 <<>>~=<@<<||>> 時,就可以使用 SP-GiST 索引做優化。

GIN

是 Generalized Inverted Index 的縮寫,適合用於多維度資料的搜尋,像是 array 和 json , GIN 的資料結構會有 Entry TreeEntry Tree 使用的資料結構是 B-Tree ,然後每個節點對應 Posting Lists ,這個 Posting Lists 包含該資料庫欄位的部分資料,如下面圖片所示。
可以想像 GIN 的設計就像是 tree 的結構在搭配 hash 的結構,並達成分群的效果,所以在文字搜尋,GIN 的效果也會比前面的索引好,搜尋條件使用 <@@>=&& ,資料庫會嘗試使用 GIN 索引做優化。

BRIN

BRINB-Tree 的變形,跟 B-Tree 不一樣的地方在於, B-Tree 的節點是一個明確的值,而 BRIN 的節點則是一個區間,這麼做的優點是可以有效的降低樹的深度,提升搜尋效率,非常適合用於資料表有多筆資料的情境,像是要從幾千萬筆 log 中,撈出特定區間的 log ,就很適合使用這個索引。
但是在資料很分散的情況下,並無法顯現這個資料結構的優勢。跟 B-Tree 類似,搜尋條件使用 <<==>=> 時,資料庫可能會使用 BRIN 索引做優化。


上一篇
Day 18: 深入淺出索引 (上)
下一篇
Day 20: 什麼是 pgbench ?
系列文
我所不知道的PostgreSQL 30天30
圖片
  熱門推薦
圖片
{{ item.channelVendor }} | {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言