产品指南
查询与优化
全文检索

全文检索

全文检索在现代数据应用中扮演着非常重要的角色，能够极大程度地丰富数据应用并充分发挥数据的价值，本文将介绍trgm/tsvector/tsquery/中文分词等功能， 用户可以非常方便的将全文检索应用到数据处理应用中。

全文检索的挑战

实现全文检索，在数据架构中面临诸多挑战。

功能挑战

  • 查询语法易用性：

    • 需要提供用户易于理解和使用的查询语法，降低学习成本和使用门槛。

  • 数据预处理功能：

    • 词干提取：不同语言的词形变化多样，需要支持词干提取。比如搜索cat，希望能匹配到包含cats的文档。

    • 停用词过滤：过滤掉常见但无意义的词汇， 如英语的“the，is”， 中文的“的地得”等。

性能挑战

  • 性能与可扩展性：需要满足业务场景下的QPS和延迟要求，确保随着数据量增长仍能水平扩展。

  • 数据实时性：保证搜索查询能检索到实时更新的数据，并确保查询结果的一致性。

基于trgm扩展计算字符串相似度

对于简单的仅需要字符串模糊匹配的检索场景，例如根据email中的几个字符搜索用户等。

ProtonBase 内置了 PostgreSQL 的pg_trgm扩展，通过使用三元组（trigram） 匹配增强了部分文本搜索的能力。pg_trgm 提供了一系列函数和操作符用于计算文本相似度。

三元组（trigram）是从字符串中取出的三个连续字符的组合。通过将文本分解为三元组，用户可以执行更有效和灵活的相似性搜索。

计算三元组

pg_trgm 模块计算文本字符串的三元组方式：

  • 只考虑字母数字字符。

  • 在计算三元组之前将字符串转换为小写。

  • 每个单词假定前缀为两个空格，后缀为一个空格。

  • 去重输出的三元组集。

计算相似性

给定两个字符串 A 和 B 的三元组集，pg_trgm 计算相似性得分为：

  • 两个集合交集的大小除以两个集合并集的大小。

我们可以使用 show_trgm和similarity 函数来查看 pg_trgm 如何在字符串中计算三元组以及计算相似性：

SELECT show_trgm('Zhangsan'), show_trgm('Zhan'), similarity('Zhangsan', 'Zhan');
 
                  show_trgm                  |          show_trgm          | similarity 
---------------------------------------------+-----------------------------+------------ 
{"  z"," zh","an ",ang,gsa,han,ngs,san,zha} | {"  z"," zh","an ",han,zha} |  0.5555556(1 row)
 
-- 两个输入字符串共有9个不同的三元组(并集），共有5个相同三元组（交集）。
-- 因此，相似性得分为 5/9 (0.5555556)。

这个扩展简单易用， 特别适用于需要模糊字符串匹配的场景。

以下是一个简单的示例：

-- 创建test_email表
CREATE TABLE email_search (
    email TEXT
) USING COLUMNAR;
 
INSERT INTO email_search (email) VALUES
('alice@example.com'),
('bob@sample.org'),
('charlie@gmail.com'),
('david@yahoo.com'),
('eve@hotmail.com'),
('frank@outlook.com'),
('gerry@qq.com'),
('lisi@sina.com'),
('zhangsan@qq.com');

SPLIT_GIN索引

ProtonBase 内置了SPLIT_GIN的分布式倒排索引，来提升全文检索场景下的查询效率。

我们可以创建 SPLIT_GIN 索引以加速相关搜索查询，使用 gin_trgm_ops 参数, 可以加速 LIKE 和 ILIKE 运算符。

-- 创建索引
CREATE INDEX email_search_email_gin ON email_search USING SPLIT_GIN(email gin_trgm_ops);  
 
-- 测试索引
textsearch=> SELECT email FROM email_search where email like '%zhangsan%';
      email      
----------------- 
 zhangsan@qq.com
(1 row) 
 
textsearch=> SELECT email FROM email_search where email ilike '%lisi%';
     email     
--------------- 
 lisi@sina.com
(1 row)

基于tsvector和tsquery实现丰富的检索表达式

通过内置的~、~*、LIKE和ILIKE等操作符，以及trgm扩展， 我们可以支持简单的文本检索能力。

但对于复杂的检索场景，例如需要严格匹配单词，以及查询单词的组合AND/OR/NOT等，我们则需要利用更高级的功能。

ProtonBase提供以下两个基本的数据类型以及查询操作符支持更复杂的全文检索。

  1. tsvector 类型：表示将字符串根据选择的分词配置规则转换为词位（lexemes）的列表。

  2. tsquery 类型：表示一个文本查询，使用布尔操作符&（AND）、|（OR）和!（NOT）来组合词位。

  3. @@ 查询操作符： tsvector @@ tsquery ， 返回bool， 表示是该分词是否命中查询。

tsvector 类型

  • tsvector的功能是将字符串，根据选择的分词配置规则，转换为分词的类型，你可以理解为一组被抽象出来的词位。更多内容参考tsvector。

    • 内置的to_tsvector函数，同时会做规范化（normalize）。
textsearch=> SELECT 'The Fat Rats'::tsvector;
      tsvector
-------------------- 
 'Fat' 'Rats' 'The'
(1 row)
 
textsearch=> SELECT to_tsvector('The Fat Rats');
   to_tsvector   
----------------- 
 'fat':2 'rat':3
(1 row)

tsquery 类型

  • 表示一个文本查询需要query的词位，并且使用布尔操作符&（AND）、|（OR）和!（NOT）来组合它们。更多内容参考tsquery。

    • 内置的to_tsquery函数，也会规范化。
textsearch=> SELECT 'Fat & Rats'::tsquery;
    tsquery     
---------------- 
 'Fat' & 'Rats'(1 row)
 
textsearch=> SELECT to_tsquery('english', 'Fat & Rats');
  to_tsquery   
--------------- 
 'fat' & 'rat'(1 row)

tsquery 操作符

  • &（AND）操作符指定它的两个参数都必须出现在文档中才表示匹配。

  • |（OR）操作符指定至少一个参数必须出现

  • !（NOT）操作符指定它的参数不出现才能匹配。例如，查询fat & ! rat匹配包含fat但不包含rat的文档。

  • <->（FOLLOWED BY）tsquery运算符，可以搜索短语， 仅当其参数具有相邻且按给定顺序匹配时才匹配。例如：

    textsearch=> SELECT to_tsvector('fatal error') @@ to_tsquery('fatal <-> error'); 
 ?column?
---------- 
 t
 
textsearch=> SELECT to_tsvector('error is not fatal') @@ to_tsquery('fatal <-> error');
 ?column?
---------- 
 f

  • <N> 是一个更通用的 FOLLOWED BY 运算符版本， 其中N是一个整数，表示匹配词元位置之间的差异。<1>等同于<->， 而<2>允许匹配之间出现一个其他词元，依此类推。phraseto_tsquery函数利用此运算符构建一个 可匹配多词短语的tsquery，当其中一些词是停用词时。例如：

    textsearch=> SELECT phraseto_tsquery('cats ate rats');     
	 phraseto_tsquery      
--------------------------- 
 'cat' <-> 'ate' <-> 'rat'(1 row)
 
textsearch=> SELECT phraseto_tsquery('the cats ate the rats');
     phraseto_tsquery      
--------------------------- 
 'cat' <-> 'ate' <2> 'rat'(1 row)

  • 圆括号可以被用来控制tsquery操作符的嵌套。

    • 如果没有圆括号，|的计算优先级最低，然后从低到高依次是&、<->、!。

tsquery相关函数

  • plainto_tsquery

plainto_tsquery将未格式化的文本querytext转换成一个tsquery值。该文本被解析并被正规化，很像to_tsvector，然后&（AND）布尔操作符被插入到留下来的词之间。

textsearch=> SELECT plainto_tsquery('english', 'The Fat Rats'); 
 plainto_tsquery 
----------------- 
 'fat' & 'rat'(1 row)

  • websearch_to_tsquery

      websearch_to_tsquery使用一种可供选择的语法从querytext创建一个tsquery值，这种语法中简单的未格式化文本是一个有效的查询。和plainto_tsquery以及phraseto_tsquery不同，它还识别特定的操作符。此外，这个函数绝不会报出语法错误，这就可以把原始的用户提供的输入用于搜索。支持下列语法：

    • 无引号文本：不在引号中的文本将被转换成由&操作符分隔的词，就像被plainto_tsquery处理过那样。

    • "引号文本"：在引号中的文本将被转换成由<->操作符分隔的词，就像被phraseto_tsquery处理过那样。

    • OR：“or”将转换为|运算符。

    • -：破折号将转换为 ! 运算符。

textsearch=> SELECT websearch_to_tsquery('english', 'The fat rats');
 websearch_to_tsquery 
----------------------
 'fat' & 'rat'
(1 row)
 
textsearch=> SELECT websearch_to_tsquery('english', '"supernovae stars" -crab');      
 websearch_to_tsquery       
----------------------------------
 'supernova' <-> 'star' & !'crab'
 
textsearch=> SELECT websearch_to_tsquery('english', '"sad cat" or "fat rat"');      
 websearch_to_tsquery       
-----------------------------------
 'sad' <-> 'cat' | 'fat' <-> 'rat'
(1 row)
 
textsearch=> SELECT websearch_to_tsquery('english', 'signal -"segmentation fault"');        
 websearch_to_tsquery          
---------------------------------------
 'signal' & !( 'segment' <-> 'fault' )
(1 row)
 
textsearch=> SELECT websearch_to_tsquery('english', '""" )( dummy \\ query <->'); 
 websearch_to_tsquery 
----------------------
 'dummi' <-> 'queri'
(1 row)

SPLIT_GIN索引

建议为ts_vector字段创建SPLIT_GIN索引。

-- 指定为列存，方便使用SPLIT_GIN index
CREATE TABLE text_search (content text) using COLUMNAR;
 
INSERT INTO text_search VALUES
('Ruby on Rails for web application development.'),
('Rust ownership and memory safety.'),
('Concurrency in Golang programming.'),
('Building web APIs with Golang and Rust.'),
('Golang vs Rust: A comparison of programming languages.'),
('Mobile app development using React Native.'),
('Functional programming with Haskell and Scala.'),
('The impact of cloud computing on modern IT.'),
('Security best practices in web development.'),
('Optimizing SQL queries for performance.'),
('JavaScript frameworks for web application development.'),
('Software development life cycle methodologies.'),
('Artificial intelligence and machine learning in Python.');
 
--为该文档增加tsvector计算列 （也可以添加为普通列）
ALTER TABLE text_search ADD tsv_content TSVECTOR
GENERATED ALWAYS AS (to_tsvector('english', content)) STORED;
 
-- 为该tsvector列创建索引
CREATE INDEX text_search_tsv_gin ON text_search USING SPLIT_GIN(tsv_content);

查询示例

textsearch=> SELECT content FROM text_search WHERE tsv_content @@ to_tsquery('english', 'web & development'); 
                   content                        
--------------------------------------------------------
 Security best practices in web development.
 Ruby on Rails for web application development.
 JavaScript frameworks for web application development.
(3 rows)
 
textsearch=> SELECT content FROM text_search WHERE tsv_content @@ to_tsquery('english', 'web <-> development');
                   content                   
---------------------------------------------
 Security best practices in web development. 
(1 row)   
 
textsearch=> SELECT content FROM text_search  WHERE tsv_content @@ to_tsquery('english', 'JavaScript | application');  
                   content                         
--------------------------------------------------------
 Ruby on Rails for web application development.
 JavaScript frameworks for web application development.
(2 rows)  
 
textsearch=> SELECT content FROM text_search  WHERE tsv_content @@ to_tsquery('english', 'Rust & !programming');     
                   content                 
-----------------------------------------
 Building web APIs with Golang and Rust.
 Rust ownership and memory safety.
(2 rows)
 
textsearch=> SELECT content FROM text_search WHERE tsv_content @@ to_tsquery('english', 'cloud | !develop');           
                   content                         
---------------------------------------------------------
 Rust ownership and memory safety.
 Concurrency in Golang programming.
 Building web APIs with Golang and Rust.
 Golang vs Rust: A comparison of programming languages.
 Functional programming with Haskell and Scala.
 The impact of cloud computing on modern IT.
 Optimizing SQL queries for performance.
 Artificial intelligence and machine learning in Python.
(8 rows)

中文分词

ProtonBase也提供了一组内置的中文分词config，只需要将 to_tsvector 以及 to_tsquery 的 english 改成 chinese 即可。

textsearch=> SELECT to_tsvector('chinese', '中国北京');
    to_tsvector    
-------------------
 '中国':1 '北京':2
(1 row)

SPLIT_GIN索引

-- 指定为列存，方便使用SPLIT_GIN index
CREATE TABLE text_search_cn (content text) using COLUMNAR;
 
INSERT INTO text_search_cn VALUES
('这是一个测试文档，用于演示中文分词功能。'),
('ProtonBase代表着先进的云原生分布式数据库，兼容PostgreSQL。'),
('在数据库设计中，合理使用索引可以显著提高查询性能。'),
('PostgreSQL是一款强大的开源关系型数据库管理系统，以其高度的可扩展性、丰富的功能集而受到广泛赞誉。'),
('Python编程语言在人工智能和机器学习中非常流行。'),
('Rust是一种强调内存安全、并发性和零成本抽象的编程语言。'),
('Golang在编程语言中以强大的并发支持闻名。'),                                                                                                                                                                                                        
('Web开发中中文分词非常重要。'),
('ProtonBase的tsvector很强大，支持中文搜索。')
;  
 
-- 为该文档增加tsvector计算列 （也可以添加为普通列）
ALTER TABLE text_search_cn ADD tsv_content TSVECTOR
GENERATED ALWAYS AS (to_tsvector('chinese', content)) STORED;    
 
-- 为该tsvector列创建索引
CREATE INDEX text_search_cn_tsv_gin ON text_search_cn USING SPLIT_GIN(tsv_content); 
-- 查询示例
textsearch=> SELECT content FROM text_search_cn 
WHERE tsv_content @@ to_tsquery('chinese', '中文');
                  content                   
--------------------------------------------
 这是一个测试文档，用于演示中文分词功能。
 ProtonBase的tsvector很强大，支持中文搜索。
 Web开发中中文分词非常重要。
(3 rows)
 
textsearch=> SELECT content FROM text_search_cn 
WHERE tsv_content @@ to_tsquery('chinese', '数据库 & 分布式');
                          content                           
------------------------------------------------------------
 ProtonBase代表着先进的云原生分布式数据库，兼容PostgreSQL。
(1 row)
 
textsearch=> SELECT content FROM text_search_cn 
WHERE tsv_content @@ to_tsquery('chinese', '数据库 <-> 设计');
                      content                       
----------------------------------------------------
 在数据库设计中，合理使用索引可以显著提高查询性能。
 
(1 row)
textsearch=> SELECT content FROM text_search_cn 
WHERE tsv_content @@ to_tsquery('chinese', 'PostgreSQL & 数据库');
                                             content                                              
--------------------------------------------------------------------------------------------------
 ProtonBase代表着先进的云原生分布式数据库，兼容PostgreSQL。
 PostgreSQL是一款强大的开源关系型数据库管理系统，以其高度的可扩展性、丰富的功能集而受到广泛赞誉。
 (2 rows)
 
textsearch=> SELECT content FROM text_search_cn
WHERE tsv_content @@ to_tsquery('chinese', 'python | 编程语言');
                        content                         
--------------------------------------------------------
 Python编程语言在人工智能和机器学习中非常流行。
 Golang在编程语言中以强大的并发支持闻名。
 Rust是一种强调内存安全、并发性和零成本抽象的编程语言。
(3 rows)
 
textsearch=> SELECT content FROM text_search_cn 
WHERE tsv_content @@ to_tsquery('chinese', '(并发|数据库)&查询');
                      content                       
----------------------------------------------------
 在数据库设计中，合理使用索引可以显著提高查询性能。
(1 row)
 
-- 排序
textsearch=> SELECT 
ts_rank(tsv_content, to_tsquery('PostgreSQL & 数据库')) as rank, 
content
FROM text_search_cn
WHERE tsv_content @@ to_tsquery('PostgreSQL & 数据库')
ORDER BY rank desc;
    rank     |                                             content                                              
-------------+--------------------------------------------------------------------------------------------------
 0.098500855 | ProtonBase代表着先进的云原生分布式数据库，兼容PostgreSQL。
  0.09148999 | PostgreSQL是一款强大的开源关系型数据库管理系统，以其高度的可扩展性、丰富的功能集而受到广泛赞誉。
(2 rows)
数据查询、更新和删除向量检索