postgresql 索引之 hash的使用詳解
os: ubuntu 16.04
postgresql: 9.6.8
ip 規(guī)劃
192.168.56.102 node2 postgresql
help create index
postgres=# \h create index
Command: CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]
( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ]
[ USING method ]
method
要使用的索引方法的名稱。可以選擇 btree、hash、 gist、spgist、 gin以及brin。 默認(rèn)方法是btree。
hash
hash 只能處理簡(jiǎn)單的等值比較，
postgres=# drop table tmp_t0;
DROP TABLE
postgres=# create table tmp_t0(c0 varchar(100),c1 varchar(100));
CREATE TABLE
postgres=# insert into tmp_t0(c0,c1) select md5(id::varchar),md5((id+id)::varchar) from generate_series(1,100000) as id;
INSERT 0 100000
postgres=# create index idx_tmp_t0_1 on tmp_t0 using hash(c0);
CREATE INDEX
postgres=# \d+ tmp_t0
Table "public.tmp_t0"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+------------------------+-----------+----------+---------+----------+--------------+-------------
c0 | character varying(100) | | | | extended | |
c1 | character varying(100) | | | | extended | |
Indexes:
"idx_tmp_t0_1" hash (c0)
postgres=# explain select * from tmp_t0 where c0 = 'd3d9446802a44259755d38e6d163e820';
QUERY PLAN
----------------------------------------------------------------------------
Index Scan using idx_tmp_t0_1 on tmp_t0 (cost=0.00..8.02 rows=1 width=66)
Index Cond: ((c0)::text = 'd3d9446802a44259755d38e6d163e820'::text)
(2 rows)
注意事項(xiàng)，官網(wǎng)特別強(qiáng)調(diào)：
Hash索引操作目前不被WAL記錄，因此存在未寫入修改，在數(shù)據(jù)庫(kù)崩潰后需要用REINDEX命令重建Hash索引。
同樣，在完成初始的基礎(chǔ)備份后，對(duì)于Hash索引的改變也不會(huì)通過(guò)流式或基于文件的復(fù)制所復(fù)制，所以它們會(huì)對(duì)其后使用它們的查詢給出錯(cuò)誤的答案。
正因?yàn)檫@些原因，Hash索引已不再被建議使用。
補(bǔ)充：Postgresql hash索引介紹
hash索引的結(jié)構(gòu)
當(dāng)數(shù)據(jù)插入索引時(shí)，我們會(huì)為這個(gè)索引鍵通過(guò)哈希函數(shù)計(jì)算一個(gè)值。 PostgreSQL中的哈希函數(shù)始終返回“整數(shù)”類型，范圍為2^32≈40億。bucket桶的數(shù)量最初為2個(gè)，然后動(dòng)態(tài)增加以適應(yīng)數(shù)據(jù)大小?？梢允褂梦凰惴◤墓４a計(jì)算出桶編號(hào)。這個(gè)bucket將存放TID。
由于可以將與不同索引鍵匹配的TID放入同一bucket桶中。而且除了TID之外，還可以將鍵的源值存儲(chǔ)在bucket桶中，但這會(huì)增加索引大小。為了節(jié)省空間，bucket桶只存儲(chǔ)索引鍵的哈希碼，而不存儲(chǔ)索引鍵。
當(dāng)我們通過(guò)索引查詢時(shí)，我們計(jì)算索引鍵的哈希函數(shù)并獲取bucket桶的編號(hào)。現(xiàn)在，仍然需要遍歷存儲(chǔ)桶的內(nèi)容，并僅返回所需的哈希碼匹配的TID。由于存儲(chǔ)的“hash code - TID”對(duì)是有序的，因此可以高效地完成此操作。
但是，兩個(gè)不同的索引鍵可能會(huì)發(fā)生以下情況，兩個(gè)索引鍵都進(jìn)入一個(gè)bucket桶，而且具有相同的四字節(jié)的哈希碼。因此，索引訪問(wèn)方法要求索引引擎重新檢查表行中的情況來(lái)驗(yàn)證每個(gè)TID。
映射數(shù)據(jù)結(jié)構(gòu)到page
Meta page - 0號(hào)page,包含索引內(nèi)部相關(guān)信息
Bucket pages - 索引的主要page,存儲(chǔ) “hash code - TID” 對(duì)
Overflow pages - 與bucket page的結(jié)構(gòu)相同，在不足一個(gè)page時(shí),作為bucket桶使用
Bitmap pages - 跟蹤當(dāng)前干凈的overflow page，并可將其重新用于其他bucket桶
注意，哈希索引不能減小大小。雖然我們刪除了一些索引行，但是分配的頁(yè)面將不會(huì)返回到操作系統(tǒng)，只會(huì)在VACUUMING之后重新用于新數(shù)據(jù)。減小索引大小的唯一選項(xiàng)是使用REINDEX或VACUUM FULL命令從頭開始重建索引
接下來(lái)看下hash索引如何創(chuàng)建
demo=# create index on flights using hash(flight_no);
demo=# explain (costs off) select * from flights where flight_no = 'PG0001';
QUERY PLAN
----------------------------------------------------
Bitmap Heap Scan on flights
Recheck Cond: (flight_no = 'PG0001'::bpchar)
-> Bitmap Index Scan on flights_flight_no_idx
Index Cond: (flight_no = 'PG0001'::bpchar)
(4 rows)
注意：10版本之前hash索引不記錄到wal中，所以hash索引不能做recovery，當(dāng)然也就不能復(fù)制了，但是從10版本以后hash所用得到了增強(qiáng)，可以記錄到wal中，創(chuàng)建的時(shí)候也不會(huì)再有警告。
查看hash訪問(wèn)方法相關(guān)的操作函數(shù)
demo=# select opf.opfname as opfamily_name,
amproc.amproc::regproc AS opfamily_procedure
from pg_am am,
pg_opfamily opf,
pg_amproc amproc
where opf.opfmethod = am.oid
and amproc.amprocfamily = opf.oid
and am.amname = 'hash'
order by opfamily_name,
opfamily_procedure;
opfamily_name | opfamily_procedure
--------------------+-------------------------
abstime_ops | hashint4extended
abstime_ops | hashint4
aclitem_ops | hash_aclitem
aclitem_ops | hash_aclitem_extended
array_ops | hash_array
array_ops | hash_array_extended
bool_ops | hashcharextended
bool_ops | hashchar
bpchar_ops | hashbpcharextended
bpchar_ops | hashbpchar
bpchar_pattern_ops | hashbpcharextended
bpchar_pattern_ops | hashbpchar
bytea_ops | hashvarlena
bytea_ops | hashvarlenaextended
char_ops | hashcharextended
char_ops | hashchar
cid_ops | hashint4extended
cid_ops | hashint4
date_ops | hashint4extended
date_ops | hashint4
enum_ops | hashenumextended
enum_ops | hashenum
float_ops | hashfloat4extended
float_ops | hashfloat8extended
float_ops | hashfloat4
float_ops | hashfloat8
...
可以用這些函數(shù)計(jì)算相關(guān)類型的哈希碼
hank=# select hashtext('zhang');
hashtext
-------------
-1172392837
(1 row)
hank=# select hashint4(10);
hashint4
-------------
-1547814713
(1 row)
hash索引相關(guān)的屬性
hank=# select a.amname, p.name, pg_indexam_has_property(a.oid,p.name)
hank-# from pg_am a,
hank-# unnest(array['can_order','can_unique','can_multi_col','can_exclude']) p(name)
hank-# where a.amname = 'hash'
hank-# order by a.amname;
amname | name | pg_indexam_has_property
--------+---------------+-------------------------
hash | can_order | f
hash | can_unique | f
hash | can_multi_col | f
hash | can_exclude | t
(4 rows)
hank=# select p.name, pg_index_has_property('hank.idx_test_name'::regclass,p.name)
hank-# from unnest(array[
hank(# 'clusterable','index_scan','bitmap_scan','backward_scan'
hank(# ]) p(name);
name | pg_index_has_property
---------------+-----------------------
clusterable | f
index_scan | t
bitmap_scan | t
backward_scan | t
(4 rows)
hank=# select p.name,
hank-# pg_index_column_has_property('hank.idx_test_name'::regclass,1,p.name)
hank-# from unnest(array[
hank(# 'asc','desc','nulls_first','nulls_last','orderable','distance_orderable',
hank(# 'returnable','search_array','search_nulls'
hank(# ]) p(name);
name | pg_index_column_has_property
--------------------+------------------------------
asc | f
desc | f
nulls_first | f
nulls_last | f
orderable | f
distance_orderable | f
returnable | f
search_array | f
search_nulls | f
(9 rows)
由于hash函數(shù)沒(méi)有特定的排序規(guī)則，所以一般的hash索引只支持等值查詢，可以通過(guò)下面數(shù)據(jù)字典看到，所有操作都是“=”，而且hash索引也不會(huì)處理null值，所以不會(huì)標(biāo)記null值，還有就是hash索引不存儲(chǔ)索引鍵，只存儲(chǔ)hash碼，所以不會(huì) index-only掃描，也不支持多列創(chuàng)建hash索引
hank=# select opf.opfname AS opfamily_name,
hank-# amop.amopopr::regoperator AS opfamily_operator
hank-# from pg_am am,
hank-# pg_opfamily opf,
hank-# pg_amop amop
hank-# where opf.opfmethod = am.oid
hank-# and amop.amopfamily = opf.oid
hank-# and am.amname = 'hash'
hank-# order by opfamily_name,
hank-# opfamily_operator;
opfamily_name | opfamily_operator
--------------------+------------------------------------------------------------
abstime_ops | =(abstime,abstime)
aclitem_ops | =(aclitem,aclitem)
array_ops | =(anyarray,anyarray)
bool_ops | =(boolean,boolean)
bpchar_ops | =(character,character)
bpchar_pattern_ops | =(character,character)
bytea_ops | =(bytea,bytea)
char_ops | =("char","char")
cid_ops | =(cid,cid)
date_ops | =(date,date)
enum_ops | =(anyenum,anyenum)
float_ops | =(real,real)
float_ops | =(double precision,double precision)
float_ops | =(real,double precision)
float_ops | =(double precision,real)
hash_hstore_ops | =(hstore,hstore)
integer_ops | =(integer,bigint)
integer_ops | =(smallint,smallint)
integer_ops | =(integer,integer)
integer_ops | =(bigint,bigint)
integer_ops | =(bigint,integer)
integer_ops | =(smallint,integer)
integer_ops | =(integer,smallint)
integer_ops | =(smallint,bigint)
integer_ops | =(bigint,smallint)
interval_ops | =(interval,interval)
jsonb_ops | =(jsonb,jsonb)
macaddr8_ops | =(macaddr8,macaddr8)
macaddr_ops | =(macaddr,macaddr)
name_ops | =(name,name)
network_ops | =(inet,inet)
numeric_ops | =(numeric,numeric)
oid_ops | =(oid,oid)
oidvector_ops | =(oidvector,oidvector)
pg_lsn_ops | =(pg_lsn,pg_lsn)
range_ops | =(anyrange,anyrange)
reltime_ops | =(reltime,reltime)
text_ops | =(text,text)
text_pattern_ops | =(text,text)
time_ops | =(time without time zone,time without time zone)
timestamp_ops | =(timestamp without time zone,timestamp without time zone)
timestamptz_ops | =(timestamp with time zone,timestamp with time zone)
timetz_ops | =(time with time zone,time with time zone)
uuid_ops | =(uuid,uuid)
xid_ops | =(xid,xid)
從10版本開始，可以通過(guò)pageinspect插件查看hash索引的內(nèi)部情況
安裝插件
create extension pageinspect;
查看0號(hào)page
hank=# select hash_page_type(get_raw_page('hank.idx_test_name',0));
hash_page_type
----------------
metapage
(1 row)
查看索引中的行數(shù)和已用的最大存儲(chǔ)桶數(shù)
hank=# select ntuples, maxbucket
hank-# from hash_metapage_info(get_raw_page('hank.idx_test_name',0));
ntuples | maxbucket
---------+-----------
1000 | 3
(1 row)
可以看到1號(hào)page是bucket,查看此bucket page的活動(dòng)元組和死元組的數(shù)量，
也就是膨脹度，以便維護(hù)索引
hank=# select hash_page_type(get_raw_page('hank.idx_test_name',1));
hash_page_type
----------------
bucket
(1 row)
hank=# select live_items, dead_items
hank-# from hash_page_stats(get_raw_page('hank.idx_test_name',1));
live_items | dead_items
------------+------------
407 | 0
(1 row)
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
相關(guān)文章
Postgresql psql文件執(zhí)行與批處理多個(gè)sql文件操作
這篇文章主要介紹了Postgresql psql文件執(zhí)行與批處理多個(gè)sql文件操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
這篇文章主要介紹了postgresql 中的序列nextval詳解，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-02-02
這篇文章主要給大家介紹了關(guān)于pgsql查詢優(yōu)化之模糊查詢的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用pgsql具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-07-07
postgresql無(wú)序uuid性能測(cè)試及對(duì)數(shù)據(jù)庫(kù)的影響
小編最近在做一個(gè)超大表的性能測(cè)試，在過(guò)程中發(fā)現(xiàn)無(wú)序uuid做主鍵對(duì)表插入性能有些影響，糾結(jié)該怎么處理這一問(wèn)題呢？接下來(lái)小編給大家分享postgresql無(wú)序uuid性能測(cè)試的相關(guān)知識(shí)幫助大家學(xué)習(xí)，需要的彭參考下吧2021-06-06
這篇文章主要介紹了淺談PostgreSQL中大小寫不敏感問(wèn)題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
這篇文章主要介紹了postgresql查詢鎖表以及解除鎖表操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-12-12
postgresql 循環(huán)函數(shù)的簡(jiǎn)單實(shí)現(xiàn)操作
這篇文章主要介紹了postgresql 循環(huán)函數(shù)的簡(jiǎn)單實(shí)現(xiàn)操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
這篇文章主要介紹了Postgresql分布式插件plproxy的使用詳解，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
Postgresql 跨庫(kù)同步表及postgres_fdw的用法說(shuō)明
這篇文章主要介紹了Postgresql 跨庫(kù)同步表及postgres_fdw的用法說(shuō)明，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
PostGresql 實(shí)現(xiàn)四舍五入、小數(shù)轉(zhuǎn)換、百分比的用法說(shuō)明
這篇文章主要介紹了PostGresql 實(shí)現(xiàn)四舍五入、小數(shù)轉(zhuǎn)換、百分比的用法說(shuō)明，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01