MySQL中带JOIN与IF的查询如何用MATCH替代LIKE提升性能？

你可以将原查询的IF逻辑拆分为两个独立的全文匹配条件，就能正常触发两个表的全文索引，改造后的SQL如下：

SELECT COUNT(*) 
FROM public as p
LEFT JOIN private as pr ON pr.id_public = p.id
WHERE 
  (pr.title IS NOT NULL AND MATCH(pr.title) AGAINST('XYZ'))
  OR 
  (pr.title IS NULL AND MATCH(p.title) AGAINST('XYZ'));

改造说明 #

• 上述条件和你原查询的IF(private.title IS NULL, public.title, private.title) LIKE '%XYZ%'逻辑完全等价，返回结果一致
• MySQL的MATCH()函数只能作用于已创建全文索引的实体列，无法作用于IF这类函数返回的计算值，因此不能直接套在IF表达式外使用。拆分为两个独立的MATCH判断后，两个判断可以分别触发public和private表上你已经建好的idx_title全文索引，性能比原LIKE写法提升非常明显。

补充注意事项 #

1. MySQL全文索引默认有最短匹配长度限制：
   • InnoDB引擎默认最短匹配长度为4，对应参数为innodb_ft_min_token_size
   • MyISAM引擎默认最短匹配长度为4，对应参数为ft_min_word_len
     如果你要搜索的关键词长度小于默认值，需要先修改对应参数，再重建全文索引才能正常返回结果。
2. 如果你需要和原LIKE '%XYZ%'完全一致的任意位置子串匹配效果，默认的自然语言全文模式是按整词匹配，无法满足需求，可以切换为布尔模式并搭配通配符使用：

   -- 注意通配符仅支持放在关键词末尾
   MATCH(title) AGAINST('XYZ*' IN BOOLEAN MODE)
   

   如果你需要匹配关键词出现在开头或者中间的场景，可以使用MySQL的ngram全文解析器创建全文索引，适配子串匹配需求。

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