起源

之前做的很多項目都使用solr/elasticsearch作為全文檢索引擎，它們功能全面而強大，但是對于較小的項目而言，構(gòu)建和維護成本顯然過高，尤其是從關(guān)系數(shù)據(jù)庫/文檔數(shù)據(jù)庫到全文檢索引擎的數(shù)據(jù)同步工作非常繁瑣，且容易出錯。

記得很久以前就知道postgresql數(shù)據(jù)庫內(nèi)置全文檢索，最近發(fā)現(xiàn)這個數(shù)據(jù)庫越來越火，于是就又研究了一番，欣喜的發(fā)現(xiàn)居然支持ef core，于是對其進行了一些研究，并整理心得如下。

前提

本文假設(shè)讀者熟悉entity framework core的基本概念和基本使用。

目的

建立dotnet core項目，使用postgres數(shù)據(jù)庫和ef core，實現(xiàn)常見的全文檢索功能，包括

建立索引字段

基本查詢

查詢結(jié)果排名

查詢結(jié)果高亮顯示

步驟1 - 新建項目并引入packages

<Project Sdk="Microsoft.NET.Sdk"><PropertyGroup><OutputType>Exe</OutputType><TargetFramework>netcoreapp3.1</TargetFramework></PropertyGroup><ItemGroup><PackageReference Include="EFCore.NamingConventions" Version="1.1.0"/><PackageReference Include="Microsoft.Extensions.Logging.Console" Version="3.1.4"/><PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="3.1.3"/><PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="3.1.3"/></ItemGroup></Project>

注意NamingConventions包是可選的，其作用是將表和字段名稱翻譯成蛇形，如MyData -> my_data，這樣比較方便手寫sql，不用寫煩人的引號。

步驟2 - 建立model和dbcontext

using System.ComponentModel.DataAnnotations;using System.ComponentModel.DataAnnotations.Schema;using NpgsqlTypes;publicclass Article

{

? ? publicintId {get;set; }

? ? [Required]

? ? [MaxLength(128)]

? ? publicstringTitle {get;set; }

? ? [MaxLength(512)]

? ? publicstringAbst {get;set; }

? ? publicNpgsqlTsVector TitleVector {get;set; }

? ? publicNpgsqlTsVector AbstVector {get;set; }

? ? [NotMapped]

? ? publicstringTitleHL {get;set; }

? ? [NotMapped]

? ? publicstringAbstHL {get;set; }

}

本model中的TitleVector和AbstVector分別用來存放Title和Abst字段的分詞結(jié)果，便于后續(xù)的查詢。不必擔(dān)心代碼會不小心改掉這些字段以至于查詢出錯，因為后續(xù)會設(shè)置一個觸發(fā)器，每次更改數(shù)據(jù)的時候都會自動更新這些字段的內(nèi)容。

using Microsoft.EntityFrameworkCore;publicclass MyDbContext : DbContext

{

? ? protectedoverridevoidOnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder

? ? ? ? .UseNpgsql("Host=localhost;Database=ft;Username=postgres;Password=123456")

? ? ? ? .UseLoggerFactory(PgFtSearch.Program.MyLoggerFactory)

? ? ? ? .UseSnakeCaseNamingConvention();

? ? protectedoverridevoid OnModelCreating(ModelBuilder modelBuilder)

? ? {

? ? ? ? base.OnModelCreating(modelBuilder);

? ? ? ? modelBuilder.Entity().HasIndex(p => p.TitleVector).HasMethod("GIN");

? ? ? ? modelBuilder.Entity().HasIndex(p => p.AbstVector).HasMethod("GIN");

? ? }

? ? publicDbSet Articles {get;set; }

}

首先UseNpgsql設(shè)置了要連接哪個數(shù)據(jù)庫，然后UseLoggerFactory用來打印日志，主要是sql語句。MyLoggerFactory是怎么來的，參考后續(xù)的代碼。

GIN的兩行，用來告訴數(shù)據(jù)庫這兩個字段是采用倒排索引。

步驟3 - 生成migration并手動添加觸發(fā)器

dotnet ef migrations add Init

然后，在生成的migration文件中手動添加觸發(fā)器，在新增或者修改數(shù)據(jù)時，自動修改索引字段的內(nèi)容，應(yīng)用程序不必擔(dān)心索引同步的問題。

migrationBuilder.Sql(

? ? ? ? ? ? @"CREATE TRIGGER article_title_search_vector_update BEFORE INSERT OR UPDATE

? ? ? ? ? ? ? ON articles FOR EACH ROW EXECUTE PROCEDURE

? ? ? ? ? ? ? tsvector_update_trigger(title_vector, 'pg_catalog.english', title);");

migrationBuilder.Sql(

? ? ? ? ? ? @"CREATE TRIGGER article_abst_search_vector_update BEFORE INSERT OR UPDATE

? ? ? ? ? ? ? ON articles FOR EACH ROW EXECUTE PROCEDURE

? ? ? ? ? ? ? tsvector_update_trigger(abst_vector, 'pg_catalog.english', abst);");

步驟4 - 編寫程序

using System;using System.Collections.Generic;using System.Linq;using Microsoft.EntityFrameworkCore;using Microsoft.Extensions.Logging;namespace PgFtSearch

{

? ? class Program

? ? {

? ? ? ? publicstaticreadonly ILoggerFactory MyLoggerFactory

? ? ? ? ? ? = LoggerFactory.Create(builder => { builder.AddConsole(); });

? ? ? ? staticvoidMain(string[] args)

? ? ? ? {

? ? ? ? ? ? using(vardb =new MyDbContext())

? ? ? ? ? ? {

? ? ? ? ? ? ? ? if(!db.Articles.Any())

? ? ? ? ? ? ? ? {

? ? ? ? ? ? ? ? ? ? vararticles =newList{

? ? ? ? ? ? ? ? ? ? ? ? newArticle{Title="testing is ok", Abst="this is a test about postgre full text searching"},

? ? ? ? ? ? ? ? ? ? ? ? newArticle{Title="tested all bugs", Abst="there is no bug exists in this app"}

? ? ? ? ? ? ? ? ? ? };

? ? ? ? ? ? ? ? ? ? db.AddRange(articles);

? ? ? ? ? ? ? ? ? ? db.SaveChanges();

? ? ? ? ? ? ? ? }

? ? ? ? ? ? ? ? varquery ="test";

? ? ? ? ? ? ? ? vardata = db.Articles

? ? ? ? ? ? ? ? ? ? .Where(p => p.TitleVector.Matches(query) || p.AbstVector.Matches(query))

? ? ? ? ? ? ? ? ? ? .OrderByDescending(p=>p.TitleVector.Rank(EF.Functions.ToTsQuery(query)) *2.0+ p.AbstVector.Rank(EF.Functions.ToTsQuery(query)))

? ? ? ? ? ? ? ? ? ? .Select(p=>new Article{

? ? ? ? ? ? ? ? ? ? ? ? Title = p.Title,

? ? ? ? ? ? ? ? ? ? ? ? Abst = p.Abst,

? ? ? ? ? ? ? ? ? ? ? ? TitleHL = EF.Functions.ToTsQuery(query).GetResultHeadline(p.Title),

? ? ? ? ? ? ? ? ? ? ? ? AbstHL = EF.Functions.ToTsQuery(query).GetResultHeadline(p.Abst),

? ? ? ? ? ? ? ? ? ? });

? ? ? ? ? ? ? ? foreach(vararticlein data)

? ? ? ? ? ? ? ? {

? ? ? ? ? ? ? ? ? ? Console.WriteLine($"{article.Title}\t{article.Abst}\t{article.TitleHL}\t{article.AbstHL}");

? ? ? ? ? ? ? ? }

? ? ? ? ? ? }

? ? ? ? }

? ? }

}

首先，如果沒有數(shù)據(jù)，插入幾條測試數(shù)據(jù)。

下面到了最關(guān)鍵的地方，編寫數(shù)據(jù)查詢的代碼，實現(xiàn)的具體功能是：

使用test關(guān)鍵字在title或abst字段中查詢數(shù)據(jù)

對查詢結(jié)果進行排序，title字段排序權(quán)重=2.0，高于abst字段權(quán)重=1.0

檢索結(jié)果的title和abst進行高亮顯示

最終生成的SQL如下：

SELECT

a.titleAS"Title",

a.abstAS"Abst",

ts_headline(a.title, to_tsquery(@__query_0))AS"TitleHL",

ts_headline(a.abst, to_tsquery(@__query_0))AS "AbstHL"FROMarticlesAS aWHERE(a.title_vector @@ plainto_tsquery(@__query_0))OR(a.abst_vector @@ plainto_tsquery(@__query_0))ORDERBY(ts_rank(a.title_vector, to_tsquery(@__query_0))::doubleprecision*2.0)+ts_rank(a.abst_vector, to_tsquery(@__query_0))::doubleprecisionDESC

代碼在這兒，相信大家都能看懂，有問題歡迎交流。

總結(jié)

目前還未研究中文分詞的支持情況，也沒有測試性能。不過大致看來，完全可以在中小型項目中使用postgres數(shù)據(jù)庫的內(nèi)置全文檢索功能替代solr/es等搜索引擎，減少系統(tǒng)的復(fù)雜程度，提升全文檢索功能的穩(wěn)定性。