MySQL如何编写高效单查询实现按标题>内容>标签权重排序搜索帖子

现有写法的问题 #

你当前基于UNION的实现存在两个核心缺陷：

• UNION默认会对合并后的结果集做去重排序，产生不必要的额外性能开销
• 合并后的结果没有显式指定排序规则，无法保证「标题匹配>内容匹配>标签匹配」的优先级顺序，不符合业务需求

优化后的实现方案 #

不需要用UNION，单条查询加匹配权重字段即可满足需求，性能远高于原有写法：

REGEXP 版本（适合10万以内小数据量） #

SELECT *,
    CASE 
        WHEN title REGEXP 'first' AND title REGEXP 'second' THEN 3
        WHEN content REGEXP 'first' AND content REGEXP 'second' THEN 2
        WHEN tag REGEXP 'first' AND tag REGEXP 'second' THEN 1
    END AS match_priority
FROM board
WHERE 
    (title REGEXP 'first' AND title REGEXP 'second')
    OR (content REGEXP 'first' AND content REGEXP 'second')
    OR (tag REGEXP 'first' AND tag REGEXP 'second')
ORDER BY match_priority DESC, board_id DESC
LIMIT 1000;

FULLTEXT 版本（适合10万以上大数据量） #

SELECT *,
    CASE 
        WHEN MATCH(title) AGAINST('+"first" +"second"' IN BOOLEAN MODE) THEN 3
        WHEN MATCH(content) AGAINST('+"first" +"second"' IN BOOLEAN MODE) THEN 2
        WHEN MATCH(tag) AGAINST('+"first" +"second"' IN BOOLEAN MODE) THEN 1
    END AS match_priority
FROM board
WHERE 
    MATCH(title) AGAINST('+"first" +"second"' IN BOOLEAN MODE)
    OR MATCH(content) AGAINST('+"first" +"second"' IN BOOLEAN MODE)
    OR MATCH(tag) AGAINST('+"first" +"second"' IN BOOLEAN MODE)
ORDER BY match_priority DESC, board_id DESC
LIMIT 1000;

如果是中文搜索场景，建议把FULLTEXT索引的分词器换成ngram，调整最小分词长度为2，能大幅提升匹配效率和准确率。

REGEXP 比 FULLTEXT 快的原因 #

你实测出现的性能差异是场景特殊导致的，并不是FULLTEXT本身性能差：

1. 数据量太小：当前表只有6万条记录，全表扫描的开销远低于FULLTEXT的检索开销。FULLTEXT需要先查倒排索引、计算相关性、回表取完整数据，多个独立FULLTEXT索引的检索叠加开销在小数据集下反而不如直接全表扫描。
2. 分词配置不适配：你用的是MySQL默认的utf8字符集，内置FULLTEXT分词器默认最小分词长度是4，如果搜索的是中文或者长度小于4的英文关键词，会出现大量匹配遗漏，检索过程也会产生额外开销。
3. 原有UNION写法放大开销：你写的FULLTEXT版本用了3次UNION，要生成3次临时表还要做去重排序，进一步拉大了性能差距。

后续优化建议 #

• 如果数据量长期维持在10万以内，直接用优化后的REGEXP版本即可满足需求
• 如果后续数据量上涨到百万级，调整FULLTEXT分词配置后，FULLTEXT的性能优势会明显超过REGEXP
• 如果搜索需求更复杂（比如高亮、同义词、权重自定义），建议引入Elasticsearch等专业搜索引擎组件

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