PostgreSQL全文搜索：数据库中的搜索引擎

发表于： 2022-09-10 23:12:38
分类：数据库文章分享
浏览：浏览(87)

在我的 SQL 之旅的早期，我认为在数据库中搜索一段文本主要涉及如下查询：

SELECT col FROM table WHERE col LIKE '%some_value%';

然后，如果我想更具体一些，我会加入一些通配符运算符或正则表达式。

后来，我与一位希望在应用程序中具有搜索功能的客户合作，因此“LIKE”和正则表达式不会削减它。我一直知道的只是模式匹配。对于某些目的，它工作得非常好，但是当它不仅仅是检索单个文本字段中的简单模式时会发生什么？

例如，如果您想跨多个字段进行搜索怎么办？即使搜索词发生拼写错误，如何返回可能的匹配项？另外，如果您有大量数据要搜索怎么办？当然，您可以为要查询模式匹配的列创建索引，但这会有一些限制（例如，B-tree 索引不适用于 col LIKE '%substring%'
）。

因此，当我们说 PostgreSQL 是“[batteries included database]”时这只是原因之一。使用 Postgres，您无需立即寻找比您自己的数据库管理系统更远的地方来获得全文搜索解决方案。如果您还没有尝试过 Postgres 的内置全文搜索，请继续阅读以获得简单的介绍。

面向初学者的 Postgres 全文搜索基础知识

核心 Postgres 包括以下全文搜索功能。仅举几例：

忽略停用词（常用词，如“the”或“an”）。
词干，其中搜索匹配可以基于单词的“根”形式或词干（“run”匹配“runs”和“running”，甚至“ran”）。
权重和排名搜索匹配（因此可以将最佳匹配排序到结果列表的顶部）。

在我们继续之前，让我们也熟悉以下概念：

文档是您要对其进行全文搜索的一组数据。在 Postgres 中，这可以从单个列或列组合构建，甚至可以从多个表构建。
文档被解析为标记，它们是来自文档文本的小片段（例如单词、短语等）。然后将标记转换为更有意义的文本单元，称为lexemes。
在 Postgres 中，这种转换是通过字典完成的 ——有内置的，但如果需要，可以创建自定义字典。这些词典有助于确定应该被忽略的停用词，以及不同派生词是否具有相同的词干。大多数词典都针对特定语言（英语、德语等），但您也可以拥有针对特定领域的词典。
文档中词位的排序列表存储在 tsvector 数据类型中。

示例：搜索风暴事件详细信息

我有一张表，其中包含美国国家气象局收集的风暴事件数据。为简单起见，我不会在下面的语句中包含所有可能的字段，但是此存储库中提供了数据的副本和一些进一步的信息。

CREATE TABLE se_details (
    episode_id int,
    event_id int primary key,
    state text,
    event_type text,
    begin_date_time timestamp,
    episode_narrative text,
    event_narrative text,
    ...
);

还假设我们要对 event_narrative 列的数据进行全文搜索。我们可以在表中添加一个新列来存储预处理的搜索文档（即词位列表）：

ALTER TABLE se_details ADD COLUMN ts tsvector
    GENERATED ALWAYS AS (to_tsvector('english', event_narrative)) STORED;

ts 是一个生成的列（Postgres 12 的新列），它会自动与源数据同步。

然后我们可以在 ts 上创建一个 GIN 索引：

CREATE INDEX ts_idx ON se_details USING GIN (ts);

然后我们可以这样查询：

SELECT state, begin_date_time, event_type, event_narrative
FROM se_details
WHERE ts @@ to_tsquery('english', 'tornado');

tsquery 是 Postgres 中的另一种全文搜索数据类型。它表示也被处理为词位的搜索词，因此我们将输入词传递给 to_tsquery 函数，以优化我们的全文搜索查询。（@@ 是匹配运算符。）

我们从这个查询中得到的是“tornado”在文本字符串中某处的记录，但除此之外，还有一些结果集中的记录，其中“tornado”也匹配为词位（"tornado-like" and "tornadoes"）：

state           | KENTUCKY
begin_date_time | 2018-04-03 18:08:00
event_type      | Thunderstorm Wind
event_narrative | A 1.5 mile wide swath of winds gusting to around 95 mph created **tornado-like** damage along Kentucky Highway 259 in Edmons
on County. The winds, extending 3/4 of a mile north and south of Bee Spring, destroyed or heavily damaged several small outbuildings, tore
part of the roof off of one home, uprooted and snapped the trunks of numerous trees, and snapped around a dozen power poles. Several othe
r homes sustained roof damage, and wind-driven hail shredded vinyl siding on a number of buildings.
state           | WISCONSIN
begin_date_time | 2018-08-28 15:30:00
event_type      | Thunderstorm Wind
event_narrative | A swath of widespread tree and crop damage across the southern portion of the county. Sections of trees and crops compl
etely flattened, and some structural damage from fallen trees or due to the strong downburst winds. Various roads closed due to fallen tre
es. Two semi-trucks were overturned on highway 57 in Waldo. The widespread wind damage and tornadoes caused structural damage to many home
s with 70 homes sustaining affected damage, 3 homes with minor damage, 2 homes with major damage, one home destroyed, and 2 businesses wit
h minor damage.

搜索短语

将短语作为搜索词处理的一种方法是在 tsquery 中使用 & (AND) 或 <-> (FOLLOWED BY) 布尔运算符。

例如，如果我们要搜索短语“rain of debris"”：

SELECT state, begin_date_time, event_type, event_narrative
FROM se_details
WHERE ts @@ to_tsquery('english', **'rain & of & debris'**);

搜索短语被规范化为“rain”和“debri”。只要 'rain' 和 'debri' 在文档中都有匹配项，顺序无关紧要，例如以下示例：

A debris flow caused by heavy rain on a saturated hillside blocked the Omak River Road one mile south of the intersection with State Route 97.

如果我们这样做to_tsquery('english', 'rain <-> of <-> debris')
，tsquery 的值为 'rain' <2> 'debri'，这意味着它只会匹配 'rain' 后面紧跟 'debri' 的两个位置，例如这里：

Heavy rain caused debris flows on the Coal Hollow Fire and Tank Hollow Fire burn scars.

（这实际上是唯一的匹配项，因此使用 <-> 运算符的限制更大一些。）

phraseto_tsquery 函数也可以解析词组本身，并插入在词位之间，其中 N 是从前一个词位开始计数到下一个词位的整数位置。此函数不识别与 to_tsquery 不同的运算符；例如，我们可以像这样传递整个短语：

phraseto_tsquery('english', 'rain of debris')

tsquery 的值是 'rain' <2> 'debri' 和上面一样，所以phraseto_tsquery 也考虑了定位。

加权和排名搜索结果的函数

分配不同权重和排名的一个非常常见的用例是搜索文章。例如，您可能希望将文章标题和摘要或内容合并在一起进行搜索，但希望标题上的匹配项被认为更相关，从而排名更高。

回到我们的风暴事件示例，除了事件叙述之外，我们的数据表还有一个情节叙述列。对于风暴数据，_event是一种单独的风暴事件类型（例如 flood, hail），而一个情节是整个风暴系统，可能包含许多不同类型的事件。

假设我们希望能够对事件和情节叙述进行全文搜索，但已决定事件叙述应该比情节叙述更重要。我们可以像这样定义 ts 列：

ALTER TABLE se_details ADD COLUMN ts tsvector
    GENERATED ALWAYS AS
     **(setweight(to_tsvector('english', coalesce(event_narrative, '')), 'A') ||**
     **setweight(to_tsvector('english', coalesce(episode_narrative, '')), 'B'))** STORED;

setweight（ https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-PARSING-DOCUMENTS ）是一个全文函数，它为文档的组件分配权重。该函数采用字符“A”、“B”、“C”或“D”（按此顺序，权重从大到小）。我们在这里还使用了合并，以便如果 episode_narrative 或 event_narrative 包含空值，则连接不会导致空值。

然后，您可以在 ORDER BY 子句中使用 ts_rank 函数来返回从最相关到不太相关的结果。

SELECT …
**ORDER BY ts_rank(ts, to_tsquery('english', 'tornado')) DESC**;

因此，该记录在搜索结果中的排名较高：

state             | MISSISSIPPI
begin_date_time   | 2018-04-06 22:18:00
event_type        | Tornado
event_narrative   | This tornado touched down near the Jefferson Davis-Covington County line along Lucas Hollow Road. It continued southeast, crossing the
 county line. Some large limbs and trees were snapped and uprooted at this location. It then crossed Lucas Hollow Road again before crossing Leonard Road.
 A tornado debris signature was indicated on radar in these locations. The tornado uprooted and snapped many trees in this region. It also overturned a sm
all tractor trailer on Oakvale Road and caused some minor shingle damage to a home. After crossing Oakvale Road twice, the tornado lifted before crossing
Highway 35. The maximum winds in this tornado was 105mph and total path length was 2.91 miles. The maximum path width was 440 yards.
**episode_narrative |** A warm front was stretched across the region on April 6th. As a disturbance rode along this stalled front, it brought copious amounts
of rain to the region thanks to ample moisture in place. As daytime heating occurred, some storms developed which brought severe weather to the region.

与此相比，episode_narrative 中匹配“tornado”，但 event_narrative 中没有匹配：

state             | NEBRASKA
begin_date_time   | 2018-06-06 18:10:00
event_type        | Hail
event_narrative   | Hail predominately penny size with some quarter size hail mixed in.
episode_narrative |** Severe storms developed in the Nebraska Panhandle during the early evening hours of Jun
e 6th. As this activity tracked east, a broken line of strong to severe thunderstorms developed. Hail up to
 the size of ping pong balls, thunderstorm wind gusts to 70 MPH and a brief tornado touchdown were reported
. Heavy rain also fell leading to flash flooding in western Keith county.

提示：ts_rank 返回一个浮点值，因此您可以在 SELECT 中包含该表达式以查看这些匹配项的得分情况。就我而言，密西西比事件我得到大约 0.890，内布拉斯加州事件得到 0.243。

是的，您可以在 Postgres 中保留全文搜索

通过实现突出显示结果等功能，或编写自己的自定义字典或函数，您可以更深入，并使 Postgres 全文搜索更加健壮。您还可以考虑启用扩展，例如 unaccent （从词位中删除变音符号）或 pg_trgm（用于模糊搜索）。说到扩展，这些只是Crunchy Bridge支持的两个扩展。我们已经构建了我们的托管云 Postgres 服务，这样您就可以直接进入并利用所有这些 Postgres 功能。

话虽如此：如您所见，您不需要非常复杂的设置即可开始。尝试一下您是否刚刚开始探索全文搜索解决方案，或者甚至只是重新评估是否需要全力以赴提供专用的全文搜索服务，这是一个好主意，特别是如果您的堆栈中已经有 Postgres .

公平地说，Postgres 没有 Elasticsearch 等平台提供的一些搜索功能。但一个主要优点是您不必维护和同步单独的数据存储。如果您不太需要超大规模搜索，那么通过最小化依赖关系可能会获得更多收益。另外，你已经知道的 Postgres 查询语法加上一些新的函数和运算符，可以让你走得更远。对于使用 Postgres 进行全文搜索还有其他问题或想法吗？我们很高兴在 @crunchydata上听到他们的消息。

赞(0)
踩(0)

免责声明：

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源，请您支持作者，购买正版内容。

6、资源失效，请下方留言，欢迎分享资源链接

文章评论

共0条评论