阿里云PostgreSQL_案例介绍1 | 学习笔记

开发者学堂课程【PostgreSQL云上开发实践:阿里云PostgreSQL_案例介绍1】学习笔记，与课程紧密联系，让用户快速学习知识。

课程地址：https://developer.aliyun.com/learning/course/528/detail/7100

阿里云PostgreSQL_案例介绍1

内容介绍：

一、OLTP Benchmark（56 Core ECS, PG 10）

二、OLAP Benchmark（56 Core ECS, PG 10）

三、HDB PG OLAP Benchmark（1 Host）

四、HDB PG OLAP Benchmark（50 Host）

五、Case1（标签-多值类型）

六、Case1（标签-多值类型）

七、案例—架构设计、代码、实操手册

一、OLTP Benchmark（56 Core ECS, PG 10）

二、OLAP Benchmark（56 Core ECS, PG 10）

OLAP Benchmark(56 Core ECS,PG 10)

测试详情:

https://github.com/digoal/blog/blob/master/201711/readme.md

https://github.com/digoal/blog/blob/master/201801/20180102_04.md https://github.com/digoal/blog/blob/master/201802/20180204_01.md https://github.com/digoal/blog/blob/master/201802/20180204_03.md

https://github.com/digoal/blog/blob/master/201802/20180201_01.md https://github.com/digoal/blog/blob/master/201802/20180202_02.md https://github.com/digoal/blog/blob/master/201802/20180210_01.md

手机行业通用场景:

（1）APP商店(实时 UV 、TOP-K 、FEED 数据透视分析、用户画像。)（2）支付平台(可靠性)

（3）对账平台(游标、数据处理逻辑复杂、FUNCTION )

（4）虚拟货币平台

（5）认证平台

（6）好友关系平台(图式搜索、好友推荐、GIS )

（7）聊天系统(文本控掘)

（8）OA 系统

（9）分析库(大数据、实时、离线计算、用户画像)

（10）游戏库(角色类、棋牌类(炸金花短平快、数据库写入、更新量大))

三、HDB PG OLAP Benchmark（1 Host）

四、HDB PG OLAP Benchmark（50 Host）

五、Case1（标签-多值类型）

·多值类型与GIN索引应用

——Array, Hstore, JSON

案例：（难以结构化）

· XX单车、新零售-XX小店。

· 人群标签  -{标签：结束时间，...}

· 透视人群、求交并差   -  包月人群

-  促销人群

· 痛点   - 无法结构化

· RDS for PG

-  多值类型解决结构化难点问题

 -  arr@>array[?,?,...]

 -  arr&&array[?,?,...]

 -  not arr@>array[?,?,...]

· 搜索加速

-  GIN倒排，（标签元素倒排索引）

-  udf1（标签s）->[1维数组]  ,  udf2（标签s）->[2维数组]

postgres=# select * from tbl where get_label(info)@>array['a'] and get_weight(info,'a')::float8 >20;

(1 row)

postgres=# select * from tb1;

(3 rows)

· XX生物科技

—几万列浮点

—几十万行

—100GB左右

—计算任意组合（物种、序列分段）的欧式空间距离

—采用 float8[]存储几万列浮点，UDF，计算XX距离。

postgres=# select * from compute_eu_dist(array[1,2,3,4,5])

(10rows)

Time:100.582 ms

案例-架构设计、代码、实操手册

· 数组类型和操作符、函数、索引

https://www.postgresql.org/docs/10/static/arrays.html

https://www.postgresql.org/docs/10/static/functions-array.html

· 多重含义数组 UDF 检索

https://github.com/digoal/blog/blob/master/201801/20180124_02.md

· 生物科技

https://github.com/digoal/blog/blob/master/201712/20171227_01.md

六、Case2（搜索-GIN）

· 搜索需求分类

—全文检索

—模糊搜索、前缀、后缀、前后模糊

—相似搜索

—任意字段组合搜索

案例：（难以加速，难以同步一致性）

· XXX域名服务

—模糊查询、相似查询

—10亿级记录模糊搜索

· XXX某 CRM 系统

—任意字段全文检索、模糊查询

—词汇( phase )查询

—10亿级记录多字段任意组合搜索

· 新零售-营销、分销链路

—多值类型检索

—10亿级记录数组&|搜索

· QA (医疗行业、搜索、知识库)

—相似问题、相似地址、相似病例。。。

·  GA

—车牌模糊搜索

痛点：

· 全文检索无法支持“模糊查询”

—（例如域名并非分词）

· 数据库与搜索引擎一致性维护麻烦

云产品方案、效果：

· RDS PG

— gin 倒排索引，

· 支持多值类型、多字段任意条件检索

— bloom 索引

· 支持多字段任意组合等值条件过滤

— 多索引 bitmap scan

· 多个索引合并扫描

— pg_trgm 支持

· 模糊查询、相似查询、正则查询

— zhparser 中文分词插件支持中文分词

1. GIN 复合（倒排+聚集）索引

2. 分词索引

3. bloom 复合索引

4. pg_trgm,fuzzymatch

全文检索技术

全文检索技术—词距离条件

select*from tbl where ts @@'速度<距离值>激情'::tsquery;

如：select* from tbl where ts @@'速度<1>激情'::tsquery;

‘优质服务’:9'公司:8'出租汽车:7'创业:6'创新：2'北京':5坚持':3'小花':10 '激情’:1 ‘絮’:11 '速度':4 ‘激情’:3‘电影’:1'破':5‘票房’:4‘速度':2

带距离

postgres=# select*from ts_test where ts @@'速度<1>激情'::tsquery;

(1 row)

不带距离

postgres=# select*from ts_test where ts @@ '速度 & 激情’'::tsquery limit 5;

(5 rows)

按距离范围搜索

自定义 UDF , RANGE 相交操作判断。

全文检索技术—内置 ranking

UPDATE tt SET ti =

setweight(to_tsvector(coalesce(title, ’ ’) ），‘A’)

setweight(to_tsvector(coalesce(keyword,’ ’) ), ‘B’)

setweight(to_tsvector(coalesce(abstract,’ ’) )，‘C’)

setweight(to_tsvector(coalesce(body, ’ ’) )，’D');

LIMIT 10;

全文检索技术—自定义 ranking

1.店铺标签表

create table tb1 (

Shop_ id int8 primary key,   --店铺ID

tegs tex[],                  --数组，标签1，标签2，......

scores flost8[]              --数组，评分1，评分2，......

）；

create index idx_tbl_1 on tb1 using gin(tags);

国民_足浴，国民_餐饮，娱乐_KTV

0.99,0.1,0.45

2.标签权值表：

create table tb1_ weight(

tagid int primary key,      --标签ID

tagname name,           --标签名

besc tent,                 --标签描述

weight flost8               --标签权值

);

(3 rows)

前缀模糊查询背景技术

· create index idx on tbl (col text_patten ops);

· select *from tbl where {col ~'^前缀’ | like '前缀%'};

· 自动

- postgres=# explain select * from pre where c1 like'你%';

-                QUERY PLAN

- Index Scan using idx_pre on pre(cost=0.29..2.71 rows=1 width=21)

- IndexCond:((c1~>=~'你'::text)AND(c1~<~'佡'::text))

- Filter:(c1~~'你%'::text)

(3 rows)

- postgres=# select chr(ascii(‘你’)+1);

- chr

-  ------

- 佡

- (1 row)

后缀模糊查询背景技术

· postgres=# create indexidx_pre1 on pre（reverse(c1)text_ pattern_ops);

· CREATE INDEX

- postgres=# select reverse(‘你好abcd ’);

- reverse

- --------

- dcba好你

- (1 row)

- postgres=# explain select * from pre where reverse(c1) like reverse('结尾')||'%';

-  QUERY PLAN

- Index Scan using idx_ pre1 on pre(cost=029..45.93rows=50width=21)

- Index Cond:((reverse(c1)~>=~'尾结::text)AND(reverse(c1)~<~'尾绔'::text))

- Filter:(reverse(c1)~~'尾结%’::text)

- (3 rows)

前后模糊查询背景技术

· postgres=# create index idx_pre2 on pre using gin (c1 gin_trgm_ops);

· CREATE INDEX

- postgres=# select show_trgm('abcde');

相似查询

· create or replace function get_res(

· text,--要按相似搜的文本

· int8,--限制返回多少条

· float4 default 0.3，--相似度阙值，低于这个值不再搜搜

· float4 default 0.1  --相似度递减步长，直至阙值

· )returns setof record as $$

·  declare

·     lim float4:= 1;

·  begin

· -- 判定

· if not ($3<= 1 and $3 >0) then

· raise notice '$3 must >0 and <=1';

· return;

· end if;

· loop

· --设置相似度阈值

· perform set_limit(lim);

·

· return query select similarity(info,$1)as sml,* from tbl where info % $1 order by sml desc limit $2；

· -- 如果有，则退出 loop

·  if found then

· return;

· end if;

·

· --否则继续，降低阈值

· --当阈值小于0.3时，不再降阈值搜索，认为没有相似。

任意字段组合条件搜索

· 多个独立的索引的BITMAP SCAN(或单个GIN多字段复合索引(using gin(c1，c2,...)))

- select * from table where col1 =? and col2 =?;

- 合并扫描后，访问的数据块非常少，速度很快。

全文检索 + RDS PG sharding

dblink 异步调用并行

https://github.com/digoal/blog/blob/master/201802/20180202_01.md https://github.com/digoal/blog/blob/master/201802/20180205_03.md

七、案例—架构设计、代码、实操手册

· 全文检索

https://github.com/digoal/blog/blob/master/201603/20160310_01.md https://github.com/digoal/blog/blob/master/201712/20171206_01.md https://github.com/digoal/blog/blob/master/201712/20171205_02.md https://www.postgresql.org/docs/10/static/textsearch.html

https://github.com/digoal/blog/blob/master/201801/20180123_01.md(含距离搜索) https://github.com/digoal/bloa/blob/master/201612/20161231_ 01.md

· 模糊、正则查询

https://www.postgresql.org/docs/10/static/pgtrgm.html

· 相似查询

https://www.postgresgl.org/docs/10/static/pgtrgm.html

算法:contrib/pg_trgm/trgm_regexp.c

https://github.com/digoal/blog/blob/master/201802/20180202_ 01.md https://github.com/digoal/blog/blob/master/201802/20180205_ 03.md

· 多字段任意组合查询

https://www.postgresgl.org/docs/10/static/indexes-bitmap-scans.htm https://www.postaresal.org/docs/10/static/bloom.html https://www.postaresgl.org/docs/10/static/btree-gin.html

· sharding

https://github.com/digoal/blog/blob/master/201608/20160824_02.md https://github.com/digoal/blog/blob/master/201110/20111025_01.md httos://github.com/digoal/blog/blob/master/201512/20151220_02.md https://github.com/digoal/blog/blob/master/201512/20151220_03.md https://github.com/digoal/blog/blob/master/201512/20151220_04.md