hstore 数据类型作为 Extension 模块， 在之前版本已经有了，之前大概知道概念，一直没怎么研究，今天做了些测试。简单的说 hstore 数据类型用来存储具有多个属性值的数据，模板如 key => value ， key 代表存储的属性， values 为相应属性对应的值，一个简单的 hstore 例子如下：
Hstore 示例
1 2 3 4 5 6 7 8 9 10
skytf=> select 'a=>1 ,b=>2 '::hstore; hstore -------------------- "a" =>"1" , "b" =>"2" (1 row) skytf=> select hstore('a=>1 ,b=>2 '); hstore -------------------- "a" =>"1" , "b" =>"2" (1 row)
Hstore 操作符演示
2.1 查询属性(key) a 的值；
1 2 3 4 5
skytf=> select hstore('a=>1,b=>2' ) ->'a' ; ?column? ---------- 1 (1 row)
2.2 判断是否包含指定属性(key)，如果包含，返回 t
1 2 3 4 5 6 7 8 9 10
skytf=> select hstore('a =>1 ,b=>2 ') ? ' a'; ?column? ---------- t (1 row) skytf=> select hstore('a =>1 ,b=>2 ') ? ' c'; ?column? ---------- f (1 row)
2.3 判断左边的 hstore 是否包含右边的 hstore ，如果包含，返回 t。
1 2 3 4 5 6 7 8 9 10
skytf=> select hstore('a=>1 ,b=>2') @> 'a=>1 '::hstore; ?column? ---------- t (1 row) skytf=> select hstore('a=>1 ,b=>2') @> 'a=>2 '::hstore; ?column? ---------- f (1 row)
备注：更多 hstore 函数和操作符，详见本文的附录部分。
Hstore 函数演示
3.1 array 类型转换成 hstore
1 2 3 4 5
skytf=> select hstore(ARRAY ['a' ,'1' ,'b' ,'2' ]); hstore -------------------- "a" =>"1" , "b" =>"2" (1 row)
3.2 将两个 array 类型数据转换成 hstore 类型，比较难表达，看例子理解。
1 2 3 4 5
skytf=> select hstore(ARRAY ['a' ,'b' ,'c' ], ARRAY ['1' ,'2' ,'3' ]); hstore ------------------------------ "a" =>"1" , "b" =>"2" , "c" =>"3" (1 row)
备注：看到了吧，非常强大。
1 2 3 4 5
skytf=> select akeys('a=>1 ,b=>2') akeys ------- {a,b} (1 row)
3.4 将 hstore 类型数据的 key 转换成 结果集
1 2 3 4 5 6
skytf=> select skeys('a=>1 ,b=>2') skeys ------- a b (2 rows)
3.5 将 hstore 类型数据的 values 转换成 array
1 2 3 4 5
skytf=> select avals('a=>1 ,b=>2 '); avals ------- {1 ,2 } (1 row)
3.6 将 hstore 类型数据的 values 转换成 结果集
1 2 3 4 5 6
skytf=> select svals('a=>1 ,b=>2 '); svals ------- 1 2 (2 rows)
3.7 删除一个属性
1 2 3 4 5
skytf=> select delete(hstore ('a=>1,b=>2'),'b') delete ---------- "a" =>"1" (1 row)
3.8 增加一个属性
1 2 3 4 5
skytf=> select hstore('a=>1 ,b=>2 ') || 'c =>3 '; ?column? ------------------------------ "a" =>"1" , "b" =>"2" , "c" =>"3" (1 row)
备注：先演示这么多吧，更多内容，参考本文的附。
Example1: Hstore 使用
4.1 环境
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
skytf => /c skytf postgres; You are now connected to database "skytf" as user "postgres" . skytf =# create extension hstore; CREATE EXTENSION skytf =# /c skytf skytf; You are now connected to database "skytf" as user "skytf" . skytf => /dT hstore; List of data types Schema | Name | Description --------+--------+------------- public | hstore | (1 row)
4.3 创建测试表，并生成测试数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14
skytf=> create table tbl_role(id serial primary key,role_name varchar(32 ), exp int8, wealth int8, status char (1 )); NOTICE: CREATE TABLE will create implicit sequence "tbl_role_id_seq" for serial column "tbl_role.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tbl_role_pkey" for table "tbl_role" CREATE TABLE skytf=> insert into tbl_role(role_name,exp ,wealth) select 'user_' || generate_series(1 ,100000 ) ,generate_series(1 ,100000 ),generate_series(100001 ,200000 ); INSERT 0 100000 skytf=> select * from tbl_role limit 3 ; id | role_name | exp | wealth | status ----+-----------+-----+--------+-------- 1 | user_1 | 1 | 100001 | 2 | user_2 | 2 | 100002 | 3 | user_3 | 3 | 100003 | (3 rows)
备注：上面创建一张角色信息表，属性值有 exp:经验值， wealth:财富值等。
4.4 增加 hstore 数据类型
1 2 3 4 5 6 7 8 9 10
skytf=> alter table tbl_role add column attr hstore; ALTER TABLE skytf=> update tbl_role set attr=('exp=>' || exp || ', wealth=>' || wealth )::hstore;UPDATE 100000 skytf=> select * from tbl_role limit 3 ;; id | role_name | exp | wealth | status | attr ----+-----------+-----+--------+--------+--------------------------------- 22 | user_22 | 22 | 100022 | | "exp" =>"22" , "wealth" =>"100022" 23 | user_23 | 23 | 100023 | | "exp" =>"23" , "wealth" =>"100023" 24 | user_24 | 24 | 100024 | | "exp" =>"24" , "wealth" =>"100024" (3 rows)
4.5 创建索引
1 2
skytf=> create index concurrently idx_tbl_role_attr on tbl_role using GIST ( attr); CREATE INDEX
备注： hstore 类型的数据支持 GIN,GIST 索引扫描的操作符有 @>, ?, ?& 和 ?|
1 2 3 4 5 6 7 8 9
skytf=> select id,role_name,attr,attr -> 'exp' From tbl_role where attr @> 'exp=>22' ; id | role_name | attr | ?column? ----+-----------+---------------------------------+---------- 22 | user_22 | =>, => | 22 (1 row) skytf=> select id,role_name,attr,attr -> 'wealth' From tbl_role where attr @> 'wealth=>100001' ; id | role_name | attr | ?column? ----+-----------+--------------------------------+---------- 1 | user_1 | =>, => | 100001
4.7 执行计划：
1 2 3 4 5 6 7 8 9 10
skytf=> explain analyze select id,role_name,attr,attr -> 'exp' From tbl_role where attr @> 'exp=>22'; QUERY PLAN ------------------------------------------------------------------------------------------- Bitmap Heap Scan on tbl_role (cost=5.36..347.63 rows=100 width=54) (actual time=3.267..7.111 rows=1 loops=1) Recheck Cond: (attr @> '"exp" =>"22" '::hstore) Rows Removed by Index Recheck: 1545 -> Bitmap Index Scan on idx_tbl_role_attr (cost=0.00..5.33 rows=100 width=0) (actual time=2.813..2.813 rows=1546 loops=1) Index Cond: (attr @> '"exp" =>"22" '::hstore) Total runtime: 7.185 ms (6 rows)
备注：消耗 7 ms 左右，这个查询速度并不很快。
Example2: 将结果集转换成 hstore 类型
5.1 测试表
1 2 3 4 5 6
skytf=> select * From test_1; id | name ----+-------- 1 | francs 2 | fpzhou (2 rows)
5.2 将结果集转换成 hstore 类型输出
1 2 3 4 5 6 7 8 9 10 11 12
skytf=> select hstore(test_1) From test_1; hstore ----------------------------- "id" =>"1" , "name" =>"francs" "id" =>"2" , "name" =>"fpzhou" (2 rows) skytf=> select hstore(test_1) From test_1 where id=1 ; hstore ----------------------------- "id" =>"1" , "name" =>"francs" (1 row)
附: Hstore 函数和操作符
总结
hstore 适用于拥有多个属性值的对像，同时这些属性值查询并不非常频繁的场合，根据上面的测试，在 10 万数据中，根据元素属性值查询需要7ms 左右，这个速度已经算慢的了。
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/tech/bigdata/237899.html
