优化超2000万条数据的MySQL LIKE模式匹配查询

问题描述 #

在MySQL中查询含2000万+记录的大表时，使用LIKE运算符的模糊查询超时，原查询语句如下：

SELECT * FROM my_table WHERE columnName LIKE '%key%';

当前配置 #

• 数据库：MySQL
• 表规模：约2000万条记录
• 搜索模式需求：
  • '%keyword%'（包含匹配）
  • 'keyword%'（开头匹配）
  • '%keyword'（结尾匹配）
• 搜索关键词允许包含：
  • 数字
  • 字母
  • 特殊字符（- 和 _）

已尝试方案 #

1. 实现FULLTEXT搜索：

ALTER TABLE my_table ADD FULLTEXT(columnName);
SELECT * FROM my_table WHERE MATCH(columnName) AGAINST('*keyword*' IN BOOLEAN MODE);

2. 配置ngram解析器：

ngram_token_size=3

遇到的问题 #

• FULLTEXT搜索无法对所有关键词模式返回准确结果
• 常规LIKE查询速度过慢
• 需在提升FULLTEXT搜索性能的同时保证结果准确性
• ngram=3配置会导致部分关键词查询变慢

咨询问题 #

1. 如何调优FULLTEXT搜索以获得类似LIKE '%keyword%'的准确结果？
2. 针对该场景有哪些替代方案或索引策略？
3. ngram解析器的最优配置是什么，可覆盖所有搜索模式？

优化方案 #

1. 调优FULLTEXT搜索实现精准包含匹配 #

• 调整全文索引参数：
  • 针对InnoDB引擎，修改innodb_ft_min_token_size为1（默认3）；MyISAM引擎修改ft_min_word_len为1，确保1-2字符的短关键词能被索引，修改后需重建FULLTEXT索引。
  • 自定义停用词表或禁用停用词过滤，避免关键词被误判为停用词，可通过innodb_ft_server_stopword_table（InnoDB）或ft_stopword_file（MyISAM）配置。
• 优化布尔模式查询语法：
  • 精确短语包含用双引号包裹关键词：MATCH(columnName) AGAINST('"keyword"' IN BOOLEAN MODE)，匹配包含完整短语的记录。
  • 结合ngram解析器时，直接用关键词查询即可实现包含匹配：MATCH(columnName) AGAINST('keyword' IN BOOLEAN MODE)，无需额外加*通配符。
• 处理特殊字符：
  ngram解析器会将-和_包含进token中，避免默认FULLTEXT把它们当成分隔符，确保含特殊字符的关键词能正确匹配。

2. 替代方案与索引策略 #

• 前缀索引（针对开头匹配）：
  创建前缀索引提升keyword%类型查询速度：

  CREATE INDEX idx_col_prefix ON my_table(columnName(20)); -- 20为前缀长度，根据字段平均长度调整
  

  查询时直接使用columnName LIKE 'keyword%'即可走索引。
• 倒序前缀索引（针对结尾匹配）：
  新增倒序生成列并创建前缀索引：

  ALTER TABLE my_table ADD COLUMN columnName_rev VARCHAR(255) GENERATED ALWAYS AS (REVERSE(columnName)) STORED;
  CREATE INDEX idx_col_rev_prefix ON my_table(columnName_rev(20));
  

  查询%keyword时转为匹配倒序列的开头：

  SELECT * FROM my_table WHERE columnName_rev LIKE REVERSE('keyword')'%';
  

• 第三方搜索引擎：
  对于超大规模数据的复杂模糊搜索，使用Elasticsearch等专门的全文检索引擎，同步MySQL数据后可高效支持各种匹配模式，性能远优于原生MySQL。
• 分区表优化：
  按时间、类别等维度将大表拆分为小分区，查询时仅扫描目标分区，降低LIKE查询的扫描范围（对%keyword%仍为分区内全扫）。

3. ngram解析器的最优配置 #

ngram_token_size取值范围为1-10，最优配置取决于关键词长度分布：

• 若需覆盖1-2字符的短关键词，设为1：能匹配任意位置的短词，但索引体积增大，查询性能略有下降。
• 若关键词多为3-5字符，设为3：平衡索引体积和查询性能，覆盖大部分关键词的包含匹配。
• 若需兼顾长短关键词，建议设为1或2：确保所有长度的关键词都能被匹配，代价是索引体积更大。
• 注意：配置后需重建FULLTEXT索引，同时配合innodb_ft_min_token_size=1，确保短token被纳入索引。

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