Google Spanner中基于索引实现高效LIKE查询的方法探讨

一、利用数据库内置全文索引 #

主流关系型数据库都提供了全文索引功能，能高效处理LIKE "%at%"这类中间匹配场景，避免全表扫描：

MySQL 实现 #

给FIRST_NAME创建全文索引后，使用MATCH() AGAINST()语法查询：

-- 创建全文索引
CREATE FULLTEXT INDEX idx_first_name_ft ON your_table(FIRST_NAME);

-- 查询包含"at"的姓名（布尔模式支持精确子串匹配）
SELECT * FROM your_table WHERE MATCH(FIRST_NAME) AGAINST('+at' IN BOOLEAN MODE);

注意：InnoDB默认最小匹配词长是3，若要匹配"at"这类短词，需修改配置innodb_ft_min_token_size并重建索引。

PostgreSQL 实现 #

通过tsvector和tsquery结合GIN/GIST索引实现：

-- 创建GIN全文索引
CREATE INDEX idx_first_name_ft ON your_table USING GIN(to_tsvector('simple', FIRST_NAME));

-- 查询包含"at"的姓名（simple配置跳过词干分析，保留原始子串）
SELECT * FROM your_table WHERE to_tsvector('simple', FIRST_NAME) @@ to_tsquery('simple', 'at');

二、自定义倒排索引（灵活适配场景） #

如果数据库全文索引的限制无法满足需求，可以手动维护倒排索引表：

• 创建name_tokens表，字段为token（存储子串，比如"at"）和user_id（关联主表ID）。
• 插入/更新主表姓名时，拆分出所有可能的连续子串（比如"Matthew"拆为"ma","at","tt","th"等），同步写入name_tokens。
• 查询时先在name_tokens表中匹配token='at'，再关联主表获取用户数据，利用name_tokens的token索引快速定位。

这种方式需要额外维护索引，但灵活性极高，适合对短词、特殊匹配规则有要求的场景。

三、ElasticSearch的适用场景 #

如果存在以下情况，专门的搜索引擎会更合适：

• 需要多字段组合检索、同义词匹配、模糊纠错等复杂需求；
• 数据量达到千万级以上，关系型数据库的全文索引性能瓶颈显现；
• 高并发检索请求，需要分布式集群支撑。

ElasticSearch基于倒排索引原生优化了文本检索，对中间匹配、模糊匹配的性能远超关系型数据库，且自带丰富的分词器和检索功能。

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