前言

索引(Index)類型的選擇對於資料庫效能的優化至關重要，不同的索引類型適用於不同的查詢和資料結構。近期開發常常會接觸到 PostgreSQL，所以順手整理幾種常見的 PostgreSQL 索引類型及其適用場景

索引類型

B-tree

B-tree 是 PostgreSQL 中最常用的索引類型，也是 PostgreSQL 建立索引時，未指名索引類型時預設的索引類型

B-tree 中的每個節點通常包含多個 key-value pair，其中 key 用於索引，value 指向表中相應的資料

它適用於以下幾種查詢：

  • 比較操作：<、>、>=、<=
  • 區間查詢：BETWEEN
  • 空值查詢：IS NULL

語法如下

-- 建立 table 的索引
CREATE INDEX index_name ON table_name;

-- 建立單獨 column 的索引
CREATE INDEX index_name ON table_name (column_name);

Hash

Hash 索引是應用 hash 的特性來達到快速查詢的情境．適合需要判定相等(equality check)的查詢：

  • 相等：=
  • 存在：IN

遇到資料異動(INSERT, UPDATE, DELETE)的時候，為了解決 hash 碰撞(collision)和重製資料(rehash data)，維護 hash 的行爲會比 B-tree 帶來更昂貴的成本．

除此之外，雖然 Hash 索引在等值查詢上的效能非常好，但它在範圍查詢(range query)和排序(sorting)的情境上不及 B-tree 來得適合，因此在使用上需要特別小心，一般來說在沒有特殊要求下，使用 B-tree 已能滿足整體效能需求

語法如下

CREATE INDEX index_name ON table_name USING HASH (column_name);

GiST

GiST（Generalized Search Tree）索引並不是一種索引，而是一種可以實作許多不同索引策略的基礎結構，GiST 是一種廣義搜索樹，適用於一些特殊的資料類型和查詢，如：

  • 地理資料(Geometric objects)
  • 全文檢索(Text documents)

GiST 是基於多維樹(multidimensional trees)，允許靈活的搜尋操作，如：相等、範圍和空間操作，以及重疊(overlaps)、包含(containment)和基於距離的搜尋(distance-based searches)

語法如下：

-- 欄位型別必須是 tsvector 或 tsquery
CREATE INDEX index_name ON table_name USING GIST (column_name);

SP-GiST

SP-GiST（Space-partitioned GiST）是 GiST 的一種變體，專門為特定類型的資料和查詢進行了優化，如：

  • GIS
  • 多媒體資料(multimedia)
  • 電話路由(phone routing)
  • IP 路由(IP routing)

SP-GiST 通過空間分割技術，它們將索引空間劃分為不重疊(non-overlapping)的區域，從而優化特定存取模式的搜尋效能，它們對於空間索引特別有效，並且可以處理複雜的空間查詢，包括交集(intersection)、最近鄰(nearest-neighbor)和聚類(cluster)操作。

語法如下

-- 欄位型別必須是 tsvector 或 tsquery
CREATE INDEX index_name ON table_name USING SPGIST (column_name);

GIN

GIN（Generalized inverted indexes）索引適用於在單個欄位中存儲多個值的情況，例如：

  • hstore
  • array
  • jsonb
  • range

GIN 索引對於處理包含多個值的複雜資料結構非常有效，特別是在需要快速檢索其中的某些元素時，一般來說適用於全文檢索(Full-text search)

GIN vs GiST

效能評估上，兩者存在巨大差異

  • 查詢效能表現: GIN > GiST
  • 建立索引的成本: GIN > GiST
  • 索引佔用的空間大小: GIN > GiST

由此可見

  • GIN 更適合靜態資料(static data)，因為靜態資料改動頻率較低，可有效降低 GIN 建立索引的成本
  • 反觀 GiST 更適合動態資料(dynamic data)

詳細比較參考PostgreSQL 官方文件的說明

語法如下

CREATE INDEX index_name ON table_name USING GIN (column);

BRIN

BRIN（Block range indexes）索引適用於具有線性排序順序(linear sort order / sequential/ natural ordering) 的欄位，例如：

  • 銷售訂單表的 created_at 日期欄位
  • 時間序列資料(time series)

BRIN 索引具有以下特色：

  • 不適用未排序的資料
  • BRIN 索引將 table 劃分為邏輯區塊(logical blocks)並儲存每個區塊的摘要資訊(summary information)，每個區塊包含一系列值，索引會儲存每個區塊內的最小值和最大值
  • BRIN 索引只會儲存區塊層級摘要(block-level summaries)，所以與其他索引類型相比其容量會較小，這使得 BRIN 索引特別適合大資料環境下的範圍查詢
  • BRIN 索引通常用於順序掃描(sequential scans)的 read-intensive 的情境

語法如下

CREATE INDEX index_name ON table_name USING BRIN (column);

Bitmap

Bitmap 索引通常適用於具有大量重複值(duplicate)的欄位，例如：

  • 性別欄位（男性或女性）

Bitmap 索引具有以下特色：

  • 適用於低基數(low cardinality)欄位，所謂的低基數指的是較少重複值
  • Bitmap 索引在低基數欄位上表現出色，因為這些欄位的可能值較少，並且每個值會在資料集中出現多次
  • 在進行涉及多個欄位的查詢時，Bitmap 索引能夠有效地結合多個條件，提高查詢效能。
    • 例如，當查詢同時涉及性別和地區等多個低基數欄位時，Bitmap 索引可以迅速過濾資料
  • 由於 Bitmap 索引使用位圖來表示資料，所需的存儲空間相對較小

語法如下

CREATE INDEX index_name ON table_name USING BITMAP (column);

其他索引相關的指令

-- 查看所有索引 (psql中使用：\di)
SELECT * FROM pg_indexes;

-- 創建一個索引而不阻塞表的讀寫操作
CREATE INDEX CONCURRENTLY index_name ON table_name (column_name);

-- 刪除index
DROP INDEX index_name;

-- 在不阻塞表操作的情況下刪除索引
DROP INDEX CONCURRENTLY index_name;

-- 重新建立索引
REINDEX INDEX index_name;
REINDEX TABLE table_name;
REINDEX DATABASE database_name;

-- 更改索引名稱
ALTER INDEX index_name RENAME TO new_index_name;

PostgreSQL 中的 JSON 資料如何透過索引加速查詢

PostgreSQL 內有幾種儲存 JSON 資料的型態，一般來說沒有特殊要求下，都會以 JSON 型態來儲存資料，但如果今天想要在 JSON 欄位上增加索引來加速查詢，就必須使用 JSONB 作為 JSON 資料的儲存型態

JSON

JSON 是 PostgreSQL 最常見的 JSON 資料儲存型態

具有以下特點

  • 完整保留 text 副本
    • JSON 資料在儲存時保留完整的文本格式
    • 代表每次處理該欄位都需要重新解析（reparse）這些文本數據
  • 保留格式和排版
    • 會保留原始 JSON 資料的縮排和排版
  • 寫入速度快
    • 由於儲存過程只需複製文本，所以插入（INSERT）操作速度較快。
  • 讀取速度慢
    • 由於每次讀取（SELECT）操作都需要重新解析文本，所以讀取性能較慢。
  • 適用場景
    • 適合大量寫入操作
    • 適合需要保留原始 JSON 格式和排版的應用
    • 適合簡單的讀取操作，當讀取性能要求不高時使用

JSONB

PostgreSQL 內有另一種 JSON 資料儲存的型態，叫做 JSONB

具有以下特點

  • 將文本轉換為二進位格式
    • JSONB 在儲存時會將 JSON 文本轉換為二進位格式，並移除縮排和排版
  • 寫入速度慢
    • 由於需要將文本轉換為二進位格式，所以插入（INSERT）操作速度較慢
  • 讀取速度快
    • 由於儲存的是已解析的二進位數據，所以讀取（SELECT）操作不需要重新解析，讀取性能較快。
  • 支持巢狀 JSON
  • 可搭配索引使用

最後一點可搭配索引使用的特性，在 PostgreSQL 中可以這樣使用：

-- 對整個 JSONB 欄位建立索引
CREATE INDEX index_name ON table_name USING index_type (jsonb_column)

-- 單獨為 JSONB 欄位的特定鍵建立索引
CREATE INDEX index_name ON table_name USING index_type ((column_name->>'json_key'));

index_type 可用任一個前面介紹的 index 類型

底下是一個簡單範例

假設我們有一個包含 JSONB 欄位的資料表 products

CREATE TABLE products (
    id serial PRIMARY KEY,
    data jsonb
);

INSERT INTO products (data) VALUES
('{"name": "Product 1", "price": "100"}'),
('{"name": "Product 2", "price": "200"}');

為 JSONB 整體欄位建立 GIN 索引，以利全文檢索(鍵值對查詢)

CREATE INDEX idx_gin_products_data ON products USING gin (data);

為 JSONB 特定鍵(price)建立 BTREE 索引，以利範圍搜尋

CREATE INDEX idx_btree_products_price ON products ((data->>'price'));

查詢 data 欄位 JSON 中 name 這個 key 的資料，將會使用 GIN 索引

SELECT * FROM products WHERE data @> '{"name": "Product 1"}';

查詢 data 欄位 JSON 中 price 這個 key 的資料，因爲有針對 price 這個鍵值單獨添加 BTREE 索引，所以這筆查詢將會使用 BTREE 索引

SELECT * FROM products WHERE data->>'price' = '100';
SELECT * FROM products WHERE data->>'price' > '100';

由此可知，PostgreSQL 支援在 JSONB 欄位上添加整體或單獨鍵值的索引，可以更細緻的控制索引的使用，使用上更為靈活

整體來說

  • JSONB 適合讀取操作較多的情境
  • JSONB 適合需要索引以提高查詢性能的應用
  • 通常推薦使用 JSONB，因為其讀取性能和靈活性優於 JSON

結論

選擇適當的索引類型可以顯著提高資料庫查詢的效能，但 Index 的天性就是以空間換取時間，過度建立索引會佔用資料庫過多的空間，因此並不是所有欄位都建立 index 就是好事．

除此之外，當資料庫存有大量資料的情境下，貿然更換欄位的 index 類型，會讓 DB 重啟時花費更多時間做 migration，不可避免地會讓 application 等待 DB 完成 migration 後才能使用(冷啟動)

所以適當了解索引類型，才能知道什麼時候該使用哪種索引，以利資料的查詢．B-tree 索引是通用的選擇，適合大多數情況；Hash 索引適用於簡單的等值查詢；GiST 和 SP-GiST 則適合處理複雜資料和特殊查詢；GIN 索引適用於包含多個值的欄位；BRIN 索引則在處理具有線性排序的大資料集時非常有效；BITMAP 索引則適用於低基數的欄位資料；JSON 欄位的資料可以全域或單獨為某個鍵增加索引，靈活的提升查詢的速度