备案控制台
开发者社区 PolarDB开源 PolarDB PostgreSQL 版 文章 正文

使用 PolarDB 开源版 smlar 插件进行高效率相似文本搜索、自助选药、相似人群圈选等业务

2022-12-29 349
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB MySQL 版，Serverless 5000PCU 100GB
推荐场景：
PolarDB for MySQL 多主集群体验
简介： PolarDB 的云原生存算分离架构, 具备低廉的数据存储、高效扩展弹性、高速多机并行计算能力、高速数据搜索和处理; PolarDB与计算算法结合, 将实现双剑合璧, 推动业务数据的价值产出, 将数据变成生产力. 本文将介绍使用 PolarDB 开源版 smlar 插件进行高效率相似文本搜索、自助选药、相似人群圈选等业务

背景

PolarDB 的云原生存算分离架构, 具备低廉的数据存储、高效扩展弹性、高速多机并行计算能力、高速数据搜索和处理; PolarDB与计算算法结合, 将实现双剑合璧, 推动业务数据的价值产出, 将数据变成生产力.

本文将介绍使用 PolarDB 开源版 smlar 插件进行高效率相似文本搜索、自助选药、相似人群圈选等业务

测试环境为macOS+docker, PolarDB部署请参考下文:

场景

1、自助匹配药品, 例如用户根据病情描述, 自动匹配相关的药品. 这个属于文本相似范畴. 文本相似性:

注意有语义的情况:

  • 感冒,不发烧,咳嗽,无痰,流清涕,肌肉酸痛
  • 感冒,发烧,咳嗽,有痰,无鼻涕

将药品主治症状的文本向量化, 存储为文本数组.

根据病人描述, 将文本向量化, 在药品库中进行文本向量的相似匹配, 快速找到最匹配的药品.

2、根据特征进行人群扩选, 例如在数据库中存储了每个用户的特征(使用数组表示)

根据输入的数组(画像)搜索相似人群, 即人群扩选, 业务上进行精准推送.

3、文章相似性搜索, 因为文章关键字很多, 每个关键字的权重也不一样, 不能只按命中多少关键字来决定相似性. 可以借助tfidf, 结合总文本数, 关键字在所有文本中出现的次数, 命中关键字等进行计算.

在所有文本中出现次数越多的关键字, 根据算法其权重可能越低. 具体算法可参考:

设计与算法

以上需求实际上都是多值列的相似计算, 使用smlar插件即可实现.

数据存储: 多值列(例如数组)

多值列的相似性算法: cosine, overlap, tfidf.

  switch(getSmlType())  
  {  
    case ST_TFIDF:  
      PG_RETURN_FLOAT4( TFIDFSml(sa, sb) );  
      break;  
    case ST_COSINE:  
      {  
        int       cnt;  
        double      power;  
  
        power = ((double)(sa->nelems)) * ((double)(sb->nelems));  
        cnt = numOfIntersect(sa, sb);  
  
        PG_RETURN_FLOAT4(  ((double)cnt) / sqrt( power ) );  
      }  
      break;  
    case ST_OVERLAP:  
      {  
        float4 res = (float4)numOfIntersect(sa, sb);  
  
        PG_RETURN_FLOAT4(res);  
      }  
      break;

元素去重后计算.

postgres=# set smlar.type='cosine';   
SET  
postgres=# SELECT smlar('{1,4,6}'::int[], '{5,4,6}' );    
  smlar     
----------  
 0.666667  
(1 row)  
postgres=# SELECT smlar('{1,4,6}'::int[], '{5,4,4,6}' );    
  smlar     
----------  
 0.666667  
(1 row)  
-- 2/sqrt(3*3)   
  
postgres=# set smlar.type='overlap';   
SET  
postgres=# SELECT smlar('{1,4,6}'::int[], '{5,4,4,6}' );    
 smlar   
-------  
     2  
(1 row)  
-- 2  
  
postgres=# set smlar.type='tfidf';   
SET  
  
-- 设置tfidf表, 这个表可以用采样文档统计得到, 也可以自由定义其内容  
set smlar.stattable = 'documents_body_stats';   
  
create table documents_body_stats (  -- tfidf权重表.   
  value text unique,  -- value表示的关键字出现在多少篇文档中; value is null的行表示总文档篇数;  
  ndoc int not null    
);   
  
insert into documents_body_stats values ('0', 1); -- 0 出现在了1篇文章中.   
insert into documents_body_stats values ('1', 100); -- 1 出现在了100篇文章中.  
insert into documents_body_stats values ('4', 101), ('6', 201);   
insert into documents_body_stats values ('5', 1001);   
insert into documents_body_stats values (null, 10000);   -- value is null的行表示总文档篇数;  
  
postgres=# SELECT smlar('{1,4,6}'::text[], '{5,4,4,6}' );    
  smlar     
----------  
 0.742594  
(1 row)  
  
postgres=# SELECT smlar('{1,4,6}'::text[], '{5,5,5,6,6}' );    
 smlar    
--------  
 0.4436  
(1 row)  
  
postgres=# SELECT smlar('{0,1,4,5,6}'::text[], '{0,1,5}' );    
  smlar     
----------  
 0.868165  
(1 row)  
  
postgres=# SELECT smlar('{0,1,4,5,6}'::text[], '{1,5,6}' );    
  smlar     
----------  
 0.531762  
(1 row)

加速原理

smlar 对数组支持gin和gist两个索引接口, 以gin为例, 如何快速筛选相似的记录?

例如, 输入条件的数组长度为6, 使用overlap算法, 要求相似度为4, 那么必须要有4个或4个以上元素命中的记录才符合要求.

  • 在gin索引中搜索元素1, 提取到ctid里的blockid, 每个blockid +1.
  • 在gin索引中搜索元素2, 提取到ctid里的blockid, 每个blockid +1.
  • 在gin索引中搜索元素3, 提取到ctid里的blockid, 每个blockid +1.
  • 在gin索引中搜索元素4, 提取到ctid里的blockid, 每个blockid +1.
  • 在gin索引中搜索元素5, 提取到ctid里的blockid, 每个blockid +1.
  • 在gin索引中搜索元素6, 提取到ctid里的blockid, 每个blockid +1.

在以上blockid中, 数据库只需要回表搜索大于等于4的blockid, recheck是否满足相似条件.

gin,gist支持的operator calss?

GiST/GIN support for % and && operations for:

Array Type GIN operator class GiST operator class
bit[] _bit_sml_ops
bytea[] _bytea_sml_ops _bytea_sml_ops
char[] _char_sml_ops _char_sml_ops
cidr[] _cidr_sml_ops _cidr_sml_ops
date[] _date_sml_ops _date_sml_ops
float4[] _float4_sml_ops _float4_sml_ops
float8[] _float8_sml_ops _float8_sml_ops
inet[] _inet_sml_ops _inet_sml_ops
int2[] _int2_sml_ops _int2_sml_ops
int4[] _int4_sml_ops _int4_sml_ops
int8[] _int8_sml_ops _int8_sml_ops
interval[] _interval_sml_ops _interval_sml_ops
macaddr[] _macaddr_sml_ops _macaddr_sml_ops
money[] _money_sml_ops
numeric[] _numeric_sml_ops _numeric_sml_ops
oid[] _oid_sml_ops _oid_sml_ops
text[] _text_sml_ops _text_sml_ops
time[] _time_sml_ops _time_sml_ops
timestamp[] _timestamp_sml_ops _timestamp_sml_ops
timestamptz[] _timestamptz_sml_ops _timestamptz_sml_ops
timetz[] _timetz_sml_ops _timetz_sml_ops
varbit[] _varbit_sml_ops
varchar[] _varchar_sml_ops _varchar_sml_ops

例子

1、部署smlar on PolarDB

git clone --depth 1  git://sigaev.ru/smlar.git  
  
  
cd smlar/  
  
USE_PGXS=1 make  
USE_PGXS=1 make install  
  
  
[postgres@aa25c5be9681 smlar]$ USE_PGXS=1 make installcheck  
/home/postgres/tmp_basedir_polardb_pg_1100_bld/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/home/postgres/tmp_basedir_polardb_pg_1100_bld/bin'      --dbname=contrib_regression smlar int2 int4 int8 float4 float8 money oid timestamp timestamptz time timetz date interval macaddr inet cidr text varchar char bytea bit varbit numeric int4g int8g intervalg textg int4i int8i intervali texti composite_int4 composite_text  
(using postmaster on 127.0.0.1, default port)  
============== dropping database "contrib_regression" ==============  
DROP DATABASE  
============== creating database "contrib_regression" ==============  
CREATE DATABASE  
ALTER DATABASE  
============== running regression test queries        ==============  
test smlar                        ... ok  
test int2                         ... ok  
test int4                         ... ok  
test int8                         ... ok  
test float4                       ... ok  
test float8                       ... ok  
test money                        ... ok  
test oid                          ... ok  
test timestamp                    ... ok  
test timestamptz                  ... ok  
test time                         ... ok  
test timetz                       ... ok  
test date                         ... ok  
test interval                     ... ok  
test macaddr                      ... ok  
test inet                         ... ok  
test cidr                         ... ok  
test text                         ... ok  
test varchar                      ... ok  
test char                         ... ok  
test bytea                        ... ok  
test bit                          ... ok  
test varbit                       ... ok  
test numeric                      ... ok  
test int4g                        ... ok  
test int8g                        ... ok  
test intervalg                    ... ok  
test textg                        ... ok  
test int4i                        ... ok  
test int8i                        ... ok  
test intervali                    ... ok  
test texti                        ... ok  
test composite_int4               ... ok  
test composite_text               ... ok  
  
  
===========================================================  
 All 34 tests passed.   
  
 POLARDB:  
 All 34 tests, 0 tests in ignore, 0 tests in polar ignore.   
===========================================================

2、安装插件

postgres=# create extension smlar ;  
CREATE EXTENSION

3、创建测试表, 写入测试数据

create table tbl (id int, propt int[]);  
  
create or replace function gen_arr(normal int, hot int) returns int[] as $$  
  select array(select (100000*random())::int+500 from generate_series(1,$1)) || array(select (500*random())::int from generate_series(1,$2));  
$$ language sql strict;  
  
insert into tbl select id, gen_arr(22, 10) from generate_series(1,2000000) id;  
  
postgres=# select * from tbl limit 5;  
 id |                                                                                     propt                                                                                       
----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
  1 | {1386,57573,55117,44934,83223,3444,77658,49523,85849,62549,99593,40714,53146,32510,68449,33662,45912,70227,64560,78831,86052,56387,157,490,51,484,53,176,273,240,300,277}  
  2 | {15075,100383,88390,18019,77540,37413,3368,39590,36506,43582,92236,68516,11532,25398,13927,81259,89457,92259,66811,45344,23676,64902,275,100,375,451,373,116,251,150,141,324}  
  3 | {16664,82803,7375,53577,85671,46465,89583,28753,38201,57599,39785,63099,71026,20543,52056,62785,86854,96900,85960,51256,51917,5901,129,208,400,244,459,49,386,283,198,467}  
  4 | {47066,46889,24635,93031,35972,52888,30732,93071,92172,93330,63597,12216,44887,25882,98570,41287,11343,49327,92704,16743,75095,34373,481,117,129,30,3,412,228,470,107,461}  
  5 | {46010,85290,76290,98398,15522,68861,90070,8352,31959,1786,52739,57341,99856,93526,68184,48683,85730,84427,23278,19603,80575,46747,224,430,234,136,159,204,243,120,406,471}  
(5 rows)

4、创建索引

create index on tbl using gin (propt _int4_sml_ops);

5、相似度搜索

overlap

postgres=# set smlar.type ='overlap';  
SET  
postgres=# set smlar.threshold=10;  
SET  
  
postgres=# explain analyze select * from tbl where propt % '{157,490,51,484,53,176,273,240,300,277}'::int[];  
                                                         QUERY PLAN                                                            
-----------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl  (cost=219.50..6871.30 rows=2000 width=36) (actual time=37.548..37.549 rows=1 loops=1)  
   Recheck Cond: (propt % '{157,490,51,484,53,176,273,240,300,277}'::integer[])  
   Heap Blocks: exact=1  
   ->  Bitmap Index Scan on tbl_propt_idx  (cost=0.00..219.00 rows=2000 width=0) (actual time=37.514..37.515 rows=1 loops=1)  
         Index Cond: (propt % '{157,490,51,484,53,176,273,240,300,277}'::integer[])  
 Planning Time: 0.161 ms  
 Execution Time: 37.593 ms  
(7 rows)  
  
Time: 38.683 ms  
postgres=# select * from tbl where propt % '{157,490,51,484,53,176,273,240,300,277}'::int[];  
 id |                                                                                   propt                                                                                     
----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
  1 | {1386,57573,55117,44934,83223,3444,77658,49523,85849,62549,99593,40714,53146,32510,68449,33662,45912,70227,64560,78831,86052,56387,157,490,51,484,53,176,273,240,300,277}  
(1 row)  
  
Time: 38.794 ms  
  
关闭索引, 性能直线下降:  
postgres=# set enable_bitmapscan =off;  
SET  
Time: 0.510 ms  
postgres=# select * from tbl where propt % '{157,490,51,484,53,176,273,240,300,277}'::int[];  
 id |                                                                                   propt                                                                                     
----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
  1 | {1386,57573,55117,44934,83223,3444,77658,49523,85849,62549,99593,40714,53146,32510,68449,33662,45912,70227,64560,78831,86052,56387,157,490,51,484,53,176,273,240,300,277}  
(1 row)  
  
Time: 12553.942 ms (00:12.554)

采用smlar提速100倍以上.

cosine

postgres=# set smlar.type ='cosine';  
SET  
  
postgres=# set smlar.threshold=0.55;  
SET  
Time: 1.107 ms  
postgres=# select * from tbl where propt % '{157,490,51,484,53,176,273,240,300,277}'::int[];  
 id |                                                                                   propt                                                                                     
----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
  1 | {1386,57573,55117,44934,83223,3444,77658,49523,85849,62549,99593,40714,53146,32510,68449,33662,45912,70227,64560,78831,86052,56387,157,490,51,484,53,176,273,240,300,277}  
(1 row)  
  
Time: 42.701 ms

tfidf

  • 例如将所有的药品说明书进行文本向量处理, 提取关键字, 生成tfidf表.
  • 请自行测试

参考

https://github.com/jirutka/smlar

文章标签：
云原生数据库 PolarDB
图像搜索
性能测试
测试技术
搜索推荐
关系型数据库
索引
PostgreSQL
PolarDB
算法
数据库
存储
分布式数据库
关键词：
云原生数据库 PolarDB开源
云原生数据库 PolarDB搜索
云原生数据库 PolarDB插件
云原生数据库 PolarDB相似
云原生数据库 PolarDB业务
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系，提升产品性能，打磨产品功能，从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手，打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系， 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
德哥,digoal
目录
相关文章
周周的奇妙编程
|
1天前
|
存储 关系型数据库 MySQL
数据管理的艺术：PolarDB开源版详评与实战部署策略（一）
PolarDB-X是阿里巴巴自研的高性能云原生分布式数据库，基于共享存储的Shared-nothing架构，支持MySQL生态，具备金融级高可用、分布式水平扩展、HTAP混合负载等能力。它通过CN（计算节点）和DN（存储节点）实现计算与存储分离，保证数据强一致性，并支持全局二级索引和多主多写。PolarDB-X开源版提供更高程度的定制化和控制权，适合追求技术自主性和成本优化的开发者。部署方式包括RPM包、PXD工具和Kubernetes，其中PXD工具提供了一键部署的便利性。
周周的奇妙编程
46715 11
PolarDB-Learning-helper
|
4天前
|
关系型数据库 分布式数据库 数据库
开源之夏2024学生报名启动！阿里云PolarDB社区项目期待你的参与！
开源之夏2024学生报名启动！阿里云PolarDB社区带你变得更强！
PolarDB-Learning-helper
43 2
开源之夏2024学生报名启动！阿里云PolarDB社区项目期待你的参与！
米粒变成爆米花
|
5天前
|
关系型数据库 MySQL 分布式数据库
快速体验开源PolarDB -X 部署安装
在CentOS上部署PolarDB-X标准版集群的体验包括三步：安装python3和docker（如果未预装），然后使用venv创建环境，安装pxd并验证。接着，通过`pxd tryout -t standard`部署集群，该过程需拉取大量镜像，可能耗时且占用数GB空间，建议事先清理空间并了解资源需求。部署后，可查询集群状态和健康信息。最终，使用`pxd cleanup`清理。过程中因磁盘空间不足遇到问题，建议体验前提供系统配置需求，并允许用户自定义MySQL参数。
米粒变成爆米花
21 0
WIFI码密
|
5天前
|
存储 关系型数据库 大数据
PolarDB 开源评测
开源PolarDB-X，源自阿里云PolarDB，具备分布式存储和计算能力，以其开源特性、成本效益、社区支持和灵活性受到青睐。支持多种部署模式，适合大规模数据处理。然而，其复杂性与文档不完善可能是挑战，建议优化部署流程以降低使用难度。
WIFI码密
15 1
阿六_Dexter
|
8天前
|
SQL JSON 关系型数据库
[UE虚幻引擎插件DTPostgreSQL] PostgreSQL Connector 使用蓝图连接操作 PostgreSQL 数据库说明
本插件主要是支持在UE蓝图中连接和操作PostgreSQL 数据库。
阿六_Dexter
16 2
桃李春风一杯酒
|
16天前
|
存储 关系型数据库 MySQL
【专栏】在众多开源数据库中，MySQL和PostgreSQL无疑是最受欢迎的两个
【4月更文挑战第27天】MySQL与PostgreSQL是两大主流开源数据库，各有特色。MySQL注重简单、便捷和高效，适合读操作密集场景，而PostgreSQL强调灵活、强大和兼容，擅长并发写入与复杂查询。MySQL支持多种存储引擎和查询缓存，PostgreSQL则具备扩展性、强事务支持和高可用特性。选择时应考虑项目需求、团队技能和预期收益。
桃李春风一杯酒
20 2
PolarDB小助手小悦
|
22天前
|
关系型数据库 OLAP 分布式数据库
「杭州*康恩贝」4月26日PolarDB开源数据库沙龙，开启报名！
4月26日周五，PolarDB开源社区联合康恩贝将共同举办开源数据库技术沙龙，本次沙龙我们邀请了众多数据库领域的专家，期待大家的参与！
PolarDB小助手小悦
26 0
「杭州*康恩贝」4月26日PolarDB开源数据库沙龙，开启报名！
PolarDB小助手小悦
|
1月前
|
运维 关系型数据库 分布式数据库
「合肥 * 讯飞」4 月 19 日 PolarDB 开源数据库沙龙，报名中！
4月19日周五，PolarDB开源社区联合科大讯飞共同举办开源数据库技术沙龙，本次沙龙我们邀请了众多数据库领域的专家，期待大家的参与！
PolarDB小助手小悦
39 1
「合肥 * 讯飞」4 月 19 日 PolarDB 开源数据库沙龙，报名中！
以山向海
|
4天前
|
SQL 关系型数据库 数据库
关系型数据库选择合适的数据库管理系统
【5月更文挑战第5天】关系型数据库选择合适的数据库管理系统
以山向海
60 2
关系型数据库选择合适的数据库管理系统
以山向海
|
5天前
|
关系型数据库 MySQL BI
关系型数据库选择合适的数据库管理系统
【5月更文挑战第4天】关系型数据库选择合适的数据库管理系统
以山向海
59 4
关系型数据库选择合适的数据库管理系统
PolarDB开源

PolarDB PostgreSQL 版

热门文章

最新文章

  • 1
    沉浸式学习PostgreSQL|PolarDB 18: 通过GIS轨迹相似伴随|时态分析|轨迹驻点识别等技术对拐卖、诱骗场景进行侦查
  • 2
    5分钟免费体验PolarDB PG版Serverless的极致弹性！
  • 3
    PolarDB 体验报告
  • 4
    【公测】PolarDB PostgreSQL版Serverless功能免费使用​！
  • 5
    沉浸式学习PostgreSQL|PolarDB 16: 植入通义千问大模型+文本向量化模型, 让数据库具备AI能力
  • 6
    开源之夏2024学生报名启动！阿里云PolarDB社区项目期待你的参与！
  • 7
    PolarDB-X 动手实践系列第一讲：如何一键部署开源 PolarDB-X
  • 8
    一文熟悉PolarDB-PG 分区表核心特性
  • 9
    PG内核解读-第1节PostgreSQL系统概述
  • 10
    手把手教你PolarDB-X中的表设计——用户表
  • 1
    智能辅助驾驶业务遭遇大表瓶颈，小鹏汽车如何破局？
    7
  • 2
    PolarDB 体验报告
    83
  • 3
    开源之夏2024学生报名启动！阿里云PolarDB社区项目期待你的参与！
    43
  • 4
    5分钟免费体验PolarDB PG版Serverless的极致弹性！
    90
  • 5
    PostgreSQL Json应用场景介绍和Shared Detoast优化
    170
  • 6
    【公测】PolarDB PostgreSQL版Serverless功能免费使用​！
    250
  • 7
    成都晨云信息技术完成阿里云PolarDB数据库产品生态集成认证
    374
  • 8
    一文熟悉PolarDB-PG 分区表核心特性
    82033
  • 9
    PolarDB PostgreSQL版：Oracle兼容的高性能数据库
    237
  • 10
    若依框架数据源切换为pg库
    189

    • 相关产品

  • 云原生数据库 PolarDB
    文档详情 产品详情

    • 相关课程

    更多
  • 7天突破PolarDB for PostgreSQL 2022版
  • 关系型数据库ACP认证课程
  • 关系型数据库线上预习视频
  • PolarDB for PostgreSQL 入门
  • PostgreSQL云上开发实践
  • PostgreSQL实战进阶

    • 相关电子书

    更多
  • 云栖大会：开源 PolarDB 架构演进、关键技术与社区建设
  • 2023云栖大会：和客户一起玩转PolarDB新特性
  • 2023云栖大会：PolarDB for AI

    • 相关实验场景

    更多
  • PolarDB NL2SQL：自然语言驱动的数据智能
  • 阿里云百炼xAnalyticDB PostgreSQL构建AIGC应用
  • 云原生数据库PolarDB快速入门
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 使用CloudLens采集PolarDB日志并进行审计分析
  • PolarDB for PostgreSQL 闪回特性体验

    • 推荐镜像

    更多
  • postgresql
  • mysql
  • clickhouse
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）