跳至主要內容
LinPolly
文章 雲端技術

PostgreSQL 索引策略：讓查詢速度提升 100 倍

11 分鐘閱讀 1,100 字

PostgreSQL 索引策略：讓查詢速度提升 100 倍

資料庫效能問題中，80% 可以透過正確的索引設計來解決。本文深入探討 PostgreSQL 的各種索引類型、使用時機，以及如何用 EXPLAIN ANALYZE 驗證效果。

索引的本質

索引是一種用空間換時間的資料結構，讓資料庫不需要做全表掃描（Sequential Scan）就能快速找到符合條件的資料。但索引不是萬能的：

  • 每個索引都會佔用額外磁碟空間
  • 每次 INSERT/UPDATE/DELETE 都需要維護索引
  • 過多索引反而拖慢寫入效能

EXPLAIN ANALYZE：診斷查詢效能

在討論索引之前，先學會看執行計畫：

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 123
  AND status = 'pending'
  AND created_at > NOW() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 20;

輸出中的關鍵數字：

Seq Scan on orders  (cost=0.00..45230.00 rows=12 width=142)
                     (actual time=0.123..2341.456 rows=15 loops=1)
  Filter: (user_id = 123 AND status = 'pending' ...)
  Rows Removed by Filter: 1234567
Planning Time: 0.5 ms
Execution Time: 2342.1 ms  ← 2.3 秒！

看到 Seq Scan 和高 Rows Removed by Filter 就是需要索引的信號。

B-Tree 索引（預設）

最常用的索引類型，適合等值查詢和範圍查詢：

-- 單欄索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 複合索引（順序很重要！）
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 含排序的索引（針對特定排序查詢最佳化）
CREATE INDEX idx_orders_created_desc ON orders(created_at DESC);

加上索引後，同樣的查詢：

Index Scan using idx_orders_user_status on orders
  (cost=0.43..124.56 rows=12 width=142)
  (actual time=0.234..1.456 rows=15 loops=1)
Planning Time: 0.8 ms
Execution Time: 1.7 ms  ← 從 2342ms 到 1.7ms！

複合索引的欄位順序

這是最容易犯錯的地方。複合索引的使用規則：

CREATE INDEX idx_example ON table(a, b, c);

-- 以下查詢能使用索引：
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND b > 5  -- a 等值，b 範圍

-- 以下查詢無法有效使用索引：
WHERE b = 2           -- 跳過了 a
WHERE b = 2 AND c = 3 -- 跳過了 a
WHERE a > 1 AND b = 2 -- a 範圍後 b 索引失效

選欄位順序的原則：

  1. 選擇性最高的欄位優先（等值查詢的欄位放前面）
  2. 等值查詢的欄位放在範圍查詢欄位之前

部分索引（Partial Index）

只索引滿足特定條件的資料列，適合資料分布不均的場景：

-- 只索引未完成的訂單（假設大部分訂單是已完成的）
CREATE INDEX idx_orders_pending
  ON orders(user_id, created_at)
  WHERE status = 'pending';

-- 查詢時條件必須匹配索引的 WHERE 子句
SELECT * FROM orders
WHERE user_id = 123 AND status = 'pending';  -- ✅ 使用部分索引

-- 這個查詢不會使用部分索引
SELECT * FROM orders
WHERE user_id = 123;  -- ❌ 沒有 status = 'pending' 條件

函式索引（Expression Index）

對計算結果建立索引，常用於不區分大小寫搜尋：

-- 不區分大小寫的 email 查詢
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- 查詢時必須使用相同的表達式
SELECT * FROM users WHERE LOWER(email) = LOWER('user@example.com');

-- JSON 欄位的特定路徑索引
CREATE INDEX idx_settings_theme
  ON users((settings->>'theme'));

SELECT * FROM users WHERE settings->>'theme' = 'dark';

GIN 索引：全文搜尋與陣列

GIN（Generalized Inverted Index）適合多值型別：

-- 全文搜尋
ALTER TABLE articles ADD COLUMN tsv tsvector;
UPDATE articles SET tsv = to_tsvector('chinese', title || ' ' || content);
CREATE INDEX idx_articles_tsv ON articles USING GIN(tsv);

-- 查詢
SELECT * FROM articles
WHERE tsv @@ to_tsquery('chinese', 'PostgreSQL & 索引');

-- JSONB 欄位的 GIN 索引（支援 @>、?、? 等運算子）
CREATE INDEX idx_users_tags ON users USING GIN(tags);

-- 陣列欄位查詢
SELECT * FROM users WHERE tags @> ARRAY['postgresql', 'backend'];

索引維護

-- 查看索引使用率（長期運行後才有意義）
SELECT schemaname, tablename, indexname,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;  -- 排序找出從未被使用的索引

-- 找出重複的索引
SELECT a.indexname AS index1, b.indexname AS index2,
       a.tablename
FROM pg_indexes a
JOIN pg_indexes b ON a.tablename = b.tablename
  AND a.indexname != b.indexname
  AND a.indexdef = b.indexdef;

-- 重建碎片化的索引（不鎖表）
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

-- 建立索引時不鎖表（正式環境必用）
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

複合索引 vs 多個單欄索引

-- 情境：查詢同時過濾 user_id 和 status
-- 方案 A：一個複合索引
CREATE INDEX idx_a ON orders(user_id, status);

-- 方案 B：兩個單欄索引（PostgreSQL 可以 Bitmap Index Scan 合併使用）
CREATE INDEX idx_b1 ON orders(user_id);
CREATE INDEX idx_b2 ON orders(status);

通常複合索引效率更高，但需要針對具體查詢模式設計。

索引策略總結

場景 建議索引
外鍵欄位 必建 B-Tree 索引
常用 WHERE 條件 B-Tree 複合索引
大量資料中少量特定狀態 部分索引
不區分大小寫搜尋 函式索引
全文搜尋、JSONB GIN 索引
地理位置 GiST 索引

總結

索引設計的黃金法則：先用 EXPLAIN ANALYZE 找出慢查詢，分析查詢模式後再建索引，建完後驗證效果。不要提前過度最佳化，也不要讓慢查詢拖垮線上服務。定期檢視索引使用率，清除無用索引，讓資料庫保持健康狀態。

#Supabase #Docker

分享這篇文章