| 导语 在很多场景下都具有关于标签相似查找的需求,业务层固然可以实现,但如果数据库能够天然支持高性能查询,岂不美哉。本文主要基于PostgreSQL实现标签查询的优化实践,供大家参考
一步一步实操,可直接复制粘贴
pg_roaringbitmap是一个基于roaringbitmap而实现的压缩位图存储数据插件,支持roaring bitmap的存取、集合操作,聚合等运算。
roaringbitmap在在实际的业务当中常使用来存储用户的属性标签,增删改查这些属性标签,以及根据这些存储的用户的标签通过并集,交集等方法来筛选出特定的用户。以达到超大规模属性数据的精准快速查找,既提升了性能的同时亦能降低存储空间,是大数据分析场景下极佳的应用实践。
如在传统模式下如有一张音乐类应用 的用户标签表,如下表:
用户ID | 用户名 | 兴趣标签 |
---|---|---|
1 | 张三 | {古典,爵士,R&B,乡村} |
2 | 李四 | {民歌,中国风,纯音乐} |
3 | 王五 | {HipHop,爵士,R&B,嘻哈,雷鬼} |
…… | ||
1000000000 | xxx | {摇滚,} |
若想要找到喜欢纯音乐的所有用户,就需要根据兴趣标签列进行搜索,找到标签中包含纯音乐的行,然后将此数据返回给应用。
那么我们一般最简单的做法会是怎么样子呢?
我们会按照上表的结构在数据库中建立一张用户兴趣表,然后执行数组查询语句,找到兴趣标签进行包含查找。
但是这么做就会有一个问题,性能,在数据量较大,并且标签值较多的场景下,不仅数据容量占用得更多,而且性能会极差。所以我们更换一种实现方案,将此表拆分为三张表,兴趣标签作为主键,包含此兴趣标签的用户作为bitmap存储。如下表所示:
用户表:
用户ID | 用户名 |
---|---|
1 | 张三 |
2 | 李四 |
N | …… |
标签表:
标签ID | 标签名 |
---|---|
1 | 古典 |
2 | 民歌 |
N | …… |
用户标签表:
标签ID | 用户ID |
---|---|
1 | [ 1,3,7,123,423 ] |
2 | [ 5,31] |
N | …… |
当需要根据查找同时喜欢听 古典和民歌的用户时候,直接在 用户标签表对 用户id 做bitmap 查询,能够极大的提升性能。并且容量占用降低极多。
1、创建一个随机字符的函数:
create or replace function random_string(length integer) returns text as
$$
declare
chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
result text := '';
i integer := 0;
length2 integer := (select trunc(random() * length + 1));
begin
if length2 < 0 then
raise exception 'Given length cannot be less than 0';
end if;
for i in 1..length2 loop
result := result || chars[1+random()*(array_length(chars, 1)-1)];
end loop;
return result;
end;
$$ language plpgsql;
2、创建一个生成随机 整形 数组的函数:
create or replace function random_int_array(int, int)
returns int[] language sql as
$$
select array_agg(round(random()* $1)::int)
from generate_series(1, $2)
$$;
3、创建一个生成随机 字符 数组的函数:
create or replace function random_string_array(int, int)
returns TEXT[] language sql as
$$
select array_agg(random_string($1)) from generate_series(1, $2);
$$;
一张表解决一切的方法。
1、创建一个表装所有数据:
create table account(
uin bigint primary KEY,
name varchar,
tag TEXT []
);
2、模拟插入1000W个账号数据(需要使用到准备工作中的函数),并且创建Gin索引。
insert into account select generate_series(1,10000000), random_string(20),random_string_array(5,10);
create index tag_inx on account USING GIN(tag);
3、执行查询:查找标签带 GN 和o的用户列表:
explain analyze select uin,name from account where tag @>ARRAY['GN','o'];
QUERY PLAN
----------------------------------------------------------------------------------------------------
-----------------
Bitmap Heap Scan on account (cost=52.81..466.86 rows=105 width=19) (actual time=4.263..4.502 rows=
184 loops=1)
Recheck Cond: (tag @> '{GN,o}'::text[])
Heap Blocks: exact=184
-> Bitmap Index Scan on tag_inx (cost=0.00..52.78 rows=105 width=0) (actual time=4.240..4.240 r
ows=184 loops=1)
Index Cond: (tag @> '{GN,o}'::text[])
Planning Time: 0.108 ms
Execution Time: 4.528 ms
3、执行查询:查找标签lvXe和Zt的人有xx个(备注第一次查询会较慢):
explain analyze select count(uin) from account where tag && ARRAY['lvXe','Zt'];
----------------------------------------------------------------------------------------------------
--------------------------
Aggregate (cost=21816.39..21816.40 rows=1 width=8) (actual time=8.236..8.238 rows=1 loops=1)
-> Bitmap Heap Scan on account (cost=109.08..21800.56 rows=6332 width=8) (actual time=1.655..7.
901 rows=5390 loops=1)
Recheck Cond: (tag && '{lvXe,Zt}'::text[])
Heap Blocks: exact=5327
-> Bitmap Index Scan on tag_inx (cost=0.00..107.49 rows=6332 width=0) (actual time=0.962.
.0.962 rows=5390 loops=1)
Index Cond: (tag && '{lvXe,Zt}'::text[])
Planning Time: 0.110 ms
Execution Time: 8.270 ms
为了降低查询中 标签字段的类型导致的性能减低,所以将上面表中的真实tag 修改为tagid
1、引入一个新的标签字典表:
create table tag_dict (
tagid int primary key,
taginfo text
);
2、假设一共有10W种字典类型:
insert into tag_dict select generate_series(1,100000), md5(random()::text);
3、创建一个新表用以存储用户和标签信息:
create table account1(
uin bigint primary KEY,
name varchar,
tag INT []
);
4、插入1000W个账号数据:
insert into account1 select generate_series(1,10000000), random_string(20),random_int_array(100000,10);
5、查找同时有 标签id 为 100 和5711的用户列表:
索引前:
test=> explain analyze select uin,name from account1 where tag @> ARRAY[100,5711];
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..191007.68 rows=250 width=19) (actual time=982.585..1000.806 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on account1 (cost=0.00..189982.68 rows=104 width=19) (actual time=962.640..962.640 rows=0 loops=3)
Filter: (tag @> '{100,5711}'::integer[])
Rows Removed by Filter: 3333333
Planning Time: 0.205 ms
JIT:
Functions: 12
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 2.280 ms, Inlining 0.000 ms, Optimization 1.176 ms, Emission 14.189 ms, Total 17.645 ms
Execution Time: 1001.574 ms
(12 rows)
加索引:
create index tag_inx_2 on account1 USING GIN(tag);
索引后:
test=> explain analyze select uin,name from account1 where tag @> ARRAY[100,5711];
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on account1 (cost=49.94..1021.13 rows=250 width=19) (actual time=0.126..0.127 rows=0 loops=1)
Recheck Cond: (tag @> '{100,5711}'::integer[])
-> Bitmap Index Scan on tag_inx_2 (cost=0.00..49.87 rows=250 width=0) (actual time=0.124..0.124 rows=0 loops=1)
Index Cond: (tag @> '{100,5711}'::integer[])
Planning Time: 0.410 ms
Execution Time: 0.171 ms
(6 rows)
6、查找同时有 标签id 为 61568 97350 的用户列表:
test=> explain analyze select uin,name from account1 where tag @> ARRAY[61568,97350];
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on account1 (cost=49.94..1021.13 rows=250 width=19) (actual time=0.130..0.131 rows=1 loops=1)
Recheck Cond: (tag @> '{61568,97350}'::integer[])
Heap Blocks: exact=1
-> Bitmap Index Scan on tag_inx_2 (cost=0.00..49.87 rows=250 width=0) (actual time=0.125..0.125 rows=1 loops=1)
Index Cond: (tag @> '{61568,97350}'::integer[])
Planning Time: 0.071 ms
Execution Time: 0.151 ms
(7 rows)
7、或者与xx 有共同爱好(标签100和5711)的人有xx个:
test=> explain analyze select count(uin) from account1 where tag && ARRAY[61568,97350];
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
------
Gather (cost=1961.06..173801.15 rows=99750 width=19) (actual time=5.020..28.885 rows=2066 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Bitmap Heap Scan on account1 (cost=961.06..162826.15 rows=41562 width=19) (actual time=1.623..3.305 rows=689 loo
ps=3)
Recheck Cond: (tag && '{61568,97350}'::integer[])
Heap Blocks: exact=2053
-> Bitmap Index Scan on tag_inx_2 (cost=0.00..936.12 rows=99750 width=0) (actual time=0.685..0.685 rows=2066 loops=1)
Index Cond: (tag && '{61568,97350}'::integer[])
Planning Time: 0.082 ms
JIT:
Functions: 12
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 2.078 ms, Inlining 0.000 ms, Optimization 0.270 ms, Emission 3.489 ms, Total 5.836 ms
Execution Time: 29.725 ms
(14 rows)
1、首先需要创建插件,云数据库PostgreSQL天然集成了此插件,无需关注编译等操作,直接进入数据库中创建即可。
create extension roaringbitmap;
2、创建标签用户对应表
create table tag_uin_list(
tagid int primary key,
uin_offset int,
uinbits roaringbitmap
);
3、根据之前的 标签表 插入10W条标签以及标签对应的用户数据。
insert into tag_uin_list
select tagid, uin_offset, rb_build_agg(uin::int) as uinbits from
(
select
unnest(tag) as tagid,
(uin / (2^31)::int8) as uin_offset,
mod(uin, (2^31)::int8) as uin
from account1
) t
group by tagid, uin_offset;
4、查询 标签有 1,3,10,200 的用户个数:
explain analyze select sum(ub) from
(
select uin_offset,rb_or_cardinality_agg(uinbits) as ub
from tag_uin_list
where tagid in (1,3,10,200)
group by uin_offset
) t;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
------------
Aggregate (cost=32.47..32.48 rows=1 width=32) (actual time=0.964..0.966 rows=1 loops=1)
-> GroupAggregate (cost=32.42..32.46 rows=1 width=12) (actual time=0.955..0.956 rows=1 loops=1)
Group Key: tag_uin_list.uin_offset
-> Sort (cost=32.42..32.43 rows=4 width=22) (actual time=0.107..0.109 rows=4 loops=1)
Sort Key: tag_uin_list.uin_offset
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on tag_uin_list (cost=17.20..32.38 rows=4 width=22) (actual time=0.044..0.067 rows=4 loops=1
)
Recheck Cond: (tagid = ANY ('{1,3,10,200}'::integer[]))
Heap Blocks: exact=4
-> Bitmap Index Scan on tag_uin_list_pkey (cost=0.00..17.20 rows=4 width=0) (actual time=0.031..0.031 rows
=4 loops=1)
Index Cond: (tagid = ANY ('{1,3,10,200}'::integer[]))
Planning Time: 0.289 ms
Execution Time: 1.083 ms
(13 rows)
5、查看标签有1,3,10,200的用户列表:
explain analyze select uin_offset,rb_or_agg(uinbits) as ub
from tag_uin_list
where tagid in (1,3,10,200)
group by uin_offset;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
------
GroupAggregate (cost=32.42..32.46 rows=1 width=36) (actual time=0.246..0.246 rows=1 loops=1)
Group Key: uin_offset
-> Sort (cost=32.42..32.43 rows=4 width=22) (actual time=0.043..0.045 rows=4 loops=1)
Sort Key: uin_offset
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on tag_uin_list (cost=17.20..32.38 rows=4 width=22) (actual time=0.029..0.036 rows=4 loops=1)
Recheck Cond: (tagid = ANY ('{1,3,10,200}'::integer[]))
Heap Blocks: exact=4
-> Bitmap Index Scan on tag_uin_list_pkey (cost=0.00..17.20 rows=4 width=0) (actual time=0.021..0.021 rows=4 loops=1)
Index Cond: (tagid = ANY ('{1,3,10,200}'::integer[]))
Planning Time: 0.119 ms
Execution Time: 0.310 ms
(12 rows)
总结
查看索引以及表占用大小:
test=> select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;
relname | pg_size_pretty
--------------+----------------
account | 1545 MB
account1 | 1077 MB
t_user | 651 MB
tag_dict | 6672 kB
tag_uin_list | 5888 kB
(5 rows)
test=> select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;
indexrelname | pg_size_pretty
-------------------+----------------
tag_inx | 1545 MB
account_pkey | 1545 MB
tag_inx_2 | 1077 MB
account1_pkey | 1077 MB
t_user_pkey | 651 MB
tag_dict_pkey | 6672 kB
tag_uin_list_pkey | 5888 kB
(7 rows)
不同方案的查询性能对比:
方案1 | 方案2 | roaringbitmap方案 | |
---|---|---|---|
查询包含指定标签的用户列表 | 4.528ms | 0.151 ms | 0.310 ms |
查询具备共同标签的用户个数 | 8.27ms | 29.725 ms | 1.083 ms |
数据容量统计 | 4635MB | 3244.344MB | 1237.12MB |
基于上述方案可以明显看到 ,优化效果非常明显。无论是容量还是性能都强于传统方案。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。