PostgreSQL 索引原理與查詢優化實戰

一次慢查詢把下單介面拖垮的事故

去年雙十一預熱，下單介面 P99 從 80ms 飆到 2.3s。監控裡 CPU 打滿，連線池被吃光。我們一開始以為是程式碼問題，翻了半天業務邏輯，最後 EXPLAIN ANALYZE 一跑：一條按 user_id + created_at 範圍查訂單的 SQL，在做全表順序掃描（Seq Scan），掃描了 800 萬行。

加了一個組合索引，P99 回到 60ms。那次之後我養成個習慣：任何慢查詢，先問它走沒走索引，再談別的。

下面把 PostgreSQL 索引這件事講透，重點是「什麼時候用哪種、為什麼」。

一、PostgreSQL 有哪些索引

不是所有索引都長一個樣。PostgreSQL 內建好幾種存取方法：

類型	適用場景	底層
B-tree	等值、範圍、排序、前綴	平衡樹（預設）
GIN	多值/反向索引：陣列、`jsonb`、全文檢索	倒排
GiST	幾何、範圍、相似度、全文檢索	通用搜尋樹
BRIN	物理上按某種順序儲存的大表（如時間序）	區塊範圍摘要
Hash	純等值（基本被 B-tree 取代）	雜湊桶

絕大多數業務用 B-tree 就夠了。下面重點講 B-tree，再補 GIN 和部分/表達式索引這兩個常被忽略的利器。

二、B-tree 是怎麼工作的

B-tree 把索引列排成有序的平衡樹。查找一個值時走樹降到葉子，複雜度 O(log n)。它支援：

等值：WHERE status = 'paid'
範圍：WHERE created_at > '2026-01-01'
排序：ORDER BY id DESC
前綴模糊：WHERE email LIKE 'ada%'（注意前模糊 '%ada' 用不上）

組合索引與最左前綴

組合索引 (a, b, c) 其實是按 a、然後 b、然後 c 排序的。所以它只在查詢命中對最左前綴時有效：

-- 能用到 (user_id, created_at)
WHERE user_id = 5 AND created_at > '2026-01-01';
WHERE user_id = 5;
WHERE user_id = 5 AND created_at = '2026-01-01';

-- 用不到（跳過了 user_id）
WHERE created_at > '2026-01-01';

這就是為什麼那次事故裡，單獨在 created_at 上建索引沒用——查詢是先按 user_id 過濾的。

三、讀懂 EXPLAIN ANALYZE

光會建索引不夠，得會看執行計畫。EXPLAIN 是預估，EXPLAIN ANALYZE 是真實執行（會真的跑，寫操作請用 EXPLAIN (ANALYZE, BUFFERS) 謹慎對待）。

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 5 AND created_at > '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

關注三件事：

Seq Scan vs Index Scan：前者是全表掃，慢查詢的頭號嫌疑人。
cost 與 actual time：(cost=0.00..123.45 rows=10) 裡的 actual time=0.02..1.20，單位是毫秒。看實際耗時，別被預估帶偏。
Buffers：Buffers: shared hit=3 read=120 表示讀了 120 個磁碟區塊、只命中 3 個快取。命中率低說明資料不在記憶體，可能是索引沒覆蓋、或 shared_buffers 太小。

一個典型的壞計畫：

Seq Scan on orders  (cost=0.00..182345.00 rows=812 width=64)
  Filter: ((user_id = 5) AND (created_at > '2026-01-01'::date))
  Rows Removed by Filter: 7999999

Rows Removed by Filter 接近全表行數，說明在逐行過濾——這正是缺索引的訊號。

四、組合索引設計三原則

等值列在前，範圍列在後。因為 B-tree 在第一個範圍條件之後就無法繼續利用有序性了。上面 (user_id, created_at) 就是正確順序（user_id 等值在前）。
選擇性高的列優先放前面（在等值條件裡）。區分度高，過濾得快。
把 ORDER BY 的列併進索引，避免額外的排序節點（Sort）。查詢 WHERE user_id=5 ORDER BY created_at DESC 用 (user_id, created_at) 就能直接按序取，省掉 Sort。

覆蓋索引（INCLUDE）

如果查詢只取索引裡已有的列，PostgreSQL 連表都不用回，這叫「索引覆蓋 / Index Only Scan」。用 INCLUDE 把不需要參與查找、但 SELECT 要用的列附加進去：

CREATE INDEX idx_orders_user_time
  ON orders (user_id, created_at)
  INCLUDE (status, total);

這樣 SELECT status, total FROM orders WHERE user_id=5 AND created_at > ... 可以純索引返回，不再回表。

五、部分索引與表達式索引

部分索引（Partial Index）

如果業務只頻繁查「未支付」的訂單，給全表建索引是浪費。只對關心的列建索引：

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

索引體積更小、維護更便宜，且對 WHERE status = 'pending' 的查詢精準命中。

表達式索引（Expression Index）

查詢裡對列做了函式運算，普通索引就用不上。比如按小寫信箱查：

CREATE INDEX idx_users_lower_email ON users (lower(email));

-- 這樣查才命中
SELECT * FROM users WHERE lower(email) = 'ada@example.com';

再比如按「月份」聚合，常寫 date_trunc('month', created_at)，就建 CREATE INDEX ... ON orders (date_trunc('month', created_at))。索引表達式和查詢表達式必須逐字一致才會被用上。

六、GIN：陣列、jsonb 與全文檢索

當你要索引「一個值對應多個元素」時，GIN 是首選。

jsonb 裡的欄位

CREATE INDEX idx_events_payload ON events USING gin (payload jsonb_path_ops);

SELECT * FROM events WHERE payload @> '{"type": "click"}';

@> 是 jsonb 包含運算符，GIN 能高效處理。

陣列包含

CREATE INDEX idx_posts_tags ON posts USING gin (tags);

SELECT * FROM posts WHERE tags @> ARRAY['go','postgres'];

全文檢索

CREATE INDEX idx_articles_body ON articles USING gin (to_tsvector('zhcfg', body));

SELECT * FROM articles
WHERE to_tsvector('zhcfg', body) @@ to_tsquery('zhcfg', '資料庫 索引');

注意 to_tsvector('zhcfg', ...) 裡的設定名要和索引裡完全一致，否則走不上索引。

七、統計資訊與 autovacuum：索引為什麼會「突然失效」

我們踩過另一個坑：一個跑了半年的查詢某天突然變慢，執行計畫從 Index Scan 退回 Seq Scan。原因不是 SQL 變了，是統計資訊過期了。

PostgreSQL 的優化器靠 pg_statistic 裡的資料分佈估算行數。如果表大量寫入後沒及時 ANALYZE，估算失真，優化器可能「算錯」而放棄索引。

autovacuum 會順帶做 ANALYZE，但大批量匯入後它可能跟不上。
手動觸發：ANALYZE orders; 或 VACUUM ANALYZE orders;
查統計：SELECT * FROM pg_stats WHERE tablename = 'orders';

經驗：大批量 ETL / 刪資料之後，務必顯式 ANALYZE。另外，如果某列值極度傾斜（比如 99% 都是 status='done'），優化器知道查 'done' 幾乎等於全表，自然不走索引——這時部分索引（只索引那 1% 的非 done）才是正解。

八、慢查詢優化 checklist

我排障時基本按這個順序：

EXPLAIN ANALYZE 看是不是 Seq Scan。
看 Rows Removed by Filter，估算是不是嚴重失真（對比實際行數）。
檢查 WHERE / ORDER BY / JOIN 列是否有索引，組合索引順序對不對。
函式包列？lower()、date_trunc() 之類需要表達式索引。
是否命中對最左前綴。
統計資訊是否新鮮（ANALYZE）。
該列是否選擇性太低，考慮部分索引。
能不能用覆蓋索引免去回表。

九、實戰：優化一個訂單查詢

原 SQL（慢，2.1s）：

SELECT id, status, total, created_at
FROM orders
WHERE user_id = 42
  AND status IN ('paid', 'shipped')
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 50;

表有 800 萬行，原執行計畫是 Seq Scan。

第一步：建組合索引，等值列 user_id 和 status 在前，範圍 created_at 在後。但 status 是 IN（多值），把它放在中間，B-tree 仍能利用 user_id 的有序前綴：

CREATE INDEX idx_orders_user_status_time
  ON orders (user_id, status, created_at)
  INCLUDE (total);

第二步：ANALYZE orders; 刷新統計。

第三步：複查執行計畫，確認變成 Index Scan，且 Buffers: shared hit 很小、actual time 降到 1ms 級別。

為什麼 IN 能放中間：B-tree 對 (user_id, status, created_at) 來說，user_id=42 把範圍縮到該用戶，status IN (...) 在這個範圍內繼續縮小，仍保留 created_at 的有序性，於是 ORDER BY created_at DESC LIMIT 50 可以直接沿索引倒序取前 50 行，不必排序、不必掃全表。這一條索引把 2.1s 幹到了個位數毫秒。

常見問題

Q1：索引是不是越多越好？

恰恰相反。索引拖慢寫入（INSERT/UPDATE/DELETE 都要維護），佔磁碟，還可能讓優化器選錯。一張頻繁寫的表，索引超過 5~6 個就要警惕。每個索引都要能回答「它加速了哪個真實查詢」。

Q2：為什麼我建了索引，查詢還是不走？

最常見：函式包列、沒命中對最左前綴、統計資訊失真、列選擇性太低、或查詢回了表裡大部分行（此時 Seq Scan 反而更便宜）。用 EXPLAIN ANALYZE 驗證，別靠猜。

Q3：LIKE 能走索引嗎？

'abc%' 這種前導模糊能走 B-tree（因為有序）。'%abc' 後導模糊不行。如果需要後導/包含匹配，考慮 pg_trgm 的 GIN trigram 索引或全文檢索。

Q4：UNIQUE 約束會自動建索引嗎？

會。PRIMARY KEY 和 UNIQUE 都會自動建立 B-tree 索引，不用你再手動建。

Q5：怎麼看一個表有哪些索引、分別多大？

SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass))
FROM pg_indexes
WHERE tablename = 'orders';

工具推薦

寫 SQL、把查詢結果和 JSON 互相倒騰時，這幾個 ToolsKu 工具順手：

SQL 格式化 — 把又長又亂的慢查詢 SQL 排整齊，便於 EXPLAIN 分析
JSON → SQL — 拿到的 JSON 結果快速生成 INSERT 做壓測資料
JSON 結構對比 — 對比優化前後兩次查詢返回的欄位差異

索引優化的本質，是幫優化器「少讀點資料」。所有的技巧——組合順序、部分索引、覆蓋索引——都指向同一件事：讓一次查詢盡可能只在索引和很少的區塊裡完成。