起源

之前做的很多项目都使用solr/elasticsearch作为全文检索引擎,它们功能全面而强大,但是对于较小的项目而言,构建和维护成本显然过高,尤其是从关系数据库/文档数据库到全文检索引擎的数据同步工作非常繁琐,且容易出错。

记得很久以前就知道postgresql数据库内置全文检索,最近发现这个数据库越来越火,于是就又研究了一番,欣喜的发现居然支持ef
core,于是对其进行了一些研究,并整理心得如下。

前提

本文假设读者熟悉entity framework core的基本概念和基本使用。

目的

建立dotnet core项目,使用postgres数据库和ef core,实现常见的全文检索功能,包括

* 建立索引字段
* 基本查询
* 查询结果排名
* 查询结果高亮显示
步骤1 - 新建项目并引入packages
<Project Sdk="Microsoft.NET.Sdk"> <PropertyGroup> <OutputType>Exe</OutputType>
<TargetFramework>netcoreapp3.1</TargetFramework> </PropertyGroup> <ItemGroup> <
PackageReferenceInclude="EFCore.NamingConventions" Version="1.1.0" /> <
PackageReferenceInclude="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; public class
Article {public int Id { get; set; } [Required] [MaxLength(128)] public string
Title {get; set; } [MaxLength(512)] public string Abst { get; set; } public
NpgsqlTsVector TitleVector {get; set; } public NpgsqlTsVector AbstVector { get;
set; } [NotMapped] public string TitleHL { get; set; } [NotMapped] public string
AbstHL {get; set; } }

本model中的TitleVector和AbstVector分别用来存放Title和Abst字段的分词结果,便于后续的查询。不必担心代码会不小心改掉这些字段以至于查询出错,因为后续会设置一个触发器,每次更改数据的时候都会自动更新这些字段的内容。
using Microsoft.EntityFrameworkCore; public class MyDbContext : DbContext {
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) =>
optionsBuilder .UseNpgsql("
Host=localhost;Database=ft;Username=postgres;Password=123456")
.UseLoggerFactory(PgFtSearch.Program.MyLoggerFactory)
.UseSnakeCaseNamingConvention();protected override void
OnModelCreating(ModelBuilder modelBuilder) {base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Article>().HasIndex(p => p.TitleVector).HasMethod("GIN");
modelBuilder.Entity<Article>().HasIndex(p => p.AbstVector).HasMethod("GIN"); }
public DbSet<Article> Articles { get; set; } }

首先UseNpgsql设置了要连接哪个数据库,然后UseLoggerFactory用来打印日志,主要是sql语句。MyLoggerFactory是怎么来的,参考后续的代码。

GIN的两行,用来告诉数据库这两个字段是采用倒排索引。

步骤3 - 生成migration并手动添加触发器

dotnet ef migrations add Init

然后,在生成的migration文件中手动添加触发器,在新增或者修改数据时,自动修改索引字段的内容,应用程序不必担心索引同步的问题。
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 { public static readonly ILoggerFactory
MyLoggerFactory= LoggerFactory.Create(builder => { builder.AddConsole(); });
static void Main(string[] args) { using (var db = new MyDbContext()) { if (!
db.Articles.Any()) {var articles = new List<Article>{ new Article{Title="
testing is ok", Abst="this is a test about postgre full text searching"}, new
Article{Title="tested all bugs", Abst="there is no bug exists in this app"} };
db.AddRange(articles); db.SaveChanges(); }var query = "test"; var data =
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 (var article
in data) { Console.WriteLine($"
{article.Title}\t{article.Abst}\t{article.TitleHL}\t{article.AbstHL}"); } } } }
}
首先,如果没有数据,插入几条测试数据。

下面到了最关键的地方,编写数据查询的代码,实现的具体功能是:

* 使用test关键字在title或abst字段中查询数据
* 对查询结果进行排序,title字段排序权重=2.0,高于abst字段权重=1.0
* 检索结果的title和abst进行高亮显示
最终生成的SQL如下:
SELECT
  a.title AS "Title",
  a.abst AS "Abst",
  ts_headline(a.title, to_tsquery(@__query_0)) AS "TitleHL",
  ts_headline(a.abst, to_tsquery(@__query_0)) AS "AbstHL" FROM articles AS a
WHERE (a.title_vector @@ plainto_tsquery(@__query_0)) OR (a.abst_vector @@
plainto_tsquery(@__query_0)) ORDER BY (ts_rank(a.title_vector, to_tsquery(
@__query_0))::double precision * 2.0) + ts_rank(a.abst_vector, to_tsquery(
@__query_0))::double precision DESC
代码在这儿,相信大家都能看懂,有问题欢迎交流。

总结

目前还未研究中文分词的支持情况,也没有测试性能。不过大致看来,完全可以在中小型项目中使用postgres数据库的内置全文检索功能替代solr/es等搜索引擎,减少系统的复杂程度,提升全文检索功能的稳定性。

技术
©2019-2020 Toolsou All rights reserved,
vue项目中对axios的全局封装单个按键控制多种流水灯状态软件测试之BUG描述随机森林篇 R语言实现TP6验证器的使用示例及正确验证数据C语言编程之查找某学号学生成绩一文揭秘阿里、腾讯、百度的薪资职级c语言的5种常用排序方法2021年1月程序员工资统计,平均14915元Bug数能否做为技术人员考核的KPI?