如何实现高性能全文检索_云原生数据仓库AnalyticDB MySQL版(AnalyticDB for MySQL)-阿里云帮助中心

本文将引导您如何使用pgsearch插件在云原生数据仓库 AnalyticDB PostgreSQL 版数据库中基于BM25算法实现全文检索。下面将使用维基百科数据集作为案例数据集，详细介绍如何导入数据集、创建索引并查询。

前提条件

实例版本为内核版本v7.1.0.0及以上的AnalyticDB PostgreSQL 7.0版。
已经安装pgsearch插件。
说明
如您未安装，请提交工单联系工作人员协助安装（需要重启实例）。

操作步骤

导入维基百科数据集到云原生数据仓库 AnalyticDB PostgreSQL 版数据库中。
本次示例数据文件为wiki-articles-1w.json。数据文件中包含以下字段：url、title、body。共有1万行数据。
1. 创建temp_json表。
```
CREATE TABLE temp_json(data JSONB);
```
2. 通过COPY命令将数据文件导入到新建的temp_json表中。请替换/path/to/wiki-articles-1w.json为数据文件的实际路径。
```
\COPY temp_json(data) FROM '/path/to/wiki-articles-1w.json' CSV QUOTE E'\x01' DELIMITER E'\x02';
```
  说明
  由于数据本身含有引号和逗号等字符，因此此处使用非标准字符 E'\x01' 作为引号，E'\x02' 作为分隔符，确保文件中的所有数据都能被正确解析而不产生歧义。
3. 创建wiki表。
```
CREATE TABLE wiki (
    id SERIAL PRIMARY KEY,
    url TEXT,
    title TEXT,
    body TEXT
);
```
4. 将temp_json表中的数据解析并通过INSERT命令导入到wiki表中。
```
INSERT INTO wiki(url, title, body)
SELECT data->>'url' AS url,
       data->>'title' AS title,
       data->>'body' AS body
FROM (SELECT data FROM temp_json) A;
```

创建BM25索引。

使用pgsearch插件提供的create_bm25函数在wiki表的body字段上创建BM25索引。

CALL pgsearch.create_bm25(
    index_name => 'wiki_idx',
    table_name => 'wiki',
    text_fields => '{body: {}}'
);

使用BM25索引进行查询。
使用新建的BM25索引进行全文检索。查找与关键词“artificial intelligence”最相关的20条数据，返回结果按相关性排序，示例如下。
```
SELECT * FROM wiki
ORDER BY body @@@ pgsearch.config('body:"artificial intelligence"')
LIMIT 20;
```

完整示例语句

以下是上文操作步骤中涉及的所有示例语句。

-- 导入JSON文件
CREATE TABLE temp_json(data JSONB);
\COPY temp_json(data) FROM '/path/to/wiki-articles-1w.json' CSV QUOTE E'\x01' DELIMITER E'\x02';

-- 创建wiki表
CREATE TABLE wiki (
    id SERIAL PRIMARY KEY,
    url TEXT,
    title TEXT,
    body TEXT
);

-- 解析JSONB数据，并导入到wiki表
INSERT INTO wiki(url, title, body)
SELECT values->>'url' AS url,
       values->>'title' AS title,
       values->>'body' AS body
FROM (SELECT data AS values FROM temp_json LIMIT 10000) A;

-- 创建BM25索引
CALL pgsearch.create_bm25(
    index_name => 'wiki_idx',
    table_name => 'wiki',
    text_fields => '{body: {}}'
);

-- 全文检索
SELECT * FROM wiki
ORDER BY body @@@ pgsearch.config('body:"artificial intelligence"')
LIMIT 20;