MySQL子串搜索查询优化：不同关键词执行时长远差问题咨询

问题根源分析 #

你的核心问题在于：LIKE '%关键词%'完全无法利用已创建的FULLTEXT索引，属于全表扫描操作；而LIMIT OFFSET在偏移量较大时，需要先扫描并跳过大量无关行才能定位目标数据。结果集小（2万行）时，符合条件的行分布更分散，MySQL需要扫描更多表数据才能凑够分页所需行数，因此耗时剧增；多词搜索时过滤条件更严格，扫描范围进一步扩大，耗时自然上升。

具体优化方案 #

1. 改用FULLTEXT索引的MATCH AGAINST语法（替代LIKE）

既然已经创建了FULLTEXT索引，必须充分利用它来避免全表扫描：

• 英文场景：使用布尔模式结合通配符（需确保MySQL配置ft_boolean_syntax包含*），支持前缀匹配，示例：

  SELECT * FROM services 
  WHERE MATCH(name) AGAINST('*关键词1* *关键词2*' IN BOOLEAN MODE)
  ORDER BY id LIMIT 10;
  

  注意：FULLTEXT默认不支持中间/后缀子串的精确匹配，若必须完全匹配任意位置的子串，需调整ft_min_word_len（减小最小索引词长度），但会增加索引体积。
• 中文场景：启用MySQL的ngram全文分词插件（MySQL 5.7+支持），配置ngram_token_size=2（根据需求调整），支持中文分词后的子串搜索，示例：

  SELECT * FROM services 
  WHERE MATCH(name) AGAINST('关键词1 关键词2' IN BOOLEAN MODE)
  ORDER BY id LIMIT 10;
  

2. 用游标分页替代OFFSET分页

彻底解决大偏移量的性能问题，基于主键（或唯一有序列）进行分页，每次以上一页的最后一条数据的主键作为条件，示例：

-- 第一页
SELECT * FROM services 
WHERE MATCH(name) AGAINST('关键词' IN BOOLEAN MODE)
ORDER BY id LIMIT 10;

-- 后续页面（假设上一页最后一条id为1000）
SELECT * FROM services 
WHERE MATCH(name) AGAINST('关键词' IN BOOLEAN MODE)
AND id > 1000
ORDER BY id LIMIT 10;

这种方式无需跳过大量行，直接定位到目标数据区间，性能不受偏移量影响。

3. 混合筛选：先用FULLTEXT缩小范围再LIKE

如果业务必须使用LIKE '%关键词%'做精确子串匹配，可以先通过FULLTEXT快速筛选出候选集，再在候选集中用LIKE过滤，大幅减少扫描行数：

SELECT * FROM services 
WHERE MATCH(name) AGAINST('关键词1 关键词2' IN BOOLEAN MODE)
AND name LIKE '%关键词1%' AND name LIKE '%关键词2%'
ORDER BY id LIMIT 10;

4. 表结构与索引优化

• 将name字段从TEXT改为VARCHAR（只要业务允许的长度范围内），VARCHAR类型的索引处理效率通常高于TEXT。
• 若使用ngram全文索引，确保索引仅包含name字段，避免冗余字段增加索引体积。

5. 极端场景：引入第三方搜索引擎

如果以上优化仍无法满足性能要求，建议引入Elasticsearch等专门的全文搜索引擎，它对多词子串搜索、分页的支持远优于MySQL，适合百万级以上数据的全文检索场景。

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