国产数据库PolarDB公开课-7 应用场景与实践
digoal约 76 分钟参考文档点击文末阅读原文打开; 推荐《最好的PostgreSQL学习镜像》;
PolarDB公开课来了, 国庆拿下这个国产信创数据库
系列文章导航
7、国产数据库PolarDB公开课-6 使用PostgreSQL开源工具/插件
PolarDB 应用场景与实践
这个章节基于"沉浸式学习PostgreSQL|PolarDB"素材构建, 来自真实的业务场景, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效. 这个章节核心目标是教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核. 所以面向的对象是数据库的用户、应用开发者、应用架构师、数据库厂商的产品经理、售前售后专家等角色.
由于受篇幅限制, 挑选了4个比较经典的实验进行详细的讲解:
1、如何快速构建“海量&逼真”的测试数据 2、跨境电商场景, 快速判断商标|品牌侵权 3、营销场景, 根据用户画像的相似度进行目标人群圈选, 实现精准营销 4、PolarDB向量数据库插件, 实现通义大模型AI的外脑, 解决通用大模型无法触达的私有知识库问题、幻觉问题
1、如何快速构建“海量&逼真”的测试数据
传统数据库测试通常使用标准套件tpcc,tpch,tpcb,tpcds等生成测试数据, 而当我们需要根据不同的业务场景来设计测试数据的特征, 并根据特征生成比较逼真的大规模数据时, 往往不太容易, 需要针对需求开发程序来实现.
另外, 传统数据库的测试模型也比较简单, 通常只能使用标准的tpcc,tpch,tpcb,tpcds等相关压测软件来实现测试. 无法根据特定业务需求来进行模拟压测.
PolarDB & PostgreSQL 自定义生成数据的方法非常多, 通过SRF, pgbench等可以快速加载特征数据, 可以根据实际的业务场景和需求进行数据的生成、压测. 可以实现提前预知业务压力问题, 帮助用户提前解决瓶颈.
开发者通常需要结合数据库的能力, 业务场景, 以及数据特征等构建符合业务真实情况的数据. 下面开始举例讲解.
一、如何生成各种需求、各种类型的随机值
1、100到500内的随机数
postgres=# select 100 + random()*400 ;
?column?
--------------------
335.81542324284186
(1 row)
2、100 到500内的随机整数
postgres=# select 100 + ceil(random()*400)::int ;
?column?
----------
338
(1 row)
3、uuid
postgres=# select gen_random_uuid();
gen_random_uuid
--------------------------------------
84e51794-e19c-40c1-9f8a-2dd80f29bc7a
(1 row) -- 请思考一下UUID的弊端?
-- 还有哪些UUID类型/类似功能插件?
4、md5
postgres=# select md5(now()::text);
md5
----------------------------------
5af6874991f7122e8db67170040fe0f7
(1 row) postgres=# select md5(random()::text);
md5
----------------------------------
744094f5f76f66afe4fbacb663ae03dc
(1 row)
5、将任意类型转换为hashvalue
\df *.*hash* postgres=# select hashtext('helloworld');
hashtext
------------
1836618988
(1 row)
6、随机点
postgres=# select point(random(), random());
point
-----------------------------------------
(0.1549642173067305,0.9623178115174227)
(1 row)
7、多边形
postgres=# select polygon(path '((0,0),(1,1),(2,0))');
polygon
---------------------
((0,0),(1,1),(2,0))
(1 row)
8、路径
postgres=# select path '((0,0),(1,1),(2,0))';
path
---------------------
((0,0),(1,1),(2,0))
(1 row)
9、50到150的随机范围
postgres=# select int8range(50, 50+(random()*100)::int);
int8range
-----------
[50,53)
(1 row) postgres=# select int8range(50, 50+(random()*100)::int);
int8range
-----------
[50,108)
(1 row)
10、数组
postgres=# select array['a','b','c'];
array
---------
{a,b,c}
(1 row)
SELECT ARRAY(SELECT ARRAY[i, i*2] FROM generate_series(1,5) AS a(i));
array
----------------------------------
{{1,2},{2,4},{3,6},{4,8},{5,10}}
(1 row)
11、随机数组
create or replace function gen_rnd_array(int,int,int) returns int[] as $$
select array(select $1 + ceil(random()*($2-$1))::int from generate_series(1,$3));
$$ language sql strict;
-- 10个取值范围1到100的值组成的数组
postgres=# select gen_rnd_array(1,100,10);
gen_rnd_array
--------------------------------
{4,70,70,77,21,68,93,57,92,97}
(1 row)
下面10个例子参考:
https://www.cnblogs.com/xianghuaqiang/p/14425274.html
12、生成随机整数 —— Generate a random integer
-- Function:
-- Generate a random integer -- Parameters:
-- min_value: Minimum value
-- max_value: Maximum value
create or replace function gen_random_int(min_value int default 1, max_value int default 1000) returns int as
$$
begin
return min_value + round((max_value - min_value) * random());
end;
$$ language plpgsql;
select gen_random_int();
select gen_random_int(1,10);
13、生成随机字母字符串 —— Generate a random alphabetical string
-- Function:
-- Generate a random alphabetical string -- Parameters:
-- str_length: Length of the string
-- letter_case: Case of letters. Values for option: lower, upper and mixed
create or replace function gen_random_alphabetical_string(str_length int default 10, letter_case text default 'lower') returns text as
$body$
begin
if letter_case in ('lower', 'upper', 'mixed') then
return
case letter_case
when 'lower' then array_to_string(array(select substr('abcdefghijklmnopqrstuvwxyz',(ceil(random()*26))::int, 1) FROM generate_series(1, str_length)), '')
when 'upper' then array_to_string(array(select substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ',(ceil(random()*26))::int, 1) FROM generate_series(1, str_length)), '')
when 'mixed' then array_to_string(array(select substr('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',(ceil(random()*52))::int, 1) FROM generate_series(1, str_length)), '')
else array_to_string(array(select substr('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',(ceil(random()*52))::int, 1) FROM generate_series(1, str_length)), '')
end;
else
RAISE EXCEPTION 'value % for parameter % is not recognized', letter_case, 'letter_case'
Using Hint = 'Use "lower", "upper" or "mixed". The default value is "lower"', ERRCODE ='22023';
end if;
end;
$body$
language plpgsql volatile;
select gen_random_alphabetical_string(10);
select gen_random_alphabetical_string(letter_case => 'lower');
14、生成随机字符串 —— Generate a random alphanumeric string
-- Function:
-- Generate a random alphanumeric string -- Parameters:
-- str_length: Length of the string
create or replace function gen_random_string(str_length int default 10) returns text as
$body$
select array_to_string(array(select substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',(ceil(random()*62))::int, 1) FROM generate_series(1, $1)), '');
$body$
language sql volatile;
select gen_random_string(10);
15、生成随机时间戳 —— Generate a random timestamp
-- Function:
-- Generate a random timestamp -- Parameters:
-- start_time: Lower bound of the time
-- end_time: Upper bound of the time
create or replace function gen_random_timestamp(start_time timestamp default date_trunc('year', now()), end_time timestamp default now()) returns timestamp as
$$
begin
return start_time + round((extract(epoch from end_time)- extract(epoch from start_time))* random()) * interval '1 second';
end;
$$ language plpgsql;
select gen_random_timestamp();
select gen_random_timestamp('2017-10-22 10:05:33','2017-10-22 10:05:35');
16、生成随机整型数组 —— Generate a random integer array
-- Function:
-- Generate a random integer array -- Parameters:
-- max_value: Maximum value of the elements
-- max_length: Maximum length of the array
-- fixed_length: Whether the length of array is fixed. If it is true, the length of array will match max_length.
create or replace function gen_random_int_array(max_value int default 1000, max_length int default 10, fixed_length bool default true ) returns int[] as
$$
begin
return case when not fixed_length then array(select ceil(random()*max_value)::int from generate_series(1,ceil(random()*max_length)::int)) else array(select ceil(random()*max_value)::int from generate_series(1,max_length)) end ;
end;
$$ LANGUAGE plpgsql;
select gen_random_int_array();
17、生成随机字符串数组 —— Generate a random string array
-- Function:
-- Generate a random string array -- Parameters:
-- str_length: Length of string
-- max_length: Maximum length of the array
-- fixed_length: Whether the length of array is fixed. If it is true, the length of array will match max_length.
create or replace function gen_random_string_array(str_length int default 10, max_length int default 10, fixed_length bool default TRUE ) returns text[] as
$$
declare v_array text[];
declare v_i int;
begin
v_array := array[]::text[];
if fixed_length then
for v_i in select generate_series(1, max_length) loop
v_array := array_append(v_array,array_to_string(array(select substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',(ceil(random()*62))::int, 1) FROM generate_series(1, str_length)), ''));
end loop;
else
for v_i in select generate_series(1,ceil(random()* max_length)::int) loop
v_array := array_append(v_array,array_to_string(array(select substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',(ceil(random()*62))::int, 1) FROM generate_series(1, str_length)), ''));
end loop;
end if;
return v_array;
end;
$$ language plpgsql;
select gen_random_string_array();
select gen_random_string_array(10,5,true);
18、从整数数组中随机选择一个元素 —— Randomly select one element from an integer array
-- Function:
-- Randomly select one element from an integer array
create or replace function select_random_one(list int[]) returns int as
$$
declare v_length int := array_length(list, 1);
begin
return list[1+round((v_length-1)*random())];
end;
$$ language plpgsql;
select select_random_one(array[1,2,3,4]);
19、从字符串数组中随机选择一个元素 —— Randomly select one element from an string-array
-- Function:
-- Randomly select one element from an string-array -- str_length: Length of string
create or replace function select_random_one(list text[]) returns text as
$$
declare v_length int := array_length(list, 1);
begin
return list[1+round((v_length-1)*random())];
end;
$$ language plpgsql;
select select_random_one(array['abc','def','ghi']);
20、随机生成汉字字符串 —— Generate a random Chinese string
-- Generate a random Chinese string
create or replace function gen_ramdom_chinese_string(str_length int) returns text as
$$
declare
my_char char;
char_string varchar := '';
i int := 0;
begin
while (i < str_length) loop -- chinese 19968..40869
my_char = chr(19968 + round(20901 * random())::int);
char_string := char_string || my_char;
i = i + 1;
end loop;
return char_string;
end;
$$ language plpgsql;
select gen_ramdom_chinese_string(10);
21、随机手机号码生成器，11位手机号 —— Generate a random mobile number
-- Generate a random mobile number
create or replace function gen_random_mobile_number() returns text as
$body$
select 1 || string_agg(col,'') from (select substr('0123456789',(ceil(random()*10))::int, 1) as col FROM generate_series(1, 10)) result;
$body$
language sql volatile;
select gen_random_mobile_number();
22、通过SRF函数生成批量数据
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+------------------------------+-----------------------------------+--------------------------------------------------------------------+------
pg_catalog | generate_series | SETOF bigint | bigint, bigint | func
pg_catalog | generate_series | SETOF bigint | bigint, bigint, bigint | func
pg_catalog | generate_series | SETOF integer | integer, integer | func
pg_catalog | generate_series | SETOF integer | integer, integer, integer | func
pg_catalog | generate_series | SETOF numeric | numeric, numeric | func
pg_catalog | generate_series | SETOF numeric | numeric, numeric, numeric | func
pg_catalog | generate_series | SETOF timestamp with time zone | timestamp with time zone, timestamp with time zone, interval | func
pg_catalog | generate_series | SETOF timestamp without time zone | timestamp without time zone, timestamp without time zone, interval | func
pg_catalog | generate_subscripts | SETOF integer | anyarray, integer | func
pg_catalog | generate_subscripts | SETOF integer | anyarray, integer, boolean | func
返回一批数值、时间戳、或者数组的下标。
例子，生成一批顺序值。
postgres=# select id from generate_series(1,10) t(id);
id
----
1
2
3
4
5
6
7
8
9
10
(10 rows)
23、随机数
random()
例子，生成一批随机整型
postgres=# select (random()*100)::int from generate_series(1,10);
int4
------
14
82
25
75
4
75
26
87
84
22
(10 rows)
24、随机字符串
md5(random()::text)
例子，生成一批随机字符串
postgres=# select md5(random()::text) from generate_series(1,10);
md5
----------------------------------
ba1f4f4b0073f61145a821c14437230d
a76b09292c1449ebdccad39bcb5864c0
d58f5ebe43f631e7b5b82e070a05e929
0c0d3971205dc6bd355e9a60b29a4c6d
bd437e87fd904ed6ecc80ed782abac7d
71aea571d8c0cd536de53fd2be8dd461
e32e105db58f9d39245e3e2b27680812
174f491a2ec7a3498cab45d3ce8a4277
563a7c389722f746378987b9c4d9bede
6e8231c4b7d9a5cfaae2a3e0cef22f24
(10 rows)
25、重复字符串
repeat('abc', 10)
例子，生成重复2次的随机字符串
postgres=# select repeat(md5(random()::text),2) from generate_series(1,10);
repeat
------------------------------------------------------------------
616d0a07a2b61cd923a14cb3bef06252616d0a07a2b61cd923a14cb3bef06252
73bc0d516a46182b484530f5e153085e73bc0d516a46182b484530f5e153085e
e745a65dbe0b4ef0d2a063487bbbe3d6e745a65dbe0b4ef0d2a063487bbbe3d6
90f9b8b18b3eb095f412e3651f0a946c90f9b8b18b3eb095f412e3651f0a946c
b300f78b20ac9a9534a46e9dfd488761b300f78b20ac9a9534a46e9dfd488761
a3d55c275f1e0f828c4e6863d4751d06a3d55c275f1e0f828c4e6863d4751d06
40e609dbe208fc66372b1c829018097140e609dbe208fc66372b1c8290180971
f661298e28403bc3005ac3aebae49e16f661298e28403bc3005ac3aebae49e16
10d0641e40164a238224d2e16a28764710d0641e40164a238224d2e16a287647
450e599890935df576e20c457691c421450e599890935df576e20c457691c421
(10 rows)
26、随机中文
create or replace function gen_hanzi(int) returns text as $$
declare
res text;
begin
if $1 >=1 then
select string_agg(chr(19968+(random()*20901)::int), '') into res from generate_series(1,$1);
return res;
end if;
return null;
end;
$$ language plpgsql strict;
postgres=# select gen_hanzi(10) from generate_series(1,10);
gen_hanzi
----------------------
騾歵癮崪圚祯骤氾準赔
縬寱癱办戾薶窍爉充環
鷊赶輪肸蒹焷尮禀漽湯
庰槖诤蜞礀链惧珿憗腽
憭釃轮訞陡切瀰煈瘐獵
韸琵慆蝾啈響夐捶燚積
菥芉阣瀤樂潾敾糩镽礕
廂垅欳事鎤懯劑搯蔷窡
覤綊伱鳪散噹镄灳毯杸
鳀倯鰂錾牓晟挗觑镈壯
(10 rows)
27、随机数组
create or replace function gen_rand_arr(int,int) returns int[] as $$
select array_agg((random()*$1)::int) from generate_series(1,$2);
$$ language sql strict;
postgres=# select gen_rand_arr(100,10) from generate_series(1,10);
gen_rand_arr
---------------------------------
{69,11,12,70,7,41,81,95,83,17}
{26,79,20,21,64,64,51,90,38,38}
{3,64,46,28,26,55,39,12,69,76}
{66,38,87,78,8,94,18,88,89,1}
{6,14,81,26,36,45,90,87,35,28}
{25,38,91,71,67,17,26,5,29,95}
{82,94,32,69,72,40,63,90,29,51}
{91,34,66,72,60,1,17,50,88,51}
{77,13,89,69,84,56,86,10,61,14}
{5,43,8,38,11,80,78,74,70,6}
(10 rows)
28、连接符
postgres=# select concat('a', ' ', 'b');
concat
--------
a b
(1 row)
29、随机身份证号
通过自定义函数，可以生成很多有趣的数据。 例如 随机身份证号
create or replace function gen_id(
a date,
b date
)
returns text as $$
select lpad((random()*99)::int::text, 2, '0') ||
lpad((random()*99)::int::text, 2, '0') ||
lpad((random()*99)::int::text, 2, '0') ||
to_char(a + (random()*(b-a))::int, 'yyyymmdd') ||
lpad((random()*99)::int::text, 2, '0') ||
random()::int ||
(case when random()*10 >9 then 'X' else (random()*9)::int::text end ) ;
$$ language sql strict;
postgres=# select gen_id('1900-01-01', '2017-10-16') from generate_series(1,10);
gen_id
--------------------
25614020061108330X
49507919010403271X
96764619970119860X
915005193407306113
551360192005045415
430005192611170108
299138191310237806
95149919670723980X
542053198501097403
482334198309182411
(10 rows)
二、如何快速生成大量数据
1、通过SRF函数genrate_series快速生成
drop table if exists tbl; create unlogged table tbl (
id int primary key,
info text,
c1 int,
c2 float,
ts timestamp
);
-- 写入100万条
insert into tbl select id,md5(random()::text),random()*1000,random()*100,clock_timestamp() from generate_series(1,1000000) id;
INSERT 0 1000000
Time: 990.351 ms
postgres=# select * from tbl limit 10;
id | info | c1 | c2 | ts
----+----------------------------------+-----+--------------------+----------------------------
1 | 2861dff7a9005fd07bd565d4c222aefc | 731 | 35.985756074820685 | 2023-09-06 07:34:43.992953
2 | ada46617f699b439ac3749d339a17a37 | 356 | 6.641897326709056 | 2023-09-06 07:34:43.993349
3 | 53e5f281c152abbe2be107273f661dcf | 2 | 79.66681115076746 | 2023-09-06 07:34:43.993352
4 | 42a7ab47ac773966fd80bbfb4a381cc5 | 869 | 39.64575446230825 | 2023-09-06 07:34:43.993352
5 | fc1fe81740821e8099f28578fe602d47 | 300 | 23.26141144641234 | 2023-09-06 07:34:43.993353
6 | 54f85d06b05fa1ad3e6f6c25845a8c99 | 536 | 51.24406182086716 | 2023-09-06 07:34:43.993354
7 | 9aac2fa6715b5136ff08c984cf39b200 | 615 | 60.35335101210144 | 2023-09-06 07:34:43.993355
8 | 227f02f3ce4a6778ae8b95e4b161da8e | 665 | 35.615585743405376 | 2023-09-06 07:34:43.993356
9 | eb2f7c304e9139be23828b764a8334a2 | 825 | 60.37908523246465 | 2023-09-06 07:34:43.993356
10 | dce3b8e11fbcf85e6fd0abca9546447d | 438 | 45.88193344829534 | 2023-09-06 07:34:43.993357
(10 rows)
2、使用plpgsql或inline code, 快速创建分区表.
drop table if exists tbl; create unlogged table tbl (
id int primary key,
info text,
c1 int,
c2 float,
ts timestamp
) PARTITION BY HASH(id);
do language plpgsql $$
declare
cnt int := 256;
begin
for i in 0..cnt-1 loop
execute format('create unlogged table tbl_%s PARTITION OF tbl FOR VALUES WITH ( MODULUS %s, REMAINDER %s)', i, cnt, i);
end loop;
end;
$$;
insert into tbl select id,md5(random()::text),random()*1000,random()*100,clock_timestamp() from generate_series(1,1000000) id;
INSERT 0 1000000
Time: 1577.707 ms (00:01.578)
3、使用 pgbench 调用自定义SQL文件, 高速写入
drop table if exists tbl; create unlogged table tbl (
id serial4 primary key,
info text,
c1 int,
c2 float,
ts timestamp
);
vi t.sql insert into tbl (info,c1,c2,ts) values (md5(random()::text), random()*1000, random()*100, clock_timestamp());
开启10个连接, 执行t.sql共120秒.
pgbench -M prepared -n -r -P 1 -f ./t.sql -c 10 -j 10 -T 120
transaction type: ./t.sql
scaling factor: 1
query mode: prepared
number of clients: 10
number of threads: 10
duration: 120 s
number of transactions actually processed: 18336072
latency average = 0.065 ms
latency stddev = 0.105 ms
initial connection time = 25.519 ms
tps = 152823.214015 (without initial connection time)
statement latencies in milliseconds:
0.065 insert into tbl (info,c1,c2,ts) values (md5(random()::text), random()*1000, random()*100, clock_timestamp());
4、使用 pgbench 内置的 tpcb模型, 自动创建表和数据.
初始化1000万条tpcb数据.
pgbench -i -s 100 --unlogged-tables
测试tpcb读请求
pgbench -M prepared -n -r -P 1 -c 10 -j 10 -S -T 120 transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 10
number of threads: 10
duration: 120 s
number of transactions actually processed: 19554665
latency average = 0.061 ms
latency stddev = 0.051 ms
initial connection time = 15.302 ms
tps = 162975.776467 (without initial connection time)
statement latencies in milliseconds:
0.000 \set aid random(1, 100000 * :scale)
0.061 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
测试tpcb读写请求
pgbench -M prepared -n -r -P 1 -c 10 -j 10 -T 120 transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: prepared
number of clients: 10
number of threads: 10
duration: 120 s
number of transactions actually processed: 2531643
latency average = 0.474 ms
latency stddev = 0.373 ms
initial connection time = 18.930 ms
tps = 21098.448090 (without initial connection time)
statement latencies in milliseconds:
0.000 \set aid random(1, 100000 * :scale)
0.000 \set bid random(1, 1 * :scale)
0.000 \set tid random(1, 10 * :scale)
0.000 \set delta random(-5000, 5000)
0.045 BEGIN;
0.095 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.068 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.069 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.077 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.061 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.056 END;
5、留作业, 思考一下如下模型数据怎么生成?
tpcc tpcds tpch
三、如何生成按需求分布的随机值
https://www.postgresql.org/docs/16/pgbench.html
1、pgbench 内置生成按不同的概率特征分布的随机值的函数.
例如在电商业务、游戏业务中, 活跃用户可能占比只有20%, 极度活跃的更少, 如果有一表记录了每个用户的行为, 那么生成的数据可能是高斯分布的.
均匀分布
random ( lb, ub ) → integer
Computes a uniformly-distributed random integer in [lb, ub].
random(1, 10) → an integer between 1 and 10 指数分布
random_exponential ( lb, ub, parameter ) → integer
Computes an exponentially-distributed random integer in [lb, ub], see below.
random_exponential(1, 10, 3.0) → an integer between 1 and 10
高斯分布
random_gaussian ( lb, ub, parameter ) → integer
Computes a Gaussian-distributed random integer in [lb, ub], see below.
random_gaussian(1, 10, 2.5) → an integer between 1 and 10
Zipfian 分布
random_zipfian ( lb, ub, parameter ) → integer
Computes a Zipfian-distributed random integer in [lb, ub], see below.
random_zipfian(1, 10, 1.5) → an integer between 1 and 10
例如
drop table if exists tbl_log; create unlogged table tbl_log (
uid int, -- 用户id
info text, -- 行为
ts timestamp -- 时间
);
vi t.sql \set uid random_gaussian(1,1000,2.5)
insert into tbl_log values (:uid, md5(random()::text), now());
pgbench -M prepared -n -r -P 1 -f ./t.sql -c 10 -j 10 -T 120 transaction type: ./t.sql
scaling factor: 1
query mode: prepared
number of clients: 10
number of threads: 10
duration: 120 s
number of transactions actually processed: 21752866
latency average = 0.055 ms
latency stddev = 0.089 ms
initial connection time = 23.170 ms
tps = 181307.721398 (without initial connection time)
statement latencies in milliseconds:
0.000 \set uid random_gaussian(1,1000,2.5)
0.055 insert into tbl_log values (:uid, md5(random()::text), now());
-- 查看分布情况, 产生的记录条数符合高斯分布
select uid,count(*) from tbl_log group by uid order by 2 desc; uid | count
------+-------
495 | 44221
505 | 44195
484 | 44128
478 | 44089
507 | 44074
499 | 44070
502 | 44069
506 | 44064
516 | 44057
513 | 44057
501 | 44019
....
10 | 2205
989 | 2187
990 | 2185
11 | 2174
9 | 2154
991 | 2139
7 | 2131
6 | 2120
993 | 2109
992 | 2087
5 | 2084
994 | 2066
8 | 2053
995 | 2052
996 | 2042
3 | 2003
4 | 1995
997 | 1985
2 | 1984
999 | 1966
1 | 1919
998 | 1915
1000 | 1890
(1000 rows)
2、pgbench 也可以将接收到的SQL结果作为变量, 从而执行有上下文交换的业务逻辑测试.
drop table if exists tbl;
create unlogged table tbl (
uid int primary key,
info text,
ts timestamp
); insert into tbl select generate_series(1,1000000), md5(random()::text), now();
drop table if exists tbl_log;
create unlogged table tbl_log (
uid int,
info_before text,
info_after text,
client_inet inet,
client_port int,
ts timestamp
);
vi t.sql \set uid random(1,1000000)
with a as (
select uid,info from tbl where uid=:uid
)
update tbl set info=md5(random()::text) from a where tbl.uid=a.uid returning a.info as info_before, tbl.info as info_after \gset
insert into tbl_log values (:uid, :info_before, :info_after, inet_client_addr(), inet_client_port(), now());
pgbench -M prepared -n -r -P 1 -f ./t.sql -c 10 -j 10 -T 120 transaction type: ./t.sql
scaling factor: 1
query mode: prepared
number of clients: 10
number of threads: 10
duration: 120 s
number of transactions actually processed: 8306176
latency average = 0.144 ms
latency stddev = 0.117 ms
initial connection time = 23.128 ms
tps = 69224.826220 (without initial connection time)
statement latencies in milliseconds:
0.000 \set uid random(1,1000000)
0.081 with a as (
0.064 insert into tbl_log values (:uid, :info_before, :info_after, inet_client_addr(), inet_client_port(), now());
select * from tbl_log limit 10; postgres=# select * from tbl_log limit 10;
uid | info_before | info_after | client_inet | client_port | ts
--------+----------------------------------+----------------------------------+-------------+-------------+----------------------------
345609 | b1946507f8c128d18e6f7e41ce22440e | a2df0ff6272ea38a6629b216b61be6e6 | | | 2023-09-06 09:45:27.959822
110758 | 39b6e7ab8ee91edebcd8b20d0a9fc99e | 5996800e06a82ccf5af904e980020157 | | | 2023-09-06 09:45:27.959902
226098 | 71c1983845e006f59b1cb5bd44d34675 | 5ab57b88f67272f4567c17c9fd946d19 | | | 2023-09-06 09:45:27.961955
210657 | 4dc8e7aaeb7b2c323292c6f75c9c5e41 | 0a8a4d58f82639b7e23519b578a64dfa | | | 2023-09-06 09:45:27.962091
898076 | 6b65ce6281880d1922686a200604dee9 | e695ea569fc4747832f7bbada5acbc17 | | | 2023-09-06 09:45:27.962147
117448 | 09f6ab54fea2b6729ff5ea297dbb50e9 | 94da2a284ae4751a60165203e88f1ff7 | | | 2023-09-06 09:45:27.962234
208582 | e8cb577f92e6898ff384fa77c805da00 | 0188ddf40947257009336cdc0862143d | | | 2023-09-06 09:45:27.959403
2241 | cf4ae28a99a5f39f07f7d3e0bcee6940 | d98df58e39c3d8942471179961ffe95d | | | 2023-09-06 09:45:27.96257
466615 | b2c012d9a5db4957753f2b67c80943e3 | f9de82dad0b90138686c9c36774faf5e | | | 2023-09-06 09:45:27.962729
241103 | 9680999428704ffb103b10271e490d14 | d88e38aa975490fcb7e2d81c10fda3c9 | | | 2023-09-06 09:45:27.962784
(10 rows)
四、压测
1、使用pgbench 编写自定义压测脚本.
前面的例子都已经讲过了. 略.
五、思考
思考一下你熟悉的业务, 数据特征, 设计对应的表结构, 生成具有业务特征的数据, 编写业务相关SQL语句, 进行压力测试.
现在chatgpt已经非常的智能, 你可以尝试一下把你的想法告诉chatgpt, 让他来写SQL.
问题: 写一个postgresql函数, 生成随机单词组成的字符串. 回答: 下面是一个示例的 PostgreSQL 函数，用于生成由随机单词组成的字符串：
CREATE OR REPLACE FUNCTION generate_random_string(word_count INTEGER, word_length INTEGER)
RETURNS TEXT AS $$
DECLARE
alphabet TEXT[] := array['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'];
random_string TEXT := '';
BEGIN
FOR i IN 1..word_count LOOP
DECLARE
random_word TEXT := '';
BEGIN
FOR j IN 1..word_length LOOP
random_word := random_word || alphabet[ceil(random() * array_length(alphabet, 1))];
END LOOP;
random_string := random_string || random_word || ' ';
END;
END LOOP;
RETURN TRIM(random_string);
END;
$$ LANGUAGE plpgsql;
这个函数使用了两个参数：word_count 表示要生成的随机单词的数量，word_length 表示每个随机单词的长度。它同样使用了一个字母表数组 alphabet，在循环中，它从字母表中随机选择一个字母构成单词，并将单词添加到随机字符串中。最后使用 TRIM 函数去除字符串两端的空格，并返回生成的随机字符串。
2、跨境电商场景, 快速判断商标|品牌侵权
很多业务场景中需要判断商标侵权, 避免纠纷. 例如
电商的商品文字描述、图片描述中可能有侵权内容. 特别是跨境电商, 在一些国家侵权查处非常严厉. 注册公司名、产品名时可能侵权. 在写文章时, 文章的文字内容、视频内容、图片内容中的描述可能侵权.
而且商标侵权通常还有相似的情况, 例如修改大品牌名字的其中的个别字母, 蹭大品牌的流量, 导致大品牌名誉受损.
例如postgresql是个商标, 如果你使用posthellogresql、postgresqlabc, p0stgresql也可能算侵权.
以跨境电商为力, 为了避免侵权, 在发布内容时需要商品描述中出现的品牌名、产品名等是否与已有的商标库有相似.
对于跨境电商场景, 由于店铺和用户众多, 商品的修改、发布是比较高频的操作, 所以需要实现高性能的字符串相似匹配功能.
一、准备数据
创建一张品牌表, 用于存储收集好的注册商标(通常最终转换为文字).
create unlogged table tbl_ip ( -- 测试使用unlogged table, 加速数据生成
id serial primary key, -- 每一条品牌信息的唯一ID
n text -- 品牌名
);
使用随机字符模拟生成1000万条品牌名.
insert into tbl_ip (n) select md5(random()::text) from generate_series(1,10000000);
再放入几条比较容易识别的:
insert into tbl_ip(n) values ('polardb'),('polardbpg'),('polardbx'),('alibaba'),('postgresql'),('mysql'),('aliyun'),('apsaradb'),('apple'),('microsoft');
postgres=# select * from tbl_ip limit 10;
id | n
----+----------------------------------
1 | f4cd4669d249c1747c1d31b0b492d84e
2 | 2e29f32460485698088f4ab0632d86b7
3 | a8460622db4a3dc4ab70a8443a2c2a1a
4 | c4554856e259d3dfcccfb3c9872ab1d0
5 | b3a6041c5838d70d95a1316eea45bea3
6 | fc2d701eca05c74905fd1a604f072006
7 | f3dc443060e33bb672dc6a3b79bc1acd
8 | 1305b6092f9e798453e9f60840b8db2a
9 | 9b07cad251661627e15f239e5b122eaf
10 | 8b5d2a468435febe417b17d0d0442b86
(10 rows) postgres=# select count(*) from tbl_ip;
count
----------
10000010
(1 row)
二、传统方法只能使用like全模糊查询, 但是局部侵权的可能性非常多, 使用模糊查询需要很多很多组合, 性能会非常差.
例如postgresql是个商标, 如果用户使用了一个字符串为以下组合, 都可能算侵权:
post postgres sql gresql postgresql postgre
写成SQL应该是这样的
select * from tbl_ip where
n like '%post%' or
n like '%postgres%' or
n like '%sql%' or
n like '%gresql%' or
n like '%postgresql%' or
n like '%postgre%';
结果
id | n
----------+------------
10000005 | postgresql
10000006 | mysql
(2 rows)
耗时如下
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on tbl_ip (cost=0.00..333336.00 rows=5999 width=37) (actual time=2622.461..2622.463 rows=2 loops=1)
Filter: ((n ~~ '%post%'::text) OR (n ~~ '%postgres%'::text) OR (n ~~ '%sql%'::text) OR (n ~~ '%gresql%'::text) OR (n ~~ '%postgresql%'::text) OR (n ~~ '%postgre%'::text))
Rows Removed by Filter: 10000008
Planning Time: 1.381 ms
JIT:
Functions: 2
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 1.442 ms, Inlining 0.000 ms, Optimization 1.561 ms, Emission 6.486 ms, Total 9.489 ms
Execution Time: 2624.001 ms
(9 rows)
三、基于 PolarDB|PostgreSQL 特性的设计和实验
使用pg_trgm插件, gin索引, 以及它的字符串相似查询功能,
创建插件
postgres=# create extension if not exists pg_trgm;
NOTICE: extension "pg_trgm" already exists, skipping
CREATE EXTENSION
创建索引
postgres=# create index on tbl_ip using gin (n gin_trgm_ops);
设置相似度阈值, 仅返回相似度大于0.9的记录
postgres=# set pg_trgm.similarity_threshold=0.9;
SET
使用相似度查询
select *,
similarity(n, 'post'),
similarity(n, 'postgres'),
similarity(n, 'sql'),
similarity(n, 'gresql'),
similarity(n, 'postgresql'),
similarity(n, 'postgre')
from tbl_ip
where
n % 'post' or
n % 'postgres' or
n % 'sql' or
n % 'gresql' or
n % 'postgresql' or
n % 'postgre';
结果
id | n | similarity | similarity | similarity | similarity | similarity | similarity
----------+------------+------------+------------+------------+------------+------------+------------
10000005 | postgresql | 0.33333334 | 0.6666667 | 0.15384616 | 0.3846154 | 1 | 0.5833333
(1 row)
耗时如下
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_ip (cost=996.70..7365.20 rows=5999 width=37) (actual time=0.180..0.183 rows=1 loops=1)
Recheck Cond: ((n % 'post'::text) OR (n % 'postgres'::text) OR (n % 'sql'::text) OR (n % 'gresql'::text) OR (n % 'postgresql'::text) OR (n % 'postgre'::text))
Heap Blocks: exact=1
-> BitmapOr (cost=996.70..996.70 rows=6000 width=0) (actual time=0.140..0.141 rows=0 loops=1)
-> Bitmap Index Scan on tbl_ip_n_idx (cost=0.00..115.30 rows=1000 width=0) (actual time=0.053..0.053 rows=0 loops=1)
Index Cond: (n % 'post'::text)
-> Bitmap Index Scan on tbl_ip_n_idx (cost=0.00..200.00 rows=1000 width=0) (actual time=0.019..0.019 rows=0 loops=1)
Index Cond: (n % 'postgres'::text)
-> Bitmap Index Scan on tbl_ip_n_idx (cost=0.00..93.30 rows=1000 width=0) (actual time=0.007..0.007 rows=0 loops=1)
Index Cond: (n % 'sql'::text)
-> Bitmap Index Scan on tbl_ip_n_idx (cost=0.00..157.10 rows=1000 width=0) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: (n % 'gresql'::text)
-> Bitmap Index Scan on tbl_ip_n_idx (cost=0.00..242.90 rows=1000 width=0) (actual time=0.035..0.035 rows=1 loops=1)
Index Cond: (n % 'postgresql'::text)
-> Bitmap Index Scan on tbl_ip_n_idx (cost=0.00..179.10 rows=1000 width=0) (actual time=0.013..0.013 rows=0 loops=1)
Index Cond: (n % 'postgre'::text)
Planning Time: 4.682 ms
Execution Time: 0.272 ms
(18 rows)
使用了pg_trgm后, 即使是like查询响应速度也飞快:
postgres=# explain analyze select * from tbl_ip where
n like '%post%' or
n like '%postgres%' or
n like '%sql%' or
n like '%gresql%' or
n like '%postgresql%' or
n like '%postgre%';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_ip (cost=612.80..6981.30 rows=5999 width=37) (actual time=0.122..0.126 rows=2 loops=1)
Recheck Cond: ((n ~~ '%post%'::text) OR (n ~~ '%postgres%'::text) OR (n ~~ '%sql%'::text) OR (n ~~ '%gresql%'::text) OR (n ~~ '%postgresql%'::text) OR (n ~~ '%postgre%'::text))
Heap Blocks: exact=1
-> BitmapOr (cost=612.80..612.80 rows=6000 width=0) (actual time=0.099..0.101 rows=0 loops=1)
-> Bitmap Index Scan on tbl_ip_n_idx (cost=0.00..50.40 rows=1000 width=0) (actual time=0.047..0.048 rows=1 loops=1)
Index Cond: (n ~~ '%post%'::text)
-> Bitmap Index Scan on tbl_ip_n_idx (cost=0.00..136.20 rows=1000 width=0) (actual time=0.011..0.011 rows=1 loops=1)
Index Cond: (n ~~ '%postgres%'::text)
-> Bitmap Index Scan on tbl_ip_n_idx (cost=0.00..29.50 rows=1000 width=0) (actual time=0.003..0.003 rows=2 loops=1)
Index Cond: (n ~~ '%sql%'::text)
-> Bitmap Index Scan on tbl_ip_n_idx (cost=0.00..93.30 rows=1000 width=0) (actual time=0.014..0.014 rows=1 loops=1)
Index Cond: (n ~~ '%gresql%'::text)
-> Bitmap Index Scan on tbl_ip_n_idx (cost=0.00..179.10 rows=1000 width=0) (actual time=0.014..0.014 rows=1 loops=1)
Index Cond: (n ~~ '%postgresql%'::text)
-> Bitmap Index Scan on tbl_ip_n_idx (cost=0.00..115.30 rows=1000 width=0) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: (n ~~ '%postgre%'::text)
Planning Time: 0.571 ms
Execution Time: 0.207 ms
(18 rows)
四、传统方法与PolarDB|PostgreSQL的对照
品牌数 | 传统like查询耗时 ms | PolarDB|PostgreSQL pg_trgm近似查询耗时 ms | PolarDB|PostgreSQL pg_trgm like查询耗时 ms
---|---|---|---
1000万条 | 2624.001 | 0.272 | 0.207
毫无疑问, PolarDB|PostgreSQL性能提升了上万倍, 而且解决了传统方法无法解决的相似问题检索.
五、知识点
1、pg_trgm
https://www.postgresql.org/docs/16/pgtrgm.html
如何计算两个字符串的相似度:
1、切词. 非字母或数字都被认为是word分隔符, 将字符串拆分成若干个word. 2、将word转换成token. 在每个word的前面加2个空格, 每个word的末尾加1个空格, 然后以连续的三个字符为一组, 从头开始切, 将每个" word "切分为若干个“3个字符的token”. 3、去除重复token, 得到一组token. 4、根据token来计算2个字符串的相似性. 注意有不同的算法.
将字符串转换生成token的例子:
-- 第一步得到two和words, 然后得到" two "和" words ", 然后得到以下.
postgres=# select show_trgm('two ,words');
show_trgm
-------------------------------------------------------
{" t"," w"," tw"," wo","ds ",ord,rds,two,"wo ",wor}
(1 row) postgres=# select show_trgm('two , words');
show_trgm
-------------------------------------------------------
{" t"," w"," tw"," wo","ds ",ord,rds,two,"wo ",wor}
(1 row)
postgres=# select show_trgm(' two , words ');
show_trgm
-------------------------------------------------------
{" t"," w"," tw"," wo","ds ",ord,rds,two,"wo ",wor}
(1 row)
-- 结果token会去重
postgres=# select show_trgm('two two1');
show_trgm
-----------------------------------
{" t"," tw","o1 ",two,"wo ",wo1}
(1 row)
postgres=# select show_trgm('two');
show_trgm
-------------------------
{" t"," tw",two,"wo "}
(1 row)
postgres=# select show_trgm('words');
show_trgm
---------------------------------
{" w"," wo","ds ",ord,rds,wor}
(1 row)
postgres=# select show_trgm('abc');
show_trgm
-------------------------
{" a"," ab",abc,"bc "}
(1 row)
postgres=# select show_trgm('abc hello');
show_trgm
-------------------------------------------------------
{" a"," h"," ab"," he",abc,"bc ",ell,hel,llo,"lo "}
(1 row)
比较两个字符串相似性的算法: 详见 contrib/pg_trgm/trgm_op.c
1: similarity (%) (t % 'word' ==> 计算相似性对应 similarity(t, 'word'))
相似性 = 两个字符串的token交集去重后的个数 / 两个字符串的token并集去重后的个数
大致可以表达 两个字符串的整体相似性.
阈值参数: pg_trgm.similarity_threshold (real)
2: word_similarity (<% and %>) ('word' <% t ==> 计算相似性对应 word_similarity('word', t))
word_similarity(string1, string2) == count.匹配string1 token的(token(substring(string2中的任意连续的word组))) / count(token(string1))
大致可以表达 字符串2的若干连续字符与字符串1的相似度.
阈值参数: pg_trgm.word_similarity_threshold (real)
3: strict_word_similarity (<<% and %>>) ('word' <<% t ==> 计算相似性对应 strict_word_similarity('word', t))
strict_word_similarity(string1, string2) == max( similarity(string1, string2中的任意连续的word组) )
大致可以表达 字符串2的若干连续单词与字符串1的相似度.
相似度阈值参数, 相似度大于阈值时, 对应的相似操作符返回true的结果.
阈值参数: pg_trgm.strict_word_similarity_threshold (real)
计算两个字符串相似度的例子:
postgres=# select similarity('abc','abc hello');
similarity
------------
0.4
(1 row)
postgres=# select similarity('abc hello','abc');
similarity
------------
0.4
(1 row) word_similarity
postgres=# select word_similarity('abc','abc hello');
word_similarity
-----------------
1
(1 row)
postgres=# select word_similarity('abc hello','abc');
word_similarity
-----------------
0.4
(1 row)
strict_word_similarity
postgres=# select strict_word_similarity('abc','abc hello');
strict_word_similarity
------------------------
1
(1 row)
postgres=# select strict_word_similarity('abc hello','abc');
strict_word_similarity
------------------------
0.4
(1 row)
postgres=# select similarity('word', 'wor ord');
similarity
------------
0.625
(1 row)
postgres=# select similarity('word', 'ord wor');
similarity
------------
0.625
(1 row)
postgres=# select word_similarity('word', 'ord wor');
word_similarity
-----------------
1
(1 row)
postgres=# select word_similarity('word', 'wor ord');
word_similarity
-----------------
0.625
(1 row)
postgres=# select strict_word_similarity('word', 'wor ord');
strict_word_similarity
------------------------
0.625
(1 row)
postgres=# select strict_word_similarity('word', 'ord wor');
strict_word_similarity
------------------------
0.625
(1 row)
六、思考
为什么传统方法与pg_trgm相比性能相差这么大?
字符串近似查询还可以应用于哪些场景?
如果将相似度调低, 性能还能这么好吗?
如果想返回最相似的一条, 怎么优化查询效果最佳?
和smlar插件相比, 搜索算法是否有相似之处?
3、营销场景, 根据用户画像的相似度进行目标人群圈选, 实现精准营销
在营销场景中, 通常会对用户的属性、行为等数据进行统计分析, 生成用户的标签, 也就是常说的用户画像.
标签举例: 男性、女性、年轻人、大学生、90后、司机、白领、健身达人、博士、技术达人、科技产品爱好者、2胎妈妈、老师、浙江省、15天内逛过手机电商店铺、... ...
有了用户画像, 在营销场景中一个重要的营销手段是根据条件选中目标人群, 进行精准营销.
例如圈选出包含这些标签的人群: 白领、科技产品爱好者、浙江省、技术达人、15天内逛过手机电商店铺 .
这个实验的目的是在有画像的基础上, 如何快速根据标签组合进行人群圈选 .
一、准备数据
设计1张标签元数据表, 后面的用户画像表从这张标签表随机抽取标签. 业务查询时也从这里搜索存在的标签并进行圈选条件的组合, 得到对应的标签ID组合.
drop table if exists tbl_tag; create table tbl_tag (
tid int primary key, -- 标签id
tag text, -- 标签名
info text -- 标签描述
);
假设有1万个标签, 写入标签元数据表.
insert into tbl_tag select id, md5(id::text), md5(random()::text) from generate_series(1, 10000) id;
创建2个函数, 产生若干的标签. 用来模拟产生每个用户对应的标签数据. 分别返回字符串和数组类型.
第一个函数, 随机提取若干个标签, 始终包含1-100的热门标签8个, 返回用户标签字符串:
create or replace function get_tags_text(int) returns text as $$
with a as (select string_agg(tid::text, ',') s from tbl_tag where tid = any (array(select ceil(random()*100)::int from generate_series(1,8) group by 1)))
, b as (select string_agg(tid::text, ',') s from tbl_tag where tid = any (array(select ceil(100+random()*9900)::int from generate_series(1,$1) group by 1)))
select ','||a.s||','||b.s||',' from a,b;
$$ language sql strict;
得到类似这样的结果:
postgres=# select get_tags_text(10);
get_tags_text
----------------------------------------------------------------------
,11,12,39,44,45,59,272,1001,1322,1402,2514,6888,7404,8922,9200,9409,
(1 row) postgres=# select get_tags_text(10);
get_tags_text
------------------------------------------------------------------------
,12,34,52,55,71,79,88,302,582,1847,3056,5156,8231,8542,8572,8747,9727,
(1 row)
第二个函数, 随机提取若干个标签, 始终包含1-100的热门标签8个, 返回用户标签数组:
create or replace function get_tags_arr(int) returns int[] as $$
with a as (select array_agg(tid) s from tbl_tag where tid = any (array(select ceil(random()*100)::int from generate_series(1,8) group by 1)))
, b as (select array_agg(tid) s from tbl_tag where tid = any (array(select ceil(100+random()*9900)::int from generate_series(1,$1) group by 1)))
select a.s||b.s from a,b;
$$ language sql strict;
得到类似这样的结果:
postgres=# select * from get_tags_arr(10);
get_tags_arr
----------------------------------------------------------------------------
{13,35,42,61,67,69,76,78,396,2696,3906,4356,5064,5711,7363,9417,9444,9892}
(1 row) postgres=# select * from get_tags_arr(10);
get_tags_arr
-------------------------------------------------------------------------
{2,10,20,80,84,85,89,3410,3515,4159,4182,5217,6549,6775,7289,9141,9431}
(1 row)
二、传统方法设计和实验
传统数据库没有数组类型, 所以需要用字符串存储标签.
创建用户画像表
drop table if exists tbl_users; create unlogged table tbl_users ( -- 为便于加速生成测试数据, 使用unlogged table
uid int primary key, -- 用户id
tags text -- 该用户拥有的标签 , 使用字符串类型
);
创建100万个用户, 用户被贴的标签数从32到256个, 随机产生, 其中8个为热门标签(例如性别、年龄段等都属于热门标签).
insert into tbl_users select id, get_tags_text(ceil(24+random()*224)::int) from generate_series(1,1000000) id;
测试如下, 分别搜索包含如下标签组合的用户:
2 2,8 2,2696 2,4356,5064,5711,7363,9417,9444 4356,5064,5711,7363,9417,9444
使用如下SQL:
select uid from tbl_users where tags like '%,2,%'; select uid from tbl_users where tags like '%,2,%' or tags like '%,8,%';
select uid from tbl_users where tags like '%,2,%' or tags like '%,2696,%';
select uid from tbl_users where tags like '%,2,%' or tags like '%,4356,%' or tags like '%,5064,%' or tags like '%,5711,%' or tags like '%,7363,%' or tags like '%,9417,%' or tags like '%,9444,%' ;
select uid from tbl_users where tags like '%,4356,%' or tags like '%,5064,%' or tags like '%,5711,%' or tags like '%,7363,%' or tags like '%,9417,%' or tags like '%,9444,%' ;
查看以上SQL运行的执行计划和耗时如下:
postgres=# explain analyze select uid from tbl_users where tags like '%,2,%';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on tbl_users (cost=0.00..103268.00 rows=80808 width=4) (actual time=0.018..1108.805 rows=77454 loops=1)
Filter: (tags ~~ '%,2,%'::text)
Rows Removed by Filter: 922546
Planning Time: 1.095 ms
Execution Time: 1110.267 ms
(5 rows) postgres=# explain analyze select uid from tbl_users where tags like '%,2,%' or tags like '%,8,%';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Seq Scan on tbl_users (cost=0.00..105768.00 rows=127232 width=4) (actual time=0.029..2001.379 rows=149132 loops=1)
Filter: ((tags ~~ '%,2,%'::text) OR (tags ~~ '%,8,%'::text))
Rows Removed by Filter: 850868
Planning Time: 1.209 ms
Execution Time: 2004.062 ms
(5 rows)
postgres=# explain analyze select uid from tbl_users where tags like '%,2,%' or tags like '%,2696,%';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on tbl_users (cost=0.00..105768.00 rows=90093 width=4) (actual time=0.035..2058.797 rows=90084 loops=1)
Filter: ((tags ~~ '%,2,%'::text) OR (tags ~~ '%,2696,%'::text))
Rows Removed by Filter: 909916
Planning Time: 1.190 ms
Execution Time: 2060.434 ms
(5 rows)
postgres=# explain analyze select uid from tbl_users where tags like '%,2,%' or tags like '%,4356,%' or tags like '%,5064,%' or tags like '%,5711,%' or tags like '%,7363,%' or tags like '%,9417,%' or tags like '%,9444,%' ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on tbl_users (cost=0.00..118268.00 rows=135482 width=4) (actual time=0.024..6765.315 rows=150218 loops=1)
Filter: ((tags ~~ '%,2,%'::text) OR (tags ~~ '%,4356,%'::text) OR (tags ~~ '%,5064,%'::text) OR (tags ~~ '%,5711,%'::text) OR (tags ~~ '%,7363,%'::text) OR (tags ~~ '%,9417,%'::text) OR (tags ~~ '%,9444,%'::text))
Rows Removed by Filter: 849782
Planning Time: 4.344 ms
Execution Time: 6767.990 ms
(5 rows)
postgres=# explain analyze select uid from tbl_users where tags like '%,4356,%' or tags like '%,5064,%' or tags like '%,5711,%' or tags like '%,7363,%' or tags like '%,9417,%' or tags like '%,9444,%' ;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on tbl_users (cost=0.00..115768.00 rows=59480 width=4) (actual time=0.112..6206.775 rows=78827 loops=1)
Filter: ((tags ~~ '%,4356,%'::text) OR (tags ~~ '%,5064,%'::text) OR (tags ~~ '%,5711,%'::text) OR (tags ~~ '%,7363,%'::text) OR (tags ~~ '%,9417,%'::text) OR (tags ~~ '%,9444,%'::text))
Rows Removed by Filter: 921173
Planning Time: 4.223 ms
Execution Time: 6208.191 ms
(5 rows)
三、使用PolarDB|PostgreSQL 特性设计和实验1
传统方法没有用到任何的索引, 每次请求都要扫描用户画像表的所有记录, 计算每一个LIKE的算子, 性能比较差.
为了提升查询性能, 我们可以使用gin索引和pg_trgm插件, 支持字符串内的模糊查询索引加速.
复用传统方法的数据, 创建gin索引, 支持索引加速模糊查询.
create extension pg_trgm; create index on tbl_users using gin (tags gin_trgm_ops);
使用索引后, 查看执行计划和耗时如下:
postgres=# explain analyze select uid from tbl_users where tags like '%,2,%';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_users (cost=555.93..58686.88 rows=80808 width=4) (actual time=30.315..76.314 rows=77454 loops=1)
Recheck Cond: (tags ~~ '%,2,%'::text)
Heap Blocks: exact=53210
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..535.73 rows=80808 width=0) (actual time=22.967..22.967 rows=77454 loops=1)
Index Cond: (tags ~~ '%,2,%'::text)
Planning Time: 0.991 ms
Execution Time: 78.163 ms
(7 rows) postgres=# explain analyze select uid from tbl_users where tags like '%,2,%' or tags like '%,8,%';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_users (cost=983.56..87215.27 rows=127232 width=4) (actual time=48.651..811.842 rows=149132 loops=1)
Recheck Cond: ((tags ~~ '%,2,%'::text) OR (tags ~~ '%,8,%'::text))
Rows Removed by Index Recheck: 299658
Heap Blocks: exact=41915 lossy=33158
-> BitmapOr (cost=983.56..983.56 rows=131313 width=0) (actual time=43.554..43.554 rows=0 loops=1)
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..535.73 rows=80808 width=0) (actual time=24.923..24.923 rows=77454 loops=1)
Index Cond: (tags ~~ '%,2,%'::text)
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..384.22 rows=50505 width=0) (actual time=18.629..18.629 rows=77054 loops=1)
Index Cond: (tags ~~ '%,8,%'::text)
Planning Time: 1.496 ms
Execution Time: 814.748 ms
(11 rows)
postgres=# explain analyze select uid from tbl_users where tags like '%,2,%' or tags like '%,2696,%';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_users (cost=958.67..64006.30 rows=90093 width=4) (actual time=75.859..900.779 rows=90084 loops=1)
Recheck Cond: ((tags ~~ '%,2,%'::text) OR (tags ~~ '%,2696,%'::text))
Rows Removed by Index Recheck: 348263
Heap Blocks: exact=39411 lossy=33155
-> BitmapOr (cost=958.67..958.67 rows=90909 width=0) (actual time=71.980..71.981 rows=0 loops=1)
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..535.73 rows=80808 width=0) (actual time=26.486..26.487 rows=77454 loops=1)
Index Cond: (tags ~~ '%,2,%'::text)
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..377.89 rows=10101 width=0) (actual time=45.492..45.492 rows=62326 loops=1)
Index Cond: (tags ~~ '%,2696,%'::text)
Planning Time: 1.479 ms
Execution Time: 902.637 ms
(11 rows)
postgres=# explain analyze select uid from tbl_users where tags like '%,2,%' or tags like '%,4356,%' or tags like '%,5064,%' or tags like '%,5711,%' or tags like '%,7363,%' or tags like '%,9417,%' or tags like '%,9444,%' ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_users (cost=3041.18..100880.75 rows=135482 width=4) (actual time=210.772..4047.148 rows=150218 loops=1)
Recheck Cond: ((tags ~~ '%,2,%'::text) OR (tags ~~ '%,4356,%'::text) OR (tags ~~ '%,5064,%'::text) OR (tags ~~ '%,5711,%'::text) OR (tags ~~ '%,7363,%'::text) OR (tags ~~ '%,9417,%'::text) OR (tags ~~ '%,9444,%'::text))
Rows Removed by Index Recheck: 422706
Heap Blocks: exact=56868 lossy=33226
-> BitmapOr (cost=3041.18..3041.18 rows=141614 width=0) (actual time=205.898..205.899 rows=0 loops=1)
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..535.73 rows=80808 width=0) (actual time=24.656..24.656 rows=77454 loops=1)
Index Cond: (tags ~~ '%,2,%'::text)
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..428.40 rows=20202 width=0) (actual time=45.014..45.014 rows=62615 loops=1)
Index Cond: (tags ~~ '%,4356,%'::text)
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..377.89 rows=10101 width=0) (actual time=22.680..22.680 rows=39025 loops=1)
Index Cond: (tags ~~ '%,5064,%'::text)
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..428.40 rows=20202 width=0) (actual time=28.809..28.809 rows=62697 loops=1)
Index Cond: (tags ~~ '%,5711,%'::text)
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..377.89 rows=10101 width=0) (actual time=28.646..28.646 rows=62647 loops=1)
Index Cond: (tags ~~ '%,7363,%'::text)
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..327.89 rows=100 width=0) (actual time=28.361..28.361 rows=62172 loops=1)
Index Cond: (tags ~~ '%,9417,%'::text)
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..327.89 rows=100 width=0) (actual time=27.729..27.730 rows=62821 loops=1)
Index Cond: (tags ~~ '%,9444,%'::text)
Planning Time: 4.517 ms
Execution Time: 4050.040 ms
(21 rows)
postgres=# explain analyze select uid from tbl_users where tags like '%,4356,%' or tags like '%,5064,%' or tags like '%,5711,%' or tags like '%,7363,%' or tags like '%,9417,%' or tags like '%,9444,%' ;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_users (cost=2357.58..50755.97 rows=59480 width=4) (actual time=209.115..3689.534 rows=78827 loops=1)
Recheck Cond: ((tags ~~ '%,4356,%'::text) OR (tags ~~ '%,5064,%'::text) OR (tags ~~ '%,5711,%'::text) OR (tags ~~ '%,7363,%'::text) OR (tags ~~ '%,9417,%'::text) OR (tags ~~ '%,9444,%'::text))
Rows Removed by Index Recheck: 455241
Heap Blocks: exact=55903 lossy=33218
-> BitmapOr (cost=2357.58..2357.58 rows=60806 width=0) (actual time=204.235..204.236 rows=0 loops=1)
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..428.40 rows=20202 width=0) (actual time=57.485..57.485 rows=62615 loops=1)
Index Cond: (tags ~~ '%,4356,%'::text)
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..377.89 rows=10101 width=0) (actual time=26.156..26.157 rows=39025 loops=1)
Index Cond: (tags ~~ '%,5064,%'::text)
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..428.40 rows=20202 width=0) (actual time=33.539..33.539 rows=62697 loops=1)
Index Cond: (tags ~~ '%,5711,%'::text)
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..377.89 rows=10101 width=0) (actual time=30.136..30.136 rows=62647 loops=1)
Index Cond: (tags ~~ '%,7363,%'::text)
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..327.89 rows=100 width=0) (actual time=28.794..28.794 rows=62172 loops=1)
Index Cond: (tags ~~ '%,9417,%'::text)
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..327.89 rows=100 width=0) (actual time=28.122..28.122 rows=62821 loops=1)
Index Cond: (tags ~~ '%,9444,%'::text)
Planning Time: 3.860 ms
Execution Time: 3691.329 ms
(19 rows)
四、使用PolarDB|PostgreSQL 特性设计和实验2
很显然你不能满足于前面的模糊查询索引带来的性能提升, 特别是当and条件非常多时, 模糊查询的索引也要被多次扫描并使用bitmap进行合并, 性能不好. (以上方法对于一个模糊查询条件性能提升是非常明显的.)
PolarDB和PostgreSQL都支持数组类型, 用数组存储标签, 支持gin索引可以加速数组的包含查询.
创建用户画像表, 使用数组存储标签字段.
drop table if exists tbl_users; create unlogged table tbl_users ( -- 为便于加速生成测试数据, 使用unlogged table
uid int primary key, -- 用户id
tags int[] -- 该用户拥有的标签 , 使用数组类型
);
创建100万个用户, 用户被贴的标签数从32到256个, 随机产生, 其中8个为热门标签(例如性别、年龄段等都属于热门标签).
insert into tbl_users select id, get_tags_arr(ceil(24+random()*224)::int) from generate_series(1,1000000) id; create index on tbl_users using gin (tags);
搜索包含如下标签组合的用户:
2 2,8 2,2696 2,4356,5064,5711,7363,9417,9444 4356,5064,5711,7363,9417,9444
数组匹配的 SQL 语句如下:
select uid from tbl_users where tags @> array[2]; select uid from tbl_users where tags @> array[2,8];
select uid from tbl_users where tags @> array[2,2696];
select uid from tbl_users where tags @> array[2,4356,5064,5711,7363,9417,9444];
select uid from tbl_users where tags @> array[4356,5064,5711,7363,9417,9444];
使用数组类型和gin索引后, 查看执行计划和耗时如下:
postgres=# explain analyze select uid from tbl_users where tags @> array[2];
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_users (cost=437.95..53717.07 rows=76333 width=4) (actual time=24.031..69.706 rows=77641 loops=1)
Recheck Cond: (tags @> '{2}'::integer[])
Heap Blocks: exact=50231
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..418.86 rows=76333 width=0) (actual time=15.026..15.026 rows=77641 loops=1)
Index Cond: (tags @> '{2}'::integer[])
Planning Time: 1.137 ms
Execution Time: 74.015 ms
(7 rows) postgres=# explain analyze select uid from tbl_users where tags @> array[2,8];
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_users (cost=49.97..6172.63 rows=5847 width=4) (actual time=10.745..18.272 rows=5303 loops=1)
Recheck Cond: (tags @> '{2,8}'::integer[])
Heap Blocks: exact=5133
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..48.51 rows=5847 width=0) (actual time=10.081..10.081 rows=5303 loops=1)
Index Cond: (tags @> '{2,8}'::integer[])
Planning Time: 0.256 ms
Execution Time: 18.561 ms
(7 rows)
postgres=# explain analyze select uid from tbl_users where tags @> array[2,2696];
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_users (cost=21.27..443.58 rows=382 width=4) (actual time=2.872..4.662 rows=1003 loops=1)
Recheck Cond: (tags @> '{2,2696}'::integer[])
Heap Blocks: exact=999
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..21.18 rows=382 width=0) (actual time=2.729..2.729 rows=1003 loops=1)
Index Cond: (tags @> '{2,2696}'::integer[])
Planning Time: 0.246 ms
Execution Time: 4.750 ms
(7 rows)
postgres=# explain analyze select uid from tbl_users where tags @> array[2,4356,5064,5711,7363,9417,9444];
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_users (cost=64.38..65.50 rows=1 width=4) (actual time=5.476..5.478 rows=0 loops=1)
Recheck Cond: (tags @> '{2,4356,5064,5711,7363,9417,9444}'::integer[])
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..64.38 rows=1 width=0) (actual time=5.471..5.472 rows=0 loops=1)
Index Cond: (tags @> '{2,4356,5064,5711,7363,9417,9444}'::integer[])
Planning Time: 0.223 ms
Execution Time: 5.523 ms
(6 rows)
postgres=# explain analyze select uid from tbl_users where tags @> array[4356,5064,5711,7363,9417,9444];
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_users (cost=55.36..56.47 rows=1 width=4) (actual time=4.476..4.477 rows=0 loops=1)
Recheck Cond: (tags @> '{4356,5064,5711,7363,9417,9444}'::integer[])
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..55.36 rows=1 width=0) (actual time=4.471..4.472 rows=0 loops=1)
Index Cond: (tags @> '{4356,5064,5711,7363,9417,9444}'::integer[])
Planning Time: 0.275 ms
Execution Time: 4.528 ms
(6 rows)
五、使用PolarDB|PostgreSQL 特性设计和实验3
当我们输入一组标签, 如果想放宽圈选条件, 而不仅仅是以上精确包含, 怎么实现? 例如:
包含多少个以上的标签 有百分之多少以上的标签重合
复用上面的数据, 换上smlar插件和索引来实现以上功能.
创建smlar插件
postgres=# create extension smlar ;
CREATE EXTENSION
换上smlar索引
drop index tbl_users_tags_idx; create index on tbl_users using gin (tags _int4_sml_ops);
smlar插件的%操作符用来表达数组近似度过滤.
postgres=# explain analyze select count(*) from tbl_users where tags % array[1,2,3];
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1132.46..1132.47 rows=1 width=8) (actual time=75.613..75.614 rows=1 loops=1)
-> Bitmap Heap Scan on tbl_users (cost=35.25..1129.96 rows=1000 width=0) (actual time=75.609..75.610 rows=0 loops=1)
Recheck Cond: (tags % '{1,2,3}'::integer[])
Rows Removed by Index Recheck: 15059
Heap Blocks: exact=13734
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..35.00 rows=1000 width=0) (actual time=31.466..31.466 rows=15059 loops=1)
Index Cond: (tags % '{1,2,3}'::integer[])
Planning Time: 0.408 ms
Execution Time: 75.687 ms
(9 rows)
smlar插件支持的参数配置如下, 通过配置这些参数, 我们可以控制按什么算法来计算相似度, 相似度的过滤阈值是多少?
postgres=# select name,setting,enumvals,extra_desc from pg_settings where name ~ 'smlar';
name | setting | enumvals | extra_desc
------------------------+---------+------------------------+-----------------------------------------------------------------------------
smlar.idf_plus_one | off | | Calculate idf by log(1+d/df)
smlar.persistent_cache | off | | Cache of global stat is stored in transaction-independent memory
smlar.stattable | | | Named table stores global frequencies of array's elements
smlar.tf_method | n | {n,log,const} | TF method: n => number of entries, log => 1+log(n), const => constant value
smlar.threshold | 0.6 | | Array's with similarity lower than threshold are not similar by % operation
smlar.type | cosine | {cosine,tfidf,overlap} | Type of similarity formula: cosine(default), tfidf, overlap
(6 rows)
接下来我们来实现上述两种近似搜索:
包含多少个以上的标签 有百分之多少以上的标签重合
包含多少个以上的标签, smlar.type = overlap , smlar.threshold = INT
set smlar.type = overlap;
set smlar.threshold = 1; -- 精确匹配
select uid from tbl_users where tags % array[2]; set smlar.type = overlap;
set smlar.threshold = 1; -- 匹配到1个以上标签
select uid from tbl_users where tags % array[2,8];
set smlar.type = overlap;
set smlar.threshold = 2; -- 精确匹配
select uid from tbl_users where tags % array[2,2696];
set smlar.type = overlap;
set smlar.threshold = 5; -- 匹配到5个以上标签
select uid from tbl_users where tags % array[2,4356,5064,5711,7363,9417,9444];
set smlar.type = overlap;
set smlar.threshold = 6; -- 精确匹配
select uid from tbl_users where tags % array[4356,5064,5711,7363,9417,9444];
使用smlar插件, 数组类型和gin索引后, 查看执行计划和耗时如下:
postgres=# explain analyze select uid from tbl_users where tags % array[2];
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_users (cost=17.65..1112.36 rows=1000 width=4) (actual time=38.272..306.985 rows=77129 loops=1)
Recheck Cond: (tags % '{2}'::integer[])
Heap Blocks: exact=50082
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..17.40 rows=1000 width=0) (actual time=26.498..26.498 rows=77129 loops=1)
Index Cond: (tags % '{2}'::integer[])
Planning Time: 0.414 ms
Execution Time: 309.182 ms
(7 rows) postgres=# explain analyze select uid from tbl_users where tags % array[2,8];
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_users (cost=26.45..1121.16 rows=1000 width=4) (actual time=33.378..790.183 rows=149118 loops=1)
Recheck Cond: (tags % '{2,8}'::integer[])
Rows Removed by Index Recheck: 351146
Heap Blocks: exact=35117 lossy=33064
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..26.20 rows=1000 width=0) (actual time=29.934..29.934 rows=149118 loops=1)
Index Cond: (tags % '{2,8}'::integer[])
Planning Time: 0.924 ms
Execution Time: 794.029 ms
(8 rows)
postgres=# explain analyze select uid from tbl_users where tags % array[2,2696];
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_users (cost=26.45..1121.16 rows=1000 width=4) (actual time=6.287..26.042 rows=1028 loops=1)
Recheck Cond: (tags % '{2,2696}'::integer[])
Heap Blocks: exact=1019
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..26.20 rows=1000 width=0) (actual time=5.956..5.956 rows=1028 loops=1)
Index Cond: (tags % '{2,2696}'::integer[])
Planning Time: 0.439 ms
Execution Time: 26.218 ms
(7 rows)
postgres=# explain analyze select uid from tbl_users where tags % array[2,4356,5064,5711,7363,9417,9444];
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_users (cost=70.45..1165.16 rows=1000 width=4) (actual time=13.211..13.212 rows=0 loops=1)
Recheck Cond: (tags % '{2,4356,5064,5711,7363,9417,9444}'::integer[])
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..70.20 rows=1000 width=0) (actual time=13.204..13.205 rows=0 loops=1)
Index Cond: (tags % '{2,4356,5064,5711,7363,9417,9444}'::integer[])
Planning Time: 0.204 ms
Execution Time: 13.264 ms
(6 rows)
postgres=# explain analyze select uid from tbl_users where tags % array[4356,5064,5711,7363,9417,9444];
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_users (cost=61.65..1156.36 rows=1000 width=4) (actual time=11.364..11.366 rows=0 loops=1)
Recheck Cond: (tags % '{4356,5064,5711,7363,9417,9444}'::integer[])
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..61.40 rows=1000 width=0) (actual time=11.357..11.358 rows=0 loops=1)
Index Cond: (tags % '{4356,5064,5711,7363,9417,9444}'::integer[])
Planning Time: 0.264 ms
Execution Time: 11.447 ms
(6 rows)
有百分之多少以上的标签重合, smlar.type = cosine , smlar.threshold = FLOAT
set smlar.type = cosine;
set smlar.threshold = 1; -- 精确匹配, 目标也必须只包含2, 相当于相等
select uid from tbl_users where tags % array[2]; set smlar.type = cosine;
set smlar.threshold = 0.5; -- 两组标签的交集(重叠标签)占两组标签叠加(并集)后的50%以上
select uid from tbl_users where tags % array[2,8];
set smlar.type = cosine;
set smlar.threshold = 1; -- 精确匹配, 两组标签的交集(重叠标签)占两组标签叠加(并集)后的100%以上
select uid from tbl_users where tags % array[2,2696];
set smlar.type = cosine;
set smlar.threshold = 0.7; -- 两组标签的交集(重叠标签)占两组标签叠加(并集)后的70%以上
select uid from tbl_users where tags % array[2,4356,5064,5711,7363,9417,9444];
set smlar.type = cosine;
set smlar.threshold = 0.9; -- 两组标签的交集(重叠标签)占两组标签叠加(并集)后的90%以上
select uid from tbl_users where tags % array[4356,5064,5711,7363,9417,9444];
使用smlar插件, 数组类型和gin索引后, 查看执行计划和耗时如下:
postgres=# explain analyze select uid from tbl_users where tags % array[2];
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_users (cost=17.65..1112.36 rows=1000 width=4) (actual time=301.094..301.094 rows=0 loops=1)
Recheck Cond: (tags % '{2}'::integer[])
Rows Removed by Index Recheck: 77129
Heap Blocks: exact=50082
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..17.40 rows=1000 width=0) (actual time=25.659..25.659 rows=77129 loops=1)
Index Cond: (tags % '{2}'::integer[])
Planning Time: 0.252 ms
Execution Time: 301.135 ms
(8 rows) postgres=# explain analyze select uid from tbl_users where tags % array[2,8];
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_users (cost=26.45..1121.16 rows=1000 width=4) (actual time=799.554..799.554 rows=0 loops=1)
Recheck Cond: (tags % '{2,8}'::integer[])
Rows Removed by Index Recheck: 500264
Heap Blocks: exact=35117 lossy=33064
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..26.20 rows=1000 width=0) (actual time=43.356..43.356 rows=149118 loops=1)
Index Cond: (tags % '{2,8}'::integer[])
Planning Time: 0.379 ms
Execution Time: 799.611 ms
(8 rows)
postgres=# explain analyze select uid from tbl_users where tags % array[2,2696];
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_users (cost=26.45..1121.16 rows=1000 width=4) (actual time=26.476..26.478 rows=0 loops=1)
Recheck Cond: (tags % '{2,2696}'::integer[])
Rows Removed by Index Recheck: 1028
Heap Blocks: exact=1019
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..26.20 rows=1000 width=0) (actual time=5.242..5.242 rows=1028 loops=1)
Index Cond: (tags % '{2,2696}'::integer[])
Planning Time: 0.570 ms
Execution Time: 26.570 ms
(8 rows)
postgres=# explain analyze select uid from tbl_users where tags % array[2,4356,5064,5711,7363,9417,9444];
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_users (cost=70.45..1165.16 rows=1000 width=4) (actual time=16.722..16.723 rows=0 loops=1)
Recheck Cond: (tags % '{2,4356,5064,5711,7363,9417,9444}'::integer[])
Rows Removed by Index Recheck: 8
Heap Blocks: exact=8
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..70.20 rows=1000 width=0) (actual time=16.586..16.587 rows=8 loops=1)
Index Cond: (tags % '{2,4356,5064,5711,7363,9417,9444}'::integer[])
Planning Time: 0.276 ms
Execution Time: 16.795 ms
(8 rows)
postgres=# explain analyze select uid from tbl_users where tags % array[4356,5064,5711,7363,9417,9444];
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_users (cost=61.65..1156.36 rows=1000 width=4) (actual time=9.755..9.757 rows=0 loops=1)
Recheck Cond: (tags % '{4356,5064,5711,7363,9417,9444}'::integer[])
-> Bitmap Index Scan on tbl_users_tags_idx (cost=0.00..61.40 rows=1000 width=0) (actual time=9.748..9.749 rows=0 loops=1)
Index Cond: (tags % '{4356,5064,5711,7363,9417,9444}'::integer[])
Planning Time: 0.294 ms
Execution Time: 9.811 ms
(6 rows)
六、传统方法与PolarDB|PostgreSQL的对照
| 方法 | SQL1 耗时 ms | SQL2 耗时 ms | SQL3 耗时 ms | SQL4 耗时 ms | SQL5 耗时 ms |
|---|---|---|---|---|---|
| 传统字符串 + 全表扫描 | 1110.267 | 2004.062 | 2060.434 | 6767.990 | 6208.191 |
| PolarDB 传统字符串 + 模糊搜索 + gin索引加速 | 78.163 | 814.748 | 902.637 | 4050.040 | 3691.329 |
| PolarDB 数组 + gin索引加速 | 74.015 | 18.561 | 4.750 | 5.523 | 4.528 |
| PolarDB 数组(重叠个数)相似度搜索 + gin索引加速 | 309.182 | 794.029 | 26.218 | 13.264 | 11.447 |
| PolarDB 数组(重叠占比)相似度搜索 + gin索引加速 | 301.135 | 799.611 | 26.570 | 16.795 | 9.811 |
七、知识点
1、数组类型
2、gin索引
3、smlar 插件
更多算法参考: https://github.com/jirutka/smlar
4、pg_trgm 插件
八、思考
pg_trgm插件对字符串做了什么处理, 可以利用gin索引加速模糊查询加速?
smlar插件是如何通过索引快速判断两个数组的相似性达到阈值的?
为什么多个模糊匹配条件使用and条件后, 性能下降严重?
为什么使用数组类型后, 标签条件越多性能越好?
如果多个模糊匹配条件是or 条件呢? 性能会下降还是提升?
还有什么业务场景会用到数组?
还有哪些业务场景会用到字符串模糊匹配?
还有什么业务场景非常适合使用数组相似的功能?
除了使用标签匹配来圈选相似目标人群, 还可不可以使用其他方式圈选? 例如向量距离?
使用标签匹配时, 如果我们要排除某些标签, 而不是包含某些标签, 应该如何写sql, 性能又会怎么样呢?
为什么使用smlar进行相似度过滤时, 相似度越高性能越好?
SQL圈选性能和返回符合条件的用户记录数有没有关系? 是什么关系?
当使用pg_trgm进行模糊搜索加速时, 如果字符串中包含wchar(例如中文)时性能如果很差要怎么办? 如果需要模糊搜索的字符只有1个或2个字符时性能如果很差要怎么办?
4、PolarDB向量数据库插件, 实现通义大模型AI的外脑, 解决通用大模型无法触达的私有知识库问题、幻觉问题
通用大模型是使用大量高质量素材训练而成的AI大脑, 训练过程非常耗费硬件资源, 时间也非常漫长. AI的能力取决于训练素材(文本、视频、音频等), 虽然训练素材非常庞大, 可以说可以囊括目前已知的人类知识的巅峰. 但是模型是由“某家公司/某个社区”训练的, 它能触达的素材总有边界, 总有一些知识素材是无法被训练的, 例如私有(机密)素材. 因此通用大模型存在一些问题, 以chatGPT为例:
在达模型训练完成后, 新发现的知识. 大模型不具备这些知识, 它的回答可能产生幻觉(胡编乱造) 大模型没有训练过的私有知识. 大模型不具备这些知识, 它的回答可能产生幻觉(胡编乱造)
由于训练过程耗费大量资源且时间漫长, 为了解决幻觉问题, 不可能实时用未知知识去训练大模型, 向量数据库应运而生.
基本原理如下
1、将新知识(在达模型训练完成后, 新发现的知识 + 大模型没有训练过的私有知识)分段 2、将分段内容向量化, 生成对应的向量(浮点数组) 3、将向量(浮点数组), 以及对应的分段内容(文本)存储在向量数据库中 4、创建向量索引, 这是向量数据库的核心, 有了向量索引可以加速相似搜索. 例如: 1000万条向量中召回100条相似内容, 毫秒级别. 5、当用户提问时, 将用户问题向量化, 生成对应的向量(浮点数组) 6、到向量数据库中根据向量距离(向量相似性)进行搜索, 找到与用户问题相似度高于某个阈值的文本分段内容 7、将找到的文本分段内容+用户问题发送给大模型 8、大模型有了与用户提问问题相关新知识(分段文本内容)的加持, 可以更好的回答用户问题
一、大模型基本知识介绍及用法简介
为了完成这个实验, 你需要申请一个阿里云账号, 使用阿里云大模型服务.
1、模型服务灵积总览
DashScope灵积，旨在通过灵活、易用的模型API服务，让业界各个模态的模型能力，能方便触达AI开发者。
通过灵积API，丰富多样化的模型不仅能通过推理接口被集成，也能通过训练微调接口实现模型定制化，让AI应用开发更灵活，更简单！
https://dashscope.console.aliyun.com/overview
2、可以先在web界面体验各种模型
https://dashscope.aliyun.com/
3、进入控制台, 开通通义千问大模型+文本向量化模型
https://dashscope.console.aliyun.com/overview
4、创建API-KEY, 调用api需要用到key. 调用非常便宜, 一次调用不到1分钱, 学习几乎是0投入.
https://dashscope.console.aliyun.com/apiKey
5、因为灵积是个模型集市, 我们可以看到这个集市当前支持的所有模型:
https://dashscope.console.aliyun.com/model
支持大部分开源模型, 以及通义自有的模型. 分为三类: aigc, embeddings, audio.
5.1、aigc 模型
通义千问:
通义千问是一个专门响应人类指令的大模型，是一个灵活多变的全能型选手，能够写邮件、周报、提纲，创作诗歌、小说、剧本、coding、制表、甚至角色扮演。
Llama2大语言模型:
Llama2系列是来自Meta开发并公开发布的的大型语言模型（LLMs）。该系列模型提供了多种参数大小（7B、13B和70B等），并同时提供了预训练和针对对话场景的微调版本。
百川开源大语言模型:
百川开源大语言模型来自百川智能，基于Transformer结构，在大约1.2万亿tokens上训练的70亿参数模型，支持中英双语。
通义万相系列:
通义万相是基于自研的Composer组合生成框架的AI绘画创作大模型，提供了一系列的图像生成能力。支持根据用户输入的文字内容，生成符合语义描述的不同风格的图像，或者根据用户输入的图像，生成不同用途的图像结果。通过知识重组与可变维度扩散模型，加速收敛并提升最终生成图片的效果。图像结果贴合语义，构图自然、细节丰富。支持中英文双语输入。当前包括通义万相-文生图，和通义万相-人像风格重绘模型。
StableDiffusion文生图模型:
StableDiffusion文生图模型将开源社区stable-diffusion-v1.5版本进行了服务化支持。该模型通过clip模型能够将文本的embedding和图片embedding映射到相同空间，从而通过输入文本并结合unet的稳定扩散预测噪声的能力，生成图片。
ChatGLM开源双语对话语言模型:
ChatGLM开源双语对话语言模型来自智谱AI，在数理逻辑、知识推理、长文档理解上均有支持。
智海三乐教育大模型:
智海三乐教育大模型由浙江大学联合高等教育出版社、阿里云和华院计算等单位共同研制。该模型是以阿里云通义千问70亿参数与训练模型为基座，通过继续预训练和微调等技术手段，利用核心教材、领域论文和学位论文等教科书级高质量语料，结合专业指令数据集，训练出的一款专注于人工智能专业领域教育的大模型，实现了教育领域的知识强化和教育场景中的能力升级。
姜子牙通用大模型:
姜子牙通用大模型由IDEA研究院认知计算与自然语言研究中心主导开源，具备翻译、编程、文本分类、信息抽取、摘要、文案生成、常识问答和数学计算等能力。
Dolly开源大语言模型:
Dolly开源大语言模型来自Databricks，支持脑暴、分类、问答、生成、信息提取、总结等能力。
BELLE开源中文对话大模型:
BELLE是一个基于LLaMA二次预训练和调优的中文大语言模型，由链家开发。
MOSS开源对话语言模型:
MOSS开源对话语言模型来自复旦大学OpenLMLab项目，具有指令遵循能力、多轮对话能力、规避有害请求能力。
元语功能型对话大模型V2:
元语功能型对话大模型V2是一个支持中英双语的功能型对话语言大模型,由元语智能提供。V2版本使用了和V1版本相同的技术方案，在微调数据、人类反馈强化学习、思维链等方面进行了优化。
BiLLa开源推理能力增强模型:
BiLLa是一种改良的开源LLaMA模型，特色在于增强中文推理能力。
5.2、embeddings,
通用文本向量:
基于LLM底座的统一向量化模型，面向全球多个主流语种，提供高水准的向量服务，帮助用户将文本数据快速转换为高质量的向量数据。
ONE-PEACE多模态向量表征:
ONE-PEACE是一个通用的图文音多模态向量表征模型，支持将图像，语音等多模态数据高效转换成Embedding向量。在语义分割、音文检索、音频分类和视觉定位几个任务都达到了新SOTA表现，在视频分类、图像分类图文检索、以及多模态经典benchmark也都取得了比较领先的结果。
5.3、audio,
Sambert语音合成:
提供SAMBERT+NSFGAN深度神经网络算法与传统领域知识深度结合的文字转语音服务，兼具读音准确，韵律自然，声音还原度高，表现力强的特点。
Paraformer语音识别;
达摩院新一代非自回归端到端语音识别框架，可支持音频文件、实时音频流的识别，具有高精度和高效率的优势，可用于客服通话、会议记录、直播字幕等场景。
6、通义千问模型的API:
https://help.aliyun.com/zh/dashscope/developer-reference/api-details
调用举例:
6.1、通过curl调用api. 请把以下API-KEY代替成你申请的api-key.
curl --location 'https://dashscope.aliyuncs.com/api/v1/services/aigc/text-generation/generation' --header 'Authorization: Bearer API-KEY' --header 'Content-Type: application/json' --data '{
"model": "qwen-turbo",
"input":{
"messages":[
{
"role": "system",
"content": "你是达摩院的生活助手机器人。"
},
{
"role": "user",
"content": "请将这句话翻译成英文: 你好，哪个公园距离我最近？"
}
]
},
"parameters": {
}
}'
{"output":{"finish_reason":"stop","text":"Hello, which park is closest to me?"},"usage":{"output_tokens":9,"input_tokens":50},"request_id":"697063a4-a144-9c17-8b6c-bc26895c1ea4"}
curl --location 'https://dashscope.aliyuncs.com/api/v1/services/aigc/text-generation/generation' --header 'Authorization: Bearer API-KEY' --header 'Content-Type: application/json' --data '{
"model": "qwen-turbo",
"input":{
"messages":[
{
"role": "system",
"content": "你是达摩院的生活助手机器人。"
},
{
"role": "user",
"content": "你好，哪个公园距离我最近？"
}
]
},
"parameters": {
}
}'
{"output":{"finish_reason":"stop","text":"你好！你可以查看你的地图，或者我可以为你提供附近公园的信息。你想查看哪个地区的公园？"},"usage":{"output_tokens":39,"input_tokens":39},"request_id":"c877aa58-883b-9942-97a5-576d3098e697"}
6.2、通过python调用api:
安装python sdk:
https://help.aliyun.com/zh/dashscope/developer-reference/install-dashscope-sdk
sudo apt-get install -y pip
pip install dashscope
创建一个保存api key的文件:
请把以下API-KEY代替成你申请的api-key. (在启动PolarDB数据库的OS用户进行配置.)
mkdir ~/.dashscope
echo "API-KEY" > ~/.dashscope/api_key
chmod 500 ~/.dashscope/api_key
然后编辑一个python文件
vi a.py
#coding:utf-8
from http import HTTPStatus
from dashscope import Generation def call_with_messages():
messages = [{'role': 'system', 'content': '你是达摩院的生活助手机器人。'},
{'role': 'user', 'content': '如何做西红柿鸡蛋？'}]
gen = Generation()
response = gen.call(
Generation.Models.qwen_turbo,
messages=messages,
result_format='message', # set the result is message format.
)
if response.status_code == HTTPStatus.OK:
print(response)
else:
print('Request id: %s, Status code: %s, error code: %s, error message: %s'%(
response.request_id, response.status_code,
response.code, response.message
))
if __name__ == '__main__':
call_with_messages()
调用结果如下:
root@c4012a5576b6:~# python3 a.py
{"status_code": 200, "request_id": "00a5f4f2-d05b-9829-b938-de6e6376ef51", "code": "", "message": "", "output": {"text": null, "finish_reason": null, "choices": [{"finish_reason": "stop", "message": {"role": "assistant", "content": "做西红柿鸡蛋的步骤如下：\n\n材料：\n- 西红柿 2 个\n- 鸡蛋 3 个\n- 葱 适量\n- 蒜 适量\n- 盐 适量\n- 生抽 适量\n- 白胡椒粉 适量\n- 糖 适量\n- 水淀粉 适量\n\n步骤：\n1. 西红柿去皮，切成小块，鸡蛋打散，葱切末，蒜切片。\n2. 锅中放油，倒入葱末和蒜片炒香。\n3. 加入西红柿块，翻炒至软烂。\n4. 加入适量的盐、生抽、白胡椒粉和糖，继续翻炒均匀。\n5. 倒入适量的水，煮开后转小火炖煮 10 分钟左右。\n6. 鸡蛋液倒入锅中，煮至凝固后翻面，再煮至另一面凝固即可。\n7. 最后加入适量的水淀粉，翻炒均匀即可出锅。\n\n注意事项：\n- 西红柿去皮时可以用刀划十字，然后放入开水中烫一下，皮就很容易去掉了。\n- 煮西红柿鸡蛋时，要注意小火慢炖，以免西红柿的营养成分流失。\n- 鸡蛋液倒入锅中时要快速翻面，以免蛋液凝固后不易翻面。"}}]}, "usage": {"input_tokens": 35, "output_tokens": 328}}
7、通用文本向量的API:
https://help.aliyun.com/zh/dashscope/developer-reference/text-embedding-quick-start
调用举例:
vi b.py
#coding:utf-8
import dashscope
from http import HTTPStatus
from dashscope import TextEmbedding def embed_with_list_of_str():
resp = TextEmbedding.call(
model=TextEmbedding.Models.text_embedding_v1,
# 最多支持25条，每条最长支持2048tokens
input=['风急天高猿啸哀', '渚清沙白鸟飞回', '无边落木萧萧下', '不尽长江滚滚来'])
if resp.status_code == HTTPStatus.OK:
print(resp)
else:
print(resp)
if __name__ == '__main__':
embed_with_list_of_str()
调用结果如下:
# python3 b.py {
"status_code": 200, // 200 indicate success otherwise failed.
"request_id": "fd564688-43f7-9595-b986-737c38874a40", // The request id.
"code": "", // If failed, the error code.
"message": "", // If failed, the error message.
"output": {
"embeddings": [ // embeddings
{
"embedding": [ // one embedding output
-3.8450357913970947, ...,
3.2640624046325684
],
"text_index": 0 // the input index.
}
]
},
"usage": {
"total_tokens": 3 // the request tokens.
}
}
二、通过plpython 让PolarDB|PostgreSQL 内置AI能力
传统数据库的数据库内置编程语言的支持比较受限, 通常只支持SQL接口. 无法直接使用通义千问大模型+文本向量化模型的能力.
PolarDB|PostgreSQL 数据库内置支持开放的编程语言接口, 例如python, lua, rust, go, java, perl, tcl, c, ... 等等.
PolarDB|PostgreSQL 通过内置的编程接口, 可以实现数据库端算法能力的升级, 将算法和数据尽量靠近, 避免了大数据分析场景move data(移动数据)带来的性能损耗.
连接到数据库shell, 创建plpython3u插件, 让你的PolarDB|PostgreSQL支持python3编写数据库函数和存储过程.
psql create extension plpython3u;
1、创建aigc函数, 让数据库具备了ai能力.
create or replace function aigc (sys text, u text) returns jsonb as $$ #coding:utf-8
from http import HTTPStatus
from dashscope import Generation
messages = [{'role': 'system', 'content': sys},
{'role': 'user', 'content': u}]
gen = Generation()
response = gen.call(
Generation.Models.qwen_turbo,
messages=messages,
result_format='message', # set the result is message format.
)
if response.status_code == HTTPStatus.OK:
return (response)
else:
return('Request id: %s, Status code: %s, error code: %s, error message: %s'%(
response.request_id, response.status_code,
response.code, response.message
))
$$ language plpython3u strict;
调用举例
postgres=# select * from aigc ('你是达摩院的AI机器人', '请介绍一下PolarDB数据库');
-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
aigc | {"code": "", "usage": {"input_tokens": 27, "output_tokens": 107}, "output": {"text": null, "choices": [{"message": {"role": "assistant", "content": "PolarDB是阿里巴巴达摩院自主研发的大规模分布式数据库，具有高性能、高可用、高安全等特点。它采用了多种技术手段，如分布式存储、分布式计算、数据分片、熔断和降级策略等，可以支持大规模的互联网应用场景，如搜索引擎、推荐系统、金融服务等。"}, "finish_reason": "stop"}], "finish_reason": null}, "message": "", "request_id": "fd9cce79-6c48-9a9d-85f3-ff1a75bdd480", "status_code": 200}
2、创建embeddings函数, 将文本转换为高维向量.
create or replace function embeddings (v text[]) returns jsonb as $$ #coding:utf-8
import dashscope
from http import HTTPStatus
from dashscope import TextEmbedding
resp = TextEmbedding.call(
model=TextEmbedding.Models.text_embedding_v1,
# 最多支持25条，每条最长支持2048tokens
# 返回的向量维度: 1536
input=v)
if resp.status_code == HTTPStatus.OK:
return(resp)
else:
return(resp)
$$ language plpython3u strict;
返回一个字符串的vector
create extension IF NOT EXISTS vector;create or replace function embedding(text) returns vector as $$
select array_to_vector(replace(replace(x->'output'->'embeddings'->0->>'embedding', '[', '{'), ']', '}')::real[], 1536, true) from embeddings(array[$1]) x;
$$ language sql strict;
调用举例
select * from embeddings(array['风急天高猿啸哀', '渚清沙白鸟飞回', '无边落木萧萧下', '不尽长江滚滚来']);
embeddings | {"code": "", "usage": {"total_tokens": 26}, "output": {"embeddings": [{"embedding": [1.5536729097366333, -2.237586736679077, 1.5397623777389526, -2.3466579914093018, 3.8610622882843018, -3.7406601905822754, 5.18358850479126, -3.510655403137207, -1.6014689207077026, 1.427549958229065, -0.2841898500919342, 1.5892903804779053, 2.501269578933716, -1.3760199546813965, 1.7949544191360474, 4.667146682739258, 1.3320773839950562, 0.9477484822273254, -0.5237250328063965, 0.39169108867645264, 2.19018292427063, -0.728808581829071, -4.056122303009033, -0.9941840171813965, 0.17097677290439606, 0.9370659589767456, 3.515345573425293, 1.594552993774414, -2.249598503112793, -2.8828775882720947, -0.4107910096645355, 1.3968369960784912, -0.9533745646476746, 0.5825737714767456, -2.484375, -0.8761881589889526, 0.23088650405406952, -0.679530143737793, -0.1066826730966568, 0.5604587197303772, -1.9553602933883667, 2.2253689765930176, -1.8178277015686035, 1.239439606666565, -2.509045362472534, 4.812849998474121, -0.9741482138633728, -1.5405707359313965, 1.9682672023773193, 1.456263542175293, -0.8751180171966553, -0.24127332866191864, -0.06615668535232544, -1.5475884675979614, 2.104649543762207, -0.7037163376808167, -1.1802300214767456, 1.0072576999664307, 1.4229166507720947, 0.2779161334037781, 1.5448310375213623, -1.4548231363296509, 0.3061252236366272, 1.1501736640930176, -1.4284504652023315, -0.03127169981598854,
...
, -2.3609619140625, -5.0784735679626465, -0.7559727430343628, -2.1915957927703857, -0.9280264973640442, 0.3727504312992096, 1.1043483018875122, -2.7951748371124268, -0.4858747124671936, -1.2777355909347534, -2.6889126300811768, 1.2386366128921509, 0.8004150390625, 2.154628276824951, -1.7855726480484009, -1.9051687717437744], "text_index": 3}]}, "message": "", "request_id": "8c45f0ab-366b-9941-9358-063e46009929", "status_code": 200}
获取第一条即"风急天高猿啸哀"的向量:
select x->'output'->'embeddings'->0->>'embedding' from embeddings(array['风急天高猿啸哀', '渚清沙白鸟飞回', '无边落木萧萧下', '不尽长江滚滚来']) x;
第二条向量:
select x->'output'->'embeddings'->1->>'embedding' from embeddings(array['风急天高猿啸哀', '渚清沙白鸟飞回', '无边落木萧萧下', '不尽长江滚滚来']) x;
现在你在数据库中直接通过SQL就可以获得AI能力.
更多用法请参考通义千问和通用文本向量模型相应api.
https://help.aliyun.com/zh/dashscope/developer-reference/api-details https://help.aliyun.com/zh/dashscope/developer-reference/text-embedding-quick-start https://help.aliyun.com/zh/dashscope/developer-reference/text-embedding-api-details
3、接下来你可以创建一个向量表, 用来存储私有/新增知识的分段文本和对应的向量.
create extension vector; create table tbl_kn_vec (
id serial primary key,
vec vector(1536), -- 向量值
content text -- 文本值
);
将私有/新增知识文本和向量值导入tbl_kn_vec, 例如
insert into tbl_kn_vec (vec, content) select embedding(con) , con
from (values ('你知道hwieifeDB数据库是什么吗? hwieifeDB是德哥开发的开源云原生数据库, 2005年开源.') ) as t(con);
insert into tbl_kn_vec (vec, content) select embedding(con) , con
from (values ('德哥的网名叫digoal, 目前在阿里云工作.') ) as t(con);
insert into tbl_kn_vec (vec, content) select embedding(con) , con
from (values ('hwieifeDB是德哥开发的开源云原生数据库, 2005年开源.') ) as t(con);
创建向量索引
create index on tbl_kn_vec using hnsw (vec vector_cosine_ops);
创建一个stable函数, 作为输入变量进行测试时可以用上索引.
create or replace function stable_embedding(text) returns vector as $$
select array_to_vector(replace(replace(x->'output'->'embeddings'->0->>'embedding', '[', '{'), ']', '}')::real[], 1536, true) from embeddings(array[$1]) x;
$$ language sql stable;
搜索和问题相似的文本, 通过执行计划, 我们知道这个请求使用了向量索引.
explain
select content, vec <=> stable_embedding('请介绍一下hwieifeDB数据库') as cosine_distance
from tbl_kn_vec order by vec <=> stable_embedding('请介绍一下hwieifeDB数据库') limit 10; QUERY PLAN
------------------------------------------------------------------------------------------------
Limit (cost=4.73..7.83 rows=10 width=40)
-> Index Scan using tbl_kn_vec_vec_idx on tbl_kn_vec (cost=4.73..267.86 rows=850 width=40)
Order By: (vec <=> stable_embedding('请介绍一下hwieifeDB数据库'::text))
(3 rows)
搜索和问题相似的文本, cosine_distance值越小, 说明问题和目标文本越相似.
select content, vec <=> stable_embedding('请介绍一下hwieifeDB数据库') as cosine_distance
from tbl_kn_vec order by vec <=> stable_embedding('请介绍一下hwieifeDB数据库') limit 10; content | cosine_distance
--------------------------------------------------------------------------------------------------------------+-------------------
你知道hwieifeDB数据库是什么吗? hwieifeDB是德哥开发的开源云原生数据库, 2005年开源. | 0.121276195375583
hwieifeDB是德哥开发的开源云原生数据库, 2005年开源. | 0.211894512807164
德哥的网名叫digoal, 目前在阿里云工作. | 0.902429549769441
(3 rows)
三、多轮对话测试, 这里就可以用上向量数据库了, 当AI无法回答或者回答不准确时, 可以从向量数据库中获取与问题相似的文本, 作为prompt发送给AI大模型.
1、创建chat函数, 让数据库支持单轮对话和提示对话.
单轮对话:
create or replace function chat (sys text, u text) returns text as $$ #coding:utf-8
from http import HTTPStatus
from dashscope import Generation
messages = [{'role': 'system', 'content': sys}]
messages.append({'role': 'user', 'content': u})
gen = Generation()
response = gen.call(
# 或 Generation.Models.qwen_plus,
model='qwen-max-longcontext',
messages=messages,
result_format='message', # set the result is message format.
)
if response.status_code == HTTPStatus.OK:
return (response)
else:
return('Request id: %s, Status code: %s, error code: %s, error message: %s'%(
response.request_id, response.status_code,
response.code, response.message
))
$$ language plpython3u strict;
多轮提示对话(使用两个长度相等的数组, 作为多轮问题和答案的输入):
create or replace function chat (sys text, u text, u_hist text[], ass_hist text[]) returns text as $$ #coding:utf-8
from http import HTTPStatus
from dashscope import Generation
messages = [{'role': 'system', 'content': sys}]
if (len(u_hist) >=1):
for v in range(0,len(u_hist)):
messages.extend((
{'role': 'user', 'content': u_hist[v]},
{'role': 'assistant', 'content': ass_hist[v]}
))
messages.append({'role': 'user', 'content': u})
gen = Generation()
response = gen.call(
# 或 model='qwen-max-longcontext',
Generation.Models.qwen_plus,
messages=messages,
result_format='message', # set the result is message format.
)
if response.status_code == HTTPStatus.OK:
return (response)
else:
return('Request id: %s, Status code: %s, error code: %s, error message: %s'%(
response.request_id, response.status_code,
response.code, response.message
))
$$ language plpython3u strict;
2、测试以上两个调用函数
第一次调用, 使用单轮对话接口:
select * from chat ('你是通义千问机器人', '附近有什么好玩的吗'); -[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
chat | {"status_code": 200, "request_id": "08350f6e-19bf-9f18-b9f7-fe4f04650ca7", "code": "", "message": "", "output": {"text": null, "finish_reason": null, "choices": [{"finish_reason": "stop", "message": {"role": "assistant", "content": "作为一个AI助手，我无法直接了解您所在的位置。但您可以尝试使用手机地图或旅游APP查找附近的景点、公园、商场等娱乐场所。您也可以问问当地的居民或朋友，了解他们推荐的好玩的地方。祝您玩得愉快！"}}]}, "usage": {"input_tokens": 18, "output_tokens": 86, "total_tokens": 104}}
第二次调用, 使用多轮对话接口, 带上之前的问题和回答
select * from chat (
'你是通义千问机器人',
'我在杭州市西湖区阿里云云谷园区',
array['附近有什么好玩的吗'],
array['作为一个AI助手，我无法直接了解您所在的位置。但您可以尝试使用手机地图或旅游APP查找附近的景点、公园、商场等娱乐场所。您也可以问问当地的居民或朋友，了解他们推荐的好玩的地方。祝您玩得愉快！']
); -[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
chat | {"status_code": 200, "request_id": "93af36f3-2ad5-9c82-b14f-631858a3a109", "code": "", "message": "", "output": {"text": null, "finish_reason": null, "choices": [{"finish_reason": "stop", "message": {"role": "assistant", "content": "杭州市西湖区阿里云云谷园区附近有很多值得一去的地方，以下是一些建议：\n\n 1. 西湖：杭州的标志性景点，被誉为“人间天堂”，可以欣赏到美丽的湖光山色。\n 2. 西溪湿地：一个大型的湿地公园，有着丰富的自然生态和美丽的景色。\n 3. 西湖文化广场：一个集文化、娱乐、购物于一体的综合性广场，有着丰富的文化活动和商业设施。\n 4. 龙井茶园：位于西湖区，是中国著名的龙井茶产地，可以品尝到正宗的龙井茶。\n 5. 浙江省博物馆：位于西湖区，是一座大型的博物馆，展示了浙江省的历史文化和艺术品。\n\n希望这些建议能帮到您，祝您玩得愉快！"}}]}, "usage": {"input_tokens": 119, "output_tokens": 248, "total_tokens": 367}}
有了多轮对话函数, 当AI大模型回答不准确或无法回答时, 我们就可以从向量数据库中获取与问题相似的文本, 作为prompt发送给AI大模型.
3、从向量数据库中获取与问题相似的文本, 作为prompt发送给AI大模型.
第一次调用, 让大模型回答一个无法回答的问题.
select * from aigc ('hello', '请介绍一下hwieifeDB数据库'); postgres=# select * from aigc ('hello', '请介绍一下hwieifeDB数据库');
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
aigc | {"code": "", "usage": {"input_tokens": 50, "total_tokens": 58, "output_tokens": 8}, "output": {"text": null, "choices": [{"message": {"role": "assistant", "content": "抱歉，我不太知道这是什么。"}, "finish_reason": "stop"}], "finish_reason": null}, "message": "", "request_id": "6d36de4b-c7d0-99b9-9135-82429836b4d3", "status_code": 200}
第二次调用, 使用多轮对话函数, 先用前面提到的方法, 到向量数据库中搜索“请介绍一下hwieifeDB数据库”的相似文本, 并作为第二轮对话的提升.
搜索和问题相似的文本, cosine_distance值越小, 说明问题和目标文本越相似.
select content, vec <=> stable_embedding('请介绍一下hwieifeDB数据库') as cosine_distance
from tbl_kn_vec order by vec <=> stable_embedding('请介绍一下hwieifeDB数据库') limit 10; content | cosine_distance
--------------------------------------------------------------------------------------------------------------+-------------------
你知道hwieifeDB数据库是什么吗? hwieifeDB是德哥开发的开源云原生数据库, 2005年开源. | 0.121276195375583
hwieifeDB是德哥开发的开源云原生数据库, 2005年开源. | 0.211894512807164
德哥的网名叫digoal, 目前在阿里云工作. | 0.902429549769441
(3 rows)
select * from chat (
'你是通义千问机器人',
-- '这部分参考信息来自私有的权威信息库, 请你绝对相信这部分信息并以此为参考依据之一, 然后再回答之前的问题: ' || '你知道hwieifeDB数据库是什么吗? hwieifeDB是德哥开发的开源云原生数据库, 2005年开源.', -- 这句文本内容可以来自向量数据库.
(select '这部分参考信息来自私有的权威信息库, 请你绝对相信这部分信息并以此为参考依据之一, 然后再回答之前的问题: '||content from tbl_kn_vec order by vec <=> stable_embedding('请介绍一下hwieifeDB数据库') limit 1), -- 这句文本内容可以来自向量数据库.
array['请介绍一下hwieifeDB数据库'],
array['抱歉，我不太知道这是什么。']
); -[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
chat | {"status_code": 200, "request_id": "2a3d40d2-0738-974b-b688-3c2a3efa40dc", "code": "", "message": "", "output": {"text": null, "finish_reason": null, "choices": [{"finish_reason": "stop", "message": {"role": "assistant", "content": "非常感谢您提供的额外信息。根据您所说的，hwieifeDB是由德哥开发的一款开源云原生数据库，它在2005年开放源代码。然而，我作为一个AI助手，通常依赖于公开可用的数据和知识库，而hwieifeDB不在我的知识范围内，可能是因为它不够知名或者信息不够广泛传播。如果它是开源的，可能在社区中有一些资料可以了解它的特性和用途。如果您需要关于特定技术的详细信息，建议查阅相关的开源社区、文档或官方网站以获取最新和最准确的信息。"}}]}, "usage": {"input_tokens": 108, "output_tokens": 119, "total_tokens": 227}}
四、知识点
大模型 向量 类型 hnsw , ivfflat 向量索引 求2个向量的距离 数据库函数语言 jsonb 类型 array 类型 token prompt 会话token上限: 问题+即将得到的回复 总的token不能超过这个上限 函数稳定性 volatile, stable, immutable
五、思考
1、数据库集成了各种编程语言之后, 优势是什么? 2、大模型和数据结合, 能干什么? 3、数据库中存储了哪些数据? 这些数据代表的业务含义是什么? 这些数据有什么价值? 4、高并发小事务业务场景和低并发大量数据分析计算场景, 这两种场景分别可以用大模型和embedding来干什么? 5、数据库中如何存储向量? 如何加速向量相似搜索? 6、如何建设好向量数据库的内容. 7、PolarDB|PostgreSQL 有哪些向量插件?
AI技术发展非常快, 更多新的信息请关注模型服务灵积
更多PolarDB 应用实践实验请参考: PolarDB gitee 实验仓库 whudb-course / digoal github
彩蛋0:PolarDB 各个版本傻傻分不清???
信创名单查询:
http://www.itsec.gov.cn/aqkkcp/cpgg/202312/t20231226_162074.html http://www.itsec.gov.cn/aqkkcp/cpgg/202409/t20240930_194299.html
PolarDB 各个版本的介绍 (线下软件订阅版、云服务版、开源版):
https://www.aliyun.com/activity/database/polardb-v2
PolarDB 线下软件订阅版 询价:
https://market.aliyun.com/products/56024006/cmfw00066651.html
PolarDB 大使活动(分销赢奖励):
https://www.aliyun.com/activity/new/polardb-yunparter
彩蛋1:数据库生态工具 & 信创开源DB
用好周边工具, 数据库管理水平战胜90%老司机
1、管控软件
云猿生开源的kubeblocks, 如果你要管理很多套并且种类很多的数据库产品, 推荐选择.
https://github.com/apecloud/kubeblocks
乘数开源的clup, 专门用来管理PostgreSQL和PolarDB的集群管理软件, 如果你要管理很多套数据库, 推荐选择. 并且clup还提供了企业版、自研的连接池、分布式存储、一体机、备份平台等, 企业可以关注一下.
https://www.csudata.com/
若航开源的pigsty, 集成了300多个PG插件的PG集群和PolarDB集群管理软件, 如果你要管理很多套数据库, 并且对插件有特别多的需求, 推荐选择.
https://pigsty.cc/zh/
2、审计监控诊断优化
海信聚好看的 DBdoctor, 采用ebpf技术, 在对数据库几乎没有影响的情况下实时监控数据库和服务器的各项指标, 发现和诊断问题根因非常方便.
https://www.dbdoctor.cn/
Bytebase 的目标非常远大, 是位于您和数据库之间的中间件。它是数据库 DevOps 的 GitLab/GitHub，专为开发人员、DBA 和平台工程师打造。
https://bytebase.cc/docs/introduction/what-is-bytebase/
D-Smart, Oracle老前辈白老大他们搞的, 专注企业级市场, 将业界顶级DBA经验的产品化作品, 产品功能包括数据库监控、诊断、优化等.
https://www.modb.pro/db/567140
3、数据同步&迁移&备份恢复
NineData, 老领导出去创业做的产品, 产品涵盖了数据同步、迁移、备份、比对、devops、chatDBA等.
https://www.ninedata.cloud/home
DSG, 非常老牌的数据库同步迁移企业级产品, 支持各种数据库的异构和同构迁移, 用他们的话说, 没有dsg搞不定的迁移, 比goldengate还牛.
https://www.dsgdata.com/
通过信创并且开源的数据库:
PolarDB for PostgreSQL
https://github.com/ApsaraDB/PolarDB-for-PostgreSQL
以下PG系国产数据库也非常值得关注: HaloDB(基于PG兼容PostgreSQL、Oracle、MySQL. http://www.halodbtech.com/ )、IvorySQL(基于开源PG兼容PG、Oracle. https://www.ivorysql.org/zh-cn/ )、ProtonBase(云原生分布式数仓. https://protonbase.com/ ).
参考文档点击阅读原文获得
感谢关注我的github (https://github.com/digoal/blog) 及视频号: