PostGreSQL不同索引類型(btree & hash)的效能問題

2020-09-23 09:30:21

在關係型資料庫調優中，查詢語句涉及到的索引類型是不得不考慮的一個問題。不同的類型的索引可能會適用不同類型的業務場景。這裡我們所說的索引類型指的是訪問方法（Access Method），至於從其他維度區分索引(Index)這裡暫不作考慮。

PostGreSQL資料庫預設的索引訪問方法是btree,其他的資料庫如Oracle預設也是btree。那麼btree到底是何方神聖呢？如果想要深入理解btree的運行原理，需要了解一下資料結構相關的知識，特別是樹形資料結構。btree運用了b+ 樹資料結構，其可以有效節省IO操作，在查詢時可以提供查詢效率。

我們沿用之前文章用過的查詢語句來做測試，其中sort_test表有500萬行資料。表創建SQL和查詢語句如下：

CREATE TABLE public.sort_test
(
    id bigint NOT NULL,
    salary numeric NOT NULL,
    CONSTRAINT sort_test_pkey PRIMARY KEY (id)
)

TABLESPACE pg_default;


explain analyze select * from public."sort_test" where salary = 101;

那麼執行計劃的細節如下圖所示：

下面將在同樣的表字段上，刪除原來的索引，新增相應的hash索引。新增語句為：

CREATE INDEX index_sort_test_salary_hash
    ON public.sort_test USING hash
    (salary)
    TABLESPACE pg_default;

新增完之後，如下圖所示：

同樣的查詢語句的執行計劃是怎樣的呢？如下圖：

可以發現0.757 ms vs. 0.022 ms還是有數量級的差別,在這種情況下，hash索引的效率比btree的效率要高很多。由於hash索引結構的特殊性，其檢索效率非常高，可以一步到位。而一般使用的B-tree索引需要從根節點->枝節點->頁節點。所以從工作模式上看，hash索引的效率要比btree索引要高。

但是我們大家都懂的，事務都有兩面性，hash索引有一定的限制和弊端，要不然這些常用的資料庫如Oracle, MySQL,PostGreSQL等也不會將btree access method設定為預設選項了。具體有如下2點限制：

Hash索引只能滿足"=","IN"等，等值查詢，不能使用範圍查詢。
在使用部分索引鍵查詢的時候，hash索引將不起作用。

所以在應用不同的索引類型時，要充分考慮具體的業務場景和實際情況，才能得到更優解。

PostGreSQL不同索引類型(btree & hash)的效能問題

熱門文章