MySQL 8带索引的中缀搜索提速方案咨询

针对你的InnoDB表中缀搜索慢的问题，推荐使用MySQL 8.0内置的ngram全文索引，无需额外工具，完全满足你的匹配规则（任意位置包含精确序列），步骤如下：

1. 创建ngram全文索引 #

先清理原有的普通索引（可选，节省存储空间），然后给key字段创建带ngram分词器的全文索引：

-- 可选：删除原有的key索引
DROP INDEX key_index ON t;
-- 创建ngram全文索引
ALTER TABLE t ADD FULLTEXT INDEX ft_key (`key`) WITH PARSER ngram;

注：ngram分词器专门针对短字符串/短语匹配优化，适合你的需求，InnoDB原生支持，不需要额外依赖。

2. 执行中缀搜索 #

使用全文搜索的精确短语匹配语法，完全符合你需要的%abc%匹配规则：

SELECT * FROM t WHERE MATCH(`key`) AGAINST('"hello"' IN BOOLEAN MODE);

这里的双引号包裹搜索词，表示匹配包含该精确短语的记录，前后可以有任意字符，性能远优于全表扫描的LIKE "%hello%"，2000万数据可将耗时压缩到秒级甚至毫秒级。

备选方案（若不想用全文索引） #

如果坚持使用普通索引，可通过反转字段+前缀索引覆盖部分中缀场景（仅适合匹配目标字符串出现在开头或结尾的情况）：

• 添加反转字段：

  ALTER TABLE t ADD COLUMN key_rev VARCHAR(200) AS (REVERSE(`key`)) STORED;
  

• 创建前缀索引：

  CREATE INDEX idx_key_prefix ON t(`key`(100));
  CREATE INDEX idx_keyrev_prefix ON t(key_rev(100));
  

• 搜索示例（匹配包含"hello"的记录，仅覆盖开头或结尾的情况）：

  SELECT * FROM t WHERE `key` LIKE 'hello%' OR key_rev LIKE 'olleh%';
  

注：该方案无法覆盖目标字符串在中间的情况，仅作为备选。

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