PostgreSQL 全文检索完全指南：从 LIKE 到中文分词，一篇就够了，Nuxt + Prisma 完整实战

domingo，2 de agosto de 2026📖 2791 Palabras⏱️ 10 min

从 1 万条到百万级数据，选对方案，搜索效率提升 10 倍

概述

PostgreSQL 作为功能最强大的开源关系型数据库，内置了丰富的全文检索能力。从最简单的 LIKE 模糊匹配，到专业的 tsvector 全文搜索，再到支持中文分词的 zhparser 扩展，PostgreSQL 覆盖了从个人博客到企业级应用的各类搜索场景。

选择哪种方案？这取决于你的数据量、语言类型和业务需求。本文逐一拆解每种方案的特点、适用场景和完整实现代码，助你做出最适合的技术选型。

一、LIKE / ILIKE：最简单的搜索

适用场景：数据量小于 1 万条，追求实现简单

这是最基础的字符串匹配方式，不需要任何额外配置。

优点：

实现极其简单
无需安装扩展
支持中文（直接匹配字符）

缺点：

数据量大时全表扫描，性能差
无法使用标准 B-Tree 索引（可用 pg_trgm 或 GIN 部分优化）
不支持相关性排序

SQL 示例

sql Copy

-- 区分大小写
SELECT * FROM posts WHERE title LIKE '%nuxt%';

-- 不区分大小写（推荐）
SELECT * FROM posts WHERE title ILIKE '%nuxt%';

-- 多字段搜索
SELECT * FROM posts 
WHERE title ILIKE '%nuxt%' 
   OR description ILIKE '%nuxt%' 
   OR content ILIKE '%nuxt%';

Prisma 实现

typescript Copy

await prisma.posts.findMany({
  where: {
    OR: [
      { title: { contains: 'nuxt', mode: 'insensitive' } },
      { description: { contains: 'nuxt', mode: 'insensitive' } },
      { content: { contains: 'nuxt', mode: 'insensitive' } },
    ]
  }
})

二、pg_trgm：模糊匹配与拼写纠错

适用场景：用户可能存在拼写错误、拼音输入场景

pg_trgm 是 PostgreSQL 的官方扩展，基于三元组（trigram）算法实现相似度匹配，非常适合处理用户输入不精确的场景。

优点：

支持模糊匹配，容错能力强
可以按相似度排序
适合拼音输入、拼写纠错

缺点：

需要单独安装扩展
GIN 索引占用空间较大

安装与配置

sql Copy

-- 1. 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 2. 创建索引（大幅提升性能）
CREATE INDEX posts_title_trgm_idx ON posts USING GIN (title gin_trgm_ops);
CREATE INDEX posts_content_trgm_idx ON posts USING GIN (content gin_trgm_ops);

SQL 示例

sql Copy

-- 相似度搜索，按匹配程度排序
SELECT 
  id, 
  title, 
  similarity(title, 'nuxt框架') as sim 
FROM posts 
WHERE title % 'nuxt框架'  -- % 表示相似度超过阈值
ORDER BY sim DESC 
LIMIT 20;

Prisma 实现（使用原始 SQL）

typescript Copy

await prisma.$queryRaw`
  SELECT id, title, similarity(title, ${searchTerm}) as sim
  FROM posts
  WHERE title % ${searchTerm}
  ORDER BY sim DESC
  LIMIT 20
`;

三、PostgreSQL 全文搜索（tsvector）

适用场景：英文或多语言搜索，对搜索质量有要求

这是 PostgreSQL 内置的专业全文搜索功能，通过将文本转换为 tsvector 向量，再与 tsquery 进行匹配，实现高效的关键词搜索。

优点：

性能优异，支持 GIN 索引加速
内置排名算法（ts_rank）
支持权重控制（标题权重 > 内容权重）
无需额外扩展，开箱即用

缺点：

中文支持需额外配置分词
配置相对复杂

核心函数速查

函数	作用
`to_tsvector()`	将文本转为 tsvector 向量
`to_tsquery()`	将查询词转为 tsquery（支持 &、\|、! 操作符）
`plainto_tsquery()`	将用户输入转为 tsquery，自动处理空格（推荐）
`ts_rank()`	计算相关性分数
`setweight()`	设置权重等级（A/B/C/D）
`coalesce()`	处理 NULL 值
`@@`	匹配操作符

安装与配置

sql Copy

-- 1. 添加搜索向量列
ALTER TABLE posts ADD COLUMN search_vector tsvector;

-- 2. 填充数据（设置权重：标题 A，描述 B，内容 C）
UPDATE posts SET search_vector = 
  setweight(to_tsvector('simple', coalesce(title, '')), 'A') ||
  setweight(to_tsvector('simple', coalesce(description, '')), 'B') ||
  setweight(to_tsvector('simple', coalesce(content, '')), 'C');

-- 3. 创建 GIN 索引
CREATE INDEX posts_search_idx ON posts USING GIN (search_vector);

-- 4. （可选）使用触发器自动更新
CREATE TRIGGER posts_search_vector_update
  BEFORE INSERT OR UPDATE ON posts
  FOR EACH ROW EXECUTE FUNCTION
  tsvector_update_trigger(search_vector, 'pg_catalog.simple', title, description, content);

SQL 示例

sql Copy

-- 基础搜索
SELECT * FROM posts 
WHERE search_vector @@ to_tsquery('simple', 'nuxt & framework');

-- 带排名排序
SELECT 
  id, 
  title, 
  ts_rank(search_vector, to_tsquery('simple', 'nuxt')) as rank 
FROM posts 
WHERE search_vector @@ to_tsquery('simple', 'nuxt') 
ORDER BY rank DESC;

-- 动态权重搜索（推荐，灵活调整字段权重）
SELECT 
  id, 
  title,
  ts_rank(
    setweight(to_tsvector('simple', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('simple', coalesce(description, '')), 'B') ||
    setweight(to_tsvector('simple', coalesce(content, '')), 'C'),
    plainto_tsquery('simple', '搜索关键词')
  ) as rank
FROM posts
WHERE 
  setweight(to_tsvector('simple', coalesce(title, '')), 'A') ||
  setweight(to_tsvector('simple', coalesce(description, '')), 'B') ||
  setweight(to_tsvector('simple', coalesce(content, '')), 'C')
  @@ plainto_tsquery('simple', '搜索关键词')
ORDER BY rank DESC;

Prisma 实现

typescript Copy

await prisma.$queryRaw`
  SELECT 
    id, title, description, excerpt, 
    ts_rank(search_vector, plainto_tsquery('simple', ${searchTerm})) as rank
  FROM posts
  WHERE search_vector @@ plainto_tsquery('simple', ${searchTerm})
  ORDER BY rank DESC
  LIMIT 20
`;

四、zhparser：中文分词搜索

适用场景：中文项目，需要精准的中文分词能力

zhparser 是 PostgreSQL 的中文分词扩展，基于 SCWS（简易中文分词系统），能够将中文句子切分成有意义的词语。

优点：

原生中文分词，搜索准确度高
支持自定义词典

缺点：

需要超级用户权限安装
部分云数据库（如 RDS）可能不支持
安装配置相对复杂

安装与配置

sql Copy

-- 1. 安装扩展（需要 superuser）
CREATE EXTENSION IF NOT EXISTS zhparser;

-- 2. 创建中文分词配置
CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);

-- 3. 映射词性（n:名词, v:动词, a:形容词, i:成语, e:叹词, l:习语）
ALTER TEXT SEARCH CONFIGURATION chinese 
  ADD MAPPING FOR n,v,a,i,e,l WITH simple;

-- 4. 创建索引
CREATE INDEX posts_content_chinese_idx 
  ON posts USING GIN (to_tsvector('chinese', content));

SQL 示例

sql Copy

-- 中文搜索
SELECT * FROM posts 
WHERE to_tsvector('chinese', content) 
  @@ to_tsquery('chinese', 'nuxt 框架');

五、混合搜索：兼顾精确与模糊（最佳实践）

适用场景：对搜索质量要求高，同时需要处理用户拼写错误

将全文搜索的精确性和 pg_trgm 的容错能力结合，取长补短。

优点：

精确匹配 + 模糊匹配双重保障
用户输入不精确时仍能返回结果

缺点：

SQL 较复杂
性能开销略大

SQL 示例

sql Copy

WITH fulltext AS (
  SELECT 
    id, title, 
    1.0 as match_type,
    ts_rank(search_vector, plainto_tsquery('simple', 'nuxt框架')) as score
  FROM posts
  WHERE search_vector @@ plainto_tsquery('simple', 'nuxt框架')
  LIMIT 20
),
fuzzy AS (
  SELECT 
    id, title, 
    0.5 as match_type,
    similarity(title, 'nuxt框架') as score
  FROM posts
  WHERE title % 'nuxt框架'
  LIMIT 20
)
SELECT * FROM fulltext
UNION ALL
SELECT * FROM fuzzy
ORDER BY score DESC, match_type DESC
LIMIT 20;

六、Nuxt + Prisma 完整实战

6.1 搜索服务层

typescript Copy

// server/services/posts.service.ts

// ========================
// 全文搜索（推荐）
// ========================
async searchFulltext(keyword: string, page: number = 1, pageSize: number = 20) {
  const skip = (page - 1) * pageSize;
  
  if (!keyword?.trim()) {
    return { data: [], total: 0 };
  }
  
  const results = await prisma.$queryRaw`
    SELECT 
      id, slug, title, description, excerpt, 
      category, tags, author, image, 
      published_at, created_at, updated_at,
      ts_rank(
        setweight(to_tsvector('simple', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('simple', coalesce(description, '')), 'B') ||
        setweight(to_tsvector('simple', coalesce(content, '')), 'C'),
        plainto_tsquery('simple', ${keyword})
      ) as rank
    FROM posts
    WHERE 
      setweight(to_tsvector('simple', coalesce(title, '')), 'A') ||
      setweight(to_tsvector('simple', coalesce(description, '')), 'B') ||
      setweight(to_tsvector('simple', coalesce(content, '')), 'C')
      @@ plainto_tsquery('simple', ${keyword})
    ORDER BY rank DESC
    LIMIT ${pageSize}
    OFFSET ${skip}
  `;
  
  const countResult = await prisma.$queryRaw`
    SELECT COUNT(*) as total
    FROM posts
    WHERE 
      setweight(to_tsvector('simple', coalesce(title, '')), 'A') ||
      setweight(to_tsvector('simple', coalesce(description, '')), 'B') ||
      setweight(to_tsvector('simple', coalesce(content, '')), 'C')
      @@ plainto_tsquery('simple', ${keyword})
  `;
  
  return { 
    data: results, 
    total: Number(countResult[0].total) 
  };
}

// ========================
// 简单搜索（ILIKE 方式）
// ========================
async searchSimple(keyword: string, page: number = 1, pageSize: number = 20) {
  const skip = (page - 1) * pageSize;
  
  const [data, total] = await Promise.all([
    prisma.posts.findMany({
      where: {
        OR: [
          { title: { contains: keyword, mode: 'insensitive' } },
          { description: { contains: keyword, mode: 'insensitive' } },
          { excerpt: { contains: keyword, mode: 'insensitive' } },
          { content: { contains: keyword, mode: 'insensitive' } },
          { author: { contains: keyword, mode: 'insensitive' } },
          { category: { contains: keyword, mode: 'insensitive' } },
        ]
      },
      skip,
      take: pageSize,
      orderBy: { createdAt: 'desc' },
      select: {
        id: true,
        slug: true,
        title: true,
        description: true,
        excerpt: true,
        category: true,
        tags: true,
        author: true,
        image: true,
        publishedAt: true,
        createdAt: true
      }
    }),
    prisma.posts.count({
      where: {
        OR: [
          { title: { contains: keyword, mode: 'insensitive' } },
          { description: { contains: keyword, mode: 'insensitive' } },
          { excerpt: { contains: keyword, mode: 'insensitive' } },
          { content: { contains: keyword, mode: 'insensitive' } },
          { author: { contains: keyword, mode: 'insensitive' } },
          { category: { contains: keyword, mode: 'insensitive' } },
        ]
      }
    })
  ]);
  
  return { 
    data, 
    total,
    page,
    pageSize,
    totalPages: Math.ceil(total / pageSize)
  };
}

6.2 API 端点

typescript Copy

// server/api/search/index.get.ts
import { postsService } from '../../services/posts.service'

export default defineEventHandler(async (event) => {
  const query = getQuery(event)
  const keyword = query.q as string
  const page = parseInt(query.page as string) || 1
  const pageSize = parseInt(query.pageSize as string) || 20
  const useFulltext = query.fulltext === 'true'
  
  if (!keyword?.trim()) {
    return { data: [], total: 0 }
  }
  
  try {
    if (useFulltext) {
      return await postsService.searchFulltext(keyword, page, pageSize)
    }
    return await postsService.searchSimple(keyword, page, pageSize)
  } catch (error) {
    console.error('搜索失败:', error)
    throw createError({ 
      status: 500, 
      message: '搜索失败，请稍后重试' 
    })
  }
})

6.3 前端搜索组件

vue Copy

<!-- components/SearchBar.vue -->
<template>
  <div class="search-container">
    <div class="search-input-wrapper">
      <input 
        v-model="keyword" 
        type="text" 
        placeholder="搜索文章..."
        @input="handleSearch"
        @keydown.enter="handleSearch"
        class="search-input"
      />
      <button @click="handleSearch" class="search-btn">搜索</button>
    </div>
    
    <!-- 加载状态 -->
    <div v-if="loading" class="loading">
      <span>搜索中...</span>
    </div>
    
    <!-- 空状态 -->
    <div v-else-if="results.length === 0 && keyword" class="empty">
      未找到与「{{ keyword }}」相关的文章
    </div>
    
    <!-- 搜索结果 -->
    <div v-else-if="results.length > 0" class="results">
      <div v-for="post in results" :key="post.id" class="result-item">
        <NuxtLink :to="`/posts/${post.slug}`" class="result-link">
          <h3 class="result-title">{{ post.title }}</h3>
          <p class="result-excerpt">{{ post.excerpt || post.description }}</p>
          <div class="result-meta">
            <span>{{ post.category }}</span>
            <span>{{ post.author }}</span>
            <span>{{ new Date(post.createdAt).toLocaleDateString() }}</span>
          </div>
        </NuxtLink>
      </div>
    </div>
  </div>
</template>

<script setup lang="ts">
import { ref, debounce } from 'vue'

const keyword = ref('')
const results = ref([])
const loading = ref(false)

// 防抖搜索，300ms 延迟
const handleSearch = debounce(async () => {
  if (!keyword.value.trim()) {
    results.value = []
    return
  }
  
  loading.value = true
  try {
    const { data } = await $fetch(`/api/search?q=${encodeURIComponent(keyword.value)}&fulltext=true`)
    results.value = data || []
  } catch (error) {
    console.error('搜索失败:', error)
  } finally {
    loading.value = false
  }
}, 300)
</script>

<style scoped>
.search-container {
  max-width: 800px;
  margin: 0 auto;
  padding: 20px;
}

.search-input-wrapper {
  display: flex;
  gap: 12px;
  margin-bottom: 20px;
}

.search-input {
  flex: 1;
  padding: 12px 16px;
  border: 2px solid #e0e0e0;
  border-radius: 8px;
  font-size: 16px;
  transition: border-color 0.2s;
}

.search-input:focus {
  outline: none;
  border-color: #4a9eff;
}

.search-btn {
  padding: 12px 24px;
  background: #4a9eff;
  color: white;
  border: none;
  border-radius: 8px;
  font-size: 16px;
  cursor: pointer;
  transition: background 0.2s;
}

.search-btn:hover {
  background: #3a8aef;
}

.results {
  display: flex;
  flex-direction: column;
  gap: 16px;
}

.result-item {
  padding: 16px;
  border: 1px solid #eee;
  border-radius: 8px;
  transition: box-shadow 0.2s;
}

.result-item:hover {
  box-shadow: 0 4px 12px rgba(0, 0, 0, 0.08);
}

.result-link {
  text-decoration: none;
  color: inherit;
}

.result-title {
  margin: 0 0 8px 0;
  color: #1a1a1a;
}

.result-excerpt {
  margin: 0 0 8px 0;
  color: #666;
  font-size: 14px;
  line-height: 1.6;
}

.result-meta {
  display: flex;
  gap: 16px;
  font-size: 12px;
  color: #999;
}

.loading,
.empty {
  text-align: center;
  padding: 40px;
  color: #999;
}
</style>

七、性能优化建议

优化策略	具体做法
索引	为搜索字段创建 GIN 索引（tsvector 或 pg_trgm）
限制结果数	使用 `LIMIT` 限制返回数量，避免大数据量传输
分页	使用 `OFFSET + LIMIT` 或游标分页
缓存	对热门搜索词使用 Redis 缓存，减少数据库压力
搜索权重	标题权重 > 描述权重 > 内容权重，提升相关性
索引覆盖	只搜索必要字段，减少 I/O 开销
VACUUM	定期执行 `VACUUM ANALYZE`，更新统计信息

八、各方案对比一览

方案	数据量	中文支持	性能	复杂度	适用场景
ILIKE	< 1 万	✅	⭐⭐	⭐	简单搜索、小数据量
pg_trgm	< 10 万	✅	⭐⭐⭐	⭐⭐	拼写错误、拼音搜索
全文搜索 (tsvector)	< 100 万	⚠️ 需配置	⭐⭐⭐⭐	⭐⭐⭐	英文/多语言搜索
中文分词 (zhparser)	< 100 万	✅ 原生	⭐⭐⭐⭐	⭐⭐⭐⭐	中文搜索
Elasticsearch	百万级以上	✅	⭐⭐⭐⭐⭐	⭐⭐⭐⭐⭐	大规模搜索引擎

九、总结与选型建议

根据你的项目情况，选择合适的方案：

你的场景	推荐方案
个人博客、小型项目	ILIKE + Prisma contains，简单够用
一般项目、英文为主	PostgreSQL 全文搜索 (tsvector)，性能和功能均衡
中文项目、数据量不大	ILIKE 或 pg_trgm，无需复杂配置
中文项目、数据量大	中文分词 (zhparser) + 全文搜索，精准高效
大规模搜索（百万级+）	Elasticsearch / Meilisearch，专业搜索引擎

我的个人建议

对于大多数技术博客、内容型网站，PostgreSQL 全文搜索（tsvector） 是性价比最高的选择。它不需要额外引入 Elasticsearch 这样的重型组件，就能提供 80% 以上的搜索体验。配合 pg_trgm 做拼写容错，完全可以满足日常需求。

如果未来数据量增长到百万级以上，再考虑迁移到 Elasticsearch 也不迟——PostgreSQL 作为数据主库，配合 ES 做搜索增强，是很多大厂的标准架构。

#PostgreSQL #全文检索 #数据库 #性能优化 #中文分词 #pg_trgm #zhparser #Nuxt #Prisma

← Volver al inicio