PostgreSQL 全文检索实现指南

在处理涉及大量文本数据的应用时，快速且精确的全文检索变得至关重要。本文将引导你如何在 PostgreSQL 中对 title、organization、description 和 location 字段进行全文检索，并通过索引提高检索性能。

实现步骤

1. 创建数据表

首先，我们有一个名为 rumi_sjsu_activity 的表，该表包含了活动的基本信息。表的结构如下：

DROP TABLE IF EXISTS rumi_sjsu_activity;
CREATE TABLE rumi_sjsu_activity (
  ID BIGINT NOT NULL,
  title VARCHAR ( 256 ),
  start_time TIMESTAMP WITH TIME ZONE,
  end_time TIMESTAMP WITH TIME ZONE,
  organization VARCHAR ( 256 ),
  LOCATION VARCHAR ( 256 ),
  link VARCHAR ( 256 ),
  description TEXT,
  likes BIGINT DEFAULT 0,
  views BIGINT DEFAULT 0,
  joins BIGINT DEFAULT 0,
  shared BIGINT DEFAULT 0,
  files json,
  category_id bigint,
  state  SMALLINT DEFAULT 0,
  status SMALLINT DEFAULT 0,
  remark VARCHAR ( 256 ),
  creator VARCHAR ( 64 ) DEFAULT '',
  create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updater VARCHAR ( 64 ) DEFAULT '',
  update_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted SMALLINT DEFAULT 0,
  tenant_id BIGINT NOT NULL DEFAULT 0,
  PRIMARY KEY ( ID )
);

2. 添加 tsvector 字段

为了实现全文检索，我们首先需要在表中添加一个 tsvector 字段，用于存储文本字段的全文检索向量：

ALTER TABLE rumi_sjsu_activity ADD COLUMN search_vector tsvector;

3. 创建触发器函数

接下来，我们创建一个触发器函数，用于在插入或更新记录时自动更新 search_vector 字段。该函数将把 title、organization、description 和 location 字段的内容转换为 tsvector 类型，并为每个字段设置不同的权重，以便在搜索结果中进行优先排序：

CREATE OR REPLACE FUNCTION update_search_vector() RETURNS trigger AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(NEW.organization, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(NEW.description, '')), 'C') ||
    setweight(to_tsvector('english', coalesce(NEW.location, '')), 'D');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

4. 创建触发器

为了让 search_vector 字段在插入或更新记录时自动更新，我们需要创建一个触发器，该触发器会在数据变动时调用上述触发器函数：

CREATE TRIGGER trigger_update_search_vector
BEFORE INSERT OR UPDATE ON rumi_sjsu_activity
FOR EACH ROW EXECUTE FUNCTION update_search_vector();

5. 创建 GIN 索引

为了加速全文检索查询，我们需要为 search_vector 字段创建一个 GIN 索引。GIN（Generalized Inverted Index）索引是专门用于加速全文检索的索引类型：

CREATE INDEX idx_rumi_sjsu_activity_search_vector ON rumi_sjsu_activity USING GIN (search_vector);

6. 更新已有数据

对于已经存在的数据，你需要执行一次性的更新操作来填充 search_vector 字段：

UPDATE rumi_sjsu_activity
SET search_vector =
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(organization, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(description, '')), 'C') ||
    setweight(to_tsvector('english', coalesce(location, '')), 'D');

这条 SQL 语句的作用是更新 rumi_sjsu_activity 表中的 search_vector 字段，将 title、organization、description 和 location 这四个字段的内容转换为 tsvector 类型，并为每个字段设置不同的权重。这条语句的详细解释如下：

解释分解

1. UPDATE rumi_sjsu_activity：

   • 这是一个 UPDATE 语句，用于更新 rumi_sjsu_activity 表中的记录。

2. SET search_vector =：

   • 这部分指定要更新的字段是 search_vector，并为其分配新的值。

3. setweight(to_tsvector('english', coalesce(title, '')), 'A')：

   • to_tsvector('english', coalesce(title, ''))：
     • to_tsvector 函数将文本字段转换为 tsvector 类型，其中 'english' 指定了文本的语言是英语，PostgreSQL 会根据指定语言的规则进行词干化和词汇过滤。
     • coalesce(title, '')：coalesce 函数用于处理空值。如果 title 字段为 NULL，则返回空字符串 ''，以确保 to_tsvector 函数始终有输入。
   • setweight(..., 'A')：setweight 函数为生成的 tsvector 赋予权重 'A'。在全文检索中，权重用于区分不同字段的重要性。权重范围从 'A'（最高）到 'D'（最低）。

4. ||（连接操作符）：

   • || 是 PostgreSQL 中的连接操作符，用于将多个 tsvector 组合成一个。这里将 title、organization、description 和 location 字段的 tsvector 组合在一起。

5. setweight(to_tsvector('english', coalesce(organization, '')), 'B')：

   • 与上面类似，但这是针对 organization 字段的操作。权重设置为 'B'，表示 organization 字段的重要性略低于 title。

6. setweight(to_tsvector('english', coalesce(description, '')), 'C')：

   • 这部分针对 description 字段，转换为 tsvector 后，赋予权重 'C'。

7. setweight(to_tsvector('english', coalesce(location, '')), 'D')：

   • 最后一部分针对 location 字段，转换为 tsvector 后，赋予权重 'D'，表示此字段在全文检索中的权重最低。

这条 SQL 语句的核心目标是生成并更新 search_vector 字段，该字段整合了 title、organization、description 和 location 四个字段的内容，并为它们赋予不同的权重，以便在进行全文检索时，结果能够根据字段的重要性进行排序。 这条语句会更新所有现有记录的 search_vector 字段，使它们能够参与全文检索。

7. 查询示例

现在，你可以使用 to_tsquery 或 plainto_tsquery 进行全文检索。以下是一个简单的查询示例：

SELECT * FROM rumi_sjsu_activity WHERE deleted=0 and search_vector @@ to_tsquery('english', 'your_search_term');

@@ 操作符的作用

@@ 是一个 PostgreSQL 特定的操作符，用于进行全文搜索。它用于测试一个 tsvector 类型的列是否匹配一个 tsquery 表达式。

to_tsquery 函数的作用

to_tsquery 是一个 PostgreSQL 提供的函数，用于生成一个 tsquery 查询对象，该对象表示一个查询条件，可以与 tsvector 类型的列进行匹配。to_tsquery 函数将输入的字符串转换为一个 tsquery 类型的值，并且它能够理解基本的布尔逻辑运算符（例如 AND、OR 和 NOT），用于定义查询的逻辑关系。

详细解释

在查询中：

• search_vector 是一个 tsvector 类型的列，通常包含文档内容的词汇信息，经过标准化处理以便于搜索。
• to_tsquery('english', 'your_search_term') 将 'your_search_term' 转换为一个 tsquery 对象，并且假设文档和查询都使用英语进行标准化处理。

该查询会返回所有 search_vector 中匹配 'your_search_term' 的行，这里使用的是英语语言处理规则。

总结：@@ 操作符用于测试 tsvector 和 tsquery 之间的匹配，而 to_tsquery 用于生成可以与 tsvector 进行匹配的查询条件。

通配符查询

使用查询通配符搜索： 如果需要搜索包含 e 字符的词，可以使用类似通配符的方式

SELECT * FROM rumi_sjsu_activity WHERE search_vector @@ to_tsquery('english', 'test:*');

8. Java 实现全文检索

如果你在使用 Java 进行数据库操作，可以通过以下代码来实现全文检索：

package com.litongjava.open.chat.services;

import java.util.List;

import com.litongjava.db.activerecord.Db;
import com.litongjava.db.activerecord.Record;

public class SjsuActivitySearchService {

  public List<Record> search(String keyWord) {
    String sql = "SELECT * FROM rumi_sjsu_activity WHERE search_vector @@ to_tsquery('english', ?);";
    return Db.find(sql, schoolId, keyWord+=":*");
  }
}

在 Java 中，你可以使用 to_tsquery 方法传递搜索关键词，结合 Db.find 方法执行查询，并获取结果。

9. 测试代码

以下是一个测试用例，展示如何使用 SjsuActivitySearchService 执行全文检索并打印结果：

package com.litongjava.open.chat.services;

import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.junit.Test;

import com.litongjava.db.activerecord.Record;
import com.litongjava.jfinal.aop.Aop;
import com.litongjava.open.chat.config.DbConfig;
import com.litongjava.tio.utils.environment.EnvUtils;
import com.litongjava.tio.utils.json.JsonUtils;

public class SjsuActivitySearchServiceTest {

  @Test
  public void test() {
    // Load environment variables
    EnvUtils.load();

    // Initialize database configuration
    new DbConfig().config();

    // Perform search using the SjsuActivitySearchService
    List<Record> result = Aop.get(SjsuActivitySearchService.class).search("Test");

    // Convert each record to a Map and collect the results into a list
    List<Map<String, Object>> mappedResult = result.stream().map(Record::toMap).collect(Collectors.toList());

    // Print the mapped results
    System.out.println(JsonUtils.toJson(mappedResult));
  }
}

10. 示例输出

执行上述代码后，你将获得类似以下的 JSON 输出，展示了搜索结果中的相关记录：

[
  {
    "id": "418706383970619392",
    "title": "Test Title2",
    "start_time": 1719360000000,
    "end_time": 1719360008000,
    "organization": "litong",
    "location": "101",
    "link": "google",
    "description": "nothing",
    "likes": "0",
    "views": "0",
    "joins": "0",
    "shared": "0",
    "files": "[{\"uid\":\"rc-upload-1719436278314-3\",\"name\":\"200-dpi.png\",\"status\":\"done\",\"size\":298723,\"type\":\"image/png\",\"id\":\"394842983935103000\",\"url\":\"https://rumiapp.s3.us-west-1.amazonaws.com/sjsu/documents/394842976444076032.png\"}]",
    "category_id": "418695042102747136",
    "state": 0,
    "status": 0,
    "remark": null,
    "creator": "0",
    "create_time": 1724903433123,
    "updater": "",
    "update_time": 1724903433123,
    "deleted": 0,
    "tenant_id": "0",
    "search_vector": {
      "value": "'101':5 'litong':3B 'noth':4C 'test':1A 'title2':2A",
      "type": "tsvector",
      "null": true
    }
  },
  {
    "id": "418713454463954944",
    "title": "Test Title2",
    "start_time": 1719360000000,
    "end_time": 1719360008000,
    "organization": "litong",
    "location": "101",
    "link": "google",
    "description": "nothing",
    "likes": "1",
    "views": "0",
    "joins": "0",
    "shared": "0",
    "files": "[{\"uid\":\"rc-upload-1719436278314-3\",\"name\":\"200-dpi.png\",\"status\":\"done\",\"size\":298723,\"type\":\"image/png\",\"id\":\"394842983935103000\",\"url\":\"https://rumiapp.s3.us-west-1.amazonaws.com/sjsu/documents/394842976444076032.png\"}]",
    "category_id": "418713356124409856",
    "state": 0,
    "status": 0,
    "remark": null,
    "creator": "0",
    "create_time": 1724905118887,
    "updater": "",
    "update_time": 172