PostgreSQL中基于子串数组的多关键词模糊查询方案咨询

表结构说明 #

Product 表 #

Manufacture 表 #

需求概述 #

用户从移动端输入的搜索串是产品名、厂商名的任意组合（无固定分隔符，支持部分内容匹配），需要精准返回匹配的产品条目，示例：

• 输入 Kyoce ecos → 返回 ECOSYS M2640idw
• 输入 kyocera mi ecosys → 返回 ECOSYS M2640idw
• 输入 ibm → 返回 Infoprint 1140

最优查询方案 #

1. 小数据量场景：直接模糊匹配 #

如果数据库中产品和厂商数据量不大，用这种简单的拼接匹配即可满足需求：

SELECT p.Name
FROM Product p
JOIN Manufacture m ON p.ManufactureId = m.Id
-- 拆分搜索词，每个词必须匹配产品名或厂商名的部分内容
WHERE EXISTS (
  SELECT 1
  FROM unnest(string_to_array(:search_str, ' ')) AS term
  WHERE p.Name ILIKE '%' || term || '%' OR m.Name ILIKE '%' || term || '%'
)
GROUP BY p.Id, p.Name
-- 确保所有搜索词都能找到匹配项
HAVING COUNT(DISTINCT term) = (SELECT COUNT(DISTINCT term) FROM unnest(string_to_array(:search_str, ' ')) AS term);

2. 大数据量场景：全文检索（性能优先） #

当数据量较大时，LIKE模糊匹配的性能会急剧下降，PostgreSQL的全文检索是更优选择，步骤如下：

第一步：创建全文检索索引

先为产品名+厂商名的组合创建GIN索引（GIN索引对全文检索的支持效率极高）：

-- 创建索引：将产品名和厂商名拼接后转换为全文检索向量
CREATE INDEX idx_product_manufacture_search ON Product 
USING GIN (to_tsvector('english', Name || ' ' || (SELECT Name FROM Manufacture WHERE Id = ManufactureId)));

第二步：查询语句

将用户输入的搜索串转换为全文查询条件，实现多词匹配：

SELECT p.Name
FROM Product p
JOIN Manufacture m ON p.ManufactureId = m.Id
WHERE to_tsvector('english', p.Name || ' ' || m.Name) @@ plainto_tsquery('english', :search_str);

进阶：支持前缀匹配

如果需要支持部分词前缀匹配（比如输入kyoc也能匹配Kyocera Mita），可以自定义转换搜索词：

SELECT p.Name
FROM Product p
JOIN Manufacture m ON p.ManufactureId = m.Id
WHERE to_tsvector('english', p.Name || ' ' || m.Name) @@ to_tsquery(
  'english', 
  array_to_string(
    array_agg(unnest(string_to_array(:search_str, ' ')) || ':*'), 
    ' & '
  )
);

内容的提问来源于stack exchange，提问作者3axap