高併發低基數多字段任意組合查詢優化：PostgreSQL GIN 索引實戰超越 MySQL 85 倍

問題定義：低基數多字段任意組合查詢

典型電商商品篩選場景：用戶任意組合品類、品牌、價格、促銷等條件，且單字段選擇性極低（如「狀態=上架」佔 90%）。傳統 B-Tree 索引面臨三大困境：

1. 全表掃描性能不足，無法支撐高併發
2. 單字段索引選擇性差，回表代價大
3. 組合索引爆炸：N 個字段需 2^N-1 個索引覆蓋所有組合，維護不可行

兩大理論方案對比

最佳實踐：PostgreSQL GIN 索引（內建倒排索引）

為什麼選 PostgreSQL？

• 無需額外組件，關係型數據庫內建
• 避免數據同步、雙寫一致性問題
• 保留 SQL 接口，應用層零改造遷移
• C 語言實現性能媲美專業搜索引擎
• 團隊已有 PG 運維經驗

GIN 多字段索引核心優勢

• 字段等價性：無「前導列」概念，一個多字段 GIN 索引覆蓋所有組合查詢
• 內部鍵結構：(column_number, key_datum) 區分字段，值為壓縮的 CTID B-Tree 集合
• 存儲極小：約等值 B-Tree 索引的 1/20
• 歸並高效：索引內部 CTID 集合歸並 ≫ 索引間 BitmapAnd/Or 歸並

真實案例：從 MySQL 64 QPS 到 PG 5412 QPS

原始 SQL（簡化）

SELECT CASE ... END AS flag, gpppur.*
FROM T_MPS_INFO gpppur
WHERE gpppur.ATTRACT_TP = 0
  AND gpppur.COLUMN_ID = 1
  AND gpppur.FIELD2 = 1
  AND gpppur.STATUS = 1
ORDER BY flag, PC_SORT_NUM, GB_BEGINDATE DESC
LIMIT 45

數據量 60 萬，MySQL 僅建 (FIELD2, STATUS) 組合索引。

四字段值分布（極度傾斜）

ATTRACT_TP: 0→6458, 5→194333, 9→326485  (11 個值)
COLUMN_ID:  1→6479, 344→199271, 361→331979 (80 個值)
FIELD2:     1→11452, 5→200497, 9→331979   (11 個值)
STATUS:     1→531829 (佔 88%)              (6 個值)

壓測對比

GIN 索引創建步驟

CREATE EXTENSION btree_gin;  -- 支持 int/varchar 等標量類型
CREATE INDEX idx3 ON t_mps_info USING GIN(attract_tp, column_id, field2, status);

深度洞察：為什麼 PG GIN 碾壓 MySQL 組合索引？

1. 索引內歸並：GIN 內部直接對多字段 CTID 集合做交集，避免多索引掃描後再 BitmapAnd
2. 低基數優化：Posting Tree 壓縮存儲、快速跳過高頻值
3. 無前導列限制：任意字段組合均能命中同一索引
4. 成熟代碼庫：PG 核心模塊經過大規模生產驗證

結論與建議

• 低基數多字段任意組合查詢首選 PostgreSQL GIN 索引
• 對比 Oracle Bitmap 索引：PG 無寫鎖表問題，支援並發寫入
• 對比 ES/Solr：無需異構同步，SQL 兼容，運維成本低
• 同場景下 MySQL 組合索引仍受前導列限制，性能上限較低
• 歡迎交流更優方案，共同探索數據庫索引邊界