0Pricing
SQL Academy · 课时

三元组搜索（pg_trgm）与模糊匹配

使用 pg_trgm 扩展、GiST/GIN 三元组索引和 similarity() 查找相似字符串

三元组搜索（pg_trgm）与模糊匹配 是 CoddyKit 上的免费 SQL Academy 课时。 这是第 1 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

为什么使用三元组

LIKE %word% 无法使用 B-tree 索引。三元组（“3 个字符的子串”）结合 GIN/GiST 后，可以实现快速的模糊搜索和子串搜索。

启用扩展

pg_trgm 随 PostgreSQL contrib 一起提供：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

单词的三元组

显示字符串的三元组：

SELECT show_trgm('database');
-- {dat, aba, atab, tab, abas, base, ase, ...}

相似度函数

范围为 [0..1] 的浮点值：

SELECT similarity('database', 'datbase');     -- ~0.75
SELECT similarity('hello', 'world');             -- ~0.0

% 运算符

如果相似度高于阈值（默认值为 0.3），则返回 true：

SELECT 'database' % 'datbase';      -- t
SET pg_trgm.similarity_threshold = 0.4;

用于子串搜索的索引

GIN 三元组索引可以加速 LIKE '%word%'：

CREATE INDEX users_name_trgm ON users USING GIN (full_name gin_trgm_ops);

-- Now this is index-supported:
SELECT * FROM users WHERE full_name ILIKE '%ali%';

GiST 与 GIN

  • GIN——读取更快，写入更慢，索引更大
  • GiST——写入更快，读取更慢，索引更小

大多数团队会为模糊搜索选择 GIN。

模糊匹配查询

即使存在拼写错误，也能找到相似的字符串：

SELECT full_name, similarity(full_name, 'datbase') AS sim
FROM users
WHERE full_name % 'datbase'
ORDER BY sim DESC
LIMIT 10;

用于排序的距离

<-> 运算符返回距离（1 - 相似度），因此您可以按 distance ASC 排序：

SELECT full_name
FROM users
WHERE full_name % 'alice'
ORDER BY full_name <-> 'alice'
LIMIT 10;

使用三元组实现自动补全

将 pg_trgm 与 LIKE 前缀结合起来，实现响应迅速的自动补全：

SELECT name
FROM products
WHERE name % $1
   OR name ILIKE $1 || '%'
ORDER BY similarity(name, $1) DESC
LIMIT 8;

三元组的局限性

三元组基于输入内容所使用的字符集工作。CJK 语言需要采用不同的分词方式。若要进行完整的自然语言搜索，请使用 tsvector + GIN。

与其他索引结合

包含多个过滤条件的 query 可以通过 Bitmap And 同时使用三元组索引和 B-tree：

EXPLAIN ANALYZE
SELECT * FROM users
WHERE full_name ILIKE '%ali%' AND country = 'US';

总结

pg_trgm 解决了“前导通配符”问题。

  • GIN 三元组索引 → 快速执行 LIKE %x%
  • % 运算符和 similarity() 用于模糊匹配
  • <-> 距离用于排序
  • 非常适合自动补全和容错拼写搜索

快速检查

为什么普通的 B-tree 索引无法帮助处理 WHERE name LIKE '%alice%'？

常见问题解答

「三元组搜索（pg_trgm）与模糊匹配」课时是免费的吗？

是的 — 「三元组搜索（pg_trgm）与模糊匹配」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。

「三元组搜索（pg_trgm）与模糊匹配」这节课中我会学到什么？

使用 pg_trgm 扩展、GiST/GIN 三元组索引和 similarity() 查找相似字符串 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 1 节课，共 4 节。

「三元组搜索（pg_trgm）与模糊匹配」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Academy 课中编写并运行代码吗？

能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

  1. 三元组搜索（pg_trgm）与模糊匹配
  2. 使用 tsvector 和 GIN 进行全文搜索
  3. 使用 PostGIS 进行地理空间索引
  4. 使用 pgvector 进行向量搜索
← 返回 SQL Academy