南大通用GBase 8c典型模糊匹配全文检索场景讲解
在普通 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 更稳健。测试 SQLEXPLAIN ANALYZE SELECT * FROM opt_jnt_box WHERE jnt_box_name LIKE 万惠%;2、后缀匹配LIKE %xxx特征用户知道结尾例如搜索“中心”。普通索引完全失效。推荐索引pg_trgm。测试 SQLEXPLAIN 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 %中心%;✅ 总结对比场景查询模式推荐索引示例前缀匹配LIKE xxx%pg_trgmLIKE 万惠%后缀匹配LIKE %xxxpg_trgmLIKE %中心任意模糊LIKE %xxx%pg_trgmLIKE %人工智能%多关键词 AND中文检索zhparser人工智能 大厦多关键词 OR中文/英文混合zhparser / pg_trgmcode人工智能中英混合中文词英文词zhparser pg_trgm/code科技城 OR %AI%code多模糊 ININ(%xx%,%yy%)pg_trgm / zhparser/code%AI% OR %大厦%
)
