聊聊PostgreSQL中的幾種索引類型
- 2019 年 12 月 18 日
- 筆記
索引是增強資料庫性能的利器，在檢索某些特定行的時候效率會有很大提升，postgresql中索引類型豐富，每種索引有著不同的應用場景，下面簡單介紹一下。
索引類型與場景
BTree
• =, >, >=, <, <=、排序
Hash
• =
GIN
• 多值類型(數組、全文檢索、枚舉、網路地址類型)：包含、相交
• JSON類型
• 普通類型(通過btree_gin 插件支援)：與B-Tree類似
• 字元串(通過pg_trgm 插件支援)：模糊查詢、相似查詢
• 多列：任意列組合查詢
GiST
• 空間類型：方位（上、下、左、右），空間關係（相交、包含），空間距離排序(KNN)
• 範圍數據：=, &&, <@, @>, <<, >>, -|-, &<, and &>
• 普通類型(通過btree_gist 插件支援)：與B-Tree類似，增加空間類型類似操作符
• 數組類型(通過intarray 插件支援)：與GIN類似
• 多列：任意列組合查詢
SPGiST
• 平面幾何類型：與GiST類似
• 範圍類型：與GiST類似
RUM
• 多值類型(數組、全文檢索類型)：包含、相交、相似排序
• 普通類型：與B-Tree類似
BRIN
• 適合線性數據、時序數據，block ranged index是oracle一體機中才有的功能。
• 普通類型：與B-Tree類似
• 空間類型：包含
Bloom
• 多列：任意列組合，等值查詢
• 表達式索引
• 搜索條件為表達式
• where st_makepoint(x,y) op ?
• create index idx on tbl ( (st_makepoint(x,y)) );
• 條件索引(定向索引)
• 搜索時，強制過濾某些條件
• where status='active' and col=?
• create index idx on tbl (col) where status='active';
• 監控系統例子select x from tbl where temp>60; — 99, 1% 異常數據
索引特性
只有B-tree，GiST，GIN和BRIN索引類型支援多列索引。最多可以指定32列。使用最左匹配原則。
在PostgreSQL當前支援的索引類型中，只有B-tree可以產生排序的輸出，當ORDER BY與LIMIT n組合：顯式排序將必須處理所有數據以識別前n行，但如果存在與ORDER BY匹配的索引，則可以直接檢索前n行，而不掃描其餘部分。升序默認null值放在最後，可以使用NULLS FIRST和/或NULLS LAST選項來進行調整。
PostgreSQL可以為表達式的結果創建索引，但是該索引維護代價太大，因為每當插入或者更新時，表達式都需要重新計算。
PostgreSQL支援對表中部分數據建立索引，使用部分索引的一個主要原因是避免索引常見值。由於搜索常見值的查詢將不會使用索引，所以根本沒有必要在索引中保留這些行，這樣可以直接排除掉一部分數據，減少了索引的大小，性能更快。
PostgreSQL支援僅索引掃描，當要查詢的目標列都在索引中時，直接使用索引中的鍵值進行返回，不需要回表操作。
技術永無止境，加油吧。
