模糊匹配、相似度查询怎么破？看PG亿级检索毫秒响应

开源软件联盟PostgreSQL分会 2020-12-23

1668

（本文阅读预计时间：31分钟）

作者：甘植恳，笔名：Aken，数据库技术爱好者，公众号《DB印象》作者

阅读使人充实，讨论使人敏捷，写作使人精确。

需求场景假设

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_txt
WHERE (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_text9
Access method: heap
Options: parallel_workers=64
akendb=#
复制

测试数据准备

为模拟海量数据场景，插入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 ms
akendb=#
复制

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 ms
akendb=#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 ms
akendb=#
复制

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

akendb=# select set_limit(0.9); 
 set_limit 
-----------
       0.9
(1 row)
Time: 0.239 ms
akendb=#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 ms
akendb=#
复制