Terry Yao's Blog

Index 設計深入：B-Tree / Hash / GIN / BRIN 和什麼時候索引反而更慢

Index 設計深入：B-Tree / Hash / GIN / BRIN 和什麼時候索引反而更慢

閱讀時間約 5 分鐘

索引是寫換讀的交易

每個索引都讓：

  • SELECT 更快（在對的查詢上）
  • INSERT / UPDATE / DELETE 更慢（要維護索引）
  • 磁碟用量增加

決策框架：這個欄位的查詢頻率 vs 寫入頻率，以及資料的分佈特性。

B-Tree（預設）

最通用的索引結構，適合等值查詢和範圍查詢：

-- 等值：WHERE user_id = 123
-- 範圍：WHERE created_at BETWEEN '2024-01-01' AND '2024-03-01'
-- 排序：ORDER BY name ASC
-- Like 前綴：WHERE name LIKE 'Alice%'（但 '%Alice' 不走索引）

B-Tree 不適合：Like %abc（中間或後綴模糊查詢）、全文搜尋、JSON 欄位的嵌套查詢、陣列包含查詢。

Hash Index

只支援等值查詢（=），不支援範圍或排序。在 PostgreSQL 12 以前 Hash index 有 crash recovery 問題，現在已修復。

Hash vs B-Tree 等值查詢：理論上 Hash 更快（O(1) vs O(log n)），但實際差距很小，且 B-Tree 更通用。除非有非常確定的等值查詢場景，通常不需要特別選 Hash。

GIN（Generalized Inverted Index）

為「一個值包含多個元素」的場景設計：

  • 全文搜尋（tsvector 欄位）
  • JSONB 欄位查詢（WHERE data @> '{"status": "active"}'）
  • 陣列欄位（WHERE tags @> ARRAY['python', 'backend']）
  • hstore
CREATE INDEX idx_products_tags ON products USING GIN(tags);
-- 現在支援：WHERE tags @> ARRAY['python']

GIN 索引的代價：構建慢，寫入慢（因為要維護倒排索引）。如果資料寫入頻繁，考慮 fastupdate 參數或定期 VACUUM。

BRIN（Block Range Index）

最小的索引，只記錄每個「資料塊範圍」的 min/max 值。適合自然排序的大表（時序資料、append-only 日誌）：

-- 按時間插入的日誌表
CREATE INDEX idx_logs_created_at ON logs USING BRIN(created_at);
-- 查詢：WHERE created_at > '2024-01-01'
-- BRIN 知道哪些 block 的 max created_at < '2024-01-01'，直接跳過

BRIN 索引大小可以是 B-Tree 的 1/1000，但只對物理順序和查詢條件相關的場景有效。

覆蓋索引（Covering Index）

把查詢需要的所有欄位都放進索引，讓 query 不需要回到主表：

-- 查詢：SELECT name, email FROM users WHERE user_id = 123
-- 普通索引：走 user_id 索引找到 row，再去主表讀 name 和 email
-- 覆蓋索引：索引裡就有 name 和 email，不需要回表
CREATE INDEX idx_users_covering ON users(user_id) INCLUDE (name, email);

適合高頻查詢的固定欄位組合。代價是索引更大。

部分索引（Partial Index）

只對滿足條件的 row 建索引：

-- 只對未完成的訂單建索引（假設 90% 的訂單已完成）
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';
-- 索引大小是全表索引的 10%，查 pending 訂單更快

索引比沒索引更慢的情況

Selectivity 太低：如果 status 欄位只有 active / inactive，50% 的 row 是 active，走索引反而比 full table scan 慢（因為要先讀索引，再跳著讀主表）。Query planner 通常會自動選 full scan，但如果估計統計不準確就可能選錯。

索引欄位被函式包裹：

-- 不走索引：
WHERE LOWER(email) = 'alice@example.com'
-- 走索引（需要建函式索引）：
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

過時的統計資訊：ANALYZE 收集的統計過時時，query planner 的估計不準，可能選錯索引。定期 VACUUM ANALYZE 或調整 autovacuum 設定。

診斷工具

-- 看 query 的執行計劃
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 123;
 
-- 找沒有被使用的索引
SELECT indexname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0;
 
-- 找缺少索引的 sequential scan
SELECT relname, seq_scan FROM pg_stat_user_tables WHERE seq_scan > 1000 ORDER BY seq_scan DESC;

索引設計沒有一個公式，要配合 EXPLAIN ANALYZE 的實際執行計劃做決定，而不是憑感覺「這個欄位應該要有索引」。

關係圖譜