带MATCH查询与ORDER BY的SQL语句应选用何种索引？

你的SQL查询语句如下：

SELECT * FROM staffs
WHERE MATCH(staff_name) AGAINST ('johnny')
ORDER BY staff_city ASC

问题分析 #

你已为staff_name创建FULLTEXT索引，WHERE条件的匹配效率达标，但ORDER BY staff_city拖慢整体速度，核心原因是：

• FULLTEXT索引的结果集默认按匹配相关性排序，数据库需要对这些匹配结果执行额外的排序操作（通常是filesort），若匹配结果行数较多，排序的IO和CPU开销会显著增加。
• MySQL优化器无法同时利用FULLTEXT索引和普通B-tree索引优化同一查询的WHERE+ORDER BY逻辑，两种索引的结构与适用场景完全不同。

可行的优化方案 #

1. 优先限制结果集大小（最实用的快速优化）

如果业务允许，给查询添加LIMIT子句，直接减少需要排序的数据量：

SELECT * FROM staffs
WHERE MATCH(staff_name) AGAINST ('johnny')
ORDER BY staff_city ASC
LIMIT 100; -- 根据业务需求调整数值

该方式能直接降低排序开销，效果立竿见影。

2. 为staff_city添加普通B-tree索引

虽然无法与FULLTEXT索引联合使用，但当匹配结果集较小时，数据库执行排序操作时可利用该索引减少开销（提升效果取决于结果集大小）：

CREATE INDEX idx_staff_city ON staffs(staff_city);

3. 重构查询逻辑，分步处理

通过子查询先获取匹配的主键ID，再关联原表并排序，利用主键索引的高效关联特性，减少排序阶段的数据处理量：

SELECT s.* 
FROM staffs s
JOIN (
    SELECT staff_id FROM staffs WHERE MATCH(staff_name) AGAINST('johnny')
) AS matched_staffs ON s.staff_id = matched_staffs.staff_id
ORDER BY s.staff_city ASC;

4. 预计算排序结果（适合数据变更频率低的场景）

如果staff_city和staff_name变更频率较低，可使用物化视图（MySQL 8.0及以上版本支持）预存储已按staff_city排序的FULLTEXT匹配结果，查询时直接从物化视图读取数据，避免实时排序。

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