在普通 B-tree 索引完全失效的情况下，如LIKE '%xxx%'、LIKE '%xxx'、LIKE 'xxx%'、IN ('xxx%','%xxx','%xxx%') 等，可以尝试考虑 GIN + pg_trgm 和 GIN + zhparser 两种索引方式。

实验脚本，包含：

• 一张测试表，数据包含 中文、英文、中英混合。
• 场景分类（前缀匹配、后缀匹配、任意位置模糊、中文分词搜索）。
• 推荐使用的索引类型（pg_trgm 或 zhparser）。
• 每个场景都附带测试 SQL。

测试环境准备

-- schema 设置 SET search_path TO tsearch; -- 测试表 DROP TABLE IF EXISTS opt_jnt_box; CREATE TABLE opt_jnt_box ( TYPEID SERIAL PRIMARY KEY, jnt_box_id BIGINT NOT NULL, jnt_box_no VARCHAR(50), jnt_box_name TEXT , delete_state SMALLINT DEFAULT 0 ); -- 插入混合数据（中文、英文、中英混合） INSERT INTO opt_jnt_box (jnt_box_id, jnt_box_no, jnt_box_name, delete_state) VALUES (1, 'BOX0001', '万惠科技园一期A栋', 0), (2, 'BOX0002', '人工智能大厦B栋', 0), (3, 'BOX0003', 'Blockchain Innovation Center', 0), (4, 'BOX0004', '未来科技城·AI Tower', 0), (5, 'BOX0005', 'Cloud数字中心', 0), (6, 'BOX0006', '深圳市南山区科技南路C栋', 0), (7, 'BOX0007', 'AI人工智能实验室', 0), (8, 'BOX0008', 'International Data Hub', 0), (9, 'BOX0009', '广州市天河区金融大厦', 0), (10,'BOX0010', 'Smart City 智慧城市示范区', 0);

索引准备

-- 1. trigram 模糊索引 CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX idx_trgm_jnt_box_name ON opt_jnt_box USING gin (jnt_box_name gin_trgm_ops); -- 2. 中文分词索引（zhparser） CREATE EXTENSION IF NOT EXISTS zhparser; CREATE TEXT SEARCH CONFIGURATION zhcfg (PARSER = zhparser.zhparser); ALTER TEXT SEARCH CONFIGURATION zhcfg ADD MAPPING FOR n,v,a,i,e,l WITH simple; CREATE INDEX idx_zhparser_jnt_box_name ON opt_jnt_box USING gin(to_tsvector('zhcfg', jnt_box_name));

使用场景对比

• 1、前缀匹配（LIKE 'xxx%'）

特征：用户知道开头一部分，例如输入“万惠”要查“万惠科技园”。

普通索引：B-tree 可以支持 LIKE 'xxx%'，但中文字段里经常失效（特别是带 COLLATE 时）。

推荐索引：pg_trgm 更稳健。

测试 SQL：

EXPLAIN ANALYZE SELECT * FROM opt_jnt_box WHERE jnt_box_name LIKE '万惠%';

• 2、后缀匹配（LIKE '%xxx'）

特征：用户知道结尾，例如搜索“中心”。

普通索引：完全失效。

推荐索引：pg_trgm。

测试 SQL：

EXPLAIN ANALYZE SELECT * FROM opt_jnt_box WHERE jnt_box_name LIKE '%中心';

• 3、任意位置模糊（LIKE '%xxx%'）

特征：用户只知道部分关键词（中文或英文）。

普通索引：失效。

推荐索引：pg_trgm。

测试 SQL：

-- 中文模糊 EXPLAIN ANALYZE SELECT * FROM opt_jnt_box WHERE jnt_box_name LIKE '%人工智能%'; -- 英文模糊 EXPLAIN ANALYZE SELECT * FROM opt_jnt_box WHERE jnt_box_name LIKE '%Data%';

• 4、多关键词匹配（分词搜索）

特征：用户输入多个词，要求都出现（比如“人工智能 & 大厦”）。

普通索引：LIKE 难以表达。

推荐索引：zhparser（中文分词）。

测试 SQL：

-- 查包含 “人工智能” 且包含“大厦”的 EXPLAIN ANALYZE SELECT * FROM opt_jnt_box WHERE to_tsvector('zhcfg', jnt_box_name) @@ to_tsquery('zhcfg', '人工智能 & 大厦');

• 5、OR 查询（多个关键词，任意出现）

特征：用户可能输入多个候选词（如“AI” 或 “人工智能”）。

推荐索引：
o中文：zhparser。
o英文：pg_trgm 也能胜任。

测试 SQL：

-- 中文 OR 搜索 SELECT * FROM opt_jnt_box WHERE to_tsvector('zhcfg', jnt_box_name) @@ to_tsquery('zhcfg', '人工智能 | 智慧城市'); -- 英文 OR 搜索 SELECT * FROM opt_jnt_box WHERE jnt_box_name LIKE '%AI%' OR jnt_box_name LIKE '%Data%';

• 6、中英混合搜索

特征：字段中同时有中英文，用户可能输入混合词。

推荐索引：

中文关键词：用 zhparser。

英文关键词：用 pg_trgm。

混合场景：可以两个索引一起建，根据查询条件走不同索引。

测试 SQL：

-- 中文分词 + 英文单词（AI Tower） SELECT * FROM opt_jnt_box WHERE to_tsvector('zhcfg', jnt_box_name) @@ to_tsquery('zhcfg', '科技城') OR jnt_box_name LIKE '%AI%';

• 7、IN 场景（多个模糊条件）

特征：用户批量匹配，例如 IN ('%AI%','%大厦%','%中心%')。

普通索引：失效。

推荐索引：
o中文：zhparser。
o英文/混合：pg_trgm。

测试 SQL：

-- trigram 支持多模糊条件 SELECT * FROM opt_jnt_box WHERE jnt_box_name LIKE '%AI%' OR jnt_box_name LIKE '%大厦%' OR jnt_box_name LIKE '%中心%';

✅ 总结对比