PostgreSQL · 特性介绍 · 全文搜索介绍

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介:

背景

在日常的数据处理中,我们经常会有这样的需求:从一个文本中寻找某个字符串(比如某个单词)。

对这个需求,我们可以用类似这样的SQL完成:SELECT * FROM tbl WHERE text LIKE '%rds PostgreSQL%';(找到含有“rds PostgreSQL”的文本)

现在我们考虑一些特殊的情形:

  1. 需要查找的文本特别多,特别大;
  2. 不做单纯的字符串匹配,而是考虑自然语言的一些特性,比如匹配某一类字符串(域名,人名)或者匹配单词的所有形式(不考虑它的词性及变化,比如have,has,had都匹配出来);
  3. 对中文自然语言特性的支持。

那么此时再用以上的“SELECT ... LIKE ...”就不明智了,因为对数据库来说,这样的SQL必然走的是全表扫描,那么当文本特别多,特别大的时候,查找效率就会很低。

另外,这样的SQL也不会智能到可以处理自然语言的特性。

怎么办呢?PostgreSQL(以下简称PG)提供了强大的全文搜索功能可以满足这样的需求。

对文本的预处理

全文搜索首先需要对文本预处理,包括3步:

  1. 将文本分解成一个个token,这些token可以是数字,单词,域名,人名,email的格式等等。在PG中可以定义一个parser来做这个工作。
  2. 将第一步分解成的token标准化,所谓的标准化就是利用一些规则将token分好类(比如人名是一类,域名是一类等等)。标准化后的token我们称之为lexeme。在PG中是通过定义一个词典来做这个工作。PG里最简单的词典simple的标准化过程就是将大写字母转成小写字母。
  3. 对文本打分,优化查找过程。比如对于待查找的词,文本1匹配的数量大于文本2匹配的数量,那么在这个查找过程,文本1的优先级大于文本2的优先级。

在PG中,以上对文本的预处理可以通过一个函数to_tsvector来完成,函数的返回值是tsvector这个数据类型。

另外,对于待查找的单词,我们也要用to_tsquery这个函数包装起来,函数的返回值是tsquery这个数据类型。

一个简单的例子见下面,to_tsquery里的参数可以使用运算符(&:与,|:或,!:非):

SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat & rat');
 ?column? 
----------
 t

Quick Start

在了解了这些概念之后,我们用实际的例子来玩一玩PG的全文搜索。

我们在client端输入以下命令,dFp显示的是所有的parser,这里只有一个默认parser(default)。

dFp+ default 显示默认parser(default)的详细信息:parse的过程(5个函数),parse的Token类型(asciihword, asciiword...)。

sbtest=# \dFp
        List of text search parsers
   Schema   |  Name   |     Description     
------------+---------+---------------------
 pg_catalog | default | default word parser
(1 row)

sbtest=# \dFp+ default
    Text search parser "pg_catalog.default"
     Method      |    Function    | Description 
-----------------+----------------+-------------
 Start parse     | prsd_start     | (internal)
 Get next token  | prsd_nexttoken | (internal)
 End parse       | prsd_end       | (internal)
 Get headline    | prsd_headline  | (internal)
 Get token types | prsd_lextype   | (internal)

        Token types for parser "pg_catalog.default"
   Token name    |               Description                
-----------------+------------------------------------------
 asciihword      | Hyphenated word, all ASCII
 asciiword       | Word, all ASCII
 blank           | Space symbols
 email           | Email address
 entity          | XML entity
 file            | File or path name
 float           | Decimal notation
 host            | Host
 hword           | Hyphenated word, all letters
 hword_asciipart | Hyphenated word part, all ASCII
 hword_numpart   | Hyphenated word part, letters and digits
 hword_part      | Hyphenated word part, all letters
 int             | Signed integer
 numhword        | Hyphenated word, letters and digits
 numword         | Word, letters and digits
 protocol        | Protocol head
 sfloat          | Scientific notation
 tag             | XML tag
 uint            | Unsigned integer
 url             | URL
 url_path        | URL path
 version         | Version number
 word            | Word, all letters
(23 rows)

输入dF+ english,给出标准化各类英语token时所用到的dictionary:

sbtest=# \dF+ english
Text search configuration "pg_catalog.english"
Parser: "pg_catalog.default"
      Token      | Dictionaries 
-----------------+--------------
 asciihword      | english_stem
 asciiword       | english_stem
 email           | simple
 file            | simple
 float           | simple
 host            | simple
 hword           | english_stem
 hword_asciipart | english_stem
 hword_numpart   | simple
 hword_part      | english_stem
 int             | simple
 numhword        | simple
 numword         | simple
 sfloat          | simple
 uint            | simple
 url             | simple
 url_path        | simple
 version         | simple
 word            | english_stem

创建以default为parser的配置defcfg,并增加token映射,这里我们只关心email, url, host:

sbtest=# CREATE TEXT SEARCH CONFIGURATION defcfg (PARSER = default);
CREATE TEXT SEARCH CONFIGURATION
sbtest=# ALTER TEXT SEARCH CONFIGURATION defcfg ADD MAPPING FOR email,url,host WITH simple;
ALTER TEXT SEARCH CONFIGURATION

建好配置defcfg后,我们看看利用defcfg对文本进行处理的结果。这里使用to_tsvector函数,可以看到email,url,host都被识别出来了:

sbtest=# select to_tsvector('defcfg','xxx yyy xxx@taobao.com yyy@sina.com http://google.com/123 12345 ');
                              to_tsvector                              
-----------------------------------------------------------------------
 'google.com':4 'google.com/123':3 'xxx@taobao.com':1 'yyy@sina.com':2
(1 row)

在实际对表内的文本做全文搜索时,一般对目标列建立gin索引(也就是倒排索引,详情见官方文档),这样可以加快查询效率,具体操作如下:

sbtest=# CREATE TABLE t1(c1 text);
CREATE TABLE
sbtest=# CREATE INDEX c1_idx ON t1 USING gin(to_tsvector('defcfg', c1));
CREATE INDEX
sbtest=# \d t1
     Table "public.t1"
 Column | Type | Modifiers 
--------+------+-----------
 c1     | text | 
Indexes:
    "c1_idx" gin (to_tsvector('defcfg'::regconfig, c1))

这里我们插入2条文本,并做一些匹配:

sbtest=# INSERT INTO t1 VALUES('xxx yyy xxx@taobao.com yyy@sina.com http://google.com 12345');
INSERT 0 1
sbtest=# INSERT INTO t1 VALUES('xxx yyy xxx@gmail.com yyy@sina.com http://google.com 12345');
INSERT 0 1
sbtest=# select * from t1;
                             c1                              
-------------------------------------------------------------
 xxx yyy xxx@taobao.com yyy@sina.com http://google.com 12345
 xxx yyy xxx@gmail.com yyy@sina.com http://google.com 12345
(2 rows)

sbtest=# select * from t1 where to_tsvector('defcfg',c1) @@ 'google.com';
                             c1                              
-------------------------------------------------------------
 xxx yyy xxx@taobao.com yyy@sina.com http://google.com 12345
 xxx yyy xxx@gmail.com yyy@sina.com http://google.com 12345
(2 rows)

sbtest=# select * from t1 where to_tsvector('defcfg',c1) @@ to_tsquery('google.com & yyy@sina.com');
                             c1                              
-------------------------------------------------------------
 xxx yyy xxx@taobao.com yyy@sina.com http://google.com 12345
 xxx yyy xxx@gmail.com yyy@sina.com http://google.com 12345
(2 rows)

sbtest=# select * from t1 where to_tsvector('defcfg',c1) @@ to_tsquery('google.com & xxx@gmail.com');
                             c1                             
------------------------------------------------------------
 xxx yyy xxx@gmail.com yyy@sina.com http://google.com 12345
(1 row)

以上的操作都是针对英文,实际上对中文也是支持的,不过会稍微麻烦点,因为中文的token必须通过分词才能产生,所以需要先装分词的组件scws和zhparser,具体可以参考这篇博文

结语

本文对PG的全文搜索做了一个入门级的介绍,方便用户快速上手,如果需要对全文搜索作更深入的研究,建议阅读官方文档第12章

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
1月前
|
关系型数据库 MySQL 测试技术
数据库专家带你体验PolarDB MySQL版 Serverless的极致弹性特性!
本次基于阿里云瑶池数据库解决方案体验馆,带你体验PolarDB MySQL Serverless形态下的性能压测环境,基于可选择的标准压测工具进行压测,构造弹性场景进行压测,实时动态展示弹性能力、价格和性价比结果,压测环境可开放定制修改、可重复验证。参与活动即有机会获得鼠标、小米打印机、卫衣等精美礼品。
数据库专家带你体验PolarDB MySQL版 Serverless的极致弹性特性!
|
2月前
|
关系型数据库 MySQL 测试技术
数据库专家带你体验PolarDB MySQL版 Serverless的极致弹性特性
本次基于阿里云瑶池数据库解决方案体验馆,带你体验PolarDB MySQL Serverless形态下的性能压测环境,基于可选择的标准压测工具进行压测,构造弹性场景进行压测,实时动态展示弹性能力、价格和性价比结果,压测环境可开放定制修改、可重复验证。参与活动即有机会获得鼠标、小米打印机、卫衣等精美礼品。
|
4月前
|
存储 关系型数据库 分布式数据库
PolarDB-X HTAP新特性 ~ 列存索引
随着数据爆炸式的增长,传统的OLTP和OLAP解决方案基于简单的读写分离或ETL模型,将在线库的数据以T+1的方式抽取到数据仓库中进行计算,这种方案存在存储成本高、实时性差、链路和维护成本高等缺陷。 为应对数据爆炸式增长的挑战,PolarDB分布式版本基于对象存储设计了一套列存索引(Clustered Columnar Index,CCI)功能,支持将行存数据实时同步到列存存储上
76007 148
|
7月前
|
关系型数据库 调度 数据库
直播预告 | PolarDB-PG 企业级特性 —— Shared Server特性详解
PolarDB-PG 提供了 Shared Server 内置连接池功能,实现了用户连接与后端进程的解绑。后端进程在运行时可以根据实时负载和进程污染情况进行动态转换。负载调度算法使用 Stall 机制弹性控制 Worker 数量,同时避免用户连接饿死。从根本上解决了高并发或者大量短连接带来的性能、稳定性问题。
|
7月前
|
关系型数据库 分布式数据库 数据库
直播预告 | PolarDB-PG 企业级特性 —— 闪回特性详解
闪回表 (Flashback Table) 功能是PolarDB-PG数据库高可用的一个重要特性,支持在数据人为误操作时,快速闪回到某个时间点,恢复以及查看丢失的数据。本期分享将会介绍闪回表、闪回日志的基本原理以及特性,并演示闪回功能的使用方法。
直播预告 | PolarDB-PG 企业级特性 —— 闪回特性详解
|
11月前
|
关系型数据库 测试技术 数据库
【全文搜索】全文搜索 PostgreSQL 或 ElasticSearch
【全文搜索】全文搜索 PostgreSQL 或 ElasticSearch
|
存储 SQL 缓存
PolarDB-MySQL 新特性 - Partial Result Cache
背景查询缓存(Query Cache)是数据库执行层的一个加速查询的特性,用来缓存一条查询语句的结果集,如果后续再有相同的查询,直接从结果集缓存中读取结果,而不用再重新执行而极大提升查询性能。但Query Cache在实际业务使用中存在较多的局限性,首先能够命中Query Cache的规则非常严格,必须是完全相同的SQL语句,并且被查询的表的数据不能有任何的修改,有任意规则不符合要求都会造成cac
164 0
PolarDB-MySQL 新特性 - Partial Result Cache
|
关系型数据库 分布式数据库 PolarDB
|
关系型数据库 分布式数据库 PolarDB
|
存储 SQL Oracle
AnalyticDB PostgreSQL 7.0 支持存储过程(CREATE PROCEDURE)特性
AnalyticDB PostgreSQL 7.0 新增了存储过程功能的支持,让用户在使用ADB PG时能够更方便高效地开发业务,并能够更好地兼容Oracle等传统数仓的业务。
423 1
AnalyticDB PostgreSQL 7.0 支持存储过程(CREATE PROCEDURE)特性