基于Mybatis实现PG文本搜索的更优实现

最新推荐文章于 2024-11-26 11:16:27 发布

mania_yan

最新推荐文章于 2024-11-26 11:16:27 发布

阅读量1.2k

点赞数 24

分类专栏：搜索文章标签： mybatis postgresql 搜索

本文链接：https://blog.youkuaiyun.com/yyw794/article/details/134673789

版权

搜索专栏收录该内容

11 篇文章

订阅专栏

基本方案

Mybatis是目前使用最广泛的数据库框架，它实现了通用性。

但对于不同数据库一些差异化的能力，不能原生的支持，所以，需要使用SQL方式来实现。

通过mapper，将java函数和sql进行映射，这样，执行java搜索函数时，实际执行的是PG对应的SQL。

代码实现

Mapper

负责将java函数和SQL进行映射，其中，SQL由另一个SQL Builder的类负责动态生成SQL语句（根据java函数的入参，动态生成包含实际入参的SQL）。

@Mapper
public interface SparringWorkflowVectorCustomizedMapper {
    @UpdateProvider(value = WorkflowVectorSqlBuilder.class, method = "updateWorkflowVector")
    void updateWorkflow(String id, String workflowName, String workflowDesc);

    @TimeIt
    @SelectProvider(value = WorkflowVectorSqlBuilder.class, method = "searchWorkflowVector")
    List<SparringWorkflowEntity> searchWorkflow(String query, String workflowType);
}

SQL Builder（重点）

核心：学习使用PG的Text Search的SQL关键词实现文本搜索。

public class WorkflowVectorSqlBuilder {
/*
需要根据名称和描述进行搜索，为什么不能将名称和描述分别放到2列，而是把2部分的内容合入到一列中。
实际测试发现，在select时，将两部分的vector拼接，name_vector || ' ' || desc_vector，将无法使用倒排索引优势。
select时间随着行数的增加而线性增加。因此，目前只能将其在insert阶段，就直接合并，这样select时才能使用到倒排索引。
coalesce的作用是防止列值为NULL导致整个拼接出错，这个函数让列为NULL时，自动转为空字符串。
to_tsvector相比tsvector会做归一化，虽然对中文无效，但是对英文有效果。
setweight负责设置权重，不同列的内容的重要性通常是不一样的，良好的权重设置会让搜索后的排序效果更佳。
 */
    public static String updateWorkflowVector(final String id , final String workflowName, final String workflowDesc){
        return new SQL(){{
            UPDATE("sparring_workflow");
            SET("workflow_name_vector = to_tsvector(coalesce(#{workflowName} ,''))");
            SET("workflow_vector = setweight(to_tsvector(coalesce(#{workflowName} ,'')), 'A') || setweight(to_tsvector(coalesce(#{workflowDesc} ,'')), 'C')");
            WHERE("id = #{id}");
        }}.toString();
    }

/*
websearch_to_tsquery是web搜索的场景，可以支持 or - ""的常见搜索语法格式

@@和ES的MATCH含义一样，代表怎样的query去哪里的vector中去搜索

排序采用cover density ranking,在输入只有1-3个词的时候，效果比TF-IDF好
ts_rank_cd的cd就是cover density，包含的输入词越多，得分越高
参考论文：Relevance ranking for one to three term queries
rank函数的第三个参数代表考虑文档长度的方式，设置为1，相当于TF-IDF的公式
 */
    public static String searchWorkflowVector(final String query, final String workflowType){
        return new SQL(){{
            SELECT(" * ");
            FROM("sparring_workflow, websearch_to_tsquery(#{query}) search, ts_rank_cd(workflow_vector, search, 1) rank");
            WHERE("workflow_vector @@ search");
            WHERE("delete_flag = 0");
            if(!Strings.isBlank(workflowType)){
                WHERE("workflow_type = #{workflowType}");
            }
            ORDER_BY("rank DESC");
        }}.toString();
    }
}