数据库：高效实现字符串包含查询的方案探讨

针对1亿+数据规模的任意子串包含查询（非前缀匹配），除了ElasticSearch，还有以下几种可行的优化方向：

1. 利用关系型数据库的trigram索引（推荐） #

比如PostgreSQL的pg_trgm扩展，专门针对模糊匹配优化，能让LIKE '%xxx%'这类查询高效利用索引：

• 第一步：启用扩展

  CREATE EXTENSION pg_trgm;
  

• 第二步：创建trigram索引（GIN更适合大数据量）

  CREATE INDEX idx_table_col_trgm ON your_table USING gin(your_column gin_trgm_ops);
  

• 第三步：直接用普通的LIKE查询即可，此时会走索引，1亿级数据的查询速度能提升几个数量级。

MySQL也有类似思路，可通过配置ngram分词器的全文索引支持子串匹配：

• 修改my.cnf配置：

  ft_min_word_len=2
  ft_ngram_token_size=2
  

• 创建全文索引：

  CREATE FULLTEXT INDEX idx_table_col_ngram ON your_table(your_column);
  

• 查询时用MATCH AGAINST（双引号包裹确保精确子串匹配）：

  SELECT * FROM your_table WHERE MATCH(your_column) AGAINST('"xxx"' IN BOOLEAN MODE);
  

2. 预构建n-gram倒排索引（自定义实现） #

如果不想依赖数据库扩展，可以自己维护倒排索引结构：

• 写入数据时，将每个字符串拆分成所有可能的n-gram（比如2或3字符组合，如"abcde"拆成"ab","bc","cd","de"）
• 用独立表或Redis的Hash/ZSet存储每个n-gram对应的记录ID列表
• 查询时，将查询字符串拆成n-gram，取所有n-gram对应ID列表的交集，再对这些ID的原字符串做精确验证（避免误匹配）

这种方案完全自定义，适合特定业务场景，但写入时会有额外性能开销，需权衡读写比例。

3. 轻量型全文搜索工具 #

如果觉得ElasticSearch太重，可选择更轻量的专门搜索工具：

• Meilisearch：部署简单，开箱即用，原生支持模糊匹配和子串搜索，性能优异
• Sphinx Search：专注全文搜索，支持高效模糊匹配，可与关系型数据库联动
• Apache Lucene：ElasticSearch的底层引擎，直接使用可减少ES的额外开销，适合定制化需求

4. 列式数据库优化 #

列式数据库如ClickHouse针对字符串匹配做了特殊优化：

• 支持position()函数快速查找子串，配合列式存储特性，扫描效率远高于传统行式数据库
• 可创建ngram类型辅助索引加速查询：

  CREATE INDEX idx_table_col_ngram ON your_table USING ngram(your_column, 2, 3);
  

• 查询时直接用WHERE position(your_column, 'xxx') > 0即可获得高效结果

为什么普通索引没用？ #

传统B树索引基于前缀匹配设计，无法处理任意位置的子串查询，所以LIKE '%xxx%'或MongoDB的无锚定$regex会触发全表扫描，大数据量下自然极慢。而上面的方案要么是基于字符组的索引（trigram/ngram），要么是专门的搜索索引结构，能快速缩小候选集，避免全表扫描。

内容的提问来源于stack exchange，提问作者Jiew Meng