KEEL · 龙骨 · A CURRICULUM FOR THE AI ERA

06 · 选型与边界 — keel 龙骨

## 现场：两个都「能跑」的方案

现场：两个都「能跑」的方案

搜索框要支持商品名模糊匹配，商品表有 12 万行。代码写的是：

SELECT * FROM t_prod WHERE name LIKE '%关键词%';

它在测试库上跑得挺好（几千行数据），上生产后变成每次全表扫描。

另一个场景是上一章留下的问题：事件表把业务字段全塞进了 jsonb，现在要做多条件统计。

这两个场景的共同点是：PostgreSQL 都有对应的解法，但解法的代价和适用边界不一样。这一章把两类方案的边界划出来，顺带回答一个更根本的问题——什么时候该承认 PG 不是对的工具。

先猜一下：给 name 建一个 pg_trgm 的 GIN 索引之后，LIKE '%关键词%' 会走索引。那么当关键词是中文时，它还会走吗？

pg_trgm：模糊匹配的索引方案

普通 B-tree 索引对 LIKE 'abc%'（前缀匹配）有效——它能把前缀当成有序的键来定位。对 LIKE '%abc%'（包含匹配）无效，因为索引的键是按前缀排序的，中间的子串没有有序位置。

先看这三种查询在没有额外索引时的计划：

-- (a) 高选择率：全部 12 万行都含 widget，全表扫本来就对
 Aggregate (actual rows=1 loops=1)
   ->  Seq Scan on t_prod (actual rows=120000 loops=1)
         Filter: (name ~~ '%widget%'::text)
         Rows Removed by Filter: 3

-- (b) 低选择率：只有 3 行含「阀」，仍然全表扫 —— 这才是要解决的问题
 Aggregate (actual rows=1 loops=1)
   ->  Seq Scan on t_prod (actual rows=3 loops=1)
         Filter: (name ~~ '%阀%'::text)
         Rows Removed by Filter: 120000

-- (c) 前缀匹配也没有索引可用（name 上原本没有 B-tree）
 Aggregate (actual rows=1 loops=1)
   ->  Seq Scan on t_prod (actual rows=31112 loops=1)
         Filter: (name ~~ 'product-1%'::text)
         Rows Removed by Filter: 88891

前两个查询要分开看。(a) 匹配了全部 12 万行，全表扫描是正确的选择——反正是要读每一行。(c) 那个前缀匹配扫了 12 万行只为了找 3 万行，是因为 name 上没有 B-tree 索引（只有一个主键在 id 上）。

(b) 是真正的病：12 万行里只有 3 行匹配，还是得全扫。

pg_trgm 的思路是把字符串切成三元组（trigram），为每个三元组建倒排索引。这样 LIKE '%keyword%' 可以被拆成「必须含 key、eyw、ywo…这些三元组」的查找。

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_prod_name_trgm ON t_prod USING gin (name gin_trgm_ops);
ANALYZE t_prod;

建完之后，一个低选择率的包含查询：

 Aggregate (actual rows=1 loops=1)
   ->  Bitmap Heap Scan on t_prod (actual rows=1 loops=1)
         Recheck Cond: (name ~~ '%product-119999-widget%'::text)
         Rows Removed by Index Recheck: 2
         Heap Blocks: exact=3
         ->  Bitmap Index Scan on idx_prod_name_trgm (actual rows=3 loops=1)
               Index Cond: (name ~~ '%product-119999-widget%'::text)

从全表扫描变成位图索引扫描，Heap Blocks: exact=3 说明最终只碰了 3 个数据页。代价是索引本身的体积：

 trgm_index_size | table_size 
-----------------+------------
 3728 kB         | 7064 kB

索引是表的一半。trigram 索引比 B-tree 大得多，因为一个字符串会被拆成很多三元组，每条都要入库。这是模糊匹配的固有成本：你要为「任意子串可查」这件事付出一份接近全表大小的索引。

注意计划里的 Rows Removed by Index Recheck: 2。GIN 索引给出的是候选集，可能包含假阳性（三元组匹配了但完整字符串不匹配），所以还要拿原始值 Recheck 一遍。这是 bitmap scan 的常规行为。

汉字为什么没走索引

回到开头那个问题。上面生效的是 ASCII 关键词。把关键词换成中文，同一张表、同一个索引：

 Aggregate (actual rows=1 loops=1)
   ->  Seq Scan on t_prod (actual rows=3 loops=1)
         Filter: (name ~~ '%阀%'::text)
         Rows Removed by Filter: 120000

索引在，查询没变，退回了全表扫描。

查一下 trigram 到底生成了什么：

SELECT show_trgm('product-widget') AS ascii_trgms;
SELECT show_trgm('银河速通阀')  AS cjk_trgms;

                                ascii_trgms                                
---------------------------------------------------------------------------
 {"  p","  w"," pr"," wi","ct ",dge,duc,"et ",get,idg,odu,pro,rod,uct,wid}

 cjk_trgms 
-----------
 {}

ASCII 字符串生成了一堆三元组（还带前后补空格的两类边界三元组），汉字字符串生成的是空集。

原因在字符分类。pg_trgm 判断「哪些字符构成词」时用的是数据库的 locale 设置。本次实验集群是用 --locale=C 初始化的：

  datname  | datcollate | datctype 
-----------+------------+----------
 labpgfund | C          | C

C locale 下，isalnum() 这类字符分类函数只认 ASCII 字母数字，一个 UTF-8 汉字（三个字节，每个字节都大于 0x7F）被判为「非字母数字」，于是被当作分隔符丢掉，什么都不剩。

影响不止是索引。相似度计算同样归零：

 ascii_sim | cjk_sim 
-----------+---------
 0.8695652 |       0

similarity('product-119999-widget', 'product-119998-widget') 得到 0.87（合理，只差一个字符），而 similarity('银河速通阀', '银河速通阀备件') 得到 0。默认的相似度阈值是 0.3，所以 % 操作符对中文永远不匹配。

这个坑值得记住的原因是它不报错。建索引成功、查询正常返回（只是慢）、结果也正确（因为退回了全表扫描）。唯一的症状是「加了这个索引好像没用」。

要让中文模糊搜索生效，集群必须用 UTF-8 的 locale 初始化（--locale 或 LC_CTYPE 指向一个 UTF-8 locale），而且这件事在 initdb 时就定死了，之后改不了（PG 15 之后可以用 CREATE DATABASE ... LOCALE_PROVIDER 配合 ICU 给单个库指定不同的 collation provider，但那是另一套机制）。

选型结论很实际：如果你的业务要按中文做模糊匹配，在初始化集群之前就要确认 locale。已经上线的集群要补这一步，代价是一次逻辑导出 + 重建 + 导回，或者上 ICU collation。

JSONB 存 vs 拆列存

上一章的遗留问题：同一份数据，用 jsonb 一个列存，和拆成普通列存，差别到底在哪。

造两份 10 万行的相同数据，一份在 doc jsonb 里，一份是四个普通列：

CREATE TABLE t_ord_j(id bigserial PRIMARY KEY, doc jsonb);
CREATE TABLE t_ord_c(id bigserial PRIMARY KEY, user_id int, amount numeric(12,2),
                     status text, created_at timestamptz);

同一个业务查询——按 user_id 过滤、按 status 分组求和。

JSONB 侧，没有针对性的索引：

 GroupAggregate (actual rows=3 loops=1)
   Group Key: ((doc ->> 'status'::text))
   ->  Sort (actual rows=100 loops=1)
         Sort Method: quicksort  Memory: 34kB
         ->  Seq Scan on t_ord_j (actual rows=100 loops=1)
               Filter: (((doc ->> 'user_id'::text))::integer = 42)
               Rows Removed by Filter: 99900

拆列侧，给 user_id 建一个普通索引：

 Sort (actual rows=3 loops=1)
   Sort Method: quicksort  Memory: 25kB
   ->  HashAggregate (actual rows=3 loops=1)
         Group Key: status
         ->  Bitmap Heap Scan on t_ord_c (actual rows=100 loops=1)
               Recheck Cond: (user_id = 42)
               Heap Blocks: exact=100
               ->  Bitmap Index Scan on idx_ord_c_user (actual rows=100 loops=1)
                     Index Cond: (user_id = 42)

拆列侧走了位图索引，Rows Removed by Filter 这个数字消失了（因为不用把 99900 行读出来再丢掉）。JSONB 侧读了全表。

但公平地说，JSONB 也能追平——建一个表达式索引：

CREATE INDEX idx_ord_j_user ON t_ord_j(((doc ->> 'user_id')::int));

 Sort (actual rows=3 loops=1)
   ->  HashAggregate (actual rows=3 loops=1)
         Group Key: (doc ->> 'status'::text)
         ->  Bitmap Heap Scan on t_ord_j (actual rows=100 loops=1)
               Recheck Cond: (((doc ->> 'user_id'::text))::integer = 42)
               Heap Blocks: exact=100
               ->  Bitmap Index Scan on idx_ord_j_user (actual rows=100 loops=1)
                     Index Cond: (((doc ->> 'user_id'::text))::integer = 42)

计划形状和拆列侧一致了。所以性能差距不是不可弥补的，差别在于「要补多少东西」。

补不回来的是另外三样。看一眼拆列表的定义：

   Column   |           Type           | Nullable |               Default               
------------+--------------------------+----------+-------------------------------------
 id         | bigint                   | not null | nextval('t_ord_c_id_seq'::regclass)
 user_id    | integer                  |          | 
 amount     | numeric(12,2)            |          | 
 status     | text                     |          | 
 created_at | timestamp with time zone |          | 
Indexes:
    "t_ord_c_pkey" PRIMARY KEY, btree (id)
    "idx_ord_c_user" btree (user_id)

类型：amount 是 numeric(12,2)，塞不进非数字。JSONB 里它可以是一个字符串、一个嵌套对象、或者根本不存在。
约束：可以给列加 NOT NULL、CHECK、外键。这些对 JSONB 里的键都给不了。
统计信息：优化器知道 user_id 的分布（第 03 章讲的「估计行数」靠的就是它）。JSONB 里的键没有统计信息，优化器只能猜。

再加上一个结构性差别：拆列的 user_id 可以建立外键关联到用户表，可以 JOIN。JSONB 里的值不能。

所以这一节的结论不是「JSONB 慢」，而是：JSONB 的查询性能可以补，数据完整性不能补。当那些键开始需要约束、需要关联、需要被优化器准确估计时，它们就该变成列。

什么时候不该用 PostgreSQL

PG 的扩展生态很宽——pg_trgm 做模糊匹配、pgvector 做向量检索、pg_partman 做分区管理。这些扩展让它能覆盖很多原本属于专用系统的场景。但覆盖不等于最优，判断依据是需求的主要矛盾在哪。

需求特征	PG 能不能做	什么时候该换成专用系统
按任意子串模糊搜索、相关度排序、高亮、聚合分析	能（`pg_trgm`，中文需注意 locale）	文档量大、需要复杂的相关性打分与多维聚合时，换全文搜索引擎
半结构化文档、结构频繁变化	能（JSONB）	结构稳定下来后该拆列；需要跨文档事务时才需要文档库
向量相似检索	能（`pgvector`）	规模过千万、需要高级量化与分布式时，换专用向量库
每秒十万级写入、按时间窗口聚合	能（分区表），但要调	时序特征明显（写多读少、按时间过期、高压缩）时，换时序库
跨多个服务的分布式事务	能（两阶段提交）	大多数情况下应该改用最终一致性 + 幂等，而不是分布式事务
张量/图/全文混合检索	有限	按主要矛盾选一个做主力，另一个做补充

判断的关键是**「主要矛盾」这个词**。PG 的扩展能让你用一个系统解决 80% 的场景，代价是那 20% 的场景永远做不到最好。什么时候值得为那 20% 引入第二个系统，取决于它的业务权重和团队维护成本——引入第二个存储意味着第二套备份、监控、迁移、容量规划。

一个务实的中间态是：先用 PG 的扩展扛住，把「换成专用系统」当成一个明确的、有触发条件的决定。触发条件要具体，比如「模糊搜索 P99 超过 500ms 且 pg_trgm 索引已经占满内存」，而不是「感觉该上搜索引擎了」。

方案选择表

需求	首选	触发换方案的条件
前缀匹配	B-tree 索引	基本不需要换
ASCII 子串匹配	`pg_trgm` GIN 索引	索引体积超过表、写入被索引拖住
中文子串匹配	先确认 locale 是 UTF-8	locale 不能改时，换外部搜索引擎
结构多变的附加属性	JSONB 列	某个键被反复查询、需要约束时提升为列
需要约束与关联的字段	普通列	——
千万级向量检索	`pgvector`	召回率与延迟曲线达不到要求时换专用库

生产边界

本课实验的数据量（12 万行商品、10 万行订单）能反映访问范围的差别，但反映不出数据量导致的计划翻转。生产上真正要小心的是那个翻转点：小表上顺序扫描常常比索引更快（优化器会主动选它），数据量涨上去之后才需要索引——所以「测试环境加了索引没用」和「生产上不加索引就慢」可以是同一份代码的两个阶段。

替换到生产环境时要改的地方：

locale 的确认要前置到建库之前。这是本章最不可逆的一条。已经建好的库要换 locale，只能逻辑导出重建。
CREATE INDEX CONCURRENTLY。trigram 和表达式索引在大表上都要用并发建索引，而且 GIN 索引的并发构建比 B-tree 更慢、更容易失败（失败后留下无效索引要手工清理）。
索引的维护成本。trigram 索引是表的一半大小，表达式索引用一个维度一个索引。上线前要算清楚「这些索引会让写入慢多少」，而不只是「查询快多少」。
pg_trgm 的参数。pg_trgm.similarity_threshold（默认 0.3）和 pg_trgm.word_similarity_threshold 决定 % 操作符的匹配松紧，需要按业务调，不能沿默认值上线。

上线后该盯的指标：pg_stat_user_indexes.idx_scan 为 0 的 GIN 索引（建了没用，白交写入成本）、pg_relation_size 里索引与表的体积比、以及搜索接口的 P99——模糊搜索是那种「加数据就变慢」的查询，它的性能曲线不会是平的。

动手

复现第 06.2 节的三种 LIKE 计划。判断标准：你能说出哪一条的全表扫描是「正确的选择」，哪一条是「缺索引」。
建 pg_trgm 索引，用 ASCII 关键词和中文关键词各查一次。判断标准：ASCII 走 Bitmap Index Scan，中文走 Seq Scan。
在你自己的实例上跑 SELECT show_trgm('测试'), datcollate, datctype FROM ...。判断标准：如果 show_trgm 返回空集，你能说出原因，以及要改变它需要做什么。
复现 06.5 的三次查询：JSONB 无索引、拆列有索引、JSONB 建表达式索引。判断标准：你能量出「JSONB 追平拆列需要额外付出什么」。
拿一张你手上的表，找出一个「在 JSONB 里放了很久、查询一直很重」的键。判断标准：你能说出它该不该提升为列，以及提升之后要付什么代价。

自测

LIKE 'abc%' 能走 B-tree，LIKE '%abc%' 不能。请从索引键的排序方式解释原因。
pg_trgm 索引在 ASCII 上生效、在汉字上失效。请说出这背后的机制，以及为什么这件事在建库之后很难补救。
06.5 里 JSONB 侧建了表达式索引之后，计划形状和拆列侧一致了。那么拆列侧究竟还有什么优势？
pg_trgm 索引的体积可能接近表本身。这个成本在什么负载下最不能接受？
「先用 PG 扩展扛住，触发条件到了再换专用系统」这个策略的风险在哪？什么情况下应该一开始就选专用系统？

↑ 回到课程入口：课程导读

进入 keel 阅读