关于postgresql:模糊匹配相似度查询怎么破看PG亿级检索毫秒响应

浏览使人空虚，探讨使人麻利，写作使人准确。

需要场景假如
Aken某天在咖啡店听到一首歌，感觉很好听，但不晓得具体的歌名，只晓得歌曲是“民谣”，歌词蕴含“一把破吉他”、“旅人”，而后是男歌手。

如果我想收藏该歌曲，请问小编如何为我找到指标歌曲、对应的歌手、专辑？

为什么要探讨这个问题
首先，搜寻需要的理论场景切实太多了，能够说处处可见，比方：

百度、Google输出关键字搜寻信息；
泛娱乐行业搜寻对应的指标音视频文件；
人脸识别、指纹验证、特定动作捕获认证等。

其次，物联网时代网络产生的数据信息浩如烟海，须要高效的搜索引擎技术帮忙咱们疾速捕捉到相关度极高的匹配信息。

所以，如果咱们不做和业务利用场景心心相印的技术钻研，那么咱们无妨就多看看和业务比拟符合的技术。

而后这里探讨的技术问题与当今、将来的业务场景的分割都十分亲密，相干技术问题的解决，也肯定能够很好的推动利用的落地。

基于DB的传统解决方案
如果咱们是一个应用过关系型数据库的同学，咱们很容易就想到SQL含糊搜寻。如：

通过Btree实现的后含糊搜寻

select * from tab_aken where col like 'aken%';

或者Btree反向索引实现的前含糊搜寻

select * from tab_aken where col like '%aken';

然而，很多时候用户并不知道须要的前缀或后缀是什么，通常只记得其中的某些关键字或完好的信息，而后查找最类似的指标。

比方目击者只记得罪犯有局部特色：胡子、脸上刀疤、黑夹克、猥琐·········，而后依据关键字含糊查问锁定肯定范畴的嫌疑犯。

于是，事实中更多的查问需要可能是上面这样的：

前后含糊

select * from tab_aken where col like '%aken%';

多字段组合

select * from tab_aken where a=? and b=? or c=? and d=? or e between ? and ? and f in (?);

类似查问、向量间隔

select * from tab_aken order by similarity(col, 'aken') desc limit 100;

此处省略N种查问场景··················

那么问题来了，数据库通常并不具备上述前后含糊查问及其后的更多场景的高效检索能力。

上面以MySQL为例，看一个背面案例。

在32C-64G-SSD高端存储的设施上，这里运行了一个mysql-5.7.27，实例中有一个800w左右数据量的表：

mysql> select count(*) from test.tab_test_txt;+----------+| count(*) |+----------+|  8814848 |+----------+1 row in set (3.46 sec)mysql>mysql>select table_name,sum(truncate((data_length+index_length)/1024/1024/1024, 2)) data_GB,sum(truncate((data_length)/1024/1024/1024, 2)) tabsize_gb,sum(truncate((index_length)/1024/1024/1024, 2)) idxsize_gb from information_schema.tables where table_name+--------------+---------+------------+------------+| table_name   | data_GB | tabsize_gb | idxsize_gb |+--------------+---------+------------+------------+| tab_test_txt |    5.59 |       4.12 |       1.47 |+--------------+---------+------------+------------+1 row in set (0.00 sec)mysql>

而后应用字段name做含糊查问，命中600w多数据，性能十分查，耗时11.14秒。

mysql> SELECT name from test.tab_test_txtWHERE (NOT (`tab_test_txt`.`name` LIKE BINARY '%娴嬭瘯%' AND `tab_test_txt`.`name` IS NOT NULL)AND NOT (`tab_test_txt`.`name` LIKE BINARY '%绂荤嚎%' AND `tab_test_txt`.`name` IS NOT NULL)AND NOT (`tab_test_txt`.`name` LIKE BINARY '%寮鍙戞満%' AND `tab_test_txt`.`name` IS NOT NULL)) ;+--------------------------------------+| name                                 |+--------------------------------------+| [N][QQ闊充箰]                        || [N][鍏ㄦ皯K姝宂                      || [newPC瀹㈡埛绔帴鍏[鐧婚檰蹇冭烦] ||            .............             || [浼村淇℃伅閫昏緫server_涓婃捣]    || [浼村淇℃伅閫昏緫server_娣卞湷]    | +--------------------------------------+6578432 rows in set (11.14 sec)

为了排除大后果的影响，咱们再看看小后果的状况。来个最简略的查问：

mysql> SELECT * from test.tab_test_txt  where name like '%鎼滅储娴嬭瘯妯%';Empty set, 1 warning (11.86 sec)mysql>

能够看到，无匹配后果，或者单行记录，耗时11.86秒，和大量后果集的状况差异不大，因为都是全扫。

mysql> insert into test.tab_test_txt(id,name) values(666666,'MYSQL全文检索测试');Query OK, 1 row affected, 0 warnings (0.59 sec)mysql> select count(*) from test.tab_test_txt  where id = 666666;+----------+|  count(*)   |+----------+|        1   |+----------+1 row in set (0.00 sec)mysql> SELECT * from test.tab_test_txt  where name like '%SQL全%';+--------+---------+--------+-----------+-------+------+-------+---------------------+---------------------+------------+----------------+----------------+--------------+--------+----------+--------------+------------+--------+--------+| id     | name    | owners | parent_id | busid | uid  | level | update_date         | create_date         | limit_load | children_count | limit_low_load | history_load | status | group_id | _alarm_types | star_level | remark | enable |+--------+---------+--------+-----------+-------+------+-------+---------------------+---------------------+------------+----------------+----------------+--------------+--------+----------+--------------+------------+--------+--------+| 666666 | MySQL全 | NULL   |      NULL |  NULL | NULL |  NULL | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |         65 |              0 |             30 | NULL         |      0 |    21576 | NULL         |          0 | NULL   |      1 |+--------+---------+--------+-----------+-------+------+-------+---------------------+---------------------+------------+----------------+----------------+--------------+--------+----------+--------------+------------+--------+--------+1 row in set (12.69 sec)mysql>

而实际上，当今搜寻需要越来越朝着社会化、实时化、情景化、挪动化等趋势倒退，会混入更多的关联关系、复合条件等因素，使得查问变更更加复杂化。

比方思考到社会化关系因素，下面的语句略微降级就是一个多表关联的例子：

SELECT `core_data`.`id` FROM `core_data`INNER JOIN `core_extend` ON ( `core_data`.`ip` = `core_extend`.`ip` )INNER JOIN `core_business` ON ( `core_extend`.`business_id` = `core_business`.`busid` )WHERE (NOT (`core_business`.`name` LIKE BINARY '%娴嬭瘯%' AND `core_business`.`name` IS NOT NULL) AND NOT (`core_business`.`name` LIKE BINARY '%绂荤嚎%' AND `core_business`.`name` IS NOT NULL) AND NOT (`core_business`.`name` LIKE BINARY '%寮鍙戞満%' AND `core_business`.`name` IS NOT NULL) AND NOT (`core_extend`.`business_id` IN (789274, 879201, 1334489, ......，1121451, 1162547, 1168113, 1071955)) AND `core_data`.`is_pushed` IS NULL AND `core_data`.`update_date` > '2020-12-03 23:47:00');

咱们先不探讨上述语句的在写法上有没有优化空间，这是最近在生产环境中遇到的实在例子，命中了MySQL多表关联及文本检索的弱点。

对于MySQL在文本检索方面的性能，可参见如下文章：《MySQL全文检索性能测试及问题总结》

所以，如果利用中有全文检索的需要，间接在数据库运行相似的查问可能会是一个走不远的计划，数据量和性能略微要求高一点就无奈反对，也就不必谈什么海量实时高并发了。

行业通用解决方案
文本检索通常是搜索引擎的专长，因而目前行业内风行的通用计划是将数据同步到业余是搜索引擎零碎以反对用户搜寻需要，比方应用es集群。

但该计划同时也引入了跨产品交互问题，因而会存在数据同步提早及数据一致性问题，对于实时性、一致性要求高的场景还是有不少挑战。

另外，在类似度检索、正则表达式、前后含糊查问方面，目前搜索引擎的在性能上并未欠缺。

目前ES集群在动态文本检索的准确查问中能做到毫秒级，但对于下面这种既有多表关联又有文本搜寻的需要场景，ES显著是解决不了，如果数据还会频繁变更，那么可能就要思考交融其余计划了。

那么，有没有一种搜索引擎计划能够高效的实现数据检索，又不失数据库固有的属性呢？

难道就不能既能够高效实现文本检索，又能够高效处理事务解决、数据更改、关联查问、类似度匹配、简单正则表达式？

等等·······

新计划的摸索
这里尝试摸索一种基于DB搭建的搜索引擎计划，如果能够反对高效文本检索的状况下仍然放弃数据库固有的属性，那么不失为一种比拟敌对的代替计划，尤其是对于不想耗费额定的技术老本搭建专门搜索引擎的公司来说，这样还不须要多保护一个技术组件。

这里波及到分词切分算法、向量间隔计算、含糊及正则等个性，PostgreSQL作为性能最为丰盛的开源数据库产品，这些个性很早就曾经具备。例如：

分词切分

akendb=# select to_tsvector('english', 'akengan-love-db,oracle mysql postgresql'); to_tsvector -------------------------------------------------------------------------------- 'akengan':2 'akengan-love-db':1 'db':4 'love':3 'mysql':6 'oracl':5 'postgresql':7(1 row)akendb=#akendb=# select show_trgm('hello'); show_trgm --------------------------------- {"  h"," he",ell,hel,llo,"lo "}(1 row) akendb=#

类似计算

akendb=# SELECT smlar('{5,2,0}'::int[], '{5,2,8}');  --计算两个数组的类似度 smlar ----------- 0.6666667(1 row)akendb=#akendb=# SELECT word_similarity('aken', 'akengan');   --计算两词的类似度 word_similarity----------------- 0.8(1 row)akendb=#

正则匹配

select * from tab_account where email ~ '^[A-H]'; --查问以A-H结尾的email地址
所以，咱们无妨能够看看PostgreSQL在全文检索方面的能力如何。

创立测试表

分区表，共64个分区，并行度64。

akendb=# d+ tab_aken_text Unlogged table "public.tab_aken_text" Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description--------+---------+-----------+----------+---------+----------+--------------+------------- id     | integer |           | not null |         | plain    |              | info   | text    |           |          |         | extended |              |Indexes: "tab_aken_text_pkey" PRIMARY KEY, btree (id) "idx_tab_aken_text_info" gin (info gin_trgm_ops)Child tables: tab_aken_text0, tab_aken_text1, tab_aken_text10, tab_aken_text11, tab_aken_text12, tab_aken_text13, tab_aken_text14, tab_aken_text15, tab_aken_text16, tab_aken_text17, tab_aken_text18, tab_aken_text19, tab_aken_text2, tab_aken_text20, tab_aken_text21, tab_aken_text22, tab_aken_text23, tab_aken_text24, tab_aken_text25, tab_aken_text26, tab_aken_text27, tab_aken_text28, tab_aken_text29, tab_aken_text3, tab_aken_text30, tab_aken_text31, tab_aken_text32, tab_aken_text33, tab_aken_text34, tab_aken_text35, tab_aken_text36, tab_aken_text37, tab_aken_text38, tab_aken_text39, tab_aken_text4, tab_aken_text40, tab_aken_text41, tab_aken_text42, tab_aken_text43, tab_aken_text44, tab_aken_text45, tab_aken_text46, tab_aken_text47, tab_aken_text48, tab_aken_text49, tab_aken_text5, tab_aken_text50, tab_aken_text51, tab_aken_text52, tab_aken_text53, tab_aken_text54, tab_aken_text55, tab_aken_text56, tab_aken_text57, tab_aken_text58, tab_aken_text59, tab_aken_text6, tab_aken_text60, tab_aken_text61, tab_aken_text62, tab_aken_text63, tab_aken_text7, tab_aken_text8, tab_aken_text9Access method: heapOptions: parallel_workers=64akendb=#

测试数据准

为模仿海量数据场景，插入10亿随机64个字符的中文文本数据。

akendb=# select count(*)  from tab_aken_text; count ------------ 1000000000(1 row)akendb=#akendb=# select * from tab_aken_text limit 3; id    |                                                                                               info ---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2799938 | 疑鬱咼韺鏂诪澍隴龌材忝兤詐瑶鑴咑鱊彦琌顬颊還謶螒暯矦愹絘瞴沬翘烇忷採賦炻紞礃鄽隢圞譪簘巅斻休荋稑棍魫亰臘壋園麗蹚鍋鴰滃艸谙躿遏湞 2799939 | 癙砆劚蔾霙损戀鯯娵鶅璭褏鬴魟频灐覞鞁獶鷋瓚笹籼趟杸贽脇驝鎄俫唝倆餟簳騪聕籼殄濛郈鴚漝躮屳錿喞脛涔惿逈趴摜侾豈魷犵秅掾徍婵嬽敒禑廻 2799940 | 劂螒萙絮婓媶賬誟慮揎鯼煣啡生孺瑀镁喚犢髕粬筗壣菅请枾勨毨轊挺缄烆貽挌啇夓齮輩髮鵺酏馣鮦揹酓痰苚殻瑺悋襖攏负埾凵穄挬鼿铹署脹呩聨錇(3 rows)akendb=#

为不便测试，将其中id=10325230这一行的文本信息批改成指标信息：

update tab_aken_text6 set info ='搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试' where id =10325230;

查问性能验证

1.全文检索前后含糊查问

能够看到，10亿级的数据量，含糊查问耗时只须要9.899 ms。

akendb=# select  * from tab_aken_text where info like '%含糊匹配%'; id    |  info ----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 10325230 | 搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试(1 row)Time: 9.899 msakendb=#

2.文本检索类似度查问

在10亿数据中筹备10条指标测试数据

update tab_aken_text6 set info ='搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜寻的百分八十百分八十测试指标' where id =2400002;update tab_aken_text6 set info ='搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜寻的百分八十百分八十测试指标' where id =2500002;update tab_aken_text6 set info ='搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜寻的百分八十百分八十测试指标' where id =2600002;update tab_aken_text6 set info ='搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜寻的百分八十百分八十测试指标' where id =2200002;update tab_aken_text6 set info ='搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索模百分九十指标' where id =2000002;update tab_aken_text6 set info ='搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索模百分九十指标' where id =2700002;update tab_aken_text6 set info ='搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索模百分九十指标' where id =2800002;update tab_aken_text6 set info ='搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配九十五' where id =2100002;update tab_aken_text6 set info ='搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配九十五' where id =2300002;update tab_aken_text6 set info ='搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配九十五' where id =2900002;

在10亿数据中查问类似度超过50%的指标数据，耗时61.175 ms，CPU等资源耗费根本没察看到有什么耗费。

akendb=# select set_limit(0.5); set_limit----------- 0.5(1 row)Time: 0.283 msakendb=#select similarity(info, '搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试') as sml, *from tab_aken_text where info % '搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试'    -- 类似度超过阈值 order by sml desc; sml     |    id    |                                                                                         info ------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 | 10325230 | 搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试 0.77272725 |  2900002 | 搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配九十五 0.77272725 |  2100002 | 搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配九十五 0.77272725 |  2300002 | 搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配九十五 0.68 |  2800002 | 搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索模百分九十指标 0.68 |  2000002 | 搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索模百分九十指标 0.68 |  2700002 | 搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索模百分九十指标 0.56666666 |  2200002 | 搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜寻的百分八十百分八十测试指标 0.56666666 |  2400002 | 搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜寻的百分八十百分八十测试指标 0.56666666 |  2500002 | 搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜寻的百分八十百分八十测试指标(10 rows)Time: 61.175 msakendb=#

查问类似度超过90%的指标，精度要求越高，查问性能则会越快，如下在10亿数据的查问中耗时只须要29.020 ms。

akendb=# select set_limit(0.9); set_limit----------- 0.9(1 row)Time: 0.239 msakendb=#select similarity(info, '搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试') as sml,*from tab_aken_text where info % '搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试' order by sml desc limit 10; sml |    id    |                                                                                         info -----+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 | 10325230 | 搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试搜索引擎全文检索含糊匹配的测试(1 row)Time: 29.020 msakendb=#

因而，应用PostgreSQL能够做到10亿级以上数据毫秒检索，其实在百亿级数据量的状况下，PostgreSQL文本检索的性能仍然能够和10亿级别的性能并驾齐驱，齐全能够满足实时搜索引擎的需要，而且应用PostgreSQL一个益处是，各种性能还能够按需扩大。

参考资料
1.https://www.postgresql.org/do...
2.https://github.com/eulerto/pg...

更多精彩内容，请关注以下平台、网站：

中国PostgreSQL分会官网公众号（技术文章、技术流动）：
开源软件联盟PostgreSQL分会

中国PostgreSQL分会技术问答社区：
www.pgfans.cn

中国PostgreSQL分会官方网站：
www.postgresqlchina.com