全文检索按score降序、id降序排序性能缓慢问题求助

当你添加ORDER BY score DESC, id DESC时，MySQL需要先通过全文索引筛选出匹配记录，再对结果进行二次排序：先按相关性分数降序，再对同分数的记录按id降序排列。如果匹配的结果集较大，这个额外的排序操作（通常对应执行计划里的Using filesort）会占用大量CPU和内存，导致性能骤降。而仅保留全文检索条件时，MySQL可以直接利用全文索引默认的分数降序返回结果，无需额外排序，因此性能表现更好。

1. 先排查执行计划 #

用EXPLAIN确认排序是否是瓶颈：

EXPLAIN
SELECT posts.*, MATCH(heading) AGAINST('+book' IN BOOLEAN MODE) as score 
FROM posts 
WHERE MATCH(heading) AGAINST('+book' IN BOOLEAN MODE) 
ORDER BY score DESC, id DESC;

如果Extra列显示Using filesort，说明排序就是性能问题的核心。

2. 限制结果集（分页场景必用） #

如果是分页查询，添加LIMIT可以大幅减少排序的数据量，性能提升明显：

SELECT posts.*, MATCH(heading) AGAINST('+book' IN BOOLEAN MODE) as score 
FROM posts 
WHERE MATCH(heading) AGAINST('+book' IN BOOLEAN MODE) 
ORDER BY score DESC, id DESC
LIMIT 20; -- 根据业务调整每页条数

3. 子查询优化排序逻辑 #

通过子查询先提取匹配记录的id和score，再关联原表获取完整数据，部分场景下能优化排序效率：

SELECT p.*, s.score
FROM posts p
INNER JOIN (
    SELECT id, MATCH(heading) AGAINST('+book' IN BOOLEAN MODE) AS score
    FROM posts
    WHERE MATCH(heading) AGAINST('+book' IN BOOLEAN MODE)
) s ON p.id = s.id
ORDER BY s.score DESC, s.id DESC;

4. 应用层处理同分数排序 #

如果匹配结果集不大，可以先按score DESC拉取所有记录，在应用层对同分数的记录按id DESC排序，把排序压力转移到应用端，减轻数据库负载。

5. 升级MySQL版本（可选） #

MySQL 8.0及以上版本的优化器对全文检索排序的支持更完善，部分场景下能自动规避不必要的Using filesort，提升排序性能。

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