一、概覽速覽表（先有直覺）

記憶法：“B 通吃、H 等值、GIN 倒排、GiST 空間、SPG 前綴、BRIN 順序”

二、B-tree（默認 & 通吃）

特性

• PostgreSQL 的 UNIQUE/主鍵約束本質(zhì)上都是 B-tree 索引。
• 適配等值/范圍/排序/聚合的常見訪問路徑與 ORDER BY … LIMIT。
• 支持覆蓋索引（INCLUDE，用于只讀回表字段；主要用于 B-tree）。

常用語法

-- 單列 / 多列
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

-- 覆蓋索引（減少回表）
CREATE INDEX idx_orders_status_created_inc
ON orders(status, created_at)
INCLUDE (amount);

-- 表達式/部分索引
CREATE INDEX idx_lower_email ON users (lower(email));
CREATE INDEX idx_paid_recent ON orders (created_at)
WHERE status = 'PAID';

實戰(zhàn)要點

• 多列 B-tree 的左前綴原則：查詢條件要盡量命中前導(dǎo)列。
• 大量 LIKE 'abc%' 的前綴匹配通常也可命中 B-tree；但包含式 %abc% 需 pg_trgm。
• 注意 NULL：唯一約束允許多行 NULL。

三、Hash（只做“等值”）

場景：極端等值查找且鍵寬/比較代價特殊時，可能略小/略快。
限制：不支持唯一約束與范圍/排序；面窄，大多數(shù)等值場景仍用 B-tree。

CREATE INDEX idx_users_hash_email ON users USING hash(email);

備注：自 PG10 起 Hash 索引 WAL 持久化，可崩潰恢復(fù)，但優(yōu)勢有限。

四、GIN（倒排：jsonb/數(shù)組/全文/trigram）

場景

• jsonb 包含/鍵值查詢：@>, ?, ?&；
• 數(shù)組元素包含/交集；
• 全文檢索 to_tsvector(...) @@ to_tsquery(...)；
• 模糊查詢：pg_trgm 的 trigram + LIKE/ILIKE '%abc%'。

語法與 opclass

-- jsonb：兩種常用 opclass
CREATE INDEX idx_doc_gin ON docs USING gin(data jsonb_ops);       -- 全功能，體積偏大
CREATE INDEX idx_doc_path ON docs USING gin(data jsonb_path_ops);  -- 對 @> 優(yōu)化更好

-- 數(shù)組包含
CREATE INDEX idx_tags_gin ON posts USING gin(tags);

-- 全文
CREATE INDEX idx_posts_fts ON posts USING gin(to_tsvector('simple', title || ' ' || body));

-- trigram 模糊（需擴展）
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_user_name_trgm ON users USING gin(name gin_trgm_ops);

調(diào)優(yōu)

-- 批量重放/更新多時：構(gòu)建或維護參數(shù)
CREATE INDEX ... USING gin(...) WITH (fastupdate = on, gin_pending_list_limit = '512MB');

• 優(yōu)點：讀極快（特別是包含/全文/模糊）；
• 缺點：寫慢、體積大；更適合“讀多寫少”或批量導(dǎo)入。

五、GiST（“通用”搜索樹：空間/范圍/KNN/排斥約束）

場景

• 幾何/地理（PostGIS）、range、inet/cidr、相交/包含等；
• KNN 最近鄰查詢：ORDER BY <->；
• 排斥約束（Exclusion Constraint）：避免時間段/空間重疊（常配合 btree_gist）。

示例

-- 范圍不重疊的預(yù)約（時間區(qū)間）
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE booking(
  room_id int,
  during tstzrange,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)  -- 禁止同房間時間相交
);

-- KNN：最近地點
CREATE INDEX idx_poi_gist ON poi USING gist (geom);
SELECT * FROM poi ORDER BY geom <-> ST_SetSRID(ST_MakePoint(121.5,31.2), 4326) LIMIT 20;

要點

• GiST 是“框架”，性能取決于具體 operator class（幾何、range 等）。
• 構(gòu)建大索引可：WITH (buffering = on)；范圍型查詢常見“回檢”。

六、SP-GiST（空間分割/前綴）

場景

• 前綴類數(shù)據(jù)（如手機號/URL/域名前綴）基于 trie；
• 坐標點的 k-d/四叉樹等強分割結(jié)構(gòu)。

-- 前綴查詢
CREATE INDEX idx_phone_prefix ON users USING spgist (phone_number);
SELECT * FROM users WHERE phone_number LIKE '1389%';

特點：對分布不均勻且可遞歸分割的數(shù)據(jù)更友好；更新/插入性能通常優(yōu)于 GiST 的某些場景。

七、BRIN（Block Range Index：大表“順序相關(guān)”神器）

場景

• 超大表（千萬/億級），created_at、自增 id 與物理順序相關(guān)性高；
• 大范圍查詢或分段掃描，BRIN 能以極小代價快速縮小掃描頁面。

-- 典型配置：pages_per_range 控制摘要粒度（越小越精細）
CREATE INDEX idx_orders_brin_created
  ON orders USING brin (created_at)
  WITH (pages_per_range = 128, autosummarize = on);

要點

• BRIN 只存儲每個范圍的 min/max 等摘要，需要回表回檢；
• 體積/維護成本極低，適合“追加寫 + 時間窗口查詢”；
• 相關(guān)性弱（數(shù)據(jù)經(jīng)常亂序?qū)懭耄r效果下降，可 CLUSTER/重寫表優(yōu)化物理順序。

八、選型決策 10 條軍規(guī)

1. 能用 B-tree 先用 B-tree：等值/范圍/排序/唯一都穩(wěn)。
2. jsonb/數(shù)組/全文/模糊 → GIN；其中 LIKE '%abc%' 強烈建議 trigram + GIN。
3. 距離/空間/范圍相交/KNN → GiST（PostGIS、range、inet 等）。
4. 前綴或可分割空間結(jié)構(gòu) → SP-GiST。
5. 超大追加型時間/ID查詢 → BRIN。
6. 純等值且確有收益證據(jù) → Hash；否則 B-tree。
7. 高頻過濾 + 低選擇度 → 部分索引（WHERE ...）勝過大而全。
8. 只讀回表字段較多 → B-tree INCLUDE 做覆蓋掃描。
9. 表達式要索引同款表達式（如 lower(email)）；否則無法命中。
10. 多列順序要按查詢使用頻次/選擇度從左到右排列；避免“全吃不著”的復(fù)合索引。

九、常見坑與對癥下藥

• 模糊查詢沒走索引：LIKE '%abc%' 需 pg_trgm + GIN/GiST；LIKE 'abc%' 可走 B-tree。
• 大小寫不敏感：lower(col) 表達式索引 + 查詢同寫法；或用 citext 類型。
• 多列索引未命中：條件沒用到前導(dǎo)列；或使用了不等價的表達式/函數(shù)。
• jsonb 慢：選對 opclass：jsonb_path_ops 對 @> 更緊湊；更新頻繁則謹慎使用 GIN。
• 索引暴脹：定期 VACUUM，必要時 REINDEX；控制 fillfactor。
• 計劃不穩(wěn)定：檢查統(tǒng)計信息與相關(guān)性（ANALYZE、default_statistics_target）；利用 EXPLAIN (ANALYZE, BUFFERS) 診斷。

十、實戰(zhàn)模板：一張訂單表怎么配索引

CREATE TABLE orders(
  id           bigserial PRIMARY KEY,
  user_id      bigint NOT NULL,
  status       text   NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  amount       numeric(12,2) NOT NULL,
  items        jsonb,             -- 訂單明細（jsonb）
  tags         text[]             -- 標簽
);

-- 1) 用戶最近訂單（分頁/排序）
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

-- 2) 狀態(tài) + 時間窗口統(tǒng)計（覆蓋金額）
CREATE INDEX idx_orders_status_created_inc
  ON orders(status, created_at)
  INCLUDE (amount);

-- 3) jsonb 包含查找（items 內(nèi)含某 SKU）
CREATE INDEX idx_orders_items_path ON orders USING gin(items jsonb_path_ops);

-- 4) 標簽包含任一/全部
CREATE INDEX idx_orders_tags_gin ON orders USING gin(tags);

-- 5) 超大表時間過濾的剪枝
CREATE INDEX idx_orders_brin_created
  ON orders USING brin (created_at) WITH (pages_per_range=128, autosummarize=on);

十一、性能與維護清單

• 分析與觀測：EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements, pg_stat_all_indexes。
• 維護：VACUUM (ANALYZE)、高寫入期適當增大 maintenance_work_mem；大索引可并行創(chuàng)建：CREATE INDEX CONCURRENTLY（無鎖長事務(wù)，但更慢）。
• 參數(shù)提示：順序 I/O 多可調(diào)低 random_page_cost；SSD 環(huán)境可適當下調(diào)以提高索引傾向。
• 物理順序：時間序列表可偶爾 CLUSTER 或重寫，提高 BRIN/JIT 效果與熱點局部性。

十二、快速對照：你在查什么，就選什么

• WHERE a = ? / ORDER BY a / BETWEEN → B-tree
• WHERE col @> '{"k":"v"}'::jsonb / tags @> '{x}' → GIN(jsonb/數(shù)組)
• title @@ to_tsquery('...') → GIN(全文)
• name ILIKE '%abc%' → GIN + pg_trgm
• geom <-> point 最近點 → GiST + KNN
• tsrange && ? 不重疊預(yù)約 → GiST + 排斥約束
• phone LIKE '1389%' → SP-GiST（或 B-tree 也可）
• WHERE created_at BETWEEN ...（億級表） → BRIN

到此這篇關(guān)于PostgreSQL 六大索引的方法小結(jié)的文章就介紹到這了,更多相關(guān)PostgreSQL 索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論