需求:
搜索由三个可搜索字段、标题和描述(使用全文搜索)和文档 ID 组成的文档,能够查找包含文档 ID 的字符串。
搜索应该在不超过 200 毫秒的时间内运行超过 100 万个文档。
PostgreSQL 支持全文搜索。全文索引允许对文档进行预处理并保存索引以供以后快速搜索。
-- Create the Documents index table CREATE TABLE IF NOT EXISTS index."documents_index" ( "id" SERIAL, "created_on" bigint NOT NULL, "updated_on" bigint NOT NULL, "customer_id" character varying(150) NOT NULL, "document_id" character varying(255) NOT NULL, "document_type" character varying(50) NOT NULL, "document_title" text, "document_description" text, "words" text, "ts" tsvector GENERATED ALWAYS AS (to_tsvector('english',document_id || ' ' || document_title || ' ' || document_description)) STORED, "metadata" jsonb, CONSTRAINT "documents_index.primary_key" PRIMARY KEY ("customer_id", "document_id", "document_type"))
-- Create GIN index on the ts (tsvector) column to improve search. CREATE INDEX IF NOT EXISTS documents_index_ts_idx ON index."documents_index" USING GIN (ts);
-- Create Trigram Index on words column. (Requires installing pg_trgm Postgres extension) CREATE INDEX IF NOT EXISTS documents_index_trgm_idx on index."documents_index" USING GIN ("words" gin_trgm_ops);
|
- 我们在表中添加了一个新的列ts,以存储预处理的搜索文件(即词库列表)。ts是一个生成的列(Postgres 12的新列),自动与源数据同步。然后我们在ts列上创建了一个tsvector类型的GIN索引。
- 为了实现模糊搜索,我们使用了pg_trgm Postgres扩展,并在表中添加了一个word列来存储可搜索文本。该列存储的是可搜索字段的连接字符串。
- 最后,pg_trgm扩展提供了GiST和GIN索引操作符类。该索引允许我们在单词文本列上创建索引,以便进行快速的相似性搜索。
import {BeforeInsert, BeforeUpdate, Column, Entity, Generated, PrimaryColumn} from 'typeorm';
@Entity({name: 'index', schema: 'documents_index'}) export class Document {
@PrimaryColumn({name: 'id', nullable: false}) @Generated('increment') id: number;
@Column({name: 'created_on', nullable: false}) createdOn: number;
@Column({name: 'updated_on', nullable: false}) updatedOn: number; @Column({name: 'customer_id', nullable: false}) zoneId: string;
@Column({name: 'document_id', nullable: false}) documentId: string;
@Column({name: 'document_type', nullable: false}) documentType: string;
@Column({name: 'document_title', nullable: false}) documentTitle: string;
@Column({name: 'document_description', nullable: false}) documentDescription: string;
@Column({name: 'words', type: 'text'}) words: string;
@BeforeInsert() @BeforeUpdate() async calculateWords() { const fullText = this.documentId + ' ' + this.documentTitle + ' ' + this.documentDescription; const unique = Array.from(new Set(fullText.split(' '))); this.words = unique.join(' '); }
@Column({name: 'metadata', type: 'jsonb'}) metadata: any;
@Column({name: 'ts', type: 'tsvector'}) tsVector: any;
}
|
我们测试了下面的查询,它使用pgbench工具返回 436 行:
SELECT id, document_id, document_title, document_description, COALESCE(similarity(words, 'management system'),0) + COALESCE(ts_rank_cd(ts, 'management & system'),0) as relevancy FROM "index"."documents_index" WHERE customer_id = '1' AND ( ts @@ to_tsquery('english', 'management & system') -- OR words ILIKE '%management system%' ) ORDER BY relevancy DESC, id ASC
|
我们能够实现每秒约 170 笔交易。
大量数据存储在数据库中,性能和扩展会随着数据的增长而受到影响。分区通过将大表分成较小的表来解决这个问题,减少内存交换问题和表扫描,并提高性能。
-- Create the Documents index table CREATE TABLE IF NOT EXISTS index."documents_index" ( "id" SERIAL, "created_on" bigint NOT NULL, "updated_on" bigint NOT NULL, "customer_id" character varying(150) NOT NULL, "document_id" character varying(255) NOT NULL, "document_type" character varying(50) NOT NULL, "document_title" text, "document_description" text, "words" text, "ts" tsvector GENERATED ALWAYS AS (to_tsvector('english',document_id || ' ' || document_title || ' ' || document_description)) STORED, "metadata" jsonb, CONSTRAINT "documents_index.primary_key" PRIMARY KEY ("customer_id", "document_id", "document_type")) PARTITION by HASH("customer_id")
-- Create GIN index on the ts (tsvector) column to improve search. CREATE INDEX IF NOT EXISTS documents_index_ts_idx ON index."documents_index" USING GIN (ts);
-- Create Trigram Index on words column. (Requires installing pg_trgm Postgres extension) CREATE INDEX IF NOT EXISTS documents_index_trgm_idx on index."documents_index" USING GIN ("words" gin_trgm_ops);
CREATE TABLE IF NOT EXISTS index."documents_index_part_1" partition of index."documents_index_part" for values with (MODULUS 10, REMAINDER 0); CREATE TABLE IF NOT EXISTS index."documents_index_part_2" partition of index."documents_index_part" for values with (MODULUS 10, REMAINDER 1); CREATE TABLE IF NOT EXISTS index."documents_index_part_3" partition of index."documents_index_part" for values with (MODULUS 10, REMAINDER 2); CREATE TABLE IF NOT EXISTS index."documents_index_part_4" partition of index."documents_index_part" for values with (MODULUS 10, REMAINDER 3); CREATE TABLE IF NOT EXISTS index."documents_index_part_5" partition of index."documents_index_part" for values with (MODULUS 10, REMAINDER 4); CREATE TABLE IF NOT EXISTS index."documents_index_part_6" partition of index."documents_index_part" for values with (MODULUS 10, REMAINDER 5); CREATE TABLE IF NOT EXISTS index."documents_index_part_7" partition of index."documents_index_part" for values with (MODULUS 10, REMAINDER 6); CREATE TABLE IF NOT EXISTS index."documents_index_part_8" partition of index."documents_index_part" for values with (MODULUS 10, REMAINDER 7); CREATE TABLE IF NOT EXISTS index."documents_index_part_9" partition of index."documents_index_part" for values with (MODULUS 10, REMAINDER 8); CREATE TABLE IF NOT EXISTS index."documents_index_part_10" partition of index."documents_index_part" for values with (MODULUS 10, REMAINDER 9);
|
对 Index Storage 表进行分区后,我们实现了近 60% 的查询性能提升。
术语
1、词干化Stemming
这是一个将一个词还原为其词干的过程,该词干与后缀和前缀或词根相连,被称为词根,以确保该词的变体在搜索中与结果相匹配。例如,管理、经理、管理可以从一个词Manag中提取词干,在搜索manag这个词时,将返回具有这个词的任何变体的结果。在线词干工具
2、词干NGram
它就像一个在单词上移动的滑动窗口--一个连续的字符序列,直到指定的长度。例如,单词将变成{'w', 'wo, 'wor', 'ord', 'rd'}。NGram可以用来搜索一个词的各个部分,甚至从中间搜索。最常用的NGram类型是Trigram 。
3、模糊性
模糊性 "指的是在比较两个字符串时,解决方案不寻求完美的、逐个位置的匹配。相反,它们允许一些不匹配(或'模糊性')。例如,对succesful这个词的搜索也会返回有success的结果。常见的应用包括拼写检查和垃圾邮件过滤。
4、相似性
两个词的相似性可以通过计算它们共有的卦数来衡量。这个简单的想法对于测量许多自然语言中单词的相似性非常有效。
5、排名
排名试图衡量文档与特定查询的相关程度,这样当有许多匹配时,最相关的文档可以被首先显示出来 Postgres支持排名和加权排名。通常情况下,加权是用来标记文档的特殊区域的词,如标题或最初的摘要,以便它们可以比文档正文中的词有更多或更少的重要性。