关联表中品牌与产品名称的多词搜索实现及性能咨询

问题背景 #

我有一对多关联的Brand表和Product表，Product表包含brandId字段，需要实现同时搜索Product名称和Brand名称的功能。

示例数据 #

Samsung - Galaxy S20 Smartphone
Samsung - Galaxy Tab 7 Tablet
Apple - iPhone 14 Pro Max

期望查询效果 #

• 查询词为Samsung时，返回两个三星产品；
• 查询词为Sams Gal Smartph时，返回三星Galaxy S20 Smartphone。

现有Prisma模型 #

model Product {
  id                String          @id @default(cuid())
  name              String
  brand             Brand           @relation(fields: [brandId], references: [id])
  brandId           Int
  // ...
}

model Brand {
  id        Int       @id @default(autoincrement())
  name      String    @unique @db.Citext
  products  Product[]
  // ...
}

现有查询实现 #

const queryWords = query
  .replace(/[&\/\\#,+()$~%.'"*?<>{}]/g, "")
  .split(" ");
await prisma.product.findMany({
  where: {
    AND: queryWords.map((word) => {
      return {
        OR: [
          {
            name: {
              contains: word,
              mode: "insensitive",
            },
          },
          {
            brand: {
              name: {
                contains: word,
                mode: "insensitive",
              },
            },
          },
        ],
      };
    }),
  },
  include: {
    brand: true,
  },
});

疑问 #

当前实现是否高效？是否应该改用全文搜索？

解答 #

现有实现的性能分析 #

• 小数据量场景：如果产品和品牌数据量在几千条以内，当前实现完全够用，逻辑清晰且能满足需求。
• 大数据量场景：存在明显性能瓶颈：
  • contains模糊查询无法利用数据库索引，会触发全表扫描，数据量上万后查询速度会急剧下降；
  • 每个查询词都会生成跨表OR条件，多次关联Brand表会增加数据库计算开销；
  • 字符串处理逻辑未过滤空字符串，可能生成无效查询条件，浪费资源。

是否需要改用全文搜索？ #

当数据量达到上万条以上，或需要更精准的搜索体验（如分词、权重排序、智能匹配）时，必须改用全文搜索。

全文搜索的优势

• 数据库层面的全文索引可避免全表扫描，大幅提升查询速度；
• 支持智能分词、同义词匹配、权重排序（比如让品牌名称匹配的结果优先级更高）；
• 能处理更复杂的查询场景，比如短语匹配、部分模糊匹配等。

基于Prisma的全文搜索实现思路

1. 数据库创建全文索引：
   • PostgreSQL：给Product.name和Brand.name创建全文索引，或生成包含品牌+产品名称的虚拟字段来建立联合索引；
   • MySQL：使用FULLTEXT索引关联目标字段。
2. Prisma查询适配：
   • PostgreSQL：可通过prisma.$queryRaw结合to_tsvector/to_tsquery语法实现，或在模型中配置全文索引后使用search字段；
   • MySQL：直接使用match...against语法，或配合contains调用全文索引。

过渡优化方案（暂不切换全文搜索） #

如果暂时不想重构为全文搜索，可先做以下优化：

• 给Product.name和Brand.name添加普通索引，若业务允许将查询改为前缀匹配（用startsWith），可直接利用索引提速；
• 优化查询词处理逻辑，过滤拆分后的空字符串，避免生成无效AND条件；
• 将品牌名称冗余到Product表（新增brandName字段，同步更新），查询时无需关联Brand表，减少跨表开销。

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